如何修复大型MySQL数据库中的UTF-8双重编码数据及转码utf8mb4

阿华AIGC实验室

2026-5-21

大规模MySQL数据库编码转换（latin1→utf8mb4）+ 双重编码乱码修复方案

这种大规模的编码转换+乱码修复确实挺头疼的，尤其是涉及70个库、每个库750+表的规模，得稳扎稳打一步步来，我给你梳理一套可落地的方案：

一、先做绝对不能省的备份！

不管操作多熟练，备份都是第一要务——毕竟数据是核心。针对你的场景，推荐分库备份，避免单文件过大：

# 循环导出所有非系统库（替换成你的MySQL用户名密码）
for db in $(mysql -u root -p -e "SHOW DATABASES WHERE Database NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys');" | grep -v Database); do
  mysqldump -u root -p --default-character-set=latin1 --databases $db > ${db}_backup_$(date +%Y%m%d).sql
done

⚠️ 这里一定要加--default-character-set=latin1，避免导出时再次触发编码转换导致乱码。

二、双重编码乱码的核心修复逻辑

你提到的乱码（比如“接近初報”变成“æŽ¥è¿‘åˆå ±”）是典型的双重编码问题：原本的UTF-8字符被当作latin1写入数据库，又被当作UTF-8读取，导致字节被重复解析。修复的关键是把乱码字符串先转回latin1二进制，再重新解析为utf8mb4：

修复单表单字段的SQL语句：

UPDATE `table_name` 
SET `column_name` = IFNULL(CONVERT(CONVERT(`column_name` USING latin1) USING utf8mb4), `column_name`)
WHERE `column_name` IS NOT NULL;

⚠️ 这个修复必须在数据库和表还是latin1编码状态下执行，要是先改了库表编码再修复，就彻底救不回来了！

三、批量处理所有库表的自动化脚本

手动处理70个库+750+表完全不现实，写个自动化脚本搞定：

1. 批量修复所有latin1库的字符型字段

创建一个bash脚本（比如fix_charset_data.sh），循环处理每个库、每个表、每个字符型字段：

#!/bin/bash
USER="root"
PASSWORD="your_mysql_password"

# 获取所有需要修复的latin1编码数据库
DBS=$(mysql -u$USER -p$PASSWORD -N -e "SELECT schema_name FROM information_schema.schemata WHERE default_character_set_name = 'latin1' AND schema_name NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys');")

for DB in $DBS; do
  echo "===== 开始处理数据库：$DB ====="
  # 获取当前库下的所有表
  TABLES=$(mysql -u$USER -p$PASSWORD -N -D$DB -e "SHOW TABLES;")
  for TABLE in $TABLES; do
    echo "--- 处理表：$TABLE ---"
    # 获取表中所有字符型字段（CHAR/VARCHAR/TEXT系列）
    COLUMNS=$(mysql -u$USER -p$PASSWORD -N -D$DB -e "SELECT column_name FROM information_schema.columns WHERE table_schema='$DB' AND table_name='$TABLE' AND data_type IN ('char', 'varchar', 'text', 'tinytext', 'mediumtext', 'longtext');")
    for COLUMN in $COLUMNS; do
      echo "修复字段：$COLUMN"
      # 执行修复SQL（注意反引号转义，避免bash解析错误）
      mysql -u$USER -p$PASSWORD -D$DB -e "UPDATE \`$TABLE\` SET \`$COLUMN\` = IFNULL(CONVERT(CONVERT(\`$COLUMN\` USING latin1) USING utf8mb4), \`$COLUMN\`) WHERE \`$COLUMN\` IS NOT NULL;"
    done
  done
done

⚠️ 要是你的表数据量极大，直接UPDATE可能锁表太久，建议改成分批次更新（比如按主键分段，每次更新1000条），避免影响业务。

2. 批量修改库表编码为utf8mb4

数据修复完成后，再修改库和表的默认编码：
创建脚本change_charset.sh：

#!/bin/bash
USER="root"
PASSWORD="your_mysql_password"

DBS=$(mysql -u$USER -p$PASSWORD -N -e "SELECT schema_name FROM information_schema.schemata WHERE default_character_set_name = 'latin1' AND schema_name NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys');")

for DB in $DBS; do
  echo "===== 更新数据库编码：$DB ====="
  mysql -u$USER -p$PASSWORD -e "ALTER DATABASE \`$DB\` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"
  TABLES=$(mysql -u$USER -p$PASSWORD -N -D$DB -e "SHOW TABLES;")
  for TABLE in $TABLES; do
    echo "--- 更新表编码：$TABLE ---"
    mysql -u$USER -p$PASSWORD -D$DB -e "ALTER TABLE \`$TABLE\` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"
  done
done

四、验证修复结果

操作完成后一定要验证，避免踩坑：

随机抽查数据：找之前乱码的字段，确认是否正常显示（比如“接近初報”是否恢复）

检查编码配置：

-- 检查数据库编码
SELECT schema_name, default_character_set_name FROM information_schema.schemata WHERE schema_name = 'your_db_name';
-- 检查表编码
SELECT table_name, table_collation FROM information_schema.tables WHERE table_schema = 'your_db_name';
-- 检查字段编码
SELECT column_name, character_set_name FROM information_schema.columns WHERE table_schema = 'your_db_name' AND table_name = 'your_table_name';