如何修改MySQL 8.0的默认排序规则为utf8mb4_0900_ai

mysql 8.0默认排序规则为utf8mb4_0900_ai_ci，但实际环境中常因my.cnf未完整配置client、mysql、mysqld三段，或未重启服务、未同步更新已有库表字段的collation而失效；须逐层检查并统一修改。

如何修改mysql 8.0的默认排序规则为utf8mb4_0900_ai_ci？

MySQL 8.0 默认就是 utf8mb4_0900_ai_ci，但很多实际环境（宝塔、Docker 镜像、Windows MSI 安装包）压根没生效——你看到的 SHOW VARIABLES LIKE 'collation%' 返回的可能是 utf8mb4_general_ci 或 latin1_swedish_ci。这不是“改不改”的问题，而是配置没写全、没重启、或已有对象未同步更新。

my.cnf 里必须同时改三处，缺一不可

只在 [mysqld] 段写 collation-server = utf8mb4_0900_ai_ci 是无效的。客户端连接、命令行工具、服务端默认值必须协同一致：

[mysqld] 段：控制新库/新表默认排序规则，必须包含：
character-set-server = utf8mb4
collation-server = utf8mb4_0900_ai_ci
[client] 段：影响 mysqldump、mysql 命令行等所有客户端工具的默认连接字符集：
default-character-set = utf8mb4
[mysql] 段：专用于 mysql 命令行客户端自身（不加它，连上去也可能还是 latin1）：
default-character-set = utf8mb4

改完必须 systemctl restart mysqld（Linux）或重启服务（Windows），然后验证：
mysql -e "SELECT @@collation_server, @@collation_database;" —— 两个都应返回 utf8mb4_0900_ai_ci。

已有数据库和表不会自动继承新配置

修改配置文件只影响后续新建的库和表。已存在的库、表、字段仍保持旧排序规则，这是最常被忽略的一环。

MySQL

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

下载

查哪些对象还没更新：
SELECT TABLE_SCHEMA, TABLE_NAME, COLLATION_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLLATION_NAME NOT IN ('utf8mb4_0900_ai_ci');
改库（仅影响后续新建的表）：
ALTER DATABASE `your_db` CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci;
改表（关键！用 CONVERT TO，不是 MODIFY）：
ALTER TABLE `table_name` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
如果某列被显式指定过旧规则（比如建表时写了 COLLATE utf8mb4_general_ci），需单独修正：
ALTER TABLE `table_name` MODIFY `column_name` VARCHAR(255) COLLATE utf8mb4_0900_ai_ci;（注意保留原类型、长度、NOT NULL 等属性）

导入旧版 SQL 时遇到 Unknown collation 错误

从 MySQL 5.7 或更早版本导出的 mysqldump 文件，里面硬编码了 utf8mb4_unicode_520_ci、utf8mb4_general_ci 等 8.0 不识别的排序规则，导入会卡在 Unknown collation 错误。

必须在导入前做文本替换，不能靠配置修复
Linux/macOS 用：
sed -i 's/utf8mb4_unicode_520_ci/utf8mb4_0900_ai_ci/g' dump.sql
sed -i 's/utf8mb4_general_ci/utf8mb4_0900_ai_ci/g' dump.sql
重点检查两处：CREATE DATABASE ... COLLATE 和每个 CREATE TABLE 末尾的 COLLATE 子句
Windows 用户可用 Notepad++ 的“全部替换”，搜索范围选“当前文档”

改完后查询仍报 Illegal mix of collations

即使所有层级都设对了，运行时仍可能触发 ERROR 1267 (HY000): Illegal mix of collations，典型场景是 FIND_IN_SET、GROUP_CONCAT 或变量拼接中隐式混用了不同排序规则的字符串。

根本原因：MySQL 对字符串字面量、用户变量、函数返回值的隐式排序规则推断逻辑很脆弱
临时绕过（不推荐长期用）：
SELECT * FROM t WHERE FIND_IN_SET(col, @var COLLATE utf8mb4_0900_ai_ci);
更稳妥做法：确保所有参与比较的字段、变量、字面量都显式声明相同 COLLATE，或统一用 CONVERT(@var USING utf8mb4) 转码
别忘了索引：排序规则变更后，原有索引的 Collation 列可能变成 NULL 或 utf8mb4_general_ci，需手动重建：
ALTER TABLE t DROP INDEX idx_col, ADD INDEX idx_col(col);