MySQL 字元集與校對規則深度解析：從底層編碼到多層級設定實戰

MySQL 字元集與校對規則深度解析：從底層編碼到多層級設定實戰

字元集決定了字元如何編碼儲存，校對規則決定了字元如何比較排序。MySQL 支援多層級（Server、Database、Table、Column）字元集繼承與覆寫機制，搭配客戶端連線字元集協商，構成完整的字元處理鏈路。本文從編碼原理、key_len 計算、多層級配置、客戶端交互五大面向，徹底釐清 MySQL 字元集體系。

1. 常見字元集編碼對照

關鍵差異：GBK 中文 2 Bytes，UTF8/UTF8MB4 中文 3 Bytes，直接影響索引長度與儲存空間。

2. 字元集與 EXPLAIN key_len 的關係

key_len 反映索引實際使用的位元組數，計算公式包含：

1. 欄位資料長度：CHAR(N) 為 N × 字元集單字元 Bytes；VARCHAR(N) 同理。
2. 變長欄位額外開銷：長度資訊 2 Bytes。
3. NULL 標記：允許 NULL 時 1 Byte；NOT NULL 則 0 Byte。

範例

• VARCHAR(10) CHARSET utf8 NULL → 10×3 + 2 + 1 = 33 Bytes
• VARCHAR(10) CHARSET utf8 NOT NULL → 10×3 + 2 = 32 Bytes
• CHAR(10) CHARSET gbk NULL → 10×2 + 1 = 21 Bytes

正確預估 key_len 有助於判斷索引是否被完整使用（如聯合索引最左前綴原則）。

3. MySQL 多層級字元集配置與繼承

層級由高到低：Server > Database > Table > Column，低層級未指定時自動繼承上層設定。

查看 Server 級預設

SHOW VARIABLES LIKE 'character_set_server';
SHOW VARIABLES LIKE 'collation_server';

修改 /etc/my.cnf 永久生效：

[mysqld]
character_set_server = utf8mb4
collation_server = utf8mb4_general_ci

Database 級

CREATE DATABASE db_name CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER DATABASE db_name CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
-- 注意：ALTER 只影響新建表，現有表/資料不自動轉碼

Table 級

CREATE TABLE t (c VARCHAR(20)) CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
ALTER TABLE t CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- 同樣只影響新增欄位，現有資料不轉碼

Column 級

ALTER TABLE t MODIFY c VARCHAR(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

查看實際生效設定

SHOW CREATE TABLE t\G
SHOW CREATE DATABASE db_name\G

4. 校對規則命名與選擇

格式：字元集_語言_後綴

• _ci：Case Insensitive（大小寫不敏感），如 utf8mb4_general_ci
• _cs：Case Sensitive（大小寫敏感）
• _bin：Binary 比較，按編碼位元組逐位比對，效能最快，無語言規則

查看某字元集支援的校對規則：

SHOW COLLATION LIKE 'utf8mb4%';

5. 客戶端與 Server 交互的三大字元集參數

連線建立時，MySQL 依據以下三個變數處理字元轉換：

• character_set_client：客戶端發送 SQL 的編碼
• character_set_connection：Server 內部處理 SQL 的編碼（將 client 編碼轉換至此）
• character_set_results：Server 回傳結果集的編碼

黃金法則：三者保持一致，才能確保「寫入什麼、讀出什麼」無亂碼。

設定方式

1. 連線級（臨時）：SET NAMES utf8mb4;（等同同時設定上述三者）
2. 配置檔永久（客戶端）：[client] default-character-set=utf8mb4 在 my.cnf
3. 驅動層參數：JDBC characterEncoding=utf8mb4、Python charset='utf8mb4'、Go charset=utf8mb4 等。

6. 避坑指南：從 latin1 遷移至 utf8mb4

1. 修改 Server/Database/Table/Column 定義為 utf8mb4。
2. 對現有資料執行實際轉碼：ALTER TABLE t CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
3. 檢查應用端連線參數均已更新為 utf8mb4。
4. 驗證 Emoji、生僻字可正常存取。

徹底理解字元集流向，是解決亂碼、優化索引、支援多語言應用的基石。