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

根因分析

資料庫預設校對規則與建表指定校對規則不一致，導致字串比較操作 (=、LIKE、JOIN ON 等) 衝突。常見場景：

• 資料庫建立時預設 gbk_chinese_ci (不區分大小寫)
• 建表未指定 CHARSET/COLLATE，繼承資料庫預設
• 某欄位明確指定 COLLATE gbk_bin (二進位、區分大小寫)
• 查詢中該欄位與其他 gbk_chinese_ci 欄位/常數比較 → 校對衝突

三種修復方案

方案一：建表時統一指定字元集與校對 (推薦、源頭治理) ⭐

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 COLLATE=gbk_chinese_ci;

關鍵：DEFAULT CHARSET=gbk COLLATE=gbk_chinese_ci 確保表級、欄位級預設一致。

方案二：修改現有表/欄位校對規則

# 修改表預設校對 (僅影響新增欄位)
ALTER TABLE `teachers` DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci;

# 修改特定欄位校對 (需重建表、鎖表)
ALTER TABLE `teachers` MODIFY `name` VARCHAR(20) 
CHARACTER SET gbk COLLATE gbk_chinese_ci;

# 或整表轉換 (MySQL 5.5+)
ALTER TABLE `teachers` CONVERT TO CHARACTER SET gbk COLLATE gbk_chinese_ci;

方案三：查詢層面強制指定校對 (應急、不改結構)

# 欄位級指定
SELECT * FROM teachers WHERE name COLLATE gbk_chinese_ci = '張三';

# 運算式指定
SELECT * FROM teachers WHERE name = '張三' COLLATE gbk_chinese_ci;

字元集配置最佳實踐

1. 伺服器層配置 (my.cnf)

[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

2. 連線層配置 (JDBC/PDO/PHP)

# JDBC URL
jdbc:mysql://host:3306/db?useUnicode=true&characterEncoding=utf8mb4&autoReconnect=true

# PHP PDO
$pdo = new PDO("mysql:host=localhost;dbname=db;charset=utf8mb4", $user, $pass);

# PHP mysqli
mysqli_set_charset($link, 'utf8mb4');

3. 驗證當前編碼

mysql -u root -p -e "SHOW VARIABLES LIKE 'character_set%'; SHOW VARIABLES LIKE 'collation%';"
# 或登入後
STATUS;
# 關鍵輸出：
# Server characterset: utf8mb4
# Db characterset: utf8mb4
# Client characterset: utf8mb4
# Conn. characterset: utf8mb4

遷移建議：GBK → UTF8MB4

1. 完整備份：mysqldump --default-character-set=gbk --all-databases > backup_gbk.sql
2. 建立新 UTF8MB4 資料庫
3. 修正備份檔：sed -i 's/CHARSET=gbk/CHARSET=utf8mb4/g; s/COLLATE=gbk_chinese_ci/COLLATE=utf8mb4_unicode_ci/g' backup_gbk.sql
4. 匯入新庫驗證
5. 應用層同步修改連線字元集