如何修复MySQL数据库字符序不匹配导致的Illegal mix of collations错误

处理 MySQL 中因字符序不匹配导致的「Illegal mix of collations」错误时，首要任务是精确定位。如果定位不清就直接修改 collation，极易改错表、遗漏字段，甚至让新字段继承旧库默认值，反而越改越乱。下面三条 SQL 语句，执行一次就能清晰看清问题根源。

快速定位 MySQL collation 混用的具体位置

执行以下三条查询，迅速摸清当前状况：

• SELECT DEFAULT_COLLATION_NAME FROM information_schema.SCHEMATA WHERE SCHEMA_NAME = 'your_db_name';
• SELECT TABLE_NAME, TABLE_COLLATION FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'your_db_name';
• SELECT COLUMN_NAME, COLLATION_NAME FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = 'your_db_name' AND TABLE_NAME = 'your_table' AND COLLATION_NAME IS NOT NULL;

重点检查结果中是否混合了不同后缀的 collation 规则，例如 utf8mb4_general_ci、utf8mb4_unicode_ci 或 utf8mb4_0900_as_cs。一旦参与 =、IN、JOIN 或 UNION 操作的列使用了不一致的 collation，就很容易触发 1267 错误。

MySQL ALTER TABLE CONVERT TO 操作需谨慎

CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci 看似一步到位，实际上会重写整张表。对于大表，执行期间会锁表、大量消耗 I/O，甚至可能拖垮线上业务。

更稳妥的做法是分层对齐：

• 首先修改数据库级别的默认字符序：ALTER DATABASE your_db_name CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
• 再批量修改表级字符序（仅改表定义，不重写数据）：ALTER TABLE your_table ROW_FORMAT=DYNAMIC; + ALTER TABLE your_table CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;（小表可直接使用，大表建议拆成 MODIFY COLUMN 逐字段修改）
• 最后仅修改存在问题的字段：ALTER TABLE your_table MODIFY COLUMN col_name VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

避免使用 utf8mb4_general_ci，因为 MySQL 8.0 及以上版本已弃用它。utf8mb4_unicode_ci 兼容性更佳；utf8mb4_0900_as_cs 区分大小写，但需要确认业务场景是否依赖此特性。

触发器和视图中必须显式指定 COLLATE

即使表和库的 collation 全部统一，触发器仍可能报错。原因在于触发器执行时采用会话级 @@collation_connection，而非字段本身的 collation。

触发器中所有字符串操作都需要显式添加 COLLATE：

• IF NEW.name COLLATE utf8mb4_unicode_ci = 'admin' COLLATE utf8mb4_unicode_ci THEN
• SET v_name = NEW.name COLLATE utf8mb4_unicode_ci;（变量声明也要带上：DECLARE v_name VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;）
• 函数参数不能遗漏：UPPER(NEW.name COLLATE utf8mb4_unicode_ci)

视图创建失败的另一个常见原因是 SELECT 中混用了常量和字段。字符串字面量（例如 'active'）默认采用连接层的 collation，必须显式转换：CONVERT('active' USING 'utf8mb4') COLLATE utf8mb4_unicode_ci，这样才能通过 CREATE VIEW 校验。

临时绕过 Illegal mix of collations 时慎用 CONVERT

CONVERT(col USING 'utf8mb4') 仅更改字符集，不修改 collation，且返回类型为 CHAR，这可能导致索引失效、类型隐式转换，后续比较依然出错。

真正可靠的临时方案是使用 COLLATE 子句：

• WHERE t1.name COLLATE utf8mb4_unicode_ci = t2.name COLLATE utf8mb4_unicode_ci
• SELECT ... FROM t1 JOIN t2 ON t1.id = t2.ref_id AND t1.code COLLATE utf8mb4_unicode_ci = t2.code COLLATE utf8mb4_unicode_ci

如果必须使用 CONVERT，则务必配合 COLLATE：例如 CONVERT(col USING 'utf8mb4') COLLATE utf8mb4_unicode_ci，同时注意长度截断风险——CONVERT(col AS CHAR(255) CHARACTER SET utf8mb4) COLLATE utf8mb4_unicode_ci 中的 255 必须大于或等于字段的实际最大长度。

最容易忽略的陷阱是连接层的 collation 与客户端字符集不一致，此时即使执行 SET NAMES utf8mb4 也可能无效。正确做法是在连接字符串中添加 ?charset=utf8mb4 或执行 SET collation_connection = 'utf8mb4_unicode_ci';，从根源上避免问题。