MySQL表混存二进制串与错编码数据如何实现无损转码

你的表中混合了两种不同编码状态的数据，之前写的转换SQL只适配了其中一类，才会导致部分数据丢失：

• 类似id2的éhhh属于双重UTF8编码错误：原本正确的UTF8字节被错误按Latin1编码解析后又二次转成了UTF8存储，你之前用的CONVERT(BINARY CONVERT(my_column USING latin1) USING utf8)逻辑正好可以修复这类数据
• 类似id1的éhhh是正常存储的UTF8二进制数据，再套上面的转码逻辑会触发无效字节截断，最终值被清空

你给出的两组HEX值正好对应这两种状态：

• 正确UTF8编码的é字符HEX为C3A9，和正确值église St Jean-Baptiste的HEX前缀完全匹配
• 双重编码错误的éHEX为C383C2A9，和错误值église St Alban de R.的HEX前缀完全匹配

操作前务必备份全表，避免不可逆的数据损失：

1. 执行备份SQL

CREATE TABLE my_table_backup LIKE my_table;
INSERT INTO my_table_backup SELECT * FROM my_table;

2. 按字节特征分场景转码，不要用统一逻辑全量更新：

UPDATE my_table 
SET my_column = 
  CASE
    -- 匹配双重编码特征的行，执行转码修复
    WHEN HEX(my_column) LIKE '%C383%' THEN CONVERT(BINARY CONVERT(my_column USING latin1) USING utf8)
    -- 已经是正确UTF8编码的行，保留原值不做处理
    ELSE my_column
  END
WHERE LENGTH(my_column) != CHAR_LENGTH(my_column);

更新完成后做两类校验：

• 抽查原双重编码的行，确认é类乱码已经转为正常的é字符
• 抽查原正常存储UTF8值的行，确认值没有被截断、置空
• 对比备份表和原表的总行数、my_column字段非空行数，确认无批量数据丢失

如果表中还混存了其他编码类型的乱码（比如GBK编码内容、其他截断错误），可以在CASE语句中追加对应特征的判断分支，不要直接对所有行套用固定转码逻辑。

内容的提问来源于stack exchange，提问作者jiboulex