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

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

字符集決定「如何存儲」，校對規則決定「如何比較」。配置不當會導致亂碼、索引失效、排序錯誤、數據截斷。本文系統梳理編碼原理、MySQL 多層級字符集設置、校對規則選型、實戰避坑指南。

一、 基礎概念

二、 常見字符集對比

結論：新項目強制使用 utf8mb4，避開 utf8 陷阱。

三、 MySQL 四層字符集設置（優先級遞增）

Server (服務器級)
  └─ Database (數據庫級)
       └─ Table (表級)
            └─ Column (列級)
                 └─ Connection (連接級：client/server/results)

1. 服務器級 my.cnf

[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_general_ci  # 或 utf8mb4_unicode_ci
init_connect = 'SET NAMES utf8mb4'     # 非超級用戶連接自動執行

2. 數據庫級

CREATE DATABASE app_db CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER DATABASE app_db CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

3. 表級

CREATE TABLE t1 (c1 VARCHAR(50)) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE t1 CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;  -- 轉換現有數據

4. 列級（最高優先級）

CREATE TABLE t2 (
    c1 VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin,  -- 區分大小寫
    c2 TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci
);

5. 連接級（客戶端↔服務器交互）

-- 客戶端發送/接收編碼
SET NAMES utf8mb4;  -- 等價於下面三條
SET character_set_client = utf8mb4;
SET character_set_results = utf8mb4;
SET character_set_connection = utf8mb4;

-- 服務器內部操作編碼
character_set_database, character_set_server, character_set_system (只讀 utf8)

驗證命令：SHOW VARIABLES LIKE 'character_set_%'; SHOW VARIABLES LIKE 'collation_%';

四、 校對規則命名與選型

命名規則：charset_language_ci/cs/bin

• _ci：Case Insensitive（不區分大小寫），如 utf8mb4_general_ci、utf8mb4_unicode_ci、gbk_chinese_ci。
• _cs：Case Sensitive（區分大小寫），如 utf8mb4_cs（8.0+）。
• _bin：Binary（按字節碼值比較），如 utf8mb4_bin、gbk_bin，性能最快、最嚴格。

gbk 校對規則實戰對比

CREATE TABLE t_ci (name VARCHAR(10)) CHARACTER SET gbk COLLATE gbk_chinese_ci;
CREATE TABLE t_bin (name VARCHAR(10)) CHARACTER SET gbk COLLATE gbk_bin;

INSERT INTO t_ci VALUES ('a'), ('B'), ('c');
INSERT INTO t_bin VALUES ('a'), ('B'), ('c');

SELECT * FROM t_ci ORDER BY name;   -- a, B, c (不區分大小寫，按拼音/字典序)
SELECT * FROM t_bin ORDER BY name;  -- B, a, c (按字節碼：B=0x42, a=0x61)

五、 實戰避坑指南

六、 字符集變更操作手冊（存量庫遷移）

1. 修改默認配置：my.cnf 設定 character-set-server=utf8mb4，重啟實例。
2. 庫級轉換：ALTER DATABASE db_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci;
3. 表級轉換（含數據）：

   ALTER TABLE tbl_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

4. 列級精細調整：ALTER TABLE tbl_name MODIFY col_name VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
5. 驗證：SHOW CREATE TABLE tbl_name\G 確認 CHARSET=utf8mb4、COLLATE=...；抽樣查詢中文/Emoji 讀寫。
6. 應用端同步：JDBC URL ?characterEncoding=utf8mb4&connectionCollation=utf8mb4_general_ci，Python charset='utf8mb4'，Go DSN charset=utf8mb4。

七、 總結：字符集治理清單

1. 新項目強制 utf8mb4 + unicode_ci/general_ci。
2. 存量庫制定遷移計劃，分庫分表分批轉換，驗證無誤後切換。
3. 關聯列（JOIN/外鍵）字符集校對規則強制一致。
4. 連接層統一 SET NAMES utf8mb4，納入連接池初始化 SQL。
5. 監控 character_set_client/results/connection 變量，防止會話級污染。