解決 MySQL "Illegal mix of collations" 編碼衝突錯誤

錯誤現象

Error: Illegal mix of collations (utf8_general_ci,IMPLICIT) and (gbk_chinese_ci,COERCIBLE) for operation '='
Errno: 1267

這是典型的字符集校對規則衝突：SQL 語句中涉及的兩個字符串（或列、參數）分別屬於 utf8_general_ci 和 gbk_chinese_ci 校對規則，MySQL 無法隱式轉換比較。

根因分析

• 數據庫/表/列級別：建表時未顯式指定字符集，繼承了服務器默認字符集 (可能是 latin1 或 utf8)，導致列校對規則不一致。
• 連接層級：應用連接時未設置字符集，客戶端發送的字符串按默認字符集 (如 gbk) 編碼，而表列為 utf8。
• 字符串字面量：SQL 中直接寫入的中文常量，其校對規則取決于連接的 collation_connection。

解決方案

方案一：統一連接字符集 (最推薦，治本)

在應用連接池配置或連接初始化時，顯式執行以下命令，強制統一會話級字符集：

SET NAMES utf8mb4 COLLATE utf8mb4_general_ci;
-- 等價於以下組合：
SET character_set_client = utf8mb4;
SET character_set_connection = utf8mb4;
SET character_set_results = utf8mb4;
SET collation_connection = utf8mb4_general_ci;

建議： 統一使用 utf8mb4 (支援 Emoji、完整 Unicode) 而非老旧的 utf8 (實為 utf8mb3)。

方案二：修正表/列字符集定義 (治標)

若歷史表已為 gbk，需轉換為 utf8mb4：

-- 1. 修改表默認字符集 (僅影響新增列)
ALTER TABLE tbl_name DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

-- 2. 轉換現有列數據 (關鍵步驟，會重建表)
ALTER TABLE tbl_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

-- 3. 單獨修改某列
ALTER TABLE tbl_name MODIFY col_name VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

方案三：SQL 中顯式轉換 (應急)

-- 在 WHERE/JOIN/比較處強制轉換
SELECT * FROM t1 JOIN t2 ON t1.col = CONVERT(t2.col USING utf8mb4);
-- 或指定校對規則
SELECT * FROM t WHERE col = '中文' COLLATE utf8mb4_general_ci;

最佳實踐檢查清單

1. 服務器配置：my.cnf 設置 character-set-server=utf8mb4, collation-server=utf8mb4_general_ci。
2. 建表規範：強制要求 CREATE TABLE ... DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci。
3. 連接配置：JDBC URL 加 ?characterEncoding=utf8&connectionCollation=utf8mb4_general_ci；Python/MySQLdb 連接參數 charset='utf8mb4'；Go sqlx SET NAMES utf8mb4。
4. 避免混用：嚴禁在同一實例混存 gbk/utf8 表，杜絕隱式轉換。