MySQL批量修改所有表字符集与校对规则教程

不能仅凭一条 SQL 语句一次性完成所有表的字符集与校对规则修改，必须逐一生成并执行 ALTER TABLE 指令；否则将遗漏字段级别的 collation 设置，或者引发隐式转换风险——因为 ALTER DATABASE 仅更改数据库的默认字符集，对已存在的表和字段并无实际效果。

核心结论：无法通过一条 SQL 语句一键变更所有表，必须生成并运行 ALTER TABLE 语句；否则会忽略字段级别的 collation，或导致隐式转换错误。

为什么不能直接使用 ALTER DATABASE ... CHARACTER SET？

你可能认为只需修改数据库的默认字符集即可解决问题。但实际上，ALTER DATABASE 仅影响数据库的“默认设置”，对现有表及其字段毫无作用。简而言之，它只对新创建的表生效，存量表的 DEFAULT CHARSET 以及各字段的实际 COLLATION 完全未变。当执行 UNION 或 JOIN 时，依然会报出 Illegal mix of collations 错误，令人费解。

常见报错场景：ERROR 1271 (HY000): Illegal mix of collations for operation 'UNION' —— 原因在于不同表中同名字段（例如 title）分别使用了 utf8mb4_general_ci 和 utf8mb4_unicode_ci，MySQL 直接拒绝执行。

• 数据库级别设置仅影响后续新建的表，无法“传递”给已有的表
• 表的 DEFAULT CHARSET 与各字段的实际 COLLATION 是不同层级，字段设置的优先级更高
• 部分字段在创建时可能显式指定了 collation（如 COLLATE utf8mb4_bin），这类字段会被完全忽略

批量生成 ALTER TABLE CONVERT TO 语句的可靠方法

规范的做法是：先查询出所有需要修改的表，然后拼接出安全的 ALTER TABLE ... CONVERT TO CHARACTER SET ... COLLATE ... 语句。关键要点是必须显式指定 COLLATE，否则 MySQL 会依据服务器的默认值进行选择（通常是 utf8mb4_0900_ai_ci，与旧库不兼容，再次引发错误）。

例如，针对数据库 mydb：

SELECT CONCAT('ALTER TABLE `', table_name, '` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;') AS stmt FROM information_schema.tables WHERE table_schema = 'mydb'   AND table_type = 'BASE TABLE';

执行后会得到一串可直接复制执行的 ALTER TABLE 命令。需要注意以下几个易错点：

• 反引号（`）不能省略，否则表名包含关键字或特殊字符时会出错
• 必须同时指定 CHARACTER SET 和 COLLATE，否则各字段的 collation 可能不统一
• 此语句会重写所有字符类型字段（如 VARCHAR/TEXT 等），但不会影响 INT、DATE 等非字符字段

字段级 collation 不一致时的单独处理方案

假设某表中存在字段 name VARCHAR(50) COLLATE utf8mb4_bin，默认情况下 CONVERT TO 会将其一并改为目标 collation —— 这通常是期望的行为。但如果确实需要保留该特定 collation（例如为了实现大小写敏感的搜索），就不能使用 CONVERT TO，而应通过 MODIFY 单独处理：

ALTER TABLE users MODIFY name VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

不过，采用 MODIFY 时也有讲究：

• MODIFY 必须完整重写字段的全部定义（类型、长度、NULL/NOT NULL 等），否则会丢失原有属性
• 如果字段上有索引，MODIFY 将触发索引重建，对大表需谨慎操作
• 建议先通过 SHOW CREATE TABLE users\G 确认当前定义，再构造准确的 MODIFY 语句

执行前务必核查的三项要点

跳过以下步骤，很可能导致表锁定失败或数据乱码，造成严重损失：

• 确认当前 MySQL 版本是否支持目标字符集 —— utf8mb4 从 5.5.3 版本起全面支持，而 utf8mb4_0900_as_cs 需要 8.0 及以上版本
• 检查表的存储引擎是否为 InnoDB：MyISAM 对 collation 更为敏感，且 CONVERT TO 可能执行失败
• 做好备份！CONVERT TO 属于 DDL 操作，会重建表，此期间写入操作被阻塞；线上环境应安排在业务低峰期执行

真正的难点并非 SQL 语法本身，而是字段 collation 的继承链（server → db → table → column）以及隐式转换逻辑。完成一批表的修改后，建议通过以下语句进行查漏补缺：SELECT table_name, column_name, collation_name FROM information_schema.columns WHERE table_schema = 'mydb' AND data_type IN ('varchar','char','text') AND collation_name != 'utf8mb4_unicode_ci';