MySQL 字元集與校對規則深度解析：亂碼成因、資料遺失與壓縮性規則

MySQL 字元集與 Collation 完整指南：從亂碼根因到壓縮性規則

字元集 是資料庫儲存與傳輸文字的編碼規則；校對規則 決定字串比較、排序的大小關係。設定錯誤會導致亂碼、資料截斷、索引失效等嚴重問題。本文系統說明 MySQL 多層級字元集繼承、亂碼/資料遺失成因、Collation 選擇與壓縮性規則。

一、字元集四層級繼承架構

伺服器級  →  資料庫級  →  資料表級  →  欄位級
(character_set_server)  (CREATE DATABASE)  (CREATE TABLE)  (COLUMN DEFINITION)
若未指定，自動繼承上一層設定

查看所有字元集：SHOW CHARACTER SET;

二、連線三大字元集變數 (Client/Connection/Results)

三者相同可簡寫：SET NAMES utf8mb4; (等同同時設定三者)

三、亂碼產生的兩大經典場景

場景一：Results 與 Client 編碼不一致

-- 建表指定 UTF8
CREATE TABLE t (name VARCHAR(10)) CHARSET utf8;

-- 連線設定為 GBK
SET NAMES gbk;
INSERT INTO t VALUES('中國');  -- 顯示正常 (GBK→UTF8 轉換正確)

-- 之後單獨改 Results 為 UTF8
SET character_set_results=utf8;
SELECT * FROM t;  -- 出現亂碼！

原因：資料實際以 UTF8 儲存，但 Client 宣稱送 GBK，伺服端按 GBK→UTF8 轉換存入。查詢時 Results=UTF8 直接回傳 UTF8 位元組，Client 以 GBK 解碼 → 亂碼。

場景二：Client 宣稱編碼與實際不符

-- 實際終端為 UTF8，但設定
SET NAMES gbk;
INSERT INTO t VALUES('中國');  -- 伺服端嘗試 GBK→UTF8，但位元組非合法 GBK → 儲存錯誤/截斷

四、資料遺失成因：Connection 編碼範圍小於 Client

SET character_set_client=gbk;
SET character_set_connection=latin1;  -- latin1 只能儲存 0-255 字碼
SET character_set_results=gbk;
INSERT INTO t VALUES('美國');  -- 中文無法用 latin1 表示 → 資料遺失/變問號

類似 Java double → int 精度損失：轉換目標編碼字碼空間不足以容納來源字元。

五、校對規則

• 查看所有：SHOW COLLATION;
• 一種字元集可有多種 Collation，如 utf8mb4_general_ci (不分大小寫)、utf8mb4_bin (二進位比較)、utf8mb4_unicode_ci (Unicode 標準)
• 建表指定：CREATE TABLE t (...) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci;

六、壓縮性規則 - 多運算元 Collision 解決

當比較運算涉及不同 Collation 的運算元 (如欄位 vs 字面值)，MySQL 依「壓縮性」優先級決定採用哪個：

規則：採用壓縮性值最小 (優先級最高) 者；若相同且 Collation 不同 → 報錯 Illegal mix of collations。

-- 查看運算元壓縮性
SELECT COERCIBILITY('A' COLLATE latin1_swedish_ci); -- 0
SELECT COERCIBILITY(VERSION());                     -- 3
SELECT COERCIBILITY('A');                           -- 4

七、最佳實踐建議

1. 全鏈路統一使用 utf8mb4 (支援 Emoji/補充字元) + utf8mb4_unicode_ci (Unicode 標準排序)
2. 連線建立後立即執行 SET NAMES utf8mb4
3. my.cnf 設定預設：
   character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
4. 應用層連線參數：?characterEncoding=utf8mb4&connectionCollation=utf8mb4_unicode_ci
5. 避免混用不同 Collation 的欄位進行 JOIN/比較