为什么MySQL 8.0的默认排序规则会导致模糊查询结果不一致？-mysql教程-PHP中文网

mysql 8.0默认collation是utf8mb4_0900_ai_ci，非utf8mb4_general_ci；它基于unicode 9.0标准，精准支持重音、大小写、emoji及中文扩展字符，而旧规则存在排序错误和兼容性风险。

为什么mysql 8.0的默认排序规则会导致模糊查询结果不一致？

MySQL 8.0 默认 collation 是 utf8mb4_0900_ai_ci，不是 utf8mb4_general_ci

这不是“排序规则变了”，而是 MySQL 8.0 彻底切换了默认排序行为：字符比较逻辑从旧版的 utf8mb4_general_ci 升级为更精确、更符合 Unicode 标准的 utf8mb4_0900_ai_ci。这个新 collation 对大小写、重音、变音符号（如 é vs e）、甚至某些中文拼音排序都做了更细粒度的处理。

问题就出在这里：如果你的表或字段仍保留着 utf8mb4_general_ci，而连接或临时变量用了 8.0 默认的 utf8mb4_0900_ai_ci，MySQL 就会在隐式转换时报错 —— 比如 FIND_IN_SET、=、LIKE 或子查询中字符串拼接后参与比较，都会触发 ERROR 1267 (HY000): Illegal mix of collations。

常见错误场景：用 GROUP_CONCAT 拼出字符串再传给 FIND_IN_SET，但拼接结果继承的是连接层 collation，而目标字段是 utf8mb4_general_ci
影响模糊查询：比如 WHERE name LIKE '%张%' 在两种 collation 下对“張”“张”“張”的匹配范围可能不同，utf8mb4_0900_ai_ci 更严格区分字形变体
升级后没改表结构？别指望 SQL 层面加 COLLATE utf8mb4_general_ci 临时兜底——它只对当前表达式生效，关联字段、索引、外键约束全会失效

ALTER TABLE CONVERT TO 不等于简单改 COLLATE

很多人执行 ALTER TABLE t CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci 后发现外键失败或索引损坏，是因为这个语句不仅改 collation，还强制重建所有列、索引、约束，并要求整个表字符集一致。一旦某列被外键引用，而引用端用的是 utf8mb4_0900_ai_ci，就会直接报 #3780 - Referencing column ... are incompatible。

真正安全的做法是统一往新标准靠拢：

优先批量把数据库、表、字段全部改成 utf8mb4_0900_ai_ci，而不是降级回 utf8mb4_general_ci
查外键依赖：用 SELECT CONSTRAINT_NAME, COLUMN_NAME, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA = 'xxx' AND REFERENCED_TABLE_NAME IS NOT NULL;
改字段时必须连带改外键列：比如 user_id 字段和它所引用的 users.id 必须用相同 collation，否则 ALTER TABLE 会拒绝

模糊查询结果不一致，往往卡在连接层 collation

即使表和字段都是 utf8mb4_0900_ai_ci，如果客户端连接没设对，LIKE 和 FIND_IN_SET 依然可能出错。MySQL 会按以下顺序决定操作时的 collation：

MySQL

编写正确的MySQL查询，避免字符集、索引和锁方面的常见陷阱。

下载

字段定义的 collation（最高优先级）
列所在表的默认 collation
数据库默认 collation
连接层的 collation_connection（最低，但影响字符串字面量、变量、函数返回值）

所以你看到“同样 SQL，本地开发环境正常，生产环境报错”，大概率是连接字符串里漏了 charset=utf8mb4&collation=utf8mb4_0900_ai_ci，或者没执行 SET NAMES utf8mb4 COLLATE utf8mb4_0900_ai_ci。

验证方式：SHOW VARIABLES LIKE 'collation%'; 看 collation_connection 是否和表字段一致；再用 SELECT @@collation_database, @@collation_server; 确认全局基准。

ORDER BY + LIKE 组合时，collation 差异会放大排序不确定性

当模糊查询结果本身就不稳定（比如多条记录都匹配 '%李%'），再叠加 collation 不一致，ORDER BY name 的结果就完全不可控——utf8mb4_general_ci 可能把“李”“里”“理”排一起，utf8mb4_0900_ai_ci 则严格按 Unicode 码点分组，导致分页跳变、重复或遗漏。

这不是 bug，是设计使然。解决路径很明确：

不要依赖隐式 collation 推导；显式指定：例如 WHERE name COLLATE utf8mb4_0900_ai_ci LIKE '%李%' ORDER BY name COLLATE utf8mb4_0900_ai_ci
但更推荐一步到位：把字段、连接、配置全统一到 utf8mb4_0900_ai_ci，然后建索引时带上 collation，比如 INDEX idx_name (name COLLATE utf8mb4_0900_ai_ci)
注意：MySQL 8.0 的 utf8mb4_0900_ai_ci 不支持前缀索引的 collation 指定，所以 VARCHAR(255) 字段若只索引前 191 字符，得确保业务能接受截断后的比较行为

最常被忽略的点：应用层连接池（比如 HikariCP、Druid）的初始化 SQL 里没加 SET NAMES utf8mb4 COLLATE utf8mb4_0900_ai_ci，导致每次 getConnection() 后 collation_connection 都是默认值，而这个默认值在不同 MySQL 实例上可能还不一样。