有次一位开发者急急忙忙找我说他们刚上线的用户系统出了怪事明明已经有人注册了 Admin可是别人还能用 admin 注册成功后台搜索用户名时大小写不同的账号也全糊在一起。我看了看建表语句第一反应就是排序规则collation没处理好。MySQL 8 默认的 utf8mb4_0900_ai_ci 恰恰是大小写不敏感case-insensitive的排序规则只要建表时没有显式指定 COLLATE所有字符串比较都会把 A 和 a 当成同一个字符。今天我想把 MySQL 8 中大小写敏感与不敏感排序规则的选择这件事讲透。它不只影响 WHERE 查询还会牵动唯一索引、ORDER BY、GROUP BY、JOIN 关联甚至数据迁移后的行为变化。文章里准备了大量可以直接运行的 SQL 例子也整理了我在实际项目里踩过的坑适合刚接手 MySQL 8 项目的开发也适合正在做查询优化、数据迁移的运维同学。看完之后你至少能回答三个问题当前系统用的是哪种排序规则业务到底需不需要区分大小写出问题时该从哪里查起。1. 先认识 MySQL 8 的排序规则字符集之外的另一半真相1.1 字符集与排序规则的关系很多人在建表时只写过 CHARSETutf8mb4很少关心 COLLATE。这两个概念很容易混字符集决定字符怎么存比如“中”这个字在 utf8mb4 下对应哪几个字节排序规则决定字符怎么比比如“A”“a”“b”谁大谁小以及“A”和“a”算不算相等。MySQL 8 的默认字符集是 utf8mb4默认排序规则是 utf8mb4_0900_ai_ci。这句默认值非常关键。也就是说只要建表时写了 utf8mb4 而没有额外指定 COLLATE最终落到表上的就是 utf8mb4_0900_ai_ci大小写不敏感、重音也不敏感。很多人以为不指定就“没有排序规则”其实数据库早就替你选了一个只是这个默认选型不一定是业务想要的。为什么要用 0900_ai_ci 作为默认因为 MySQL 8 引入了基于 Unicode 9.0 排序算法的 0900 系列排序规则比老的 general_ci 更符合标准对多语言文本、重音字符、特殊符号的处理也更规范。而 ai、ci 这两个后缀代表默认的文本比较体验重音不敏感、大小写不敏感。对大多数搜索、标签、标题类字段来说这种宽松比较确实符合直觉。1.2 排序规则后缀到底代表什么MySQL 8 的排序规则命名其实是有一套规律的拆开看并不难。以 utf8mb4_0900_ai_ci 为例utf8mb4 是字符集0900 表示基于 Unicode 9.0 的排序权重表ai 是 accent insensitive表示重音不敏感ci 是 case insensitive表示大小写不敏感。把这些后缀组合起来就能快速判断一个排序规则的行为。后缀含义示例说明ai重音不敏感é 和 e 视为相等as重音敏感é 和 e 视为不同ci大小写不敏感A 和 a 视为相等cs大小写敏感A 和 a 视为不同bin二进制比较按字符编码值直接比较所以 utf8mb4_0900_as_cs 就是重音敏感、大小写敏感utf8mb4_0900_ai_cs 就是重音不敏感但大小写敏感utf8mb4_0900_bin 则直接把字符串按二进制编码比较A 和 a 的编码值不同结果自然也不同。日常选择时我不建议死记硬背只需要抓住两个维度你希望重音区分吗你希望大小写区分吗然后去后缀里找对应组合。1.3 如何查看当前环境正在用的排序规则排查问题永远从观察现状开始。连上 MySQL 8 后可以先跑这几条命令SHOW VARIABLES LIKE character_set%; SHOW VARIABLES LIKE collation%;重点看 character_set_server、collation_server、character_set_database、collation_database 这几项。server 级别是实例默认值database 级别是当前库的默认值而建表时如果不指定会沿用库的默认值。也可以用 SHOW COLLATION 查看某个字符集下所有可用的排序规则SHOW COLLATION WHERE Charset utf8mb4 AND Collation LIKE %0900%;如果想看某张表实际用的排序规则可以用 SHOW TABLE STATUS或者直接看 SHOW CREATE TABLE 的尾部SHOW CREATE TABLE user_info;这条命令会把表定义的 COLLATE 原样显示出来。我习惯了每次建表后都跑一次几十秒的时间能避免很多后来才发现的诡异行为。2. 大小写敏感与不敏感到底差在哪查询、排序、约束三个维度2.1 查询匹配一样的 WHERE不一样的结果先看最直观的差异。假设有一张表 user_ci排序规则是 utf8mb4_0900_ai_ci里面有一行 user_name 为 Admin。执行SELECT * FROM user_ci WHERE user_name admin;结果会把这行 Admin 查出来。因为在 _ci 排序规则下A 和 a 被认为是同一个字符admin 和 Admin 是等价的。如果换一张表 user_cs排序规则改成 utf8mb4_0900_as_cs同样一行 Admin再执行同样的查询就查不到任何记录。这个行为背后的逻辑是 MySQL 的强制力规则。简单说比较两个值时MySQL 会给每个值算一个 coercibility 值数字越小优先级越高显式 COLLATE 是 0列本身的排序规则是 2连接层默认是 3普通字符串字面量是 4。所以列和字面量 admin 比较时以列的排序规则为准。这就是为什么不需要对查询语句做任何特殊处理列定义就已经决定了匹配行为。2.2 唯一索引与约束冲突令人头疼的重复数据比查询更容易踩雷的是唯一索引。同样是 _ci 排序规则的表如果在 user_name 上建了唯一索引先插入 Admin再插入 admin第二条会直接报错Duplicate entry admin for key uk_user_name因为唯一索引在判断是否重复时用的就是列的排序规则既然 A 和 a 相等admin 自然就是 Admin 的重复值。反过来在 _as_cs 或 _bin 排序规则下Admin 和 admin 是不同的两条都能正常插入。这个行为对业务设计是双刃剑。如果你的用户名希望大小写不敏感比如登录时用户输入大写小写都能命中那 _ci 加唯一索引反而是最好的兜底方案应用层不用每次先查一遍再决定是否允许注册。但如果你的订单号、优惠码、文件路径希望严格区分大小写就千万不能用 _ci否则两个看起来不同的码会被当成同一个唯一索引形同虚设。2.3 排序、分组与去重报表数字少了半截ORDER BY 同样受排序规则影响。在 _ci 下A 和 a 的排序权重相同排序时这两个字符会挨在一起大小写差异只影响同组内的先后在 _cs 或 _bin 下大小写参与权重计算排序结果会有肉眼可见的区别。如果你的分页查询依赖固定排序顺序切换排序规则后很可能出现页面顺序漂移。GROUP BY 和 DISTINCT 的坑更隐蔽。在 _ci 的表里如果同时存在 Admin 和 adminGROUP BY user_name 会把它们归到同一组统计结果只显示一行COUNT(DISTINCT user_name) 也会把两者当成一个值。我遇到过一张用户标签表业务上用 DISTINCT 统计标签数量数据变多后数字不涨反降最后发现就是大小写不敏感的排序规则把不同写法合并了。这类问题不查 collation 很难想到。2.4 关联查询与索引JOIN 时的连锁反应排序规则还会影响 JOIN。假如一张表是 utf8mb4_0900_ai_ci另一张表是 utf8mb4_0900_bin两张表用字符串字段做等值关联时很可能会直接报错Illegal mix of collations (utf8mb4_0900_ai_ci,IMPLICIT) and (utf8mb4_0900_bin,IMPLICIT) for operation 原因是两个字段的 coercibility 值相同MySQL 无法自动决定用哪边的规则只能抛错。即使没报错只要发生隐式转换索引列可能被包上转换表达式优化器就很难用上原有索引进而出现全表扫描。所以在一个数据库里统一字符串字段的排序规则不只是强迫症更是性能和稳定性的要求。3. 按业务场景选排序规则从建库到查询的完整配置链路3.1 先给字段分个类再决定用哪种规则我不建议一股脑把整个数据库改成 _bin也不建议所有字段都默认 _ai_ci 不管。更务实的做法是按字段语义分类。下面是我自己常用的选择参考场景推荐排序规则理由标题、标签、搜索备注、普通文本utf8mb4_0900_ai_ci用户输入大小写不同不影响匹配体验好用户名、邮箱希望大小写不敏感登录utf8mb4_0900_ai_ci配合唯一索引天然防止大小写变体重复订单号、优惠码、兑换码、文件路径、密钥utf8mb4_0900_bin 或 utf8mb4_0900_as_cs严格区分大小写避免两个不同码互相覆盖需要按拼音排序的中文字段0900 系列中的中文分支排序更符合中文使用习惯不确定怎么选的普通业务字段utf8mb4_0900_ai_ci与默认值一致兼容性最好性能也足够这里有个细节_bin 和 _as_cs 都能做到大小写敏感但语义略有不同。_as_cs 是文本意义上的大小写敏感、重音敏感符合词典规则_bin 直接按字符编码值比较更底层。如果只是需要严格区分大小写、不希望有其他复杂规则介入选 _bin 通常最省心。如果业务有重音区分的需求则用 _as_cs 更合适。3.2 从服务器到列的配置链路优先级别记反排序规则可以配置在多个层级服务器级、库级、表级、列级、连接级。MySQL 在比较时会优先使用更具体的定义列定义大于表定义表定义大于库定义库定义大于服务器默认。这就是为什么有时候你以为整个库都是 _bin但个别列还是表现成 _ci因为那列在建表时被单独指定过。服务器级配置在 my.cnf 的 [mysqld] 段里设置[mysqld] character-set-serverutf8mb4 collation-serverutf8mb4_0900_ai_ci建库时可以显式指定CREATE DATABASE app CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;建表时可以单独指定CREATE TABLE coupon_code ( id INT PRIMARY KEY AUTO_INCREMENT, code VARCHAR(64) NOT NULL, UNIQUE KEY uk_code(code) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_0900_bin;如果表已经建好了想改某一列用 MODIFY COLUMN但要注意这会重建表大表操作要评估锁表时间和磁盘占用ALTER TABLE coupon_code MODIFY COLUMN code VARCHAR(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_bin NOT NULL;连接级也很重要。应用连上 MySQL 后连接层会有一套字符集和排序规则字符串字面量的比较会受它影响。JDBC 或各类连接串里配置好 UTF-8 编码只是第一步更关键的是让应用连接的 collation_connection 和库表保持一致。好消息是只要库表统一连接层即使有偏差列本身的排序规则仍然能压住字面量。3.3 查询时临时指定排序规则的两种思路有时候你不想改表结构只是某个查询需要临时做大小写敏感匹配。可以在查询里带上 COLLATESELECT * FROM user_ci WHERE user_name admin COLLATE utf8mb4_0900_as_cs;这里 COLLATE 加在字面量上强制把右边的字符串按 as_cs 规则比较而左边列本身是 ai_ci两边强制力不同时会采用显式指定的规则。这种写法适合排查问题但我不建议把它长期留在业务代码里因为如果列本身是 _ci每次查询都要额外写 COLLATE容易漏也容易让索引优化受到限制。另一个常用写法是用 BINARYSELECT * FROM user_ci WHERE BINARY user_name admin;BINARY 会把比较转换为字节级比较效果上也能区分大小写。但它和 _bin 不是完全等同BINARY 是字节串语义在多字节字符场景下要小心_bin 则是字符集内的二进制编码比较语义更清晰。我的建议是能改列定义就改列定义临时排查用 COLLATE不要长期依赖 BINARY。4. 实操实录与坑位排查从建表演示到 Illegal mix of collations4.1 一张对比表亲手验证行为差异我习惯在排查问题时从头建两张对比表直观确认当前环境的行为。先建一张 _ci 表CREATE TABLE t_ci ( id INT AUTO_INCREMENT PRIMARY KEY, user_name VARCHAR(50) NOT NULL, UNIQUE KEY uk_name(user_name) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_0900_ai_ci;再建一张 _cs 表CREATE TABLE t_cs ( id INT AUTO_INCREMENT PRIMARY KEY, user_name VARCHAR(50) NOT NULL, UNIQUE KEY uk_name(user_name) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_0900_as_cs;向 t_ci 插入 Admin 后再插入 adminINSERT INTO t_ci(user_name) VALUES (Admin); INSERT INTO t_ci(user_name) VALUES (admin);第二条大概率报 Duplicate entry。换成 t_cs 表两条插入都能成功。接着验证查询SELECT * FROM t_ci WHERE user_name admin; SELECT * FROM t_cs WHERE user_name admin;第一条能查出 Admin第二条查不到。再试试分组统计SELECT user_name, COUNT(*) FROM t_ci GROUP BY user_name; SELECT user_name, COUNT(*) FROM t_cs GROUP BY user_name;t_ci 会把 Admin 和 admin 合并成一组t_cs 则保留两条独立记录。这组对照实验做完基本就对排序规则的影响范围有了体感。我建议读者在测试库亲手跑一遍比看十篇文档都管用。4.2 Illegal mix of collations 的定位与修复这个报错我见过太多次尤其是从老库迁移数据、或者几个同事各建各的表时最容易出现。复现起来很简单SELECT * FROM t_ci c JOIN t_cs b ON c.user_name b.user_name;两边字段的 coercibility 都是 2collation 不同MySQL 无法自动选边直接报 Illegal mix of collations。解决办法有几种。第一JOIN 时显式指定 COLLATESELECT * FROM t_ci c JOIN t_cs b ON c.user_name b.user_name COLLATE utf8mb4_0900_as_cs;第二两边都转到同一个规则SELECT * FROM t_ci c JOIN t_cs b ON c.user_name COLLATE utf8mb4_0900_bin b.user_name COLLATE utf8mb4_0900_bin;第三治本的办法是把关联字段的排序规则统一。修改前先用查询找出系统中不一致的列SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, COLLATION_NAME FROM information_schema.COLUMNS WHERE COLLATION_NAME IS NOT NULL AND TABLE_SCHEMA 你的库名 ORDER BY TABLE_NAME, COLUMN_NAME;把输出结果拉出来对比凡是同名字段却挂了不同 COLLATE 的都是潜在风险点。我处理过一个系统就是因为用户表是 _ai_ci订单表是 _binJOIN 时频繁报错最后统一成 _ai_ci 才消停。4.3 从 5.7 迁到 8.0 时默认排序规则变化带来的隐形影响很多人做版本升级时只关心语法兼容性忽略了默认排序规则的变化。MySQL 5.7 时代如果显式指定 utf8mb4默认排序规则是 utf8mb4_general_ci到了 MySQL 8默认变成 utf8mb4_0900_ai_ci。两者都是大小写不敏感但具体比较规则并不完全一样。最危险的情况是旧库的建表语句里没有写 COLLATEmysqldump 导出的 DDL 也没带 COLLATE导入新库后表会按 8.0 的默认值重新解析。表面看索引、字段、数据都没丢但 WHERE 等值判断、ORDER BY 顺序、某些字符的等价关系可能已经悄悄变了。尤其是分页接口如果排序顺序变了用户翻页时会看到数据交错重复。迁移前建议做两件事。第一用 SHOW CREATE TABLE 导出旧库 DDL检查哪些表没有显式 COLLATE第二迁移后在测试环境跑一轮典型查询回归重点看唯一索引有没有新冲突、GROUP BY 结果是否和旧库一致、LIKE 搜索出来的是不是同样的集合。如果业务对排序结果有强依赖考虑在迁移时把表显式指定成旧排序规则比如 utf8mb4_general_ci保留历史行为。4.4 我踩过的几个高频坑第一个坑是 LIKE 也受排序规则影响。比如 _ci 表里查 LIKE %admin%会匹配 Admin、ADMIN、aDmiN 等所有大小写变体。这在搜索框里往往没问题但在做敏感词过滤、代码片段匹配时会造成误伤。需要精确匹配时可以临时用 LIKE BINARY或者干脆把相关字段改成 _bin。第二个坑是尾随空格。老的 utf8mb4 排序规则在比较时默认忽略字符串末尾空格所以 abc 和 abc 可能被视为相同0900 系列是 NO PAD 逻辑abc 和 abc 会被视为不同。这个差异平时很少遇到但一旦出现排查起来非常费劲。业务上我尽量保持文本字段两端干净避免依赖数据库对尾随空格的宽容处理。第三个坑是 _bin 不等于 BINARY。_bin 是字符集下的二进制排序规则比较的是字符的编码值BINARY 运算符把操作数转成字节串再比较。对纯英文字符来说两者很接近但涉及多字节字符时语义和结果都可能不同。如果只是想区分大小写优先考虑 _as_cs如果想完全按编码严格比较选 _bin不要随手用 BINARY 顶替。如果让我给一个不那么复杂但稳妥的经验新库直接保持 MySQL 8 默认的 utf8mb4_0900_ai_ci别全局改成 _bin只有用户名、优惠码、文件路径这类明确需要严格区分的字段单独把列设置成 utf8mb4_0900_bin 或 utf8mb4_0900_as_cs并在这些列上用唯一索引兜底。我按这个原则处理过好几个项目之后很少再因为大小写问题半夜救火。最后一个小技巧每次建完表顺手执行 SHOW CREATE TABLE 看一眼 COLLATE 是不是自己想要的这一步只要几十秒能省掉后面好几个小时的排查时间。