MySQL 字符集 GBK 升級 UTF8MB4 實戰：排序規則變更影響、數據遷移步驟與兼容性測試

MySQL 字符集 GBK 升級 UTF8MB4 實戰

生產環境因業務國際化需將字符集從 GBK 升級至 UTF8MB4。本文聚焦**升級後排序規則 (Collation) 變更對業務的影響**，結合源碼分析原理，給出安全遷移步驟與驗證方案。

核心影響：排序順序改變

GBK 與 UTF8MB4 的字符編碼順序不同，導致 ORDER BY、GROUP BY、索引範圍掃描、BETWEEN、LIKE 範圍查詢結果可能改變。

測試用例對比

-- 建表
CREATE TABLE cmp_t (c1 VARCHAR(20)) CHARSET=gbk;
CREATE TABLE cmp_t2 (c1 VARCHAR(20)) CHARSET=utf8mb4;

-- 插入測試字符
INSERT INTO cmp_t VALUES ('a'), ('中'), ('文'), ('A'), ('1');
INSERT INTO cmp_t2 VALUES ('a'), ('中'), ('文'), ('A'), ('1');

-- 查看排序
SELECT c1, HEX(c1) FROM cmp_t ORDER BY c1;   -- GBK 排序
SELECT c1, HEX(c1) FROM cmp_t2 ORDER BY c1;  -- UTF8MB4 排序

預期差異 (源碼級原理)

• GBK (雙字節) 排序： 按字節值比較，漢字編碼區間 (0x8140-0xFEFE) **大於** ASCII (0x00-0x7F) → 順序：1, A, a, 中, 文。
• UTF8MB4 (多字節) 排序： 按 Unicode 碼點比較，數字 < 英文大寫 < 英文小寫 < CJK 統一表意文字 → 順序：1, A, a, 中, 文 (此處巧合一致，但擴展字符、生僻字、Emoji 會有巨大差異)。
• Collation 細節： gbk_chinese_ci vs utf8mb4_general_ci / utf8mb4_unicode_ci (UCA 算法)，對拼音、聲調、大小寫敏感度處理不同。

安全遷移四步法

1. 全量備份 + 驗證還原 (必做)。
2. 影子表驗證： 建立同結構 UTF8MB4 表，導入樣本數據，跑核心業務 SQL 對比結果集 (特別關注 ORDER BY、分頁、範圍查詢)。
3. 轉換執行 (低峰期)：

   ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

   CONVERT TO 會重建表並轉換數據，CHARSET= 僅修改元數據**不轉換數據** (會導致亂碼)。

4. 全鏈路驗證： 應用層連接串加 charset=utf8mb4，重啟服務，跑回歸測試。

大表優化策略 (pt-online-schema-change)

pt-online-schema-change \
  --alter="CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci" \
  D=db,t=big_table \
  --execute --max-load="Threads_running=100" --critical-load="Threads_running=200"

避免鎖表長時間阻塞寫入，利用觸發器同步增量數據。

兼容性檢查清單

• ☐ 索引長度限制：UTF8MB4 單字符 4 字節，VARCHAR(255) 索引可能超 767 字節限制 (MySQL 5.6 / innodb_large_prefix=OFF) → 需縮短欄位或開啟 innodb_large_prefix。
• ☐ 應用層字符串長度截斷邏輯 (按字節 vs 字符)。
• ☐ 正則表達式、LIKE 模式匹配行為變化。
• ☐ 外鍵關聯表必須**同步轉換**字符集，否則報錯。

結語： 字符集升級非單純 ALTER TABLE，**排序規則變更**是隱形殺手。務必以影子表驗證核心查詢語義不變，再配合 PT-OSC 平滑落地。