MySQL 字元集層級機制深度解析：資料庫、資料表、欄位三層編碼獨立性與亂碼根因排查

MySQL 字元集三層獨立架構

MySQL 字元集設定呈現層級繼承與獨立覆蓋機制：

• 伺服器層：character_set_server，啟動預設
• 資料庫層：DEFAULT CHARSET，建庫指定，僅作為後續建表的預設值
• 資料表層：DEFAULT CHARSET，建表指定，僅作為後續加欄位的預設值
• 欄位層：CHARACTER SET，建表/加欄位時具體指定，**最終決定資料實際儲存編碼**

關鍵認知：**修改上層預設值，不會自動變更下層已建立物件的編碼**。

實證過程還原

-- 1. 建表指定 latin1
CREATE TABLE `category` (
  `Id` binary(16) NOT NULL,
  `UniqueName` varchar(64) NOT NULL,
  `Name` varchar(64) NOT NULL,
  `CreatedAt` datetime NOT NULL,
  PRIMARY KEY (`Id`),
  KEY `IX_Category_UniqueName_CreatedAt` (`UniqueName`,`CreatedAt`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- 2. 修改表預設字元集為 utf8
ALTER TABLE `category` DEFAULT CHARSET=utf8;

-- 3. 查看 DDL 變化
SHOW CREATE TABLE `category`\G
-- 結果：
-- 表級 DEFAULT CHARSET=utf8
-- 但現有欄位 UniqueName、Name 仍保持 character set latin1
-- 新增欄位才會預設 utf8

編碼層級驗證指令

亂碼根因分析：KIGG 案例還原

-- 現象：應用寫入 UTF-8 資料，讀出亂碼
-- 根因鏈：
1. 應用連線字元集：UTF-8 (SET NAMES utf8 / JDBC useUnicode=true&characterEncoding=utf8)
2. 資料庫/表預設：UTF-8
3. **但欄位實際編碼：latin1** (建表時未指定，繼承舊預設)
4. 寫入流程：應用 UTF-8 bytes → 連線層視為 latin1 → 儲存為 latin1 bytes
5. 讀取流程：latin1 bytes → 連線層轉為 UTF-8 → 應用收到錯誤字元

-- 解決：修改欄位編碼
ALTER TABLE `category` 
MODIFY `UniqueName` varchar(64) CHARACTER SET utf8mb4 NOT NULL,
MODIFY `Name` varchar(64) CHARACTER SET utf8mb4 NOT NULL;

字元集與排序規則關係

• 字元集** = 字元集合 + 編碼方式 (如 utf8mb4 = Unicode BMP+增補平面，1-4 bytes)
• 排序規則** = 字元集下的比較/排序規則 (如 utf8mb4_general_ci、utf8mb4_unicode_ci、utf8mb4_0900_ai_ci)
• 每個字元集有預設排序規則，可在欄位/表/庫/連線層級覆蓋
• _ci = case insensitive，_cs = case sensitive，_bin = binary 比較

連線字元集三大變數

SET NAMES utf8mb4;
-- 等同於：
SET character_set_client = utf8mb4;    -- 客戶端發送編碼
SET character_set_connection = utf8mb4; -- 連線處理編碼
SET character_set_results = utf8mb4;    -- 結果集返回編碼

**必須與應用實際編碼一致**，否則發生雙重轉碼導致亂碼。

完整編碼一致性檢查清單

1. 伺服器 character_set_server = utf8mb4
2. 資料庫 DEFAULT CHARACTER SET utf8mb4
3. 資料表 DEFAULT CHARSET=utf8mb4
4. 欄位 CHARACTER SET utf8mb4 (逐欄確認)
5. 連線 SET NAMES utf8mb4 / 驅動參數 characterEncoding=utf8
6. 應用程式碼/檔案編碼 = UTF-8 (無 BOM)
7. 終端/IDE/瀏覽器顯示編碼 = UTF-8

utf8 vs utf8mb4 關鍵差異

-- 1. 修改資料庫預設
ALTER DATABASE `mydb` CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

-- 2. 修改表預設 + 所有現有欄位 (需逐表執行)
ALTER TABLE `mydb`.`table1` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- CONVERT TO 會同時修改表預設、所有文字欄位編碼、資料重新編碼

-- 3. 僅修改表預設 (不影響現有欄位)
ALTER TABLE `mydb`.`table2` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 4. 修改單一欄位
ALTER TABLE `mydb`.`table3` MODIFY `col1` VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL;