Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.
Original topic: 怎么批量查表的字符集和排序规则?
[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.
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%';
The character set should be for the columns, right? Not for the table…
Yes, but there are only collations in the tables. However, as the expert upstairs mentioned, you can just match them accordingly.
I use Navicat to directly view the table creation SQL, which includes the table’s character set and collation. This method is relatively primitive.
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.
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
.
It’s already resolved, just followed the best answer.
This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.