MySQL 字符集衝突 (Illegal mix of collations) 根因與徹底解決方案

報錯 `ERROR 1267 (HY000): Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (latin1_swedish_ci,COERCIBLE) for operation '='` 是字符集不一致的經典表現。本文從校對規則優先級原理出發，給出徹底解決方案與預防體系。

核心原理：校對規則強制提升規則

MySQL 解析表達式時，根據 **校對規則強制提升規則** 決定結果校對規則：

1. **Explicit (顯式)**：`COLLATE` 子句指定 → 最高優先級
2. **Implicit (隱式)**：欄位/變數/函數返回值自帶校對規則
3. **Coercible (可強制)**：字串常數、用戶變數、函數參數 → 最低優先級，會被隱式/顯式規則「吸收」

**衝突發生條件**：同一表達式中出現 **不同優先級且不兼容** 的校對規則，且無法按規則自動提升統一。

典型觸發場景

解決方案：按優先級從高到低

方案一：顯式指定 COLLATE (最精準、影響面最小)

-- 查詢層面修正
SELECT * FROM t1 JOIN t2 ON t1.col COLLATE utf8mb4_unicode_ci = t2.col COLLATE utf8mb4_unicode_ci;

-- 常數比較
WHERE col_utf8 = _utf8mb4'literal'   -- 引入符 _charset'str' 指定常數字符集
WHERE col_utf8 = 'literal' COLLATE utf8mb4_unicode_ci

方案二：統一連線字符集 (應用層根治)

-- 連線建立後立即執行 (所有驅動支援)
SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci;
-- 等效於：
SET character_set_client = utf8mb4;
SET character_set_connection = utf8mb4;
SET character_set_results = utf8mb4;
SET collation_connection = utf8mb4_unicode_ci;

**效果**：連線層面統一後，字串常數、用戶變數、函數參數 (Coercible) 會自動提升為連線校對規則，消絕 90% 衝突。

方案三：統一表/欄位字符集 (存儲層根治)

-- 整表轉換 (資料實際重新編碼)
ALTER TABLE tbl_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 僅修改預設 (不轉換現有資料，慎用)
ALTER TABLE tbl_name DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 單欄位轉換
ALTER TABLE tbl_name MODIFY col_name VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

方案四：Server 預設配置 (基建層)

# my.cnf
[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
init_connect = 'SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci'
skip-character-set-client-handshake  # 強制忽略客戶端宣告

驗證與診斷指令

-- 1. 檢查 Server/資料庫/表/欄位/連線 當前字符集
SHOW VARIABLES LIKE 'character_set_%';
SHOW VARIABLES LIKE 'collation_%';

-- 2. 檢查具體表/欄位
SHOW CREATE TABLE tbl_name\G
SHOW FULL COLUMNS FROM tbl_name;

-- 3. 檢查常數/函數/變數 校對規則
SELECT COLLATION('literal'), COLLATION(USER()), COLLATION(@var);

-- 4. 找出庫內非 utf8mb4 表
SELECT TABLE_NAME, TABLE_COLLATION FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'my_db' AND TABLE_COLLATION NOT LIKE 'utf8mb4%';

預防體系：零容忍清單

1. ✅ **新專案強制 utf8mb4**：建庫、建表、連線、驅動、IDE 檔案編碼全鏈路統一。
2. ✅ **遷移存量**：`pt-online-schema-change` + `CONVERT TO CHARSET utf8mb4` 線上無鎖轉碼。
3. ✅ **CI/CD 靜態檢查**：`grep -r "latin1\|gbk\|utf8[^mb4]" sql/ schema/` 禁止合併。
4. ✅ **監控告警**：`SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE CHARACTER_SET_NAME != 'utf8mb4' AND TABLE_SCHEMA NOT IN ('mysql','sys','performance_schema','information_schema');` 非零即報警。
5. ✅ **文檔規範**：團隊《資料庫開發規範》明文規定：字符集僅允許 utf8mb4，校對規則預設 utf8mb4_unicode_ci (或 0900_ai_ci)。

避坑提醒

• ❌ **不要用 `utf8` (即 `utf8mb3`)** — 不支援 Emoji、補充平面字符，且 MySQL 8.0 已棄用警告。
• ❌ **不要混用 `_ci` (不分大小寫) 和 `_bin` (二進位比較)** — 除非業務明確需要精確比較 (如密碼雜湊驗證)，統一 `_ci` 或 `_ai_ci`。
• ❌ **不要依賴隱式轉換** — 顯式 `COLLATE` / `_charset` 引入符 讓意圖可見、可維護。
• ⚠️ **MySQL 8.0 預設校對規則變更**：`utf8mb4_0900_ai_ci` (Unicode 9.0、accent/case insensitive) 取代 `utf8mb4_general_ci`。新建表建議用 `0900_ai_ci`，遷移存量可保持 `unicode_ci` 兼容。

字符集衝突本質是**「協議雙方未達成一致」**。從 Server 預設、連線初始化、建表規範、CI 攔截四層防線同步推進，才能徹底消滅 `Illegal mix of collations`，讓團隊專注於業務價值而非編碼糾纏。