企业官网建设流程全解析

说实话，这个问题我一开始是有点懵的。那天下班前，同事跑过来问了一句：“咱们新库的排序规则用的 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：排列算法或适用范围；
• ci：case insensitive，大小写不敏感。

同理你还会看到：

• _bin：按字节二进制比较，最严格，区分大小写；
• _cs：case sensitive，区分大小写（在 MySQL 里存在但默认用得少）；
• _ai：accent insensitive，不区分重音；
• _as：accent sensitive，区分重音。

MySQL 8.0 之后，默认排序规则变成了utf8mb4_0900_ai_ci，从名字就能读出两层意思：ai（accent insensitive），ci（case insensitive）。这个规则并不是“随便改的默认值”，而是 MySQL 跟随 Unicode 排序算法更新后顺手把 defaults 往前推了一大步。版本差异、默认值变化，恰恰是很多人迁移升级后突然发现排序结果变了、查询结果不对的核心原因。

1.3 为什么平时不炸，一炸就是事故

排序规则平时极少被感知到，因为如果你从头到尾都用默认值，所有表、所有连接、所有字段都一致，数据库就不会抱怨。它真正出现问题，通常是下面几种情况：

1. 项目从一个库迁移到另一个库时，源库和目标库的默认字符集/排序规则不一致；
2. 手工建表时有些字段指定了COLLATE binary之类特殊规则，有些没有；
3. 两个表明明字符集相同，但 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 Algorithm（UCA）实现，规则更接近各语言官方排序习惯。它的好处不是肉眼可见的快，而是“正确”。

这里有个细节容易误导人：在 MySQL 5.7 及更早版本里，unicode_ci在性能上是比general_ci慢一点的，因为要做更复杂的多级权重比较。很多 DBA 基于这个原因会主动选general_ci。但实际上，在现代服务器和普通业务量下，这二者的性能差异微乎其微，除非你是在做每秒十万级纯比较运算的极端场景。相比之下，因为排序规则太粗糙导致数据语义出错、甚至需要重建表来修正，代价大得多。所以我的个人倾向很明确：与其省那点比较时间，不如从一开始选更标准、语义更准确的规则。

2.3 utf8mb4_0900_ai_ci：MySQL 8.0 的默认新王

到了 MySQL 8.0，官方把默认字符集直接改成了 utf8mb4，排序规则默认值也变成了utf8mb4_0900_ai_ci。注意，这个0900不是版本号，而是引用的 Unicode Collation Algorithm 版本，对应 Unicode 9.0 的权重表。

相比老前辈，它有几个明显优势：

1. 支持更多现代 Unicode 字符和特殊符号的合理排序；
2. 对音乐符号、表情符号、生僻符号的权重处理更规范；
3. 实现层面做了优化，相当一部分场景下它甚至比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。

为了看得更清楚，我整理了一张速查表：

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 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_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，很可能会拖垮线上业务。

所以在生产环境里，我不会直接一把梭。比较稳妥的做法是：

1. 先在一台从库或者测试库上执行同样的变更，观察耗时；
2. 选择流量低峰期操作；
3. 提前备份，mysqldump或物理备份至少来一个；
4. 如果表数据量太大，优先用在线变更工具，拆分批次重建表；
5. 改完之后立刻执行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 = utf8mb4

MySQL 客户端连接时，这些参数可以由连接字符串或初始化命令指定。如果你用连接池，建议在初始化 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 到底是什么

这个报错是排序规则问题里曝光率最高的。字面意思是“非法混用排序规则”，常见于下面三种操作：

1. join 连接时两表的关联字段 collation 不一致；
2. 对两个不同 collation 的字段做UNION；
3. 字段是某种 collation，但你查询条件里的常量或函数结果用了另一种 collation。

举一个最简单复现例子：

SELECT * FROM a JOIN b ON a.name = b.name;

如果 a.name 是utf8mb4_unicode_ci，b.name 是utf8mb4_general_ci，MySQL 不知道按谁的规则去比，就直接报错。处理手段我之前已经提过，临时加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 码位排，没有任何实际语义。要给中文按拼音排序，常见方案有几种：

1. 在表中增加一列存拼音或拼音首字母，排序时按这一列排；
2. 应用层取出数据后用程序排序；
3. 使用 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导出再导入。导入之后，应用正常运行，但运营反馈“用户列表出来的顺序跟以前不一样了”。

排查过程很有意思：

1. 先查表结构，发现SHOW CREATE TABLE里字符集确实还是 utf8mb4，但排序规则已经不再是原来的utf8mb4_general_ci，而是变成了utf8mb4_0900_ai_ci；
2. 原因是在导出 SQL 里，建表语句没有显式COLLATE，导入到 8.0 后，MySQL 直接用了新默认；
3. 由于两个库对同一批姓名的排序顺序不同，所以列表顺序全变。

这个案例给我的教训有两条：第一，跨版本迁移前，要主动对比所有库表的字符集和排序规则设置；第二，导出备份后的 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 写错了，而是字符集和排序规则在跨库、跨表、跨版本时悄悄变得不一致。你越早把它当成建表规范的一部分，后面要填的坑就越少。