MySQL 排序规则 collation 怎么理解？查询中文数据时需要注意什么

MySQL中的排序规则（Collation）经常被误认为只是决定 ORDER BY 的排列顺序。实际上，Collation参与的是字符串比较规则，因此它不仅影响排序，也会影响 =、LIKE、GROUP BY、DISTINCT 以及唯一索引等行为。对于保存中文、英文、数字和多语言内容的新闻网站、SaaS系统以及用户数据库来说，理解字符集和排序规则之间的关系，比简单记住一个 utf8mb4 配置更加重要。

首先要把两个概念分开。字符集（Character Set）解决的是“字符如何编码和存储”，Collation解决的是“字符串如何比较”。例如 utf8mb4 是字符集，可以使用一到四个字节表示Unicode字符；utf8mb4_0900_ai_ci 则是一套比较规则。MySQL官方文档目前明确推荐优先使用 utf8mb4，而MySQL 8.0及后续版本的 utf8mb4 默认排序规则已经不是过去常见的 utf8mb4_general_ci，而是 utf8mb4_0900_ai_ci。因此，如果服务器已经升级到MySQL 8.x，不能继续按照早期教程所说的“默认使用utf8mb4_unicode_ci”来理解。

Collation名称本身也包含很多信息。例如 utf8mb4_0900_ai_ci 中的 0900 表示它采用Unicode Collation Algorithm 9.0.0相关权重，ai表示accent-insensitive，也就是通常意义上的不区分重音，ci表示case-insensitive，也就是不区分大小写。与之对应的 utf8mb4_0900_as_cs 则具有accent-sensitive和case-sensitive特性。对于英文、欧洲语言等数据，这些区别会直接影响字符串比较结果。

这里有一个非常重要的误区：Collation并不等于“中文拼音排序规则”。使用 utf8mb4_unicode_ci 或 utf8mb4_0900_ai_ci，并不能简单理解为MySQL会按照现代汉语拼音把所有中文姓名、新闻标题或者地名排好。Unicode Collation Algorithm首先解决的是字符比较和排序权重问题，而某一种语言真正的词典排序规则还可能需要专门的语言规则。MySQL官方也特别提醒，能够区分Unicode字符与按照某一种CJK语言习惯排序是两个不同的问题。

这意味着，如果新闻网站执行 ORDER BY title，开发者不能先入为主地认为结果一定就是我们习惯的中文词典顺序。如果业务明确要求“按照拼音排序”“按照笔画排序”或者按照某种中文词典规则排列，通常需要设计专门的排序键，而不是单纯更换一个通用Collation。实际系统中可以额外保存拼音、排序权重或者其他规范化字段，再针对该字段建立索引。

另一个常见错误，是把 utf8mb4_unicode_ci 和 utf8mb4_general_ci 简单理解为“一个准确、一个高速”。两者确实存在比较规则上的差异，但MySQL 8.x已经提供了基于更高版本Unicode Collation Algorithm的 0900 系列Collation。utf8mb4_general_ci属于较早的legacy collation，不能把它当成现代MySQL项目默认的性能优化方案。官方文档指出，general_ci的比较实现较简单，而unicode_ci支持更多Unicode比较规则；同时，较新的UCA 9.0.0 Collation在性能和Unicode处理方面又有进一步改进。

中文查询时真正容易出现的问题，往往不是“中文字符排序错误”，而是字符集、连接字符集和字段Collation没有形成一致的体系。例如数据库使用 utf8mb4，但应用连接仍然使用其他字符集，数据在客户端和MySQL服务器之间传输时就可能发生乱码或者字符转换问题。MySQL官方也明确指出，字符集问题不仅存在于数据存储阶段，还存在于客户端与服务器通信阶段。

因此PHP应用连接MySQL时，应该明确设置连接字符集。例如MySQLi可以使用：

mysqli_set_charset($conn, 'utf8mb4');

PDO则应该在连接配置中明确使用 charset=utf8mb4。这里的重点是“连接字符集”，而不仅仅是数据库建表时写了一句 DEFAULT CHARSET=utf8mb4。数据库存储正确，并不意味着应用连接层一定正确。

对于新项目，通常可以从数据库层统一使用 utf8mb4，再根据实际业务决定默认Collation。例如MySQL 8.x可以使用：

CREATE DATABASE mydb
DEFAULT CHARACTER SET utf8mb4
DEFAULT COLLATE utf8mb4_0900_ai_ci;

表和列如果没有单独指定，就会继承相应的默认设置。MySQL的字符集和Collation实际上存在服务器、数据库、表、列以及字符串表达式等多个层级，因此同一个数据库里面不同字段完全可能使用不同的Collation。

这也解释了为什么有些SQL会突然出现“Illegal mix of collations”之类的问题。例如一个字段使用某种 utf8mb4 Collation，另一个字段或者查询表达式使用另一种Collation，在比较、连接或其他字符串操作时，MySQL需要确定应该按照哪一种规则进行比较。如果规则之间无法按照预期转换，就可能出现错误或者产生与开发者预期不同的结果。

COLLATE可以在查询中临时指定比较规则。例如：

SELECT *
FROM users
WHERE name COLLATE utf8mb4_0900_as_cs = 'ABC';

但是不要把在每一条SQL里临时指定 COLLATE 当成数据库设计方案。频繁改变表达式的字符集和Collation会增加查询复杂度，而且某些对列进行函数或表达式处理的写法可能影响优化器使用索引的方式。更合理的办法是首先确定字段本身应该采用什么比较规则，再让索引和查询尽量保持一致。

全角和半角问题也不能简单归结为“某个Collation一定把它们当成相同字符”。例如 ABC、ＡＢＣ、不同Unicode表示形式以及各种标点符号之间的比较结果，需要根据具体Collation进行验证。开发者不应该凭名字猜测结果，而应该使用实际数据测试。MySQL提供的 SHOW COLLATION 和 INFORMATION_SCHEMA.COLLATIONS可以查看可用的Collation及其属性，WEIGHT_STRING()还可以用于观察字符串比较所使用的排序权重。

中文姓名和新闻标题尤其需要注意这一点。假设用户搜索“张三”，不能简单假定所有看起来相似的汉字都会被Collation自动归一化。字符相等、兼容字符、全角半角、繁简体、异体字以及Unicode规范化属于不同层面的问题。Collation负责的是字符串比较规则，并不是一个万能的中文文本标准化引擎。

对于需要严格区分字符的字段，例如账号标识、内部编码、某些Token或者具有严格字节语义的值，可以考虑使用合适的binary Collation。utf8mb4_bin和utf8mb4_0900_bin都属于二进制排序规则，但两者在padding和排序权重实现方面并不完全相同。MySQL 8.x还提供了 utf8mb4_0900_bin。因此不能简单认为所有带 _bin 的Collation完全等价。

Collation与索引也存在直接关系。对于一个字符串列，如果索引建立在某种Collation下，那么查询条件的字符集和比较规则最好与索引设计保持一致。真正影响索引使用的并不是“中文字段天然不能走索引”，而是查询表达式、隐式字符集转换、函数处理、前缀匹配方式以及数据分布等因素共同决定优化器最终采用什么执行计划。

例如：

CREATE INDEX idx_users_name
ON users(name);

如果 name 本身采用合适的 utf8mb4 字符集和Collation，那么普通的等值查询：

SELECT *
FROM users
WHERE name = '张三';

完全可以使用索引。中文本身并不会自动导致全表扫描。

同样，LIKE '张%'与LIKE '%张%'也不能混为一谈。前者在合适的索引和查询条件下可能利用索引范围，后者由于通配符位于开头，通常无法采用普通B-tree索引完成同样的高效前缀定位。这个问题主要是索引访问方式，而不是“中文Collation导致索引失效”。

新闻网站尤其应该避免把Collation当成全文搜索系统。假设一个新闻数据库有几十万甚至几百万篇文章，用户搜索“加拿大房价”“人工智能”“川普”等关键词，仅靠 LIKE '%关键词%' 并不能构成理想的全文搜索方案。此时应该根据规模和搜索要求考虑MySQL FULLTEXT、专门搜索引擎或者其他检索架构。Collation负责字符比较，全文搜索负责文本检索，两者是不同层次的问题。

还有一个经常被忽略的细节是字符串尾部空格。不同Collation具有不同的pad属性。MySQL 8.x的UCA 9.0.0系列Collation采用 NO PAD，而较早的一些Collation使用 PAD SPACE。这意味着某些字符串在进行比较时，对尾部空格的处理可能不同。对于需要严格字符串唯一性的字段，这种差异值得在设计阶段进行测试。

至于过去经常看到的 utf8，现代MySQL项目最好不要继续把它当成真正的UTF-8完整实现来使用。MySQL中的 utf8实际上是已经被弃用的 utf8mb3别名，只支持最多三个字节的UTF-8字符，而utf8mb4能够覆盖一到四字节的Unicode字符。包括部分表情符号以及Unicode补充平面字符在内的数据，需要使用utf8mb4才能完整处理。MySQL官方目前明确建议使用utf8mb4。

检查一个现有数据库时，可以从实际结构开始，而不是凭配置文件猜测：

SHOW CREATE DATABASE mydb;

SHOW CREATE TABLE users;

SHOW FULL COLUMNS FROM users;

SHOW COLLATION WHERE Charset = 'utf8mb4';

如果发现数据库、表和字段使用了不同的字符集或Collation，也不要急着执行一次全库转换。ALTER TABLE ... CONVERT TO CHARACTER SET可能涉及大量数据重写、索引重建以及锁和磁盘空间问题，而且MySQL官方明确提醒，字符集转换如果存在不兼容映射，可能造成数据损失。

对于已经运行多年的生产系统，正确的迁移方法应该是先盘点数据库、表、字段、连接字符集以及应用代码，再确定目标字符集和Collation，然后进行小范围测试，检查中文、英文、繁体中文、表情符号、特殊符号、唯一索引和搜索结果，最后再安排生产环境迁移。尤其是新闻网站、论坛、SaaS和电商系统，历史数据往往比新建数据库复杂得多。

MySQL排序规则真正值得理解的地方，是它并不只是“中文怎么排序”的设置。字符集决定数据如何编码，Collation决定字符串如何比较，索引决定数据库如何快速定位数据，而应用连接字符集决定客户端与服务器之间如何正确传输这些字符。四个环节任何一个没有统一设计，都可能表现成乱码、查询不命中、唯一索引冲突、排序异常或者性能问题。

对于新的MySQL 8.x项目，一个比较稳妥的基础是统一使用utf8mb4，不要继续采用已经过时的utf8mb3体系，然后根据业务需求选择合适的Collation。需要注意的是，中文排序如果有明确的拼音、笔画、繁简转换或者词典顺序要求，应该单独设计排序逻辑，而不是期待通用Unicode Collation替你完成所有中文语言学规则。数据库的Collation是一套比较规则，不是一套中文搜索引擎。理解这一点，很多所谓的“中文查询问题”其实就已经解决了一半。