第 43 章 字符集与乱码踩坑

📖 本篇简介：中文一入库就变 ???、emoji 存不进去、两表 JOIN 字符集不同走不了索引——这些几乎都是字符集与排序规则没对齐导致的。本章汇总字符集与乱码方向最经典的 11 个坑，覆盖服务端/库/表/连接四层、utf8mb3 与 utf8mb4、collation、乱码补救与导出导入。示例基于 MySQL 8.0。读完你能定位并根治绝大多数"中文乱码"问题。

目录

• 坑 1：库、表、连接三层字符集不一致
• 坑 2：把 utf8 当成真正的 UTF8
• 坑 3：emoji 存入报截断
• 坑 4：两表 collation 不一致导致 JOIN 不走索引
• 坑 5：连接层客户端字符集没设
• 坑 6：latin1 存中文后乱码如何补救
• 坑 7：升级到 8.0 默认字符集变了
• 坑 8：导出导入字符集不匹配
• 坑 9：CONVERT 转码丢字符
• 坑 10：把二进制列当字符列用
• 坑 11：排序规则 ci 与 bin 混用
• 坑 12：中文 ORDER BY 排序结果不符合预期
• 速查表：字符集乱码速记
• 自测：你能避坑吗

坑 1：库、表、连接三层字符集不一致

场景 / 错误示范：

-- 库是 utf8mb4，但某张老表建的时候忘了指定，落到了 latin1
mysql> SHOW CREATE TABLE old_user\G
CREATE TABLE `old_user` (
  `id` int,
  `name` varchar(50)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;   -- 表级是 latin1！

INSERT INTO old_user VALUES (1, '张三');
-- 插入不报错，但查出来是乱码

为什么是坑：字符集是层层继承的：服务端默认 -> 库默认 -> 表默认 -> 列。只要其中一层不是 utf8mb4，那一层就成了乱码源头。很多人只在服务端设了 utf8mb4，却没检查具体表，建表时漏掉 DEFAULT CHARSET，老表就悄悄用了 latin1，中文存进去那一刻就已经损坏。

正确做法：

-- 1) 一次性排查库里所有非 utf8mb4 的表
SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_COLLATION
FROM information_schema.TABLES
WHERE TABLE_COLLATION NOT LIKE 'utf8mb4%'
  AND TABLE_SCHEMA NOT IN ('mysql','sys','information_schema','performance_schema');

-- 2) 把老表改成 utf8mb4（先小表试，大表走在线 DDL）
ALTER TABLE old_user CONVERT TO CHARACTER SET utf8mb4
  COLLATE utf8mb4_0900_ai_ci;

-- 3) 新表一律显式指定，不靠继承
CREATE TABLE new_user (
  id BIGINT PRIMARY KEY,
  name VARCHAR(50)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

加分点：记住"四层都要对齐"：服务端 character_set_server、库、表、连接。任何一层漏网都可能出乱码，排查时从这四层逐个 SHOW VARIABLES/SHOW CREATE TABLE 查。

坑 2：把 utf8 当成真正的 UTF8

场景 / 错误示范：

-- 以为 utf8 就是完整的 UTF-8，建表用了 utf8
CREATE TABLE t (
  name VARCHAR(100)
) CHARSET=utf8;
-- 在 MySQL 里 utf8 = utf8mb3，最多每字符 3 字节

为什么是坑：MySQL 里的 utf8 历史上是 utf8mb3 的别名，最多存 3 字节/字符，而真正的 Unicode 辅助平面字符（emoji、生僻字）要 4 字节。用 utf8mb3 存 4 字节字符会直接报错或截断。这是 MySQL 著名的历史包袱——名字叫 utf8，却不是完整的 UTF-8。

正确做法：

-- 永远用 utf8mb4（完整 UTF-8，支持 4 字节 emoji）
CREATE TABLE good_t (
  name VARCHAR(100)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- 8.0 已经把默认字符集从 utf8mb3 改成 utf8mb4，但老库老表仍是 utf8mb3，要巡检

-- 巡检：找出还是 utf8mb3 的列
SELECT TABLE_NAME, COLUMN_NAME, CHARACTER_SET_NAME
FROM information_schema.COLUMNS
WHERE CHARACTER_SET_NAME = 'utf8mb3';

加分点：面试必考点——"MySQL 的 utf8 为什么不够用？"答案：它是 utf8mb3，最多 3 字节，emoji 等 4 字节字符要 utf8mb4。生产一律 utf8mb4。

坑 3：emoji 存入报截断

场景 / 错误示范：

-- 用户昵称里带 emoji，插入报错
INSERT INTO t(name) VALUES('小明🐶');
-- ERROR 1366 (HY000): Incorrect string value: '\xF0\x9F\x90\xB6' for column 'name'

为什么是坑：emoji（🐶）是 4 字节字符，落在 utf8mb3 列里放不下，MySQL 直接拒绝写入。根因还是列/表字符集是 utf8mb3，而不是连接或客户端的问题。有些人会去改客户端字符集，方向错了——列本身得是 utf8mb4 才能装下。

正确做法：

-- 把列/表改成 utf8mb4
ALTER TABLE t CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

-- 之后 emoji 正常存入
INSERT INTO t(name) VALUES('小明🐶');
SELECT name FROM t;   -- 正常显示 小明🐶

加分点：改完表字符集后，连接字符集也要是 utf8mb4，否则客户端传回数据库时仍可能在连接层被拦。JDBC 连接串里加 characterEncoding=utf8mb4（或直接 useUnicode=true&characterEncoding=UTF-8）。

坑 4：两表 collation 不一致导致 JOIN 不走索引

场景 / 错误示范：

-- A 表 user_id 是 utf8mb4_general_ci，B 表是 utf8mb4_bin
SELECT a.* FROM order_a a
JOIN user_b b ON a.user_id = b.user_id;
-- EXPLAIN 显示被驱动表 type=ALL，索引没走，还报warning

-- SHOW WARNINGS 里常见：
-- /* select_type = SIMPLE */ /* join_condition = concat(...) collations differ */

为什么是坑：JOIN 不仅要求字符集一致，还要求排序规则 collation 也一致。utf8mb4_general_ci 和 utf8mb4_bin 比较规则不同，MySQL 无法直接用两边的索引做等值匹配，被迫做转换和全表扫。这种性能问题很隐蔽——SQL 写得对、索引也建了，就是慢。

正确做法：

-- 1) 统一两表的排序规则
ALTER TABLE user_b MODIFY user_id VARCHAR(64)
  CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

-- 2) 临时止血：在 JOIN 条件里显式统一（但会影响索引使用，不如统一表结构）
SELECT a.* FROM order_a a
JOIN user_b b
  ON a.user_id = b.user_id COLLATE utf8mb4_general_ci;

加分点：建库时就定死统一 collation（如 utf8mb4_general_ci 或 8.0 的 utf8mb4_0900_ai_ci），全库一致，从根上避免这类 JOIN 失效。排查慢 SQL 时，SHOW WARNINGS 里出现 "illegal mix of collations" 就是这个坑。

坑 5：连接层客户端字符集没设

场景 / 错误示范：

-- 表是 utf8mb4，但连接进来时 client 还是 latin1
mysql> SHOW VARIABLES LIKE 'character_set%';
+--------------------------+--------+
| character_set_client     | latin1 |   -- 客户端发的字节按 latin1 解释
| character_set_connection | latin1 |
| character_set_results    | latin1 |
+--------------------------+--------+

INSERT INTO t VALUES('张三');
-- 表是 utf8mb4，但连接按 latin1 解释这串字节，存进去就是错的

为什么是坑：连接层有三个变量：client（客户端发来的字节按什么编码解释）、connection、results（返回给客户端时转成什么编码）。即便表是 utf8mb4，只要 client 是 latin1，MySQL 会先把"被错误解释为 latin1 的字节"再转成 utf8mb4 存库，等于错误转码两次，中文必乱。这是"表对了但连接没对齐"的典型。

正确做法：

-- 会话级修正（立刻生效）
SET NAMES utf8mb4;
-- 等价于同时设置 client / connection / results 为 utf8mb4

-- 永久生效：写进配置文件

[mysqld]
character_set_server=utf8mb4
[client]
default-character-set=utf8mb4
[mysql]
default-character-set=utf8mb4

加分点：SET NAMES utf8mb4 是连接后第一句要做的事；JDBC/ODBC 连接串里指定编码，比每条 SQL 前 SET 更省心。SHOW VARIABLES LIKE 'character_set%' 排乱码永远从这里看起。

坑 6：latin1 存中文后乱码如何补救

场景 / 错误示范：

-- 历史遗留：表是 latin1，中文早就以"latin1 字节"形式存进去了
SELECT name FROM old_t;
-- 显示：'å¼ ä¸‰'（乱码）

-- 错误补救：直接 ALTER 改成 utf8mb4，结果更乱
ALTER TABLE old_t CONVERT TO CHARACTER SET utf8mb4;

为什么是坑：这些中文当初是以 utf8 字节、却被当成 latin1 存下的。直接 CONVERT TO 会按"latin1 -> utf8mb4"再转一次，等于转码两次，乱上加乱。正确的补救要先把列转回二进制、再用 utf8mb4 解释那批原始字节。这种"转两次"是乱码修复最容易翻车的地方。

正确做法：

-- 正确补救：先转 BINARY，再转 utf8mb4，绕过错误的二次转码
ALTER TABLE old_t
  MODIFY name VARBINARY(100);                 -- 第一步：先转二进制，保留原始字节
ALTER TABLE old_t
  MODIFY name VARCHAR(100) CHARACTER SET utf8mb4;  -- 第二步：按 utf8mb4 解释

-- 更稳妥的写法（用 CONVERT 验证后再落库）：
SELECT CONVERT(BINARY(name) USING utf8mb4) FROM old_t;
-- 先 SELECT 验证能正确显示中文，再 ALTER

加分点：补救前务必 mysqldump 备份整表；先在测试列上验证转换结果正确，再批量改。线上数据修复永远先备份、再小范围试。

坑 7：升级到 8.0 默认字符集变了

场景 / 错误示范：

-- 从 5.7 升到 8.0 后，新建的库/表默认字符集变了
-- 5.7 默认 latin1，8.0 默认 utf8mb4
-- 老代码里"靠默认值"的行为和新环境不一致，跨版本同步数据时 collation 冲突

-- 主从跨版本同步时报：
-- Cannot convert collation ... 5.7 的 utf8mb4_general_ci 与 8.0 的 utf8mb4_0900_ai_ci

为什么是坑：8.0 把默认字符集改成 utf8mb4、默认 collation 改成 utf8mb4_0900_ai_ci，而 5.7 是 utf8mb4_general_ci。跨版本主从、跨版本数据迁移时，两侧 collation 名字不一样，可能触发"illegal mix of collations"。升级后若不统一，老 SQL 和新 SQL 行为也可能微妙不同。

正确做法：

-- 升级后显式统一 collation，别让它停在历史值
SET GLOBAL collation_server = 'utf8mb4_general_ci';  -- 与老库保持一致便于迁移
-- 或在配置里写死：

[mysqld]
character_set_server=utf8mb4
collation_server=utf8mb4_general_ci   -- 跨 5.7/8.0 迁移期用 general_ci 最稳

加分点：跨版本迁移期统一用 utf8mb4_general_ci，等全部升到 8.0 且数据对齐后，再考虑切到 utf8mb4_0900_ai_ci。升级清单里要加一条"核对默认字符集/collation"。

坑 8：导出导入字符集不匹配

场景 / 错误示范：

# 导出时没指定字符集，导入到新库后中文变乱码
shell> mysqldump -uroot -p appdb > app.sql
# 导入：
shell> mysql -uroot -p newdb < app.sql
# 打开 app.sql 一看：里面中文全是乱码——导出客户端连接用了 latin1

为什么是坑：mysqldump 导出的内容是"客户端连接看到的字符"。如果导出时连接字符集不对，dump 文件里的中文在写出那一刻就已经错了，后续导入再怎么调也救不回来。导入侧字符集和导出侧不一致，也会再次转错。

正确做法：

# 导出时显式指定 utf8mb4，并在 dump 里带上 SET NAMES
shell> mysqldump -uroot -p --default-character-set=utf8mb4 appdb > app.sql

# 导入时同样指定 utf8mb4
shell> mysql -uroot -p --default-character-set=utf8mb4 newdb < app.sql

# 大库导出还可加 --set-gtid-purged=OFF 等，字符集这一项别漏

加分点：dump 文件开头应有 /*!40101 SET NAMES utf8mb4 */; 之类语句；导入前 head 看一眼文件里中文是否正常，能早一步发现导出环节就坏了。

坑 9：CONVERT 转码丢字符

场景 / 错误示范：

-- 想把某列转成另一种字符集，结果生僻字/emoji 变 ?
SELECT CONVERT(name USING latin1) FROM t;
-- 原本 小明🐶，转完变成 小明?（emoji 在 latin1 里没有对应，被替换成 ?）

为什么是坑：从大字符集往小字符集转（utf8mb4 -> latin1）时，目标字符集里不存在的字符会被替换成 ?，这是不可逆的丢字符操作。一旦执行 UPDATE ... SET name = CONVERT(name USING latin1)，生僻字就永久丢失，无法恢复。

正确做法：

-- 不要做"大转小"的字符集退化转换
-- 正确方向永远是小转大（latin1 -> utf8mb4），或保持 utf8mb4
SELECT CONVERT(name USING utf8mb4) FROM t;   -- 升级方向，安全

-- 真要跨字符集，先 SELECT 预览，确认没有变成 ? 再 UPDATE
SELECT name, CONVERT(name USING utf8mb4) FROM t WHERE name LIKE '%?%';

加分点：任何 CONVERT(... USING ...) 写进 UPDATE/DELETE 前，先 SELECT 出来肉眼核对，目标字符集装不下的字符会变成 ?，这是不可逆数据损失。

坑 10：把二进制列当字符列用

场景 / 错误示范：

-- 存图片、加密密文却用 VARCHAR
CREATE TABLE secret (id INT, token VARCHAR(64));
INSERT INTO secret VALUES (1, 0xA1B2C3D4);   -- 二进制字节被当文本解释

为什么是坑：二进制数据（密文、图片、文件字节）没有字符语义，用 VARCHAR/TEXT 存储会经过字符集校验和转码，遇到不在字符集里的字节序列就报错或被改写，密文直接损坏。这类数据必须用二进制类型，绕开字符集。

正确做法：

-- 二进制数据用 VARBINARY / BLOB
CREATE TABLE good_secret (
  id INT PRIMARY KEY,
  token VARBINARY(64)      -- 存原始字节，不做字符集解释
);

-- 大文件字节用 BLOB；文件本身更建议放对象存储，库里存路径

加分点：判断标准——"这串字节是不是给人读的文字？"是就用字符类型，否则用二进制类型。密文、哈希、图片、压缩包一律 BLOB/VARBINARY。

坑 11：排序规则 ci 与 bin 混用

场景 / 错误示范：

-- ci（不区分大小写）与 bin（区分大小写）混用，查询结果和预期不符
CREATE TABLE t1 (name VARCHAR(50) COLLATE utf8mb4_general_ci);
CREATE TABLE t2 (name VARCHAR(50) COLLATE utf8mb4_bin);

SELECT * FROM t1 WHERE name = 'Apple';   -- ci：apple/APPLE 都能命中
SELECT * FROM t2 WHERE name = 'Apple';   -- bin：只命中 'Apple'，大小写敏感

为什么是坑：_ci（case insensitive）比较时忽略大小写和重音，_bin 按二进制逐字节比较、严格区分大小写。混用会导致"同一个查询条件，在 A 表命中一堆、在 B 表一个不命中"，业务表现忽对忽错。排序时也一样：ci 把大小写视为相同，bin 把大写排前面还是小写排前面都不同。

正确做法：

-- 全库统一一种 collation，需要区分大小写的列单独处理
ALTER TABLE t2 MODIFY name VARCHAR(50) COLLATE utf8mb4_general_ci;

-- 若某列确实需要大小写敏感（如验证码、哈希），单独声明
CREATE TABLE code_t (
  code VARCHAR(32) COLLATE utf8mb4_bin   -- 只有这一列区分大小写
);

加分点：密码哈希、验证码这类天然大小写敏感的列才用 _bin；普通文本、用户名统一 _ci。混用 collation 还会在 JOIN 时触发"illegal mix of collations"，见坑 4。

坑 12：中文 ORDER BY 排序结果不符合预期

场景 / 错误示范：

-- 按城市名排序，结果不是按拼音/笔画，而是乱七八糟
SELECT city FROM shop ORDER BY city;
-- 输出：北京、上海、广州、深圳（看起来还行？）换一批字就露馅
-- 输出变成："成都"排在"北京"前面，因为 utf8mb4_general_ci 不支持中文拼音排序

为什么是坑：utf8mb4_general_ci 是为欧洲语言设计的通用规则，对中文的排序既不按拼音也不按笔画，而是按 Unicode 码点排。业务上"按拼音字母序排列城市/联系人"这种需求，用默认 collation 排出来根本不对，前端还以为是数据错了。这不是乱码，是"排序规则不认识中文"。

正确做法：

-- 方案一：需要中文拼音排序时，显式指定 collation
SELECT city FROM shop
ORDER BY CONVERT(city USING utf8mb4) COLLATE utf8mb4_zh_0900_as_cs;

-- 方案二：更常见、也更可控——在表里冗余一个"拼音/排序键"列，按它排
ALTER TABLE shop ADD pinyin_key VARCHAR(64);
UPDATE shop SET pinyin_key = CONVERT(city USING utf8mb4);  -- 应用层写入拼音
SELECT city FROM shop ORDER BY pinyin_key;

加分点：中文排序本质是业务需求，数据库 collation 很难两全。生产上最稳的做法是应用层算好拼音/排序键存一列，ORDER BY 它，而不是指望数据库 collation 理解中文。

速查表：字符集乱码速记

| 现象 | 根因 | 对策 |

|---|---|---|

| 入库变 ??? | 列/表是 latin1 或 utf8mb3 | 改 utf8mb4 |

| emoji 报 Incorrect string value | 列是 utf8mb3 装不下 4 字节 | 列改 utf8mb4 |

| JOIN 慢/illegal mix of collations | 两表 collation 不一致 | 统一 collation |

| 表对了中文仍乱码 | 连接层 client 是 latin1 | SET NAMES utf8mb4 |

| ALTER 改字符集后更乱 | latin1 中文被二次转码 | 先 BINARY 再 utf8mb4 |

| dump 文件里中文乱 | 导出连接没指定 utf8mb4 | --default-character-set=utf8mb4 |

| 转码后生僻字变 ? | 大字符集往小转 | 禁止退化转换，先 SELECT 预览 |

| 查询大小写行为不一致 | ci 与 bin 混用 | 全库统一，敏感列单独 bin |

| 中文 ORDER BY 乱序 | general_ci 不按拼音 | 冗余拼音键列排序 |

| 升级 8.0 后跨版本同步报错 | 默认 collation 变为 0900 | 迁移期统一 general_ci |

| 二进制密文存进 VARCHAR 报错 | 二进制被当文本校验 | 改用 VARBINARY/BLOB |

自测：你能避坑吗

题 1：MySQL 的 utf8 和 utf8mb4 有什么区别？为什么生产必须用 utf8mb4？

<details>

<summary>参考答案</summary>

MySQL 的 utf8 是 utf8mb3，最多每字符 3 字节；utf8mb4 才是完整 UTF-8，支持 4 字节的 emoji 和生僻字。生产一律 utf8mb4，否则 emoji 入库会报 Incorrect string value 或被截断。

</details>

题 2：SHOW VARIABLES LIKE 'character_set%' 里 client=gbk、表=utf8mb4，插入中文为什么乱码？

<details>

<summary>参考答案</summary>

连接层 client 决定 MySQL 按什么编码解释客户端发来的字节。client=gbk 时，MySQL 把 utf8 字节当 gbk 解释，再转成 utf8mb4 存库，等于错误转码两次。执行 SET NAMES utf8mb4 并在连接串指定 utf8mb4 即可。

</details>

题 3：一张 latin1 老表里存的中文已经乱码，为什么不能直接 ALTER ... CONVERT TO CHARACTER SET utf8mb4？

<details>

<summary>参考答案</summary>

这些中文是"以 utf8 字节被当成 latin1 存下的"。直接 CONVERT 会按 latin1->utf8mb4 再转一次，二次转码乱上加乱。正确做法：先把列改成 VARBINARY 保留原始字节，再改成 utf8mb4 让 MySQL 按正确编码解释；操作前先备份并 SELECT 验证。

</details>

题 4：两张字符集都是 utf8mb4 的表，JOIN 还是报 collation 冲突，为什么？

<details>

<summary>参考答案</summary>

字符集相同不等于排序规则相同。一张是 utf8mb4_general_ci、另一张是 utf8mb4_bin，比较规则不同就会触发 "illegal mix of collations"，且无法用索引做等值匹配。应统一两表的 collation。

</details>

题 5：为什么说"转码方向只能小转大"？把 utf8mb4 列 UPDATE 成 latin1 会怎样？

<details>

<summary>参考答案</summary>

从大字符集往小字符集转（utf8mb4 -> latin1）时，目标字符集里不存在的字符（emoji、生僻字）会被不可逆地替换成 ?，永久丢数据。永远只做 latin1->utf8mb4 这类升级方向；任何退化转换前必须先 SELECT 预览、再备份。

</details>

题 6：线上中文乱码，排查时第一步该看哪组变量？

<details>

<summary>参考答案</summary>

先执行 SHOW VARIABLES LIKE 'character_set%'，重点看 character_set_client、character_set_connection、character_set_results 是不是 utf8mb4；再看表 SHOW CREATE TABLE 的 CHARSET。三层中任何一层是 latin1/gbk 都可能是乱码源头。常见根因是连接层没对齐，一句 SET NAMES utf8mb4 即可验证。

</details>

下一篇预告：第 44 章 索引使用踩坑