企业官网建设流程全解析

有次一位开发者急急忙忙找我，说他们刚上线的用户系统出了怪事：明明已经有人注册了 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 字符集与排序规则的关系

很多人在建表时只写过 CHARSET=utf8mb4，很少关心 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，表示大小写不敏感。把这些后缀组合起来，就能快速判断一个排序规则的行为。

所以 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 和 admin，GROUP 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 不管。更务实的做法是按字段语义分类。下面是我自己常用的选择参考：

这里有个细节：_bin 和 _as_cs 都能做到大小写敏感，但语义略有不同。_as_cs 是文本意义上的大小写敏感、重音敏感，符合词典规则；_bin 直接按字符编码值比较，更底层。如果只是需要严格区分大小写、不希望有其他复杂规则介入，选 _bin 通常最省心。如果业务有重音区分的需求，则用 _as_cs 更合适。

3.2 从服务器到列的配置链路，优先级别记反

排序规则可以配置在多个层级：服务器级、库级、表级、列级、连接级。MySQL 在比较时会优先使用更具体的定义，列定义大于表定义，表定义大于库定义，库定义大于服务器默认。这就是为什么有时候你以为整个库都是 _bin，但个别列还是表现成 _ci，因为那列在建表时被单独指定过。

服务器级配置在 my.cnf 的 [mysqld] 段里设置：

[mysqld] character-set-server=utf8mb4 collation-server=utf8mb4_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) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_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 查询时临时指定排序规则的两种思路

有时候你不想改表结构，只是某个查询需要临时做大小写敏感匹配。可以在查询里带上 COLLATE：

SELECT * FROM user_ci WHERE user_name = 'admin' COLLATE utf8mb4_0900_as_cs;

这里 COLLATE 加在字面量上，强制把右边的字符串按 as_cs 规则比较，而左边列本身是 ai_ci，两边强制力不同时会采用显式指定的规则。这种写法适合排查问题，但我不建议把它长期留在业务代码里，因为如果列本身是 _ci，每次查询都要额外写 COLLATE，容易漏，也容易让索引优化受到限制。

另一个常用写法是用 BINARY：

SELECT * FROM user_ci WHERE BINARY user_name = 'admin';

BINARY 会把比较转换为字节级比较，效果上也能区分大小写。但它和 _bin 不是完全等同：BINARY 是字节串语义，在多字节字符场景下要小心；_bin 则是字符集内的二进制编码比较，语义更清晰。我的建议是，能改列定义就改列定义，临时排查用 COLLATE，不要长期依赖 BINARY。

4. 实操实录与坑位排查：从建表演示到 Illegal mix of collations

4.1 一张对比表，亲手验证行为差异

我习惯在排查问题时从头建两张对比表，直观确认当前环境的行为。先建一张 _ci 表：

CREATE TABLE t_ci ( id INT AUTO_INCREMENT PRIMARY KEY, user_name VARCHAR(50) NOT NULL, UNIQUE KEY uk_name(user_name) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_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) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_as_cs;

向 t_ci 插入 Admin 后，再插入 admin：

INSERT 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 都是 2，collation 不同，MySQL 无法自动选边，直接报 Illegal mix of collations。解决办法有几种。

第一，JOIN 时显式指定 COLLATE：

SELECT * 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，订单表是 _bin，JOIN 时频繁报错，最后统一成 _ai_ci 才消停。

4.3 从 5.7 迁到 8.0 时，默认排序规则变化带来的隐形影响

很多人做版本升级时只关心语法兼容性，忽略了默认排序规则的变化。MySQL 5.7 时代，如果显式指定 utf8mb4，默认排序规则是 utf8mb4_general_ci；到了 MySQL 8，默认变成 utf8mb4_0900_ai_ci。两者都是大小写不敏感，但具体比较规则并不完全一样。

最危险的情况是：旧库的建表语句里没有写 COLLATE，mysqldump 导出的 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 是不是自己想要的，这一步只要几十秒，能省掉后面好几个小时的排查时间。