MySQL 中文查詢亂碼/失效根因與四大解決方案

MySQL 早期版本（3.23/4.0/4.1 前）因默認字符集 latin1（ISO-8859-1）導致中文 LIKE 查詢錯誤：WHERE name LIKE '%李%' 會匹配不含「李」的記錄。根因是大小寫不敏感比較時，多字節中文編碼被錯誤拆分轉換。

四大解決方案（按推薦度排序）

方案一：列級 BINARY 屬性（即時生效，無需重啟）

-- 建表時指定
CREATE TABLE t (name CHAR(10) BINARY);
-- 或修改現有列
ALTER TABLE t MODIFY name CHAR(10) BINARY;

原理：強制二進制比較，逐字節匹配，繞過字符集排序規則。副作用：該列失去大小寫不敏感特性，排序按字節序。

方案二：編譯/啟動指定字符集 GBK（根治）

# 源碼編譯時
./configure --with-charset=gbk --with-extra-charsets=gbk,utf8

# 或啟動參數/配置文件
[mysqld]
default-character-set=gbk
character_set_server=gbk

適用：MySQL 4.0 及之前版本。現代版本（5.5+）請直接用 utf8mb4。

方案三：LOCATE 函數繞過 LIKE（無需改結構）

SELECT * FROM t WHERE LOCATE('李', field) > 0;

原理：LOCATE(substr, str) 返回子串首次出現位置（1-based），0 表示不含。按字節匹配，不受字符集排序規則影響。性能：無法使用索引，全表掃描，大表慎用。

方案四：查詢級 BINARY 強制（臨時修正）

SELECT * FROM t WHERE field LIKE BINARY '%李%';

效果：單次查詢強制二進制比較，不改表結構。

現代最佳實踐（MySQL 5.5+ / 8.0）

1. 全鏈路 utf8mb4：庫/表/列/連接/客戶端/驅動程序全部統一 utf8mb4（支持完整 Unicode，含 Emoji）
2. 連接字串顯式指定：JDBC ?characterEncoding=utf8mb4、PHP mysqli_set_charset($conn, 'utf8mb4')、Python charset='utf8mb4'
3. 排序規則選擇：utf8mb4_general_ci（速度快）或 utf8mb4_unicode_ci（排序準確，含多語言規則）
4. 避免 BINARY 欄位：除非業務強制區分大小寫，否則用 _bin 排序規則（如 utf8mb4_bin）替代

遷移檢查清單

• SHOW VARIABLES LIKE 'character_set%'; 確認全鏈路一致
• SHOW CREATE TABLE t; 檢查表/列級字符集
• 應用層輸入輸出編碼統一（HTML <meta charset="utf-8">、HTTP Header Content-Type: text/html; charset=utf-8）