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，表創建時未指定，字段隱式繼承不同校對。
• 連接層字符集與存儲層不匹配，隱式轉換產生衝突。
• JOIN、UNION、子查詢涉及不同校對規則的列。

修復方案

1. 顯式指定表級字符集（推薦）

CREATE TABLE `teachers` (
  `id` int(11) NOT NULL DEFAULT '0',
  `name` varchar(20) DEFAULT NULL,
  `password` varchar(20) DEFAULT NULL,
  `department_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=gbk;

DEFAULT CHARSET=gbk 會將表級默認校對設為 gbk_chinese_ci，字段無顯式 COLLATE 時自動繼承，消除混合。

2. 不指定字符集（風險較高）

CREATE TABLE `teachers` (
  `id` int(11) NOT NULL DEFAULT '0',
  `name` varchar(20) DEFAULT NULL,
  `password` varchar(20) DEFAULT NULL,
  `department_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
);

完全依賴數據庫默認字符集，若庫級設置變更或連接層干擾，極易復發。

現代最佳實踐：全鏈路 utf8mb4

-- 庫
CREATE DATABASE `db` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- 表
CREATE TABLE `t` (...) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- 連接
SET NAMES utf8mb4;
-- 或驅動層配置 characterEncoding=utf8mb4

• utf8mb4 支持完整 Unicode（含 Emoji、生僻字）。
• utf8mb4_unicode_ci 基於 UCA 排序，比 _general_ci 更準確。
• 統一字符集消除隱式轉換，徹底根治「Illegal mix of collations」。

排查檢查清單

1. SHOW VARIABLES LIKE 'character_set%'; 確認各層設置。
2. SHOW CREATE TABLE t; 檢查表/字段級字符集。
3. SELECT CHARSET(col), COLLATION(col) FROM t LIMIT 1; 確認實際列屬性。
4. 應用連接串顯式指定 characterEncoding。
5. 避免在同一 SQL 中混用不同校對列進行比較/連接。