背景與挑戰

• 數百台 MySQL 歷史編碼 latin1（實際存儲 UTF-8 位元組流）。
• 需同步至統一匯總庫，再推送至 Oracle (ZHS16GBK) 並由 Java (UTF-8) 前端展示。
• 資料含中文、火星文（生僻字、Emoji），直接 SET NAMES utf8 讀取會亂碼。

編碼知識速查

• latin1 (ISO-8859-1)：單字節、無損透傳任意位元組流，MySQL 預設編碼利用此特性「不解碼直接存」。
• GBK/GB18030：雙字節中文編碼，GB18030 涵蓋生僻字。
• UTF-8/utf8mb4：變長 1-4 字節，Unicode 標準，Java/Web 標配。

方案演進路線

❌ 方案一：Java 端 latin1→GBK→UTF-8

部分火星字無法在 GBK 表示，顯示 ?。

❌ 方案二：Java 端 latin1→UTF-8 直接轉

MySQL 以 latin1 解碼後的字串非合法 UTF-8，轉碼失敗全亂碼。

❌ 方案三：mysqldump latin1 導出 → iconv latin1→UTF-8

iconv -f latin1 -t UTF-8 無法正確處理「以 latin1 存儲的 UTF-8 位元組」。

✅ 方案四：mysqldump latin1 導出 → iconv GB18030→UTF-8（關鍵洞察）

Linux 終端下以 GB18030 讀取 latin1 導出檔，實際能正確還原中文與火星字，再轉 UTF-8 入庫。

標準化遷移流程

1. 邏輯備份（保持 latin1 解碼）：

   mysqldump --default-character-set=latin1 -q --single-transaction \
     -t db_collect table1 table2 > db_collect.sql
   

2. 建立目標庫與表（utf8mb4）：

   CREATE DATABASE db_collect2 DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci;
   -- 在 db_collect2 執行 db_collect.sql 的結構部分（去除數據），或用 SHOW CREATE TABLE 重建
   

3. Linux 環境轉碼入庫：

   export LANG=en_US
   export CRT=default
   sed -i 's/latin1/utf8mb4/g' db_collect.sql
   iconv -f gb18030 -c -t UTF-8 db_collect.sql -o db_collect_utf8.sql
   mysql -f db_collect2 < db_collect_utf8.sql
   

4. 驗證：SELECT * FROM table1 WHERE name LIKE '%😀%'; 確認 Emoji 正常。

生產級增量同步：PHP + iconv 腳本

<?php
$src = mysqli_connect('10.0.0.1', 'root', 'pwd', 'db_collect');
$dst = mysqli_connect('10.0.0.2', 'root', 'pwd', 'db_collect2');
mysqli_set_charset($src, 'latin1');   // 關鍵：以 latin1 讀取
mysqli_set_charset($dst, 'utf8mb4');  // 寫入 utf8mb4
mysqli_query($dst, "SET NAMES utf8mb4");

$res = mysqli_query($src, "SELECT id, name FROM table1");
while ($row = mysqli_fetch_assoc($res)) {
    $name_utf8 = iconv('GB18030', 'UTF-8//IGNORE', $row['name']);
    $name_utf8 = mysqli_real_escape_string($dst, $name_utf8);
    mysqli_query($dst, "INSERT INTO table1 (id, name) VALUES ({$row['id']}, '$name_utf8')");
}
mysqli_close($src); mysqli_close($dst);

排程執行：export LANG=en_US.UTF-8 && php /opt/sync_latin1_to_utf8.php

遷移後收尾

• 修正應用連線字串 characterEncoding=utf8mb4。
• 調整 my.cnf character-set-server=utf8mb4。
• 清理臨時表、備份檔。