MySQL 程序中 SQL 報「非法混合字符集」錯誤的定位與修復

MySQL 程序中 SQL 報「非法混合字符集」錯誤的定位與修復

應用層執行含中文條件的 SQL 時拋出 Error 1267: Illegal mix of collations (gbk_bin,IMPLICIT) and (latin1_swedish_ci,COERCIBLE) for operation '='。根因是列字符集與字符串字面量字符集不一致，MySQL 無法隱式轉換導致比較失敗。

錯誤現場

SELECT t.EVENT_TYPE_ID
FROM RATABLE_EVENT_TYPE t
WHERE t.NAME = '帳期末費用轉移事件';
-- 報錯：mix of collations (gbk_bin,IMPLICIT) and (latin1_swedish_ci,COERCIBLE)

根因分析

• t.NAME 列字符集為 gbk，校對規則 gbk_bin（IMPLICIT：列隱式字符集）
• 字符串字面量 '帳期末費用轉移事件' 在當前連接字符集下被解釋為 latin1（COERCIBLE：可強制轉換）
• MySQL 嘗試將 latin1 字面量隱式轉為 gbk 比較，但 latin1 無法表示中文，導致「非法混合」

快速修復方案

方案一：強制字面量按二進制比較（推薦，零侵入）

SELECT t.EVENT_TYPE_ID
FROM RATABLE_EVENT_TYPE t
WHERE t.NAME = BINARY '帳期末費用轉移事件';

BINARY 強制字面量視為二進制字節流，繞過字符集轉換，直接按字節比較。

方案二：顯式指定字面量字符集（標準寫法）

WHERE t.NAME = _gbk '帳期末費用轉移事件'
-- 或
WHERE t.NAME = CONVERT('帳期末費用轉移事件' USING gbk)

方案三：統一連接層字符集（治本）

-- 連接建立後立即執行
SET NAMES gbk;  -- 或 utf8mb4，需與列字符集一致

排查檢查清單

1. SHOW CREATE TABLE tbl 確認列字符集/校對規則
2. SHOW VARIABLES LIKE 'character_set%' 確認連接層 client/connection/results
3. 應用層連接池配置 characterEncoding/connectionCollation
4. JDBC URL 追加 ?characterEncoding=gbk&connectionCollation=gbk_bin

最佳實踐

• **新建表統一 utf8mb4**，避免 gbk/latin1 混用
• 連接池初始化強制 SET NAMES utf8mb4
• ORM 框架配置 hibernate.connection.characterEncoding / spring.datasource.connectionInitSqls