How to Batch Query the Character Set and Collation of Tables?

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: 怎么批量查表的字符集和排序规则?

| username: Kongdom

[TiDB Usage Environment] Testing
[TiDB Version] v7.1.0
[Reproduction Path] None
[Encountered Problem: Problem Phenomenon and Impact]
How to batch query the character set and collation of tables? Currently, I see collation in TABLES, but no character set.

| username: tidb菜鸟一只 | Original post link

How about looking at it this way, character sets and collations are actually bound together:

SELECT CCSA.character_set_name AS character_set_name, CCSA.collation_name AS collation_name
FROM information_schema.tables IST
JOIN information_schema.collation_character_set_applicability CCSA ON IST.table_collation = CCSA.collation_name
WHERE IST.table_schema = 'automaildb' AND IST.table_name LIKE 'bi_email_excel_config%';
| username: Miracle | Original post link

The character set should be for the columns, right? Not for the table…

| username: tidb菜鸟一只 | Original post link

Yes, indeed.

| username: Kongdom | Original post link

:call_me_hand: :call_me_hand: :call_me_hand: I see, I thought there was no strong relationship.

| username: Kongdom | Original post link

Yes, but there are only collations in the tables. However, as the expert upstairs mentioned, you can just match them accordingly.

| username: 像风一样的男子 | Original post link

I use Navicat to directly view the table creation SQL, which includes the table’s character set and collation. This method is relatively primitive.

| username: Kongdom | Original post link

Yes, you can look at it this way for a single instance. We have a requirement to batch modify character sets and collation rules, so we want to find a statement that can batch query and then verify.

| username: 像风一样的男子 | Original post link

In MySQL, you can easily write a stored procedure with a for loop to fetch cursor objects. In TiDB, you can only write a shell script to loop and execute SHOW CREATE TABLE user.

| username: Kongdom | Original post link

:yum: It’s already resolved, just followed the best answer.

| username: Kongdom | Original post link

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.