MySQL转换函数致数据丢失：如何避免Mojibake转Emoji时误删数据？

问题背景 #

数据库表内Emoji与外文字符全部变为Mojibake乱码，尝试执行以下语句恢复：

UPDATE table SET user_post = convert(cast(convert(user_post using latin1) as binary) using utf8mb4);

该语句多数场景有效，但导致大量数据被删除，同时抛出错误：

Invalid utf8 character string: 'FC6265'

在含50万条帖子的表中，约5万条数据受影响，最终只能回滚恢复数据库。需求为：转换遇到无效字符时避免数据删除，寻找更优的Mojibake还原函数（支持Emoji及正常字符）。

更新1：双重编码问题尝试 #

经HEX测试确认字符为双重编码，尝试以下查询：

UPDATE table SET description = IFNULL(CONVERT(CONVERT(CONVERT(description USING latin1) USING binary) USING utf8mb4), description );

执行后抛出错误并导致产品描述被截断：

Warning: #1300 Invalid utf8mb4 character string: 'A02047'

回滚后尝试ALTER方法（列已为utf8mb4，直接执行步骤3-5）：

Step 3) ALTER TABLE table MODIFY description LONGTEXT CHARSET latin1;
Step 4) ALTER TABLE table MODIFY description LONGBLOB;
Step 5) ALTER TABLE table MODIFY description LONGTEXT CHARSET utf8mb4;

步骤3出现大量警告：

Warning: #1366 Incorrect string value: '\xE2\x86\x91\xE2\x86\x91...' for column 'description' at row 34882
Warning: #1366 Incorrect string value: '\xE2\x86\x91\xE2\x86\x93...' for column 'description' at row 45270
...

步骤5同样出现警告，且描述仍被截断：

Warning: #1366 Incorrect string value: '\xA0our m...' for column 'description' at row 20450
Warning: #1366 Incorrect string value: '\xA0</div...' for column 'description' at row 20484

更新2：硬空格清理尝试 #

为清理A0字符，执行以下语句：

UPDATE table SET description = UNHEX(REPLACE(HEX(description), 'A0', ''));

仍出现错误并导致内容截断：

Warning: #1366 Incorrect string value: '\xC2 GO F...' for column 'description' at row 1

数据库存储HTML格式字符串，推测截断由case.后的硬空格（对应HTML ）导致：

<p><strong><span style="font-size:22px;"><span style="font-family:Arial, Helvetica, sans-serif;">It is covered by the case.  GO FIGURE ???</span></span></strong></p>

更新3：HEX值对比 #

更新前HEX：

3C703E3C7374726F6E673E3C7370616E207374796C653D22666F6E742D73697A653A323270783B223E3C7370616E207374796C653D22666F6E742D66616D696C793A417269616C2C2048656C7665746963612C2073616E732D73657269663B223E4E6F746520746865206C657474657265642065646765206973206E6F742076697369626C652E20497420697320636F76657265642062792074686520636173652EC2A020474F20464947555245203F3F3F3C2F7370616E3E3C2F7370616E3E3C2F7374726F6E673E3C2F703E

更新后HEX：

3C703E3C7374726F6E673E3C7370616E207374796C653D22666F6E742D73697A653A323270783B223E3C7370616E207374796C653D22666F6E742D66616D696C793A417269616C2C2048656C7665746963612C2073616E732D73657269663B223E497420697320636F76657265642062792074686520636173652E

解决方案 #

1. 前置操作：必做备份 #

所有操作前，务必全量备份目标表或数据库，避免不可逆数据损失。

2. 安全转换（避免截断/删除） #

针对MySQL 8.0+版本

使用TRY_CONVERT函数，转换失败时保留原数据：

UPDATE your_table 
SET description = CASE 
    WHEN TRY_CONVERT(CONVERT(CONVERT(description USING latin1) USING binary) USING utf8mb4) IS NOT NULL 
    THEN TRY_CONVERT(CONVERT(CONVERT(description USING latin1) USING binary) USING utf8mb4)
    ELSE description 
END;

针对MySQL 5.7及以下版本

使用UPDATE IGNORE跳过转换错误的行，仅修改可正常转换的数据：

UPDATE IGNORE your_table 
SET description = CONVERT(CONVERT(CONVERT(description USING latin1) USING binary) USING utf8mb4);

或者先验证编码有效性，再执行更新：

UPDATE your_table 
SET description = CONVERT(CONVERT(CONVERT(description USING latin1) USING binary) USING utf8mb4)
WHERE VALIDATE_STRING(CONVERT(CONVERT(description USING latin1) USING binary), 'utf8mb4') = 1;

3. 处理硬空格（C2A0）问题 #

硬空格是UTF-8编码的非断空格（对应HTML ），直接删除A0会破坏编码结构，需整体替换为普通空格：

UPDATE your_table 
SET description = REPLACE(description, UNHEX('C2A0'), ' ');

可结合编码转换，先处理硬空格再恢复乱码：

UPDATE IGNORE your_table 
SET description = CONVERT(CONVERT(REPLACE(CONVERT(description USING latin1), UNHEX('C2A0'), ' ') USING binary) USING utf8mb4);

4. 安全ALTER表方案 #

跳过转latin1的步骤，直接通过BLOB保留原始字节再转回utf8mb4，避免中间编码错误：

-- 先转BLOB，保留原始二进制数据
ALTER TABLE your_table MODIFY description LONGBLOB;
-- 再转utf8mb4，MySQL自动解析字节为正确编码
ALTER TABLE your_table MODIFY description LONGTEXT CHARSET utf8mb4;

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