MySQL 字符集與校對規則深度解析：從原理到實戰避坑

MySQL 字符集與校對規則深度解析

字符集與校對規則是 MySQL 中處理多語言資料的核心，配置不當會導致亂碼、排序錯誤、比較失效甚至報錯 ERROR 1253 (42000): COLLATION 'xxx' is not valid for CHARACTER SET 'yyy'。本文系統梳理核心概念、命名規則、層級繼承與常見錯誤處理。

核心概念

校對規則命名慣例

格式：字符集名_語言_後綴

• _ci (case-insensitive)：不區分大小寫，如 utf8mb4_general_ci（預設）
• _cs (case-sensitive)：區分大小寫，如 utf8mb4_general_cs
• _bin (binary)：二進制比較，按字符編碼值直接比較，區分大小寫且無語言語義，效能最高，如 utf8mb4_bin

範例： GBK 字符集常見校對：gbk_chinese_ci（中文不區分大小寫）、gbk_bin（二進制）。

常見錯誤與排查

錯誤： COLLATION 'gbk_chinese_ci' is not valid for CHARACTER SET 'utf8'

原因： 表/欄位字符集為 utf8，但指定的校對規則屬於 gbk 字符集，兩者不匹配。

解決： 統一字符集與校對規則，例如：

-- 建表時指定
CREATE TABLE t (c VARCHAR(100)) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

-- 修改既有表
ALTER TABLE t CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

-- 修改單一欄位
ALTER TABLE t MODIFY c VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

字符集與校對規則的四個層級

MySQL 採用層級繼承機制，優先級由高到低：

1. 欄位級：COLLATE utf8mb4_bin 直接定義在欄位上
2. 表級：DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
3. 資料庫級：CREATE DATABASE db CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
4. 伺服器級：character-set-server、collation-server 參數（my.cnf）

未顯式指定時，向上繼承最近一層的設定。

實戰建議

• 新專案統一使用 utf8mb4 + utf8mb4_general_ci 或 utf8mb4_0900_ai_ci (MySQL 8.0+)，完整支援 Emoji 與多語言。
• 需精確比較（如密碼驗證、案件編號）的欄位，明確指定 _bin 或 _cs 校對。
• 避免在 JOIN/WHERE 條件中混用不同字符集欄位，會導致隱式轉換、索引失效。
• 使用 SHOW VARIABLES LIKE 'character_set%';、SHOW VARIABLES LIKE 'collation%'; 檢視當前實例設定。

查看字符集支援的校對規則

SHOW COLLATION LIKE 'utf8mb4%';
-- 或指定字符集
SHOW COLLATION WHERE Charset = 'utf8mb4';