数据库字符集与排序规则治理实战：从乱码问号到 utf8mb4 统一

dbaai

一、具体的问题

上个月会员系统上线新功能，昵称允许自定义。上线第二天客服反馈：一部分用户昵称显示成了四个问号 ????，另一部分用户的昵称里带了 Emoji，保存时报错 Incorrect string value: '\xF0\x9F...' for column 'nick'。同一个接口，两种失败方式，看上去像两个问题。

排查下来其实是一个问题的两面。库里那张会员表建得早，nick 列是 utf8mb3，装不下 4 字节字符，Emoji 直接写不进去；而写入 ???? 的那批请求，走的是一个老版本的连接池配置，会话的 character_set_client 还是 latin1，服务端按 latin1 去解码客户端发来的 UTF-8 字节流，解不出来就替换成 ?，等数据落到磁盘上，原始字节已经没了。

这件事暴露的不是某个参数没设对，而是一个更基础的问题：字符集管的是"存不下"，排序规则管的是"比不对"，这两件事经常被混在一起谈，结果两边都没治理干净。

这篇文章给出一条能照着走的路径：先用 HEX() 判断数据是真坏还是只是显示坏，再用三个 SHOW 定位乱码发生在哪一跳，然后分层统一到 utf8mb4，最后处理排序规则切换会带来的唯一键与比较行为变化——这一步不做，治理完成当天就可能出现新的主键冲突。

二、核心原理

字符集与排序规则是两件独立的事

字符集（character set）决定"一个字符用哪几个字节表示"，排序规则（collation）决定"两个字符串怎么比大小、怎么排序、是否区分大小写和重音"。同一个字符集可以挂很多个排序规则，比如 utf8mb4 就有 utf8mb4_general_ci、utf8mb4_unicode_ci、utf8mb4_0900_ai_ci、utf8mb4_bin 等几十个。

关键在于：唯一索引、JOIN、ORDER BY、GROUP BY、DISTINCT 全都按排序规则执行。所以排序规则不是"技术细节"，它是业务语义的一部分。把一张 general_ci 的表和一张 0900_ai_ci 的表做 JOIN，MySQL 8.0 会直接抛 Illegal mix of collations，这不是锦上添花的问题，而是语句根本跑不过去。

MySQL 的字符集转换链

MySQL 的乱码几乎都出在这条链的某一跳上，每一跳都有一个对应的系统变量：

变量	作用	设错时的典型症状
character_set_client	服务端认为客户端发来的字节是什么编码	写入变 ????
character_set_connection	服务端内部处理 SQL 文本时用的编码	比较、LIKE 结果异常
character_set_results	返回给客户端时的编码	查询结果显示乱码，但数据本身是好的
character_set_database	库的默认字符集	新建表继承了错的默认值
character_set_server	实例级默认	新库新表持续复制错误
collation_connection	会话比较规则	临时表、字面量比较行为不一致

SET NAMES utf8mb4 做的事情就是同时把 client / connection / results 三项设成 utf8mb4，这是排查时最常用的止血动作。

这里有个判据要记牢：???? 是不可逆的。? 的字节是 0x3F，源字符表达不了时被替换成它，原始信息在那一刻就丢了，后面任何参数调整都救不回来，只能从业务侧重新录入。反过来，如果看到的是 å¼ 这类"看起来像乱码但字符还在"的形态，那是双重编码——UTF-8 字节被当成 latin1 存了一遍，还能用 CONVERT(BINARY(CONVERT(...))) 还原。

utf8 和 utf8mb4 不是一回事

MySQL 里的 utf8 是 utf8mb3 的别名，最多 3 字节，只能覆盖 BMP（基本多文种平面），Emoji、部分生僻汉字、化学符号都装不下。utf8mb4 才是完整的 UTF-8，最多 4 字节。8.0 里写 utf8 已经会收到 utf8mb3 的弃用告警，新建库表应该直接上 utf8mb4。

还有一个容易翻车的连带影响：VARCHAR(n) 的 n 是字符数，但索引长度上限是按字节算的。InnoDB 在 COMPACT 行格式下索引前缀上限 767 字节，utf8mb4 单字符最大 4 字节，于是单列索引最多只能用 191 个字符。这就是 MySQL 5.7 时代把表从 utf8mb3 转 utf8mb4 时最常见的报错 Specified key was too long; max key length is 767 bytes 的根因。解法是先把索引列从 255 缩到 191，或者把行格式改成 DYNAMIC（上限提升到 3072 字节）。

排序规则名字怎么读

以 utf8mb4_0900_ai_ci 为例拆开看：utf8mb4 是字符集，0900 表示基于 Unicode 9.0 的 UCA 算法，ai = accent-insensitive（重音不敏感，cafe 等于 café），ci = case-insensitive（大小写不敏感，A 等于 a）。

三代排序规则的实际差异主要在两处：

排序规则	Unicode 版本	特点与差异
utf8mb4_general_ci	4.0	快但粗糙，不做完整 UCA 展开
utf8mb4_unicode_ci	4.0.1	基于 UCA，排序更"正确"，性能略低
utf8mb4_0900_ai_ci	9.0	8.0 默认，排序结果与通用预期最接近

差异的典型体现是 ß 与 ss：general_ci 下两者不相等，0900_ai_ci 下 UCA 把它展开成 ss，两者相等。如果唯一索引建在昵称/名称列上，切换排序规则就可能让原本能共存的 ß 和 ss 变成冲突行，这是切换前必须查的。

其他库的对应概念

数据库	"字符集"载体	"排序规则"载体	备注
MySQL	character_set_* + 列级 charset	collation_* + 列级 collation	库/表/列三层可不同
SQL Server	varchar（代码页）/ nvarchar（UTF-16）	Collation，如 Chinese_PRC_CI_AS	跨库 JOIN 易报 collation conflict
Oracle	NLS_CHARACTERSET（varchar2）/ NLS_NCHAR_CHARACTERSET（nvarchar2）	排序行为由 NLS_SORT 等参数控制	库字符集装完后基本不可改
PostgreSQL	库级 encoding（创建后不可改）	库级 collate/ctype，10+ 支持 ICU	乱码主因是 client_encoding 不匹配

三、实例参考（动手步骤）

步骤 0：先把两种坏法复现出来

在测试库上造一张"混搭"的表，让问题可控地出现：

2. CREATE DATABASE coll_demo DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
3. USE coll_demo;
5. CREATE TABLE t_nick (
6.   id   INT PRIMARY KEY AUTO_INCREMENT,
7.   nick VARCHAR(50) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci,
8.   memo VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci
9. ) ENGINE=InnoDB;
11. -- 情形一：列装不下 4 字节字符
12. INSERT INTO t_nick(nick, memo) VALUES ('张三😀', '张三😀');

复制代码

执行结果是 ERROR 1366: Incorrect string value: '\xF0\x9F\x98\x80...' for column 'nick'——注意 memo 列是 utf8mb4，同样的值没问题，说明问题精确定位在列级字符集上。

情形二需要另开一个会话，把连接层故意设错：

2. SET NAMES latin1;
3. INSERT INTO t_nick(nick, memo) VALUES ('李四', '李四');
4. -- 会话恢复
5. SET NAMES utf8mb4;
6. SELECT id, nick, HEX(nick) FROM t_nick;

复制代码

第二条记录的 nick 会显示成 ????，HEX() 返回 3F3F，两个问号，原始信息已经丢了。

步骤 1：三个 SHOW 定位乱码发生在哪一跳

2. -- 1. 连接层
3. SHOW VARIABLES LIKE 'character_set_%';
4. SHOW VARIABLES LIKE 'collation_%';
6. -- 2. 存储层
7. SHOW CREATE TABLE t_nick\G
9. -- 3. 当前会话的即时值（连接池里最该看的一条）
10. SELECT @@character_set_client, @@character_set_connection,
11.        @@character_set_results, @@collation_connection;

复制代码

判读方法很直接：如果 character_set_client / character_set_results 是 latin1 而 character_set_server 是 utf8mb4，那么写入侧的乱码就来自连接层，属于"改配置就能防住"；如果连接层全是 utf8mb4 但列上有 CHARACTER SET utf8mb3 或 latin1，那必须改列，改连接参数一点用都没有。

步骤 2：用 HEX 判断数据是真坏还是只是显示坏

2. SELECT id, nick, HEX(nick), CHAR_LENGTH(nick), LENGTH(nick) FROM t_nick;

复制代码

对着结果分三类处理：

HEX 结果特征	结论	处理方式
全是 3F（问号）	已不可逆丢失	只能业务侧重新录入
形如 C3A5C2BC...，字节数明显翻倍	双重编码，数据还在	转码还原后可继续用
与预期 UTF-8 字节一致	数据完好，仅显示问题	修 client/result 编码即可

还原双重编码的写法（务必先用 SELECT 验证，确认无误再 UPDATE）：

2. SELECT id,
3.        CONVERT(BINARY(CONVERT(nick USING latin1)) USING utf8mb4) AS fixed
4. FROM t_nick
5. WHERE HEX(nick) LIKE 'C3%' OR HEX(nick) LIKE 'C2%';

复制代码

步骤 3：库、表、列三层统一到 utf8mb4

2. -- 库级默认（影响之后新建的表）
3. ALTER DATABASE coll_demo CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
5. -- 表级默认（只改默认值，不动存量列）
6. ALTER TABLE t_nick DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
8. -- 列级转换（会真正转换数据，可能重建表）
9. ALTER TABLE t_nick
10.   MODIFY nick VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

复制代码

只做前两步是典型的"半治理"：存量列还挂在旧字符集上，问题照旧。生产库上列很多，用 information_schema 批量生成语句比手写可靠：

2. SELECT CONCAT('ALTER TABLE `', TABLE_NAME, '` MODIFY `', COLUMN_NAME, '` ',
3.               COLUMN_TYPE,
4.               ' CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci',
5.               IF(IS_NULLABLE = 'NO', ' NOT NULL', ' NULL'),
6.               IF(COLUMN_DEFAULT IS NULL, '', CONCAT(' DEFAULT ', COLUMN_DEFAULT)),
7.               ';') AS stmt
8. FROM information_schema.COLUMNS
9. WHERE TABLE_SCHEMA = 'coll_demo'
10.   AND CHARACTER_SET_NAME IN ('utf8mb3', 'utf8', 'latin1');

复制代码

顺手再查一遍排序规则不统一的列，这份清单就是治理的验收依据：

2. SELECT TABLE_NAME, COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME
3. FROM information_schema.COLUMNS
4. WHERE TABLE_SCHEMA = 'coll_demo'
5.   AND (COLLATION_NAME <> 'utf8mb4_0900_ai_ci' OR COLLATION_NAME IS NULL);

复制代码

步骤 4：连接层和导入导出一起收口

改完列不代表结束，写入路径上还有三处要一起处理：

2. -- 环节一：会话初始化
3. SET NAMES utf8mb4;
5. -- 环节二：连接池（以 JDBC 为例，参数要写在连接串里，并在池上配置初始化语句）
6. -- jdbc:mysql://host:3306/db?useUnicode=true&characterEncoding=UTF-8&connectionCollation=utf8mb4_0900_ai_ci
7. -- connectionInitSql = SET NAMES utf8mb4

复制代码

环节三是导出导入，这一步最容易被漏掉：mysqldump 默认会读服务端字符集，如果实例里混着 latin1，导出的 SQL 文件本身可能就是错的。规范写法是两端都显式指定：

2. mysqldump --default-character-set=utf8mb4 -u dba -p db1 t_nick > t_nick.sql
3. mysql --default-character-set=utf8mb4 -u dba -p db1 < t_nick.sql

复制代码

另外，从 Excel 或 CSV 导入时要清楚文件本身的编码（GBK 的 CSV 很常见），导入前先 iconv -f GBK -t UTF-8 转一次，比事后修数据省事得多。

步骤 5：排序规则切换的实测影响

切换排序规则前，先在一张测试表上把影响量出来，重点看两件事：

2. -- 影响一：唯一键行为变化（ß 与 ss 在 UCA 下相等）
3. CREATE TABLE t_uniq (u VARCHAR(20) UNIQUE) ENGINE=InnoDB
4.   DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
5. INSERT INTO t_uniq VALUES ('straße'), ('strasse');   -- general_ci 下可共存，两条都成功
6. ALTER TABLE t_uniq MODIFY u VARCHAR(20) COLLATE utf8mb4_0900_ai_ci;
7. -- ERROR 1062: Duplicate entry 'strasse' for key 'u'
9. -- 影响二：ORDER BY 顺序变化
10. CREATE TABLE t_sort (name VARCHAR(20)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
11. INSERT INTO t_sort VALUES ('a'),('A'),('ä'),('z'),('ß'),('ss');
12. SELECT name FROM t_sort ORDER BY name COLLATE utf8mb4_general_ci;
13. SELECT name FROM t_sort ORDER BY name COLLATE utf8mb4_0900_ai_ci;

复制代码

切换前先做一次"冲突预检"，避免上线当天才发现主键冲突：

2. -- 以拟切换的排序规则分组，找出会被判定为重复的键
3. SELECT u COLLATE utf8mb4_0900_ai_ci AS k, COUNT(*) AS c, GROUP_CONCAT(u)
4. FROM t_uniq GROUP BY k HAVING c > 1;

复制代码

步骤 6：大表列转换怎么不锁死

VARCHAR 从 utf8mb3 转 utf8mb4 通常需要重建表。先试跑，让 MySQL 自己告诉你能不能在线做：

2. ALTER TABLE t_big MODIFY c VARCHAR(191) CHARACTER SET utf8mb4
3.   COLLATE utf8mb4_0900_ai_ci, ALGORITHM=INPLACE, LOCK=NONE;

复制代码

报 ALGORITHM=INPLACE is not supported 就说明必须走 COPY（全程锁写），这种时候用 gh-ost 或 pt-online-schema-change 做在线变更，或者走"先改从库、再切换"的路径。转换前务必先校验索引列长度，避免中途撞上 767/3072 字节的上限：

2. SELECT TABLE_NAME, INDEX_NAME, COLUMN_NAME,
3.        SUM(CHARACTER_MAXIMUM_LENGTH) AS chars
4. FROM information_schema.STATISTICS s
5. JOIN information_schema.COLUMNS c USING (TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME)
6. WHERE s.TABLE_SCHEMA = 'coll_demo'
7. GROUP BY TABLE_NAME, INDEX_NAME, COLUMN_NAME
8. HAVING chars * 4 > 3072;

复制代码

步骤 7：其他库的对照处理

2. -- SQL Server：查库与列的排序规则，列转 Unicode（想存 Emoji 必须用 nvarchar）
3. SELECT DATABASEPROPERTYEX('db1', 'Collation');
4. SELECT name, collation_name FROM sys.columns WHERE object_id = OBJECT_ID('dbo.t1');
5. ALTER TABLE dbo.t1 ALTER COLUMN nick NVARCHAR(50);
6. -- 跨库 JOIN 报 collation conflict 时显式指定
7. -- ... ON a.n COLLATE Chinese_PRC_CI_AS = b.n
9. -- Oracle：确认数据库字符集与国家字符集
10. SELECT parameter, value FROM nls_database_parameters
11. WHERE parameter IN ('NLS_CHARACTERSET', 'NLS_NCHAR_CHARACTERSET');
13. -- PostgreSQL：库编码创建后不可改，乱码优先查 client_encoding
14. SHOW server_encoding;
15. SHOW client_encoding;
16. SELECT datname, pg_encoding_to_char(encoding), datcollate FROM pg_database;
17. SET client_encoding = 'UTF8';

复制代码

治理前后对比

指标	治理前	治理后
字符集分布	utf8mb3 / latin1 / utf8mb4 三种并存，17 个列不符	全部 utf8mb4，不符列为 0
会话 character_set_client	latin1	utf8mb4
排序规则	general_ci / unicode_ci / 0900_ai_ci 三套并存	统一 0900_ai_ci
Emoji 与生僻字写入	报 1366 错误或写入 ???	正常写入，HEX 为完整 4 字节
跨列 JOIN	报 Illegal mix of collations	正常执行
两表 ORDER BY 同名结果	顺序不一致，对账脚本误报	完全一致
唯一键冲突	切换排序规则当天新增 3 条冲突	预检后提前处理，变更零中断

四、实操检查清单

• [ ] 执行 SHOW VARIABLES LIKE 'character_set_%'，确认 client / connection / results 三项都是 utf8mb4
  
• [ ] 查 information_schema.COLUMNS，列出所有 CHARACTER_SET_NAME 不是 utf8mb4 的列并登记数量
  
• [ ] 查排序规则分布，列出 COLLATION_NAME 不一致的表与列，作为验收基线
  
• [ ] 用 HEX() 抽样确认乱码数据类型：真丢失（3F）/ 双重编码 / 仅显示问题，分别制定处理方案
  
• [ ] 库、表、列三层 DEFAULT 与列定义全部改为 utf8mb4，不留"半治理"的列
  
• [ ] 索引列长度校验：utf8mb4 下 5.7 的 COMPACT 行格式单列索引不超过 191 字符，8.0 的 DYNAMIC 不超过 768 字符
  
• [ ] 应用连接串与连接池初始化语句都带上 utf8mb4，验证连接池复用后仍生效
  
• [ ] 备份与导入导出脚本统一加 --default-character-set=utf8mb4
  
• [ ] 切换排序规则前跑冲突预检 SQL，确认不会有唯一键冲突
  
• [ ] 大表变更先在预发环境试跑 ALGORITHM=INPLACE, LOCK=NONE，不支持则走在线 DDL 工具
  
• [ ] 变更后回归验证：Emoji 写入、跨表 JOIN、排序结果、唯一键行为四项各测一次
  
• [ ] 把字符集与排序规则检查项写进建表规范，新表建表语句必须显式声明 CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
  

几个容易踩的坑

第一，把"改成 utf8mb4"当成一条 ALTER 就完事。 库级和表级的 DEFAULT 只影响将来新建的对象，存量列必须逐列 MODIFY，否则治理做完还剩一堆 utf8mb3 列在那儿继续报错。

第二，用 SET NAMES 去救已经写成 ???? 的数据。 ? 的字节是 0x3F，原始字符在转换那一刻已经不存在，任何字符集参数都变不回来。这部分数据只能从业务侧重新采集。

第三，以为 utf8mb4 一定比 utf8mb3 慢一半。 实际差异只体现在"需要 4 字节的字符"上，纯中文和 ASCII 的存储占用完全一样，真正需要评估的是索引长度上限和行格式，而不是整体性能。

第四，忽略排序规则切换对唯一键的影响。 general_ci 下 ß 和 ss 可以共存，切到 0900_ai_ci 就冲突。切换前不做预检，变更脚本跑到一半失败，索引可能停在中间状态。

第五，混用排序规则做 JOIN 却不写 COLLATE。 8.0 对 0900_ai_ci 与 general_ci 的混合比较是直接报错，不是隐式转换。老库升级到 8.0 后这类报错尤其集中。

第六，把 SQL Server 的 varchar 当 nvarchar 用。 varchar 走代码页，中文实例下是 GBK，存不下 Emoji；SQL_Latin1_General_CP1_CI_AS 的库里存中文更是全靠运气。要存 Unicode 就得用 nvarchar，长度按字符算。

第七，忘了连接池里的连接是复用的。 只在应用启动时执行一次 SET NAMES 是不够的，连接池新建连接时必须通过初始化语句统一设置，否则会随机出现"一部分请求乱码"的诡异现象。