MySQL — JOIN 或 WHERE 中的 Illegal mix of collations

症状

　　错误直接打印碰撞对：Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_0900_ai_ci,IMPLICIT) for operation ‘=’——这一对正是 MySQL 8.0 升级的标准剧情：新默认 0900_ai_ci 遇上 5.7 时代建的 general_ci 表。更危险的是沉默的孪生兄弟：当两个排序规则“可转换”时，join 照跑但某一侧的索引变得不可用——EXPLAIN 里 eq_ref 变成 range 或全扫描。审计要同时找两种表现：报错的与静默变慢的。

诊断思路

　　先查 information_schema.columns 列出参与比较的两侧列的字符集与排序规则，冲突对一目了然；再查 collation_server 与 collation_connection，新对象的排序规则来源就清楚了；最后 EXPLAIN 确认 join 是否已丢索引——同一疾病的静默变体。重建过的表、新增列继承服务器默认、临时表/视图/生成列取了不同来源，都是常见起源。

根因

　　以不兼容排序规则比较或连接字符串没有可定义的顺序，MySQL 报 ERROR 1267。当排序规则兼容到可转换时，比较可能转而在一侧禁用索引——同一配置错误呈现为报错或静默减速两种形态。

修复与验证

　　统一模式：按表 CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci；对齐连接字面量（SET NAMES … COLLATE …）；转换必须等待时用显式 COLLATE 桥接单条查询（保持有索引的一侧不被转换）；迁移脚本显式声明字符集与排序规则，新表不再继承服务器默认。验证时 information_schema 全 schema 一个排序规则、失败查询成功、EXPLAIN 恢复 eq_ref/ref。注意转换会重写并锁表，且可能改变唯一键语义（大小写/重音不敏感性）——排序顺序与唯一性预期都要在预发验证。

代码示例

ERROR 1267 (HY000): Illegal mix of collations
(utf8mb4_general_ci,IMPLICIT) and (utf8mb4_0900_ai_ci,IMPLICIT)
for operation '='

ALTER TABLE orders
  CONVERT TO CHARACTER SET utf8mb4
  COLLATE utf8mb4_0900_ai_ci;

SET NAMES utf8mb4 COLLATE utf8mb4_0900_ai_ci;

SELECT * FROM a JOIN b
  ON a.k = b.k COLLATE utf8mb4_general_ci;  -- explicit, indexed side kept