MySQL 字符編碼全維度配置指南：從伺服器、庫、表、欄位到連線的 utf8mb4 遷移實戰

字符編碼亂碼是 MySQL 開發運維的頭號痛點。本文系統梳理 character_set 六大層級配置、utf8 vs utf8mb4 本質差異、遷移實戰步驟、驗證腳本與常見坑點，構建「零亂碼」交付標準。

一、編碼層級架構與繼承鏈

┌─────────────────────────────────────┐
│ 1. Server (character_set_server)    │  ← my.cnf [mysqld] 全域預設
├─────────────────────────────────────┤
│ 2. Database (DEFAULT CHARACTER SET) │  ← CREATE/ALTER DATABASE 繼承 Server
├─────────────────────────────────────┤
│ 3. Table (DEFAULT CHARSET)          │  ← CREATE/ALTER TABLE 繼承 Database
├─────────────────────────────────────┤
│ 4. Column (CHARACTER SET)           │  ← CREATE/ALTER TABLE ... COLUMN 繼承 Table
├─────────────────────────────────────┤
│ 5. Connection (character_set_*)     │  ← 客戶端連線協議，三要素：client/connection/results
├─────────────────────────────────────┤
│ 6. Literal/Constant (_charset'str') │  ← SQL 字面值強制編碼，最高優先級
└─────────────────────────────────────┘

連線三要素 (關鍵！)

• character_set_client：客戶端發送 SQL 的編碼
• character_set_connection：Server 解析 SQL 時的編碼
• character_set_results：Server 返回結果集的編碼

最佳實踐： 三者統一為 utf8mb4，避免隱式轉換資訊損失。

二、utf8 vs utf8mb4：本質差異與選型鐵律

三、配置修改四大入口

1. 伺服器配置檔 (my.cnf / my.ini) —— 根基

[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
# 可選：強制客戶端協議
[client]
default-character-set = utf8mb4
[mysql]
default-character-set = utf8mb4
[mysqldump]
default-character-set = utf8mb4

生效： 重啟服務。影響後續新建庫/表/欄位預設值。

2. 資料庫層級

-- 建庫指定
CREATE DATABASE app_db DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 修改現有庫 (僅影響後續新建表)
ALTER DATABASE app_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

3. 資料表層級

-- 建表指定
CREATE TABLE users (
  id BIGINT PRIMARY KEY,
  name VARCHAR(50)
) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 修改現有表 (僅影響後續新增欄位)
ALTER TABLE users DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

4. 欄位層級 (核心！實際儲存編碼決定者)

-- 修改欄位編碼 (會重建表，大表謹慎操作)
ALTER TABLE users MODIFY name VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 批次修改表所有字串欄位 (資訊庫生成)
SELECT CONCAT('ALTER TABLE `', TABLE_NAME, '` MODIFY `', COLUMN_NAME, '` ', COLUMN_TYPE, ' CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;')
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'app_db' AND CHARACTER_SET_NAME = 'utf8';

四、遷移實戰：從 utf8 到 utf8mb4 零停機方案

方案 A：pt-online-schema-change (Percona Toolkit，推薦大表)

pt-online-schema-change \
  --alter "CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci" \
  --execute \
  --max-load "Threads_running=50" \
  --critical-load "Threads_running=100" \
  --chunk-time 0.5 \
  --chunk-size 1000 \
  D=app_db,t=users

方案 B：雙寫遷移 (應用層協同，零鎖風險)

1. 新增 name_utf8mb4 欄位，應用雙寫 (舊欄位 + 新欄位)
2. 歷史資料離線遷移 (分批 UPDATE ... SET new_col = CONVERT(old_col USING utf8mb4))
3. 驗證一致性 → 切換讀取到新欄位 → 停寫舊欄位 → 刪除舊欄位 → 重命名

方案 C：維護窗口 ALTER TABLE (中小表、可接受鎖表)

ALTER TABLE users CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- CONVERT TO 會同時修改表預設、所有字串欄位編碼、資料重新編碼
-- 僅修改預設用 ALTER TABLE ... DEFAULT CHARACTER SET ... (不轉換既有資料)

五、驗證腳本：全方位零亂碼檢查

#!/bin/bash
# verify_charset.sh
mysql -u root -p -e "
SELECT 
  @@character_set_server AS server_charset,
  @@collation_server AS server_collation;
  
SELECT 
  TABLE_SCHEMA, TABLE_NAME, TABLE_COLLATION
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA NOT IN ('mysql','information_schema','performance_schema','sys')
  AND TABLE_COLLATION NOT LIKE 'utf8mb4%';

SELECT 
  TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA NOT IN ('mysql','information_schema','performance_schema','sys')
  AND CHARACTER_SET_NAME != 'utf8mb4'
  AND DATA_TYPE IN ('char','varchar','text','longtext','mediumtext','tinytext','enum','set');
"

# 實測寫入 Emoji/生僻字
mysql -u root -p -e "
USE app_db;
CREATE TABLE IF NOT EXISTS charset_test (c VARCHAR(20)) CHARSET=utf8mb4;
INSERT INTO charset_test VALUES ('中文測試😀🚀'), ('生僻字：𠮷𠮷𠮷');
SELECT * FROM charset_test;
SELECT HEX(c), CHAR_LENGTH(c), LENGTH(c) FROM charset_test;
"

六、常見坑點與避雷指南

七、排序規則選型：unicode_ci vs general_ci vs 0900_ai_ci

從 latin1 到 utf8 再到 utf8mb4，每一次編碼升級都是為了擁抱更豐富的文字世界。建立「零亂碼」標準，讓每一個中文、Emoji、生僻字都能在資料庫中安穩棲息。