MySQL 字元集修改與查看完整指南：資料庫、資料表、欄位三層級操作

字元集層級架構

MySQL 採四層預設值繼承機制：

伺服器預設 → 資料庫預設 → 資料表預設 → 欄位實際

修改上層不影響已建立的下層物件，只影響後續新建物件。

修改操作語法大全

1. 資料庫層級

-- 修改資料庫預設字元集（僅影響後續新建表）
ALTER DATABASE db_name DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

2. 資料表層級

-- 方案 A：僅修改表預設字元集（不轉換現有欄位）
ALTER TABLE tbl_name DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

-- 方案 B：完整轉換（表預設 + 所有字串欄位資料重新編碼）⭐ 推薦
ALTER TABLE tbl_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

差異關鍵：

• DEFAULT CHARACTER SET：只改預設，現有 CHAR/VARCHAR/TEXT 欄位不變
• CONVERT TO：將所有字串欄位資料按新字元集重新編碼，資料實際改變

3. 欄位層級

-- 修改單一欄位字元集（需重複指定型別與長度）
ALTER TABLE tbl_name 
    CHANGE col_name col_name VARCHAR(100) 
    CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

查看操作語法

實戰：將舊庫 latin1 完整遷移至 utf8mb4

步驟 1：修改資料庫預設

ALTER DATABASE legacy_db DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

步驟 2：逐表轉換（生成腳本）

SELECT CONCAT('ALTER TABLE ', TABLE_NAME, ' CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;')
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'legacy_db' AND TABLE_TYPE = 'BASE TABLE';

步驟 3：執行轉換（建議維護窗口、分批執行）

# 檢查大表
mysql -e "SELECT TABLE_NAME, ROUND(DATA_LENGTH/1024/1024,2) AS size_mb 
FROM information_schema.TABLES WHERE TABLE_SCHEMA='legacy_db' ORDER BY size_mb DESC;"

# 大表用 pt-online-schema-change 線上轉換
pt-online-schema-change --alter="CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci" \
    D=legacy_db,t=large_table --execute

步驟 4：驗證

-- 檢查是否有殘留非 utf8mb4 欄位
SELECT TABLE_NAME, COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'legacy_db' 
  AND CHARACTER_SET_NAME != 'utf8mb4'
  AND DATA_TYPE IN ('char','varchar','text','enum','set');

常見坑點與避雷

1. utf8 ≠ utf8mb4：MySQL utf8 實為 utf8mb3（最多 3 字節），無法儲存 Emoji、生僻字，必須用 utf8mb4
2. COLLATE 不匹配：連線、資料庫、表、欄位 Collate 不一致會導致 Illegal mix of collations 錯誤
3. 索引長度限制：utf8mb4 每字元 4 字節，VARCHAR(255) 索引長度 1020 字節，超過 InnoDB 767 字節（舊版）或 3072 字節（新版）限制 → 需縮短長度或升級 innodb_large_prefix=ON
4. 資料截斷：CONVERT TO 若目標字元集無法表示原字元，會報錯或替換為 ?，轉換前必須備份
5. 應用層連線字元集：JDBC characterEncoding=utf8mb4、PHP SET NAMES utf8mb4、Python charset='utf8mb4'

快速參考：推薦字元集組合