MySQL 中文查詢字符集匹配問題完整解決方案

問題現象

• 中文儲存變亂碼 (如 ???、、錯誤編碼字元)
• 查詢條件 `WHERE name = '中文'` 無法命中明明存在的資料
• 排序順序錯誤、GROUP BY 分組異常
• 報錯：Illegal mix of collations

核心概念：字符集 vs 校對規則

原則： 統一全鏈路使用 utf8mb4 (支援 Emoji、完整 Unicode) + utf8mb4_unicode_ci (Unicode 標準排序) 或 utf8mb4_0900_ai_ci (MySQL 8.0 預設，效能更好、支援音標不敏感)。

完整設定檢核清單

1. 伺服器層級 (my.cnf / my.ini)

[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
init_connect = 'SET NAMES utf8mb4'  -- 非 super 用戶自動執行

[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

修改後重啟 MySQL：sudo systemctl restart mysqld

2. 資料庫與表層級

-- 新建
CREATE DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE TABLE users (
  id INT PRIMARY KEY,
  name VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 既有資料庫/表轉換 (需停機或 pt-online-schema-change)
ALTER DATABASE mydb CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;
ALTER TABLE users CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

3. 連線層級 (最常被忽略)

• JDBC： URL 加 ?characterEncoding=utf8mb4&connectionCollation=utf8mb4_unicode_ci 或驅動 8.0+ 自動協商
• Python (PyMySQL/mysqlclient)： charset='utf8mb4'
• PHP (PDO)： new PDO("mysql:host=...;dbname=...;charset=utf8mb4", ...)
• Go (go-sql-driver/mysql)： DSN 加 charset=utf8mb4,utf8
• Node.js (mysql2)： charset: 'utf8mb4'

或在連線建立後立即執行：SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci;

查詢匹配失效的常見原因與對策

遷移舊資料的實務步驟

1. 備份：mysqldump --default-character-set=utf8mb4 --single-transaction db > backup.sql
2. 建立新庫/表指定 utf8mb4
3. 若原資料實際編碼為 utf8 (非 utf8mb4)，直接 CONVERT TO 即可；若混雜 latin1/utf8 需先 ALTER TABLE ... CHANGE col col BLOB 再 CHANGE col col VARCHAR(...) CHARACTER SET utf8mb4 (二進位中轉)
4. 驗證：SELECT HEX(name), name FROM users WHERE name LIKE '%中文%'; 確認 HEX 為正確 UTF-8 位元組 (E4B8ADE69687)

結語

MySQL 中文亂碼與匹配失效 90% 源於「連線字符集」與「欄位校對規則」不一致。統一全鏈路 utf8mb4 + unicode_ci、驅動正確設定 charset、既有資料正確遷移，即可徹底解決。