从MySQL 5.7迁移至8.0时latin1字符集数据乱码问题求助

核心问题分析 #

你的情况属于典型的**“隐式UTF-8存储”**：MySQL 5.7中字段字符集是latin1，但应用实际是把UTF-8编码的字节直接写入字段（因客户端与数据库字符集均为latin1，MySQL未做编码转换）。升级到8.0后直接修改字段字符集为utf8mb4时，MySQL仅更新字段的字符集标记，未将原存储的UTF-8字节重新解析为utf8mb4字符串，导致乱码。

修复步骤 #

1. 先备份表 #

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

CREATE TABLE stores_backup LIKE stores;
INSERT INTO stores_backup SELECT * FROM stores;

2. 批量转换字段数据 #

对有问题的字符串字段（比如format）执行以下更新，将存储的UTF-8字节正确解析为utf8mb4字符串：

UPDATE stores 
SET format = CONVERT(CAST(format AS BINARY) USING utf8mb4);

• CAST(format AS BINARY)：忽略字段当前的字符集标记，直接取出原始字节。
• CONVERT(...) USING utf8mb4：把这些原始字节按utf8mb4编码解析为正常字符串。

3. 验证修复结果 #

执行查询确认数据是否恢复正常：

SELECT id, format, HEX(format) FROM stores;

正常情况下，€符号的HEX值应为E282AC，显示内容为€{{amount_with_comma_separator}}。

4. 处理非法字节（可选） #

如果执行更新时出现Illegal character错误，说明部分数据的字节不是合法的UTF-8。可以使用IGNORE参数跳过非法字节（会丢失对应字符，谨慎使用）：

UPDATE stores 
SET format = CONVERT(CAST(format AS BINARY) USING utf8mb4 IGNORE);

后续配置 #

• 应用连接编码：确保PHP/Node.js连接数据库时指定utf8mb4编码：
  • PHP：mysqli_set_charset($conn, 'utf8mb4')
  • Node.js：连接字符串添加charset=utf8mb4（如mysql://user:pass@host/db?charset=utf8mb4）
• 数据库全局配置：将RDS MySQL的默认字符集设置为utf8mb4，排序规则设为utf8mb4_900_ai_ci，避免新表再出现编码问题。

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