MySQL 字符集遷移全攻略：latin1 → utf8/gbk、版本升降級、亂碼根因與工具鏈

為什麼會亂碼？

MySQL 4.1+ 引入四層字符集體系：server → database → table → connection。預設編譯/安裝/啟動參數皆為 latin1（ISO-8859-1），導致：

• 應用以 UTF-8/GBK 寫入，伺服器以 latin1 解碼存儲 → 存進去就是錯誤碼點。
• 讀取時再按 latin1 解碼 → 雙重錯誤偶爾「巧合」顯示正常，實則資料已壞。
• mysqldump 導出時未指定 --default-character-set，進一步加劇碼點錯位。

核心參數：SET NAMES

SET NAMES 'gbk';  -- 相當於
SET character_set_client = gbk;
SET character_set_connection = gbk;
SET character_set_results = gbk;

應用連線建立後第一條 SQL必須執行此語句，確保「連線層」編碼與應用一致。

永久生效：my.cnf / my.ini 雙區段配置

[mysqld]
default-character-set = utf8   # 5.5+ 建議 utf8mb4
collation-server = utf8_general_ci

[mysql]          # 客戶端工具 (mysql, mysqladmin, mysqldump 等)
default-character-set = utf8

重啟服務生效。注意：[client] 區段同樣影響連線層，建議同步設定。

存量資料庫字符集轉換：兩大方案

方案 A：mysqldump 導出轉碼再導入（推薦，適合大庫）

# 1. 以 latin1 讀取，輸出為 gbk（或 utf8）
mysqldump -uroot -p --default-character-set=latin1 \
  --set-charset=gbk --skip-opt db_name > db_gbk.sql

# 2. 建立目標字符集資料庫
mysql -uroot -p -e "CREATE DATABASE db_name DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci;"

# 3. 以 gbk 導入
mysql -uroot -p --default-character-set=gbk -f db_name < db_gbk.sql

參數解析：--default-character-set=latin1 告訴 dump「源資料按 latin1 讀」；--set-charset=gbk 在輸出檔寫入 SET NAMES gbk；--skip-opt 關閉預設優化（避免 SET NAMES latin1 干擾）。

方案 B：文本編輯器直接替換 .sql 檔（僅適合小庫）

1. 用 UltraEdit/VS Code 開啟導出檔。
2. 全域替換 DEFAULT CHARSET=latin1 → DEFAULT CHARSET=gbk（或刪除該子句，依賴庫級預設）。
3. 逐表替換欄位級 CHARACTER SET latin1 → CHARACTER SET gbk。
4. 特殊表 cdb_sessions (HEAP) 建議刪除該段，重新安裝後再導入。
5. 存檔導入。

缺點：大檔案編輯器易崩潰、易漏替、不易自動化。

版本升級 / 降級實戰

4.0 → 4.1/5.0 升級

• 不可直接拷貝 data 目錄：4.0 無字符集元數據，4.1+ 會誤判為 latin1，導致 VARCHAR/CHAR 長度變半（中文容量不變，英文容量減半）。
• 必須 mysqldump 導出 → 建立正確字符集庫 → 導入。

4.1/5.0 → 4.0 降級

mysqldump -uroot -p --default-character-set=latin1 \
  --set-charset=gbk --skip-opt --compatible=mysql40 db_name > db_40.sql

--compatible=mysql40 生成 4.0 相容 SQL（無 ENGINE=、無 CREATE TABLE ... SELECT 等）。

常見坑與檢查清單

工具鏈建議

• 遷移驗證：pt-table-checksum (Percona Toolkit) 比對源/目標資料一致性。
• 線上遷移：gh-ost / pt-online-schema-change 避免鎖表。
• 編碼檢測：SELECT HEX(col), CHARSET(col) FROM t LIMIT 10 直觀查看碼點。

總結

字符集遷移核心三步：統一連線層 → 正確導出轉碼 → 目標庫正確字符集導入。版本跨度大時務必加 --compatible。生產環境嚴禁直接拷貝 data 目錄升級。建立「連線即 SET NAMES」開發規範，從源頭杜絕亂碼。