如何批量修改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 两种校对规则。

• 数据库级设置只影响后续新建的表，不“传染”给存量表
• 表级 DEFAULT CHARSET 和字段实际 COLLATION 是两回事，后者优先级更高
• 某些字段可能被显式指定过 collation（比如建表时写了 COLLATE utf8mb4_bin），会被忽略

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

核心是查出所有需要修改的表，再拼出安全的 ALTER TABLE ... CONVERT TO CHARACTER SET ... COLLATE ... 语句。关键点：必须指定 COLLATE，否则 MySQL 会按 server 默认选（通常是 utf8mb4_0900_ai_ci，和旧库不兼容）。

示例（针对数据库 mydb）：

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

执行结果是一堆可复制粘贴的 ALTER TABLE 语句。注意：

• 不要省略反引号（`），防止表名含关键字或特殊字符时报错
• 必须同时指定 CHARACTER SET 和 COLLATE，否则字段 collation 可能不统一
• 该语句会重写所有字符型字段（VARCHAR/TEXT 等），但不会动 INT、DATE 等非字符字段

字段级 collation 不一致时要单独处理

如果某张表里部分字段用了不同 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 必须重写完整字段定义（类型、长度、NULL/NOT NULL 等），否则会丢属性
• 如果字段有索引，MODIFY 会重建索引，大表慎用
• 建议先用 SHOW CREATE TABLE usersG 确认当前定义，再构造 MODIFY 语句

执行前必须检查的三件事

跳过这步，很可能锁表失败或数据乱码：

• 确认当前 MySQL 版本支持目标字符集（utf8mb4 在 5.5.3+ 全面支持，utf8mb4_0900_as_cs 需要 8.0+
• 检查表引擎是否为 InnoDB：MyISAM 对 collation 更敏感，且 CONVERT TO 可能失败
• 备份！CONVERT TO 是 DDL 操作，会重建表，期间写入阻塞；线上环境务必在低峰期执行

真正麻烦的不是语法，而是字段 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';