MySQL 字符集衝突錯誤 (Illegal mix of collations) 徹底解決指南

錯誤現場重現

Illegal mix of collations (gbk_chinese_ci,IMPLICIT)
and (gbk_bin,IMPLICIT) for Operation '='
SQL State: HY000, Error Code: 1267

經典場景：資料庫預設 gbk_chinese_ci，但某資料表或欄位被建立為 gbk_bin，JOIN 或 WHERE 比較時引發衝突。

根因剖析

• 層級不一致：Server → Database → Table → Column 四層字符集可獨立指定，任一層不匹配即可觸發。
• 隱式衍生：未顯式指定時，下層繼承上層預設；若中間環節被手動修改，導致鏈路斷裂。
• Collation 優先級：_bin (二進位比較) vs _ci (大小寫不敏感) 屬於不同 Collation 家族，MySQL 5.1+ 拒絕隱式轉換。

三步徹底修復

1. 診斷當前狀態

SHOW VARIABLES LIKE 'character_set%';
SHOW VARIABLES LIKE 'collation%';
SELECT TABLE_NAME, TABLE_COLLATION FROM information_schema.TABLES WHERE TABLE_SCHEMA='your_db';
SELECT COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME FROM information_schema.COLUMNS WHERE TABLE_SCHEMA='your_db' AND TABLE_NAME='your_table';

2. 統一字符集 (推薦 UTF8MB4)

-- 修改資料庫預設
ALTER DATABASE your_db CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

-- 修改資料表預設 (僅影響新增欄位)
ALTER TABLE your_table CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

-- 修改現有欄位 (需逐欄位轉換數據)
ALTER TABLE your_table
  CHANGE col_name col_name VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL;

注意：大表轉換會鎖表，建議使用 pt-online-schema-change 或維護窗口操作。

3. 快速臨時方案 (不改結構)

-- 查詢層級強制指定
SELECT * FROM t1 JOIN t2 ON t1.id = t2.id COLLATE utf8mb4_unicode_ci;

-- 或連線層級設定
SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci;

最佳實踐：從源頭杜絕

1. 配置文件鎖定：my.cnf 強制 character-set-server=utf8mb4、collation-server=utf8mb4_unicode_ci、skip-character-set-client-handshake。
2. 建表規範：所有 CREATE TABLE 必須顯式帶 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci。
3. 連線驗證：應用啟動檢查 SHOW SESSION VARIABLES LIKE 'character_set%' 確保一致。

總結

字符集衝突本質是「編碼契約不一致」。統一全鏈路為 utf8mb4 + utf8mb4_unicode_ci，並在配置、建表、連線三環節加鎖，即可一次性根治 1267 錯誤，同時完美支援 Emoji 與多語言。