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

核心成因：四層字符集繼承鏈斷裂

Server (character_set_server, collation_server)
    ↓ 默認繼承
Database (DEFAULT CHARACTER SET, COLLATE)
    ↓ 默認繼承
Table (DEFAULT CHARACTER SET, COLLATE)  ← 只影響新增欄位
    ↓ 默認繼承
Column (CHARACTER SET, COLLATE)         ← 實際存儲編碼

當 Table/Column 顯式指定了與 Database 不同的 Collation (如 gbk_bin vs gbk_chinese_ci)，JOIN/WHERE 比較觸發隱式轉換失敗。

診斷腳本 (一鍵定位不一致)

-- 庫級
SELECT SCHEMA_NAME, DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME
FROM information_schema.SCHEMATA WHERE SCHEMA_NAME='your_db';

-- 表級
SELECT TABLE_NAME, TABLE_COLLATION
FROM information_schema.TABLES WHERE TABLE_SCHEMA='your_db' AND TABLE_COLLATION != 'gbk_chinese_ci';

-- 欄位級
SELECT TABLE_NAME, COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA='your_db' AND COLLATION_NAME != 'gbk_chinese_ci';

修復策略：統一遷移至 utf8mb4 (推薦) 或 統一 gbk

方案 A：全鏈路 utf8mb4 (現代標準，支援 Emoji)

-- 1. 修改庫默認
ALTER DATABASE your_db CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

-- 2. 修改表默認 (僅影響後續新增欄位)
ALTER TABLE your_table CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

-- 3. 逐欄位轉換現有數據 (大表必須用 pt-online-schema-change)
ALTER TABLE your_table
  CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
  -- 或逐欄位精細控制：
  -- CHANGE col_name col_name VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL;

方案 B：臨時兼容 gbk (不改結構，僅查詢層面)

-- 會話級強制統一
SET NAMES gbk COLLATE gbk_chinese_ci;
-- 或單條 SQL 強制
SELECT * FROM t1 JOIN t2 ON t1.id = t2.id COLLATE gbk_chinese_ci;

配置文件鎖定 (源頭治理)

[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
skip-character-set-client-handshake  -- 強制忽略客戶端發送的字符集
init_connect = 'SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci'

應用層最佳實踐

1. 連接池初始化執行 SET NAMES utf8mb4。
2. ORM 配置 charset: utf8mb4 (如 Django DATABASES['OPTIONS']['charset']、Laravel database.connections.mysql.charset)。
3. CI/CD 流水線加入 mysql -e "SHOW SESSION VARIABLES LIKE 'character_set%'" 斷言檢查。

總結

字符集衝突本質是「契約不一致」。**統一全棧為 utf8mb4 + utf8mb4_unicode_ci**，並在 my.cnf、建表 DDL、連接池初始化三處加鎖，即可一次性根治 1267 錯誤，同時擁抱完整 Unicode 支援。