MySQL 修改数据库字符集 - 虾米的个人笔记

Unicode 与 UTF-8#

Unicode 只是一个符号集，它只规定了符号的二进制代码，却没有规定这个二进制代码应该如何存储。

UTF-8 就是在互联网上使用最广的一种 Unicode 的实现方式（其他实现方式还有 UTF-16, UTF-32）。

MySQL 字符集 collation#

collation定义了哪个字符和哪个字符是“等价”的。所以如果指定“不区分大小写”，那么a和A，e和E就是等价的，这样查找时就会方便很多。但这还不够，世界上的文字很多，所以才会有“不区分音调”的要求，这时候e、ē、é、ě、è就是等价的，那么假设我们要进行拼音查找，只要按e去找就可以全部列出来，很方便。甚至，它们也和ê、ë也是等价的，这样就更方便了。

collation也定义了字符的排序规则，如果按照“字符顺序（而不是简单的‘字母顺序’）”来排序，哪个字符应当排在哪个字符前面。所以，尽管“啊”、“副”、“德”三个字的拼音开头分别为A、F、D，但直接选定collation为utf8mb4，它们并不会按照“啊”、“德”、“副”的顺序排序，而是会排成“副”、“啊”、“德”。如果你希望把中文字符按照拼音（英文字母）来排序，指定使用gb18030_chinese_ci作为collation就可以了。

团队初期不注意 mysql 字符集和字符集 **collation **问题，导致部分业务场景出现不可预料的问题，可以用以下脚本，自动生成修改语句，然后进行更新。

SQL 脚本#

修改 schema 的默认字符集

1
-- 通过 infomation_schema 自动批量生成修改 schema 默认字符集和collation语句
2
-- ALTER DATABASE `table_schema_name` CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
3
SELECT
4
  SCHEMA_NAME,
5
  concat( 'ALTER DATABASE `', SCHEMA_NAME, '` CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;' )
6
FROM
7
  `information_schema`.`SCHEMATA`
8
WHERE
9
  DEFAULT_COLLATION_NAME != 'utf8mb4_general_ci'

转换表的字符集

1
-- 通过 infomation_schema 自动批量生成修改表字符集和collation语句
2
-- ALTER TABLE `table_name` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
3
SELECT
4
  TABLE_SCHEMA,
5
  concat( 'ALTER TABLE `', TABLE_SCHEMA, '`.`', TABLE_NAME, '` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci, ALGORITHM = COPY;' )
6
FROM
7
  `information_schema`.`TABLES`
8
WHERE
9
  TABLE_COLLATION != 'utf8mb4_general_ci'
10
  AND TABLE_SCHEMA = 'table_schema_name'
11
ORDER BY
12
  TABLE_SCHEMA

Unicode 与 UTF-8#

MySQL 字符集 collation#

SQL 脚本#

📎 参考文章#

文章分享