MySQL collation-server 配置

collation-server 是与 character-set-server 配对使用的核心参数，定义服务器默认的排序规则 ，直接影响字符串的 比较、排序和分组行为。

一、核心作用

字符串比较规则：决定 'A' = 'a' 是否成立，以及 ORDER BY 的排序顺序
索引顺序：影响索引的构建和查询优化
新建对象的默认值：新建数据库/表/列时，若不指定校对规则，将继承此设置

与 character-set-server 的关系：

每个字符集都有一套对应的校对规则
collation-server 的字符集前缀必须与 character-set-server 一致

二、推荐配置值

首选：`utf8mb4_unicode_ci` （MySQL 5.7 及以下）

[mysqld]
collation-server = utf8mb4_unicode_ci

基于 Unicode 官方排序算法，排序更精确
正确处理各种语言的排序规则（如中文拼音、德语变音符号）

次选：`utf8mb4_0900_ai_ci` （MySQL 8.0+ 默认）

[mysqld]
collation-server = utf8mb4_0900_ai_ci

基于 Unicode 9.0，性能更优
ai = accent insensitive（不区分重音），ci = case insensitive（不区分大小写）

不推荐：`utf8mb4_general_ci`

性能略好，但排序不准确（如某些中文、德语的排序异常）
为兼容性保留，新项目应避免使用

三、配置方法

标准配置（推荐）

必须与 character-set-server 配对设置：

[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci  # 或 utf8mb4_0900_ai_ci

配置文件路径

Linux : /etc/my.cnf 或 /etc/mysql/my.cnf
macOS : /usr/local/etc/my.cnf
Windows : C:\ProgramData\MySQL\MySQL Server X.X\my.ini

修改后必须重启 MySQL ：

sudo systemctl restart mysqld  # Linux
brew services restart mysql    # macOS
net stop mysql && net start mysql  # Windows

四、验证配置

1. 查看服务器校对规则

SHOW VARIABLES LIKE 'collation_server';

期望输出：

+------------------+--------------------+
| Variable_name    | Value              |
+------------------+--------------------+
| collation_server | utf8mb4_unicode_ci |
+------------------+--------------------+

2. 查看完整字符集链路

SHOW VARIABLES LIKE 'collation%';

关键参数说明：

参数名	作用	推荐值
`collation_server`	服务器默认	`utf8mb4_unicode_ci`
`collation_database`	当前数据库默认	应与服务器一致
`collation_connection`	当前连接校对规则	应与服务器一致

五、最佳实践与注意事项

✅ 正确做法

永远配对配置：character-set-server 和 collation-server 必须同时设置
应用层指定：在连接串中明确指定校对规则（可选但推荐）
保持全链路一致：服务器、数据库、表、列、连接五级统一

⚠️ 重要警告

已存在数据不受影响 ：修改配置 不会自动转换已有表的校对规则
校对规则不兼容错误：若 collation-server 与 character-set-server 字符集不匹配，MySQL 将无法启动并报错：
ERROR 1932 (HY000): Collation 'xxx' is not valid for character set 'yyy'
索引长度限制：utf8mb4 下，单个索引键最大长度从 767 字节降至 191 字符（767/4）

六、转换已有数据的校对规则

转换单个表

ALTER TABLE your_table 
CONVERT TO CHARACTER SET utf8mb4 
COLLATE utf8mb4_unicode_ci;

⚠️ 警告：会锁表，大表请在业务低峰期执行

批量转换所有表

-- 生成转换 SQL 语句
SELECT CONCAT(
    'ALTER TABLE `', TABLE_SCHEMA, '`.`', TABLE_NAME, '` ',
    'CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;'
) 
FROM information_schema.TABLES 
WHERE TABLE_SCHEMA = 'your_database';

七、不同版本默认值对比

MySQL 版本	`character-set-server` 默认	`collation-server` 默认
5.7 及以下	`latin1`	`latin1_swedish_ci`
8.0+	`utf8mb4`	`utf8mb4_0900_ai_ci`

结论：即使 MySQL 8.0+ 已有合理默认值，仍建议 显式配置，避免被其他配置文件覆盖，确保环境一致性。

核心记忆点：collation-server 是 character-set-server 的"排序规则搭档"，两者必须 同时配置、字符集匹配、重启生效。