说实话这个问题我一开始是有点懵的。那天下班前同事跑过来问了一句“咱们新库的排序规则用的 utf8mb4_unicode_ci这到底该咋选我看工具默认生成的还有 general_ci 和 0900_ai_ci这东西有啥区别”我第一反应是平时建库建表不都是工具自动生成吗谁会认真看那一长串COLLATE啊。可被他这么一问我发现这个看似不起眼的参数其实决定了数据库里大小写敏不敏感、查询能不能走索引、甚至两个表 join 起来会不会直接报错。而且真要给一套“为什么这么选”的说法我确实差点接不上来。后来我专门把 MySQL 排序规则collation这套东西翻了个底朝天才理清楚里面的门道。这篇文章就当是一次复盘总结写给那些天天和数据库打交道、却很少深究COLLATE的同学希望能帮你少踩几个坑。内容偏实战会先讲清楚原理再给选型建议最后把线上真会遇到的坑一个个拆开。哪怕你现在只用默认配置看完之后至少再被同事问到能头头是道聊上十分钟。1. 排序规则到底是什么从“储存”到“比较”的那一步1.1 字符集决定“装什么”排序规则决定“怎么比”很多人会把字符集charset和排序规则collation混在一起其实它们根本不在一个层面。字符集回答的问题是“这个字段能存哪些字符、用多少字节存”比如 utf8mb4 表示能存下完整的 Unicode 字符包括 emoji 和生僻字。而排序规则回答的是“这些字符谁大谁小、怎么排、怎么算相等”。生活里最贴近的类比是字典。字符集相当于字典收了多少字、每页印什么字形排序规则则是“第一种读音排序法”还是“按笔画排序法”。同一个“发”字按拼音排和按部首排它在整本字典里的位置完全不同。数据库也一样同一列数据用utf8mb4_unicode_ci和utf8mb4_bin去ORDER BY出来的顺序可能大不一样。MySQL 里一个字符集可以对应多种排序规则。你可以执行SHOW CHARACTER SET注意看每行后面的 Default collation 列那只是这个字符集的默认值不代表强制值。1.2 排序规则命名的规律看后缀就能猜出八成虽然不同的库版本会有各种各样的 collation但命名基本有套路。拿最典型的utf8mb4_general_ci拆开看utf8mb4对应的字符集general排列算法或适用范围cicase insensitive大小写不敏感。同理你还会看到_bin按字节二进制比较最严格区分大小写_cscase sensitive区分大小写在 MySQL 里存在但默认用得少_aiaccent insensitive不区分重音_asaccent sensitive区分重音。MySQL 8.0 之后默认排序规则变成了utf8mb4_0900_ai_ci从名字就能读出两层意思aiaccent insensitivecicase insensitive。这个规则并不是“随便改的默认值”而是 MySQL 跟随 Unicode 排序算法更新后顺手把 defaults 往前推了一大步。版本差异、默认值变化恰恰是很多人迁移升级后突然发现排序结果变了、查询结果不对的核心原因。1.3 为什么平时不炸一炸就是事故排序规则平时极少被感知到因为如果你从头到尾都用默认值所有表、所有连接、所有字段都一致数据库就不会抱怨。它真正出现问题通常是下面几种情况项目从一个库迁移到另一个库时源库和目标库的默认字符集/排序规则不一致手工建表时有些字段指定了COLLATE binary之类特殊规则有些没有两个表明明字符集相同但 collation 不同做 join 时直接报Illegal mix of collations。我在实际排查里见过最经典的一次两个订单表字符集都是 utf8mb4可是一个用了utf8mb4_general_ci另一个被 DBA 顺手改成了utf8mb4_unicode_ci然后两表关联做统计报表SQL 一跑就报错。报错信息并不提示“哪个字段需要改正”只会给一个笼统的 collation 混用提示。刚接触的人往往会卡很久其实解决方式非常简单——统一成一种排序规则或者在某次比较里显式指定COLLATE后面我会专门讲。2. 常见排序规则对比utf8mb4 家族里的三足鼎立2.1 utf8mb4_general_ci快但有硬伤utf8mb4_general_ci是 MySQL 5.x 时代非常常见的默认选择。它的比较算法比较“野路子”本质上是先把字符映射到一组权重再做对比好处是速度快、实现简单坏处是不符合 Unicode 官方推荐的排序规则。对纯英文、纯数字场景它几乎没什么感知差异。但对德语、法语这类带重音字符的语言它会直接不区分重音导致café和cafe被当成同一个值在一些欧洲语言的词典顺序上general_ci的排序结果也不够准确。中文场景下它的汉字排序基本等于按 Unicode 码位硬排看不出任何拼音或部首规律。如果你只是做中英文系统的普通数据存储它够用但如果你要做国际化的多语言系统它就不太靠谱了。2.2 utf8mb4_unicode_ci用“官方标准”换更好的语义utf8mb4_unicode_ci基于 Unicode Collation AlgorithmUCA实现规则更接近各语言官方排序习惯。它的好处不是肉眼可见的快而是“正确”。这里有个细节容易误导人在 MySQL 5.7 及更早版本里unicode_ci在性能上是比general_ci慢一点的因为要做更复杂的多级权重比较。很多 DBA 基于这个原因会主动选general_ci。但实际上在现代服务器和普通业务量下这二者的性能差异微乎其微除非你是在做每秒十万级纯比较运算的极端场景。相比之下因为排序规则太粗糙导致数据语义出错、甚至需要重建表来修正代价大得多。所以我的个人倾向很明确与其省那点比较时间不如从一开始选更标准、语义更准确的规则。2.3 utf8mb4_0900_ai_ciMySQL 8.0 的默认新王到了 MySQL 8.0官方把默认字符集直接改成了 utf8mb4排序规则默认值也变成了utf8mb4_0900_ai_ci。注意这个0900不是版本号而是引用的 Unicode Collation Algorithm 版本对应 Unicode 9.0 的权重表。相比老前辈它有几个明显优势支持更多现代 Unicode 字符和特殊符号的合理排序对音乐符号、表情符号、生僻符号的权重处理更规范实现层面做了优化相当一部分场景下它甚至比unicode_ci更快。但这里要敲个重点千万不要以为升级到 MySQL 8.0 就可以“零成本”升级排序规则。你已有的表如果是在 5.7 建的用的很可能还是utf8mb4_unicode_ci或者utf8mb4_general_ci。即使数据库版本升级了存量表结构不会自动跟着变查询行为也不会变。你只有主动执行ALTER TABLE ... CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci才算是真正切到新默认。2.4 utf8mb4_bin当你要“绝对精确”的时候如果你做过用户登录、优惠券核销、密钥校验之类功能大概率遇到过大小写敏感问题。存用户名时用了username字段查询时WHERE username Admin居然能查到admin那条记录看起来是“很智能”但对某些业务来说这就是事故。该怎么解决把字段的排序规则改成utf8mb4_bin它按 UTF-8 编码后的字节序列直接二进制比较区分大小写也区分重音。代价是什么排序会比较快但不具备任何“字符语义”所以别指望它对中文、英文的大小写折叠做智能化处理。在做字段选型前你只需要记住凡是要求精确匹配、严格区分的字段优先用_bin凡是要做展示排序、模糊搜索、语义对比的字段优先用_ai_ci或_unicode_ci。为了看得更清楚我整理了一张速查表排序规则大小写重音排序语义适用场景utf8mb4_general_ci不敏感不敏感简单粗暴不够标准老系统、纯英文/中文存储utf8mb4_unicode_ci不敏感不敏感Unicode 官方规则MySQL 5.7 多语言系统utf8mb4_0900_ai_ci不敏感不敏感Unicode 9.0 新规则MySQL 8.0 新项目首选utf8mb4_bin敏感敏感二进制比对精确匹配场景3. 实战选型与迁移从建库到改表的一次说清3.1 新项目建库把“默认值”变成“显式值”新建项目时很多开发同学的做法是直接执行CREATE DATABASE test DEFAULT CHARACTER SET utf8mb4;后边不写排序规则让服务器默认值决定一切。这样不是不行但隐患是如果你接手的不同服务器 MySQL 版本不同默认排序规则可能是latin1_swedish_ci、utf8mb4_general_ci或者utf8mb4_0900_ai_ci同一套建表脚本在不同环境里跑出来的排序行为不一致。所以我的习惯是从最开始就把排序规则写死。比如CREATE DATABASE app_db DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;建表语句也同理关键字段能显式指定就显式指定CREATE TABLE users ( id BIGINT PRIMARY KEY, username VARCHAR(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL, nickname VARCHAR(128) NOT NULL, email VARCHAR(255) NOT NULL ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_0900_ai_ci;如果目标环境是 MySQL 5.7推荐把0900_ai_ci换成utf8mb4_unicode_ci因为 5.7 不认识前者。如果拿不准服务器版本可以先执行SELECT VERSION();看一眼再决定默认值。3.2 存量表改排序规则别在生产库上直接 ALTER存量项目要改排序规则场景一般有两种一是原来的 latin1/gbk 导致中文乱码或 emoji 存不进去想整体迁移到 utf8mb4二是两个表的 collation 不一致导致查询报错需要统一排序规则。第一种情况最直接的语句是ALTER TABLE order_info CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;这条语句会做什么呢它会修改表默认字符集同时把所有字符串类型字段的字符集和排序规则一并改成目标值并重写整张表的数据。如果表里有几千万行你要做好心理准备这个过程会锁表、会产生大量 IO很可能会拖垮线上业务。所以在生产环境里我不会直接一把梭。比较稳妥的做法是先在一台从库或者测试库上执行同样的变更观察耗时选择流量低峰期操作提前备份mysqldump或物理备份至少来一个如果表数据量太大优先用在线变更工具拆分批次重建表改完之后立刻执行SHOW CREATE TABLE和几条抽样查询验证数据是否完好。如果是第二种情况仅仅因为两个表的 collation 不一致而报错其实不一定要改表。可以在查询时临时指定SELECT a.name, b.nickname FROM user_profile a JOIN order_log b ON a.uid b.uid WHERE a.name b.nickname COLLATE utf8mb4_unicode_ci;或者用CONVERT()函数转换某一侧SELECT a.name FROM user_profile a JOIN order_log b ON a.uid b.uid WHERE a.name CONVERT(b.nickname USING utf8mb4);前者适合比较少的临时查询后者适合固定业务逻辑但又不方便改表结构的场景。能少动就少动是生产环境的第一原则。3.3 连接参数也要对齐别让客户端拖后腿很多时候你以为自己已经把库表都设成 utf8mb4 了可程序连上来之后写入的数据还是乱码或者查询结果出现“问号”。这里十有八九是连接层的字符集没对齐。连接层常见设置是character_set_client utf8mb4 character_set_connection utf8mb4 character_set_results utf8mb4MySQL 客户端连接时这些参数可以由连接字符串或初始化命令指定。如果你用连接池建议在初始化 SQL 里执行SET NAMES utf8mb4 COLLATE utf8mb4_0900_ai_ci;这里有个容易被忽略的点SET NAMES utf8mb4只设置字符集并不设置排序规则。排序规则会跟随该字符集的默认值。如果你希望连接层显式使用某个 collation就不要漏掉COLLATE部分。另外character_set_server和collation_server是服务端全局默认character_set_database和collation_database是当前库的默认二者可能不一样排查问题时可以分别看看。还有一个更隐蔽的情况某些老框架在建立连接时强制指定SET NAMES gbk或SET NAMES latin1这会覆盖你服务端的 utf8mb4 设置。如果程序里已经配置了 utf8mb4 但发现无法插入 emoji请重点排查框架的字符集初始化语句。3.4 索引与排序规则的关系影响可能比你想的大排序规则不仅影响比较结果还影响 B 树索引的排序方向和范围查询。简单说索引是按索引列的排序规则组织数据的。如果列是utf8mb4_general_ci索引里字符大小关系就是该规则下的关系如果你在WHERE条件里用_bin去比较同一列MySQL 会认为你的比较条件与索引顺序不一定兼容可能会放弃索引扫描改走全表扫描。这在实践中非常坑。比如用户表 user 表有个用户名索引列本身是utf8mb4_general_ci平时WHERE username abc秒回。有一天你为了区分大小写写成了WHERE username BINARY ABC或WHERE username COLLATE utf8mb4_bin ABC结果慢查询日志里突然多了个全表扫描的 SQL。并不是 MySQL 不会做而是索引顺序和这次比较需要的顺序不一致优化器权衡后可能放弃索引。这个坑只要踩一次你就永远不会再忽略排序规则的“副作用”。4. 排序规则相关的常见问题与排查方法4.1 Illegal mix of collations 到底是什么这个报错是排序规则问题里曝光率最高的。字面意思是“非法混用排序规则”常见于下面三种操作join 连接时两表的关联字段 collation 不一致对两个不同 collation 的字段做UNION字段是某种 collation但你查询条件里的常量或函数结果用了另一种 collation。举一个最简单复现例子SELECT * FROM a JOIN b ON a.name b.name;如果 a.name 是utf8mb4_unicode_cib.name 是utf8mb4_general_ciMySQL 不知道按谁的规则去比就直接报错。处理手段我之前已经提过临时加COLLATE或者在涉及的两个字段上做转换。注意COLLATE必须放在被比较表达式的任意一侧语法上一般是SELECT * FROM a JOIN b ON a.name b.name COLLATE utf8mb4_unicode_ci;这个写法的意思很明确后一侧临时显式使用utf8mb4_unicode_ci让双方能在同一套规则下比较。4.2 大小写不敏感造成的“假重复”和“查不到”这是我实际项目中遇到过最典型的问题。业务方要求用户名不分大小写注册也就是Admin和admin只能注册一个。可上线一段时间后运营手工导入了一批 CSV里面既有大写又有小写结果两个账号都创建成功了。查下来原因很简单用户名字段排序规则是utf8mb4_general_ci唯一索引对这个字段生效时“大小写相同”会被认定是同一个值。照理说不该插进去两行可导入工具是通过INSERT ... ON DUPLICATE KEY UPDATE做的如果唯一键不是用户名而是另一个字段就完全绕过了这个限制。如果业务原本就要求大小写不敏感那问题可能不大但如果你有像 invite_code 这种需要严格保留大小写、且不可重复的字段一定要在建表时把它定义成invite_code VARCHAR(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL同时建立唯一索引。这样数据库级别就能做好区分不用每次查询都主动加BINARY也不用担心索引失效。反过来如果业务要求大小写不敏感那用_ci就是最合适的。4.3 中文排序MySQL 默认规则帮不了太多很多人以为把排序规则换成utf8mb4_unicode_ci后ORDER BY name就能按拼音排序了。这个理解并不准确。Unicode 排序算法对 CJK 字符的排序权重不是按普通话拼音那么简单。你很可能得到既不像拼音、也不像部首的“鬼畜顺序”。出名的反例是ORDER BY使用utf8mb4_unicode_ci时汉字基本按 Unicode 码位排没有任何实际语义。要给中文按拼音排序常见方案有几种在表中增加一列存拼音或拼音首字母排序时按这一列排应用层取出数据后用程序排序使用 GBK 字符集下的GBK_CHINESE_CI排序规则它可以按拼音排序但缺点是 GBK 存不了 emoji不推荐新项目使用。如果你只是特定字段需要拼音排序可以在查询时对字段做转码SELECT * FROM city ORDER BY CONVERT(name USING gbk) COLLATE gbk_chinese_ci;这种写法只对当前查询生效性能一般而且要求该字段里的字符都能在 GBK 里表示否则会变成乱码。我的建议是先和产品确认排序规则如果产品要的是拼音序明确告诉对方这个需求不适合用数据库默认排序解决。不是 MySQL 不行而是它根本不提供“按中文拼音”的通用排序规则。4.4 一次迁移后排序规则“被悄悄改掉”的排查这个坑我自己踩得记忆深刻。当时要把一个老库从 MySQL 5.6 搬到 MySQL 8.0数据用mysqldump导出再导入。导入之后应用正常运行但运营反馈“用户列表出来的顺序跟以前不一样了”。排查过程很有意思先查表结构发现SHOW CREATE TABLE里字符集确实还是 utf8mb4但排序规则已经不再是原来的utf8mb4_general_ci而是变成了utf8mb4_0900_ai_ci原因是在导出 SQL 里建表语句没有显式COLLATE导入到 8.0 后MySQL 直接用了新默认由于两个库对同一批姓名的排序顺序不同所以列表顺序全变。这个案例给我的教训有两条第一跨版本迁移前要主动对比所有库表的字符集和排序规则设置第二导出备份后的 SQL 脚本最好检查一下SET NAMES和DEFAULT CHARSET部分是不是随版本智能变化了。不要以为“数据没丢、字段没变”就万事大吉排序规则的默认值差异会在你看不到的地方悄悄影响业务。5. 个人使用习惯和小技巧如果你问我后来给同事的最终答复是什么我总结下来就三条MySQL 8.0 新项目直接用默认的utf8mb4_0900_ai_ci不要动MySQL 5.7 需要强调多语言规范就选utf8mb4_unicode_ci涉及大小写敏感的精确匹配字段单独设成utf8mb4_bin。这三种场景覆盖了绝大多数业务。最后再分享两个小技巧。第一个排查 collation 相关问题时优先执行SHOW VARIABLES LIKE collation%; SHOW FULL COLUMNS FROM 表名;前者看服务器当前生效的各层排序规则后者直接列出每一列字符集和排序规则比一张张翻表结构高效得多。第二个进入生产库做任何修改前先执行SELECT version和SHOW CREATE TABLE把当前状态留个底。排序规则这种东西不炸的时候你感觉不到它存在等它真出头露面往往已经是需要处理事故的状态了。从我个人的经验看排序规则不是“选个默认值就完事”的配置项。很多线上故障的根因都不是数据或 SQL 写错了而是字符集和排序规则在跨库、跨表、跨版本时悄悄变得不一致。你越早把它当成建表规范的一部分后面要填的坑就越少。 
长河君 · 2026/10/13 13:23:26 · 阅读 1.2k MySQL排序规则选型指南：从utf8mb4_unicode_ci到0900_ai_ci的实战解析
说实话这个问题我一开始是有点懵的。那天下班前同事跑过来问了一句“咱们新库的排序规则用的 utf8mb4_unicode_ci这到底该咋选我看工具默认生成的还有 general_ci 和 0900_ai_ci这东西有啥区别”我第一反应是平时建库建表不都是工具自动生成吗谁会认真看那一长串COLLATE啊。可被他这么一问我发现这个看似不起眼的参数其实决定了数据库里大小写敏不敏感、查询能不能走索引、甚至两个表 join 起来会不会直接报错。而且真要给一套“为什么这么选”的说法我确实差点接不上来。后来我专门把 MySQL 排序规则collation这套东西翻了个底朝天才理清楚里面的门道。这篇文章就当是一次复盘总结写给那些天天和数据库打交道、却很少深究COLLATE的同学希望能帮你少踩几个坑。内容偏实战会先讲清楚原理再给选型建议最后把线上真会遇到的坑一个个拆开。哪怕你现在只用默认配置看完之后至少再被同事问到能头头是道聊上十分钟。1. 排序规则到底是什么从“储存”到“比较”的那一步1.1 字符集决定“装什么”排序规则决定“怎么比”很多人会把字符集charset和排序规则collation混在一起其实它们根本不在一个层面。字符集回答的问题是“这个字段能存哪些字符、用多少字节存”比如 utf8mb4 表示能存下完整的 Unicode 字符包括 emoji 和生僻字。而排序规则回答的是“这些字符谁大谁小、怎么排、怎么算相等”。生活里最贴近的类比是字典。字符集相当于字典收了多少字、每页印什么字形排序规则则是“第一种读音排序法”还是“按笔画排序法”。同一个“发”字按拼音排和按部首排它在整本字典里的位置完全不同。数据库也一样同一列数据用utf8mb4_unicode_ci和utf8mb4_bin去ORDER BY出来的顺序可能大不一样。MySQL 里一个字符集可以对应多种排序规则。你可以执行SHOW CHARACTER SET注意看每行后面的 Default collation 列那只是这个字符集的默认值不代表强制值。1.2 排序规则命名的规律看后缀就能猜出八成虽然不同的库版本会有各种各样的 collation但命名基本有套路。拿最典型的utf8mb4_general_ci拆开看utf8mb4对应的字符集general排列算法或适用范围cicase insensitive大小写不敏感。同理你还会看到_bin按字节二进制比较最严格区分大小写_cscase sensitive区分大小写在 MySQL 里存在但默认用得少_aiaccent insensitive不区分重音_asaccent sensitive区分重音。MySQL 8.0 之后默认排序规则变成了utf8mb4_0900_ai_ci从名字就能读出两层意思aiaccent insensitivecicase insensitive。这个规则并不是“随便改的默认值”而是 MySQL 跟随 Unicode 排序算法更新后顺手把 defaults 往前推了一大步。版本差异、默认值变化恰恰是很多人迁移升级后突然发现排序结果变了、查询结果不对的核心原因。1.3 为什么平时不炸一炸就是事故排序规则平时极少被感知到因为如果你从头到尾都用默认值所有表、所有连接、所有字段都一致数据库就不会抱怨。它真正出现问题通常是下面几种情况项目从一个库迁移到另一个库时源库和目标库的默认字符集/排序规则不一致手工建表时有些字段指定了COLLATE binary之类特殊规则有些没有两个表明明字符集相同但 collation 不同做 join 时直接报Illegal mix of collations。我在实际排查里见过最经典的一次两个订单表字符集都是 utf8mb4可是一个用了utf8mb4_general_ci另一个被 DBA 顺手改成了utf8mb4_unicode_ci然后两表关联做统计报表SQL 一跑就报错。报错信息并不提示“哪个字段需要改正”只会给一个笼统的 collation 混用提示。刚接触的人往往会卡很久其实解决方式非常简单——统一成一种排序规则或者在某次比较里显式指定COLLATE后面我会专门讲。2. 常见排序规则对比utf8mb4 家族里的三足鼎立2.1 utf8mb4_general_ci快但有硬伤utf8mb4_general_ci是 MySQL 5.x 时代非常常见的默认选择。它的比较算法比较“野路子”本质上是先把字符映射到一组权重再做对比好处是速度快、实现简单坏处是不符合 Unicode 官方推荐的排序规则。对纯英文、纯数字场景它几乎没什么感知差异。但对德语、法语这类带重音字符的语言它会直接不区分重音导致café和cafe被当成同一个值在一些欧洲语言的词典顺序上general_ci的排序结果也不够准确。中文场景下它的汉字排序基本等于按 Unicode 码位硬排看不出任何拼音或部首规律。如果你只是做中英文系统的普通数据存储它够用但如果你要做国际化的多语言系统它就不太靠谱了。2.2 utf8mb4_unicode_ci用“官方标准”换更好的语义utf8mb4_unicode_ci基于 Unicode Collation AlgorithmUCA实现规则更接近各语言官方排序习惯。它的好处不是肉眼可见的快而是“正确”。这里有个细节容易误导人在 MySQL 5.7 及更早版本里unicode_ci在性能上是比general_ci慢一点的因为要做更复杂的多级权重比较。很多 DBA 基于这个原因会主动选general_ci。但实际上在现代服务器和普通业务量下这二者的性能差异微乎其微除非你是在做每秒十万级纯比较运算的极端场景。相比之下因为排序规则太粗糙导致数据语义出错、甚至需要重建表来修正代价大得多。所以我的个人倾向很明确与其省那点比较时间不如从一开始选更标准、语义更准确的规则。2.3 utf8mb4_0900_ai_ciMySQL 8.0 的默认新王到了 MySQL 8.0官方把默认字符集直接改成了 utf8mb4排序规则默认值也变成了utf8mb4_0900_ai_ci。注意这个0900不是版本号而是引用的 Unicode Collation Algorithm 版本对应 Unicode 9.0 的权重表。相比老前辈它有几个明显优势支持更多现代 Unicode 字符和特殊符号的合理排序对音乐符号、表情符号、生僻符号的权重处理更规范实现层面做了优化相当一部分场景下它甚至比unicode_ci更快。但这里要敲个重点千万不要以为升级到 MySQL 8.0 就可以“零成本”升级排序规则。你已有的表如果是在 5.7 建的用的很可能还是utf8mb4_unicode_ci或者utf8mb4_general_ci。即使数据库版本升级了存量表结构不会自动跟着变查询行为也不会变。你只有主动执行ALTER TABLE ... CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci才算是真正切到新默认。2.4 utf8mb4_bin当你要“绝对精确”的时候如果你做过用户登录、优惠券核销、密钥校验之类功能大概率遇到过大小写敏感问题。存用户名时用了username字段查询时WHERE username Admin居然能查到admin那条记录看起来是“很智能”但对某些业务来说这就是事故。该怎么解决把字段的排序规则改成utf8mb4_bin它按 UTF-8 编码后的字节序列直接二进制比较区分大小写也区分重音。代价是什么排序会比较快但不具备任何“字符语义”所以别指望它对中文、英文的大小写折叠做智能化处理。在做字段选型前你只需要记住凡是要求精确匹配、严格区分的字段优先用_bin凡是要做展示排序、模糊搜索、语义对比的字段优先用_ai_ci或_unicode_ci。为了看得更清楚我整理了一张速查表排序规则大小写重音排序语义适用场景utf8mb4_general_ci不敏感不敏感简单粗暴不够标准老系统、纯英文/中文存储utf8mb4_unicode_ci不敏感不敏感Unicode 官方规则MySQL 5.7 多语言系统utf8mb4_0900_ai_ci不敏感不敏感Unicode 9.0 新规则MySQL 8.0 新项目首选utf8mb4_bin敏感敏感二进制比对精确匹配场景3. 实战选型与迁移从建库到改表的一次说清3.1 新项目建库把“默认值”变成“显式值”新建项目时很多开发同学的做法是直接执行CREATE DATABASE test DEFAULT CHARACTER SET utf8mb4;后边不写排序规则让服务器默认值决定一切。这样不是不行但隐患是如果你接手的不同服务器 MySQL 版本不同默认排序规则可能是latin1_swedish_ci、utf8mb4_general_ci或者utf8mb4_0900_ai_ci同一套建表脚本在不同环境里跑出来的排序行为不一致。所以我的习惯是从最开始就把排序规则写死。比如CREATE DATABASE app_db DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;建表语句也同理关键字段能显式指定就显式指定CREATE TABLE users ( id BIGINT PRIMARY KEY, username VARCHAR(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL, nickname VARCHAR(128) NOT NULL, email VARCHAR(255) NOT NULL ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_0900_ai_ci;如果目标环境是 MySQL 5.7推荐把0900_ai_ci换成utf8mb4_unicode_ci因为 5.7 不认识前者。如果拿不准服务器版本可以先执行SELECT VERSION();看一眼再决定默认值。3.2 存量表改排序规则别在生产库上直接 ALTER存量项目要改排序规则场景一般有两种一是原来的 latin1/gbk 导致中文乱码或 emoji 存不进去想整体迁移到 utf8mb4二是两个表的 collation 不一致导致查询报错需要统一排序规则。第一种情况最直接的语句是ALTER TABLE order_info CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;这条语句会做什么呢它会修改表默认字符集同时把所有字符串类型字段的字符集和排序规则一并改成目标值并重写整张表的数据。如果表里有几千万行你要做好心理准备这个过程会锁表、会产生大量 IO很可能会拖垮线上业务。所以在生产环境里我不会直接一把梭。比较稳妥的做法是先在一台从库或者测试库上执行同样的变更观察耗时选择流量低峰期操作提前备份mysqldump或物理备份至少来一个如果表数据量太大优先用在线变更工具拆分批次重建表改完之后立刻执行SHOW CREATE TABLE和几条抽样查询验证数据是否完好。如果是第二种情况仅仅因为两个表的 collation 不一致而报错其实不一定要改表。可以在查询时临时指定SELECT a.name, b.nickname FROM user_profile a JOIN order_log b ON a.uid b.uid WHERE a.name b.nickname COLLATE utf8mb4_unicode_ci;或者用CONVERT()函数转换某一侧SELECT a.name FROM user_profile a JOIN order_log b ON a.uid b.uid WHERE a.name CONVERT(b.nickname USING utf8mb4);前者适合比较少的临时查询后者适合固定业务逻辑但又不方便改表结构的场景。能少动就少动是生产环境的第一原则。3.3 连接参数也要对齐别让客户端拖后腿很多时候你以为自己已经把库表都设成 utf8mb4 了可程序连上来之后写入的数据还是乱码或者查询结果出现“问号”。这里十有八九是连接层的字符集没对齐。连接层常见设置是character_set_client utf8mb4 character_set_connection utf8mb4 character_set_results utf8mb4MySQL 客户端连接时这些参数可以由连接字符串或初始化命令指定。如果你用连接池建议在初始化 SQL 里执行SET NAMES utf8mb4 COLLATE utf8mb4_0900_ai_ci;这里有个容易被忽略的点SET NAMES utf8mb4只设置字符集并不设置排序规则。排序规则会跟随该字符集的默认值。如果你希望连接层显式使用某个 collation就不要漏掉COLLATE部分。另外character_set_server和collation_server是服务端全局默认character_set_database和collation_database是当前库的默认二者可能不一样排查问题时可以分别看看。还有一个更隐蔽的情况某些老框架在建立连接时强制指定SET NAMES gbk或SET NAMES latin1这会覆盖你服务端的 utf8mb4 设置。如果程序里已经配置了 utf8mb4 但发现无法插入 emoji请重点排查框架的字符集初始化语句。3.4 索引与排序规则的关系影响可能比你想的大排序规则不仅影响比较结果还影响 B 树索引的排序方向和范围查询。简单说索引是按索引列的排序规则组织数据的。如果列是utf8mb4_general_ci索引里字符大小关系就是该规则下的关系如果你在WHERE条件里用_bin去比较同一列MySQL 会认为你的比较条件与索引顺序不一定兼容可能会放弃索引扫描改走全表扫描。这在实践中非常坑。比如用户表 user 表有个用户名索引列本身是utf8mb4_general_ci平时WHERE username abc秒回。有一天你为了区分大小写写成了WHERE username BINARY ABC或WHERE username COLLATE utf8mb4_bin ABC结果慢查询日志里突然多了个全表扫描的 SQL。并不是 MySQL 不会做而是索引顺序和这次比较需要的顺序不一致优化器权衡后可能放弃索引。这个坑只要踩一次你就永远不会再忽略排序规则的“副作用”。4. 排序规则相关的常见问题与排查方法4.1 Illegal mix of collations 到底是什么这个报错是排序规则问题里曝光率最高的。字面意思是“非法混用排序规则”常见于下面三种操作join 连接时两表的关联字段 collation 不一致对两个不同 collation 的字段做UNION字段是某种 collation但你查询条件里的常量或函数结果用了另一种 collation。举一个最简单复现例子SELECT * FROM a JOIN b ON a.name b.name;如果 a.name 是utf8mb4_unicode_cib.name 是utf8mb4_general_ciMySQL 不知道按谁的规则去比就直接报错。处理手段我之前已经提过临时加COLLATE或者在涉及的两个字段上做转换。注意COLLATE必须放在被比较表达式的任意一侧语法上一般是SELECT * FROM a JOIN b ON a.name b.name COLLATE utf8mb4_unicode_ci;这个写法的意思很明确后一侧临时显式使用utf8mb4_unicode_ci让双方能在同一套规则下比较。4.2 大小写不敏感造成的“假重复”和“查不到”这是我实际项目中遇到过最典型的问题。业务方要求用户名不分大小写注册也就是Admin和admin只能注册一个。可上线一段时间后运营手工导入了一批 CSV里面既有大写又有小写结果两个账号都创建成功了。查下来原因很简单用户名字段排序规则是utf8mb4_general_ci唯一索引对这个字段生效时“大小写相同”会被认定是同一个值。照理说不该插进去两行可导入工具是通过INSERT ... ON DUPLICATE KEY UPDATE做的如果唯一键不是用户名而是另一个字段就完全绕过了这个限制。如果业务原本就要求大小写不敏感那问题可能不大但如果你有像 invite_code 这种需要严格保留大小写、且不可重复的字段一定要在建表时把它定义成invite_code VARCHAR(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL同时建立唯一索引。这样数据库级别就能做好区分不用每次查询都主动加BINARY也不用担心索引失效。反过来如果业务要求大小写不敏感那用_ci就是最合适的。4.3 中文排序MySQL 默认规则帮不了太多很多人以为把排序规则换成utf8mb4_unicode_ci后ORDER BY name就能按拼音排序了。这个理解并不准确。Unicode 排序算法对 CJK 字符的排序权重不是按普通话拼音那么简单。你很可能得到既不像拼音、也不像部首的“鬼畜顺序”。出名的反例是ORDER BY使用utf8mb4_unicode_ci时汉字基本按 Unicode 码位排没有任何实际语义。要给中文按拼音排序常见方案有几种在表中增加一列存拼音或拼音首字母排序时按这一列排应用层取出数据后用程序排序使用 GBK 字符集下的GBK_CHINESE_CI排序规则它可以按拼音排序但缺点是 GBK 存不了 emoji不推荐新项目使用。如果你只是特定字段需要拼音排序可以在查询时对字段做转码SELECT * FROM city ORDER BY CONVERT(name USING gbk) COLLATE gbk_chinese_ci;这种写法只对当前查询生效性能一般而且要求该字段里的字符都能在 GBK 里表示否则会变成乱码。我的建议是先和产品确认排序规则如果产品要的是拼音序明确告诉对方这个需求不适合用数据库默认排序解决。不是 MySQL 不行而是它根本不提供“按中文拼音”的通用排序规则。4.4 一次迁移后排序规则“被悄悄改掉”的排查这个坑我自己踩得记忆深刻。当时要把一个老库从 MySQL 5.6 搬到 MySQL 8.0数据用mysqldump导出再导入。导入之后应用正常运行但运营反馈“用户列表出来的顺序跟以前不一样了”。排查过程很有意思先查表结构发现SHOW CREATE TABLE里字符集确实还是 utf8mb4但排序规则已经不再是原来的utf8mb4_general_ci而是变成了utf8mb4_0900_ai_ci原因是在导出 SQL 里建表语句没有显式COLLATE导入到 8.0 后MySQL 直接用了新默认由于两个库对同一批姓名的排序顺序不同所以列表顺序全变。这个案例给我的教训有两条第一跨版本迁移前要主动对比所有库表的字符集和排序规则设置第二导出备份后的 SQL 脚本最好检查一下SET NAMES和DEFAULT CHARSET部分是不是随版本智能变化了。不要以为“数据没丢、字段没变”就万事大吉排序规则的默认值差异会在你看不到的地方悄悄影响业务。5. 个人使用习惯和小技巧如果你问我后来给同事的最终答复是什么我总结下来就三条MySQL 8.0 新项目直接用默认的utf8mb4_0900_ai_ci不要动MySQL 5.7 需要强调多语言规范就选utf8mb4_unicode_ci涉及大小写敏感的精确匹配字段单独设成utf8mb4_bin。这三种场景覆盖了绝大多数业务。最后再分享两个小技巧。第一个排查 collation 相关问题时优先执行SHOW VARIABLES LIKE collation%; SHOW FULL COLUMNS FROM 表名;前者看服务器当前生效的各层排序规则后者直接列出每一列字符集和排序规则比一张张翻表结构高效得多。第二个进入生产库做任何修改前先执行SELECT version和SHOW CREATE TABLE把当前状态留个底。排序规则这种东西不炸的时候你感觉不到它存在等它真出头露面往往已经是需要处理事故的状态了。从我个人的经验看排序规则不是“选个默认值就完事”的配置项。很多线上故障的根因都不是数据或 SQL 写错了而是字符集和排序规则在跨库、跨表、跨版本时悄悄变得不一致。你越早把它当成建表规范的一部分后面要填的坑就越少。