MySQL 字符集转换怎样定位非法字节

MySQL 字符集转换报错或转换后出现乱码时，先不要直接把整列改成 utf8mb4。真正要定位的是：这列的声明是什么、原始字节实际上来自什么编码、连接层是否又做了一次转换，以及哪些行无法被目标字符集解释。最稳妥的顺序是先看元数据，再用十六进制保留现场，最后在副本上执行转换。

官方资料：https://dev.mysql.com/doc/refman/8.4/en/charset-conversion.html

如果数据只是“被错误标注”，可以通过二进制中转重新解释；如果同一列混有多种来源编码，必须先按行隔离，不能用一次 ALTER TABLE 猜出正确结果。直接转换还可能因为目标字符集不支持某些字符而有损。

这篇按一次旧表迁移的工作流展开：先确认入口，接着定位字节，再决定整列转换还是分流修复，最后把失败样本留下。

先确认列声明和连接字符集

“看起来乱码”不等于“库里存了非法字节”。CHAR、VARCHAR、TEXT 列应该按列字符集存储；BINARY、VARBINARY、BLOB 只保存字节，MySQL 不知道这些字节原来属于哪种编码。先把列定义和当前会话的字符集一起记录下来。

-- 先记录列本身的声明，避免把连接层问题误判成数据损坏
SHOW CREATE TABLE legacy_text;
SHOW FULL COLUMNS FROM legacy_text;

-- 记录查询发送、表达式计算和结果返回分别使用的字符集
SHOW SESSION VARIABLES LIKE 'character_set_%';
SHOW SESSION VARIABLES LIKE 'collation_%';

-- 只在确认客户端实际使用 utf8mb4 后再改变当前连接
SET NAMES 'utf8mb4';

重点看 CHARACTER_SET_NAME、列类型和 character_set_client、character_set_connection、character_set_results。例如列声明为 latin1，但历史程序实际写入的是 GBK 字节，列定义本身没有告诉 MySQL 真正来源；此时直接改为 utf8mb4 就可能得到错误字符。

用 HEX 把非法字节从显示层剥离出来

排查时不要只看客户端显示的文字，因为显示过程已经可能发生转换。对可疑字段同时取 HEX()、LENGTH()、CHAR_LENGTH()，再显式按候选源字符集解释。HEX() 显示每个字节的十六进制形式，适合保存迁移前证据。

-- 小批量取样：保留原始字节，并分别观察按 utf8mb4 解释的结果
SELECT
    id,
    HEX(body) AS raw_hex,
    LENGTH(body) AS byte_length,
    CHAR_LENGTH(body) AS character_length,
    CONVERT(body USING utf8mb4) AS as_utf8mb4
FROM legacy_text
WHERE body IS NOT NULL
ORDER BY id
LIMIT 50;

-- 对刚才的转换语句查看服务器给出的警告
SHOW WARNINGS;

LENGTH() 统计字节数，CHAR_LENGTH() 统计字符数；二者差异本身不能证明非法，但能帮助发现“字段声明和实际内容不匹配”的候选。看到替换字符或警告后，再回看同一行的 raw_hex，不要把替换后的显示结果当成原始数据。

可以用同一批行分别尝试来源字符集，例如 CONVERT(body USING latin1)、CONVERT(body USING gb18030)。这一步只是建立证据，不是让 MySQL 自动选择正确编码；当结果仍有歧义时，把原始字节导出到隔离表或文件，由业务确认来源。

把单一编码和混合编码分开处理

判断标准不是“转换后能显示”，而是同一列的全部值是否遵循同一种来源编码。MySQL 官方文档明确指出，二进制列只有在所有值使用同一种字符集时才能安全转换；非二进制列如果实际内容属于其他字符集，可以先转成二进制列，再按正确字符集转回非二进制列。

如果确认整列的字节都来自同一种编码，先在副本上采用二进制中转，保留原始字节，再恢复为目标字符集：

-- 在副本表上操作：先去掉错误的字符集解释，不改动字节本身
ALTER TABLE legacy_text_copy
    MODIFY body BLOB;

-- 再按已确认的来源编码解释，并转换到目标字符集
ALTER TABLE legacy_text_copy
    MODIFY body TEXT CHARACTER SET utf8mb4;

-- 转换后保留关键列属性；真实表还要补回 NOT NULL、DEFAULT 等定义
SHOW CREATE TABLE legacy_text_copy;

上面的写法适合“整列其实都是同一种来源编码，只是列声明错了”的场景。若旧列中一部分是 latin1、一部分是 GBK，或新旧程序在升级后混写，就不能把所有行放进同一条 ALTER。先复制 id、原始字节和来源判断到隔离表，分别修复后再回写。

转换前设置失败门禁

字符集转换属于结构变更，不能只依赖一条成功返回。先做副本、保留备份和回滚点；把严格模式、目标字符集、列属性和影响范围写进迁移记录。目标字符集不包含源字符时，转换可能有损，因此“没有 SQL 错误”也不代表数据完整。

-- 先确认目标字符集存在，并记录其默认排序规则
SHOW CHARACTER SET LIKE 'utf8mb4';
SHOW COLLATION LIKE 'utf8mb4%';

-- 在维护连接中启用严格模式，减少静默截断或替换
SET SESSION sql_mode = 'STRICT_TRANS_TABLES';

-- 全表转换时明确目标字符集；执行前确认已保留原列属性
ALTER TABLE legacy_text_copy
    CONVERT TO CHARACTER SET utf8mb4
    COLLATE utf8mb4_0900_ai_ci;

如果只是改列或表的字符集/排序规则，MySQL 8.4 文档提示这类 ALTER TABLE 需要使用 ALGORITHM=COPY。这意味着要预估锁表、空间和执行窗口，不能把它当作无阻塞的小配置修改。

失败后怎么留下可复用的定位结果

把失败拆成三类，处理速度会快很多：

• 列声明错误：字节本身属于一种编码，只是 CHAR/VARCHAR 的字符集写错，优先考虑 BLOB 中转。
• 原始字节非法：按候选编码解释仍有错误或警告，保存 id、HEX(body) 和来源系统，交给数据修复流程。
• 连接字符集错误：库中数据正常，但客户端把结果按错误字符集发送或显示，修复连接参数，不要改表结构。

迁移记录至少保留旧列定义、目标字符集、抽样行的十六进制值、警告信息、隔离行数和回滚位置。这样下一次遇到“转换后乱码”，可以先复用证据，不必再次猜测。

常见问题

为什么把 latin1 改成 utf8mb4 后仍然乱码？

很可能原始字节从未按 latin1 写入，或者客户端连接字符集仍然不匹配。先看 HEX() 和会话字符集，不要连续尝试多个 ALTER。

能不能只用 CONVERT(body USING utf8mb4) 修复数据？

它适合取样和表达式级转换，不会自动修复混合编码，也不应替代迁移前的备份、隔离和属性核对。

为什么转换成功还要检查原列属性？

用 ALTER TABLE ... MODIFY 重写列定义时，如果没有再次写出 NOT NULL、默认值等属性，结果定义可能丢掉这些约束。

总结

定位 MySQL 字符集转换的非法字节，可以记住三句话：先看列和连接的声明，再用 HEX() 观察未解释的字节，最后按单一编码或混合编码选择整列转换、二进制中转或隔离修复。只要保留原始字节和迁移前证据，就能把“乱码”变成可定位、可回滚的问题。