MySQL 字符集編碼查看、修改與亂碼排查全攻略

核心概念：字符集 vs 校對規則

• 字符集：字符到二進制的映射（如 utf8mb4、gbk、latin1）
• 校對規則：字符比較/排序規則（如 utf8mb4_general_ci、utf8mb4_unicode_ci），_ci 為大小寫不敏感

四層編碼體系與查看命令

常見亂碼場景與根因

• 插入亂碼：客戶端編碼 ≠ 表列編碼 → 寫入時錯誤轉換
• 查詢亂碼：character_set_results ≠ 客戶端期望編碼 → 返回時錯誤轉換
• 「雙重編碼」：utf8 數據被當 latin1 存入，再按 utf8 讀出 → 亂碼加倍

修改編碼的正確姿勢

1. 修改配置文件（持久化，需重啟）

[mysqld]
character-set-server=utf8mb4
collation-server=utf8mb4_general_ci
skip-character-set-client-handshake  # 強制忽略客戶端請求編碼

2. 會話級臨時修改（不重啟）

SET NAMES utf8mb4;  -- 等同於同時設置 client/connection/results
-- 或
SET character_set_client = utf8mb4;
SET character_set_connection = utf8mb4;
SET character_set_results = utf8mb4;

3. 對象級修改（需鎖表，大表慎用）

-- 庫級
ALTER DATABASE db_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci;
-- 表級（僅修改默認，不轉換現有列）
ALTER TABLE tbl_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci;
-- 列級（實際轉換數據，會鎖表重建）
ALTER TABLE tbl_name MODIFY col_name VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
-- 一鍵轉換表及所有列（慎用！大表極慢）
ALTER TABLE tbl_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

歷史數據編碼修復實戰

1. 導出數據：mysqldump -u root -p --default-character-set=latin1 db_name > dump.sql（以舊編碼導出，防止雙重轉換）
2. 修改導出文件：將 SET NAMES latin1 改為 SET NAMES utf8mb4，表定義中 CHARSET=latin1 改為 utf8mb4
3. 導入新庫：mysql -u root -p new_db < dump.sql

或使用 iconv 工具轉換文件編碼：

iconv -f utf8 -t gbk dump_utf8.sql > dump_gbk.sql
mysql -u root -p --default-character-set=gbk new_db < dump_gbk.sql

最佳實踐建議

• 全鏈路統一 utf8mb4：支持 Emoji、生僻字，避免 utf8 (utf8mb3) 截斷 4 字節字符
• 應用層強制設置：JDBC URL 加 characterEncoding=utf8mb4，PHP PDO 設置 SET NAMES utf8mb4
• 避免隱式轉換：SQL 中字符串字面量盡量用 _utf8mb4'字符串' 或 N'字符串' 顯式聲明