MySQL 中文亂碼問題完整排查與解決方案

MySQL 中文亂碼問題完整排查與解決方案

MySQL 中文顯示異常 (亂碼、問號、截斷) 是開發常見痛點。本文提供系統性排查框架：從伺服器配置、資料庫/表/欄位字符集、連線字符集、客戶端工具到 SQL 模式差異的全鏈路解決方案。

第一層：伺服器全局字符集配置 (my.cnf / my.ini)

[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
init_connect = 'SET NAMES utf8mb4'  -- 針對非超級用戶自動執行

[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

重啟驗證： SHOW VARIABLES LIKE 'character_set_%'; 確認 character_set_server, character_set_database, character_set_connection, character_set_client, character_set_results 均為 utf8mb4。

第二層：資料庫、表、欄位字符集檢查與修正

-- 檢查資料庫
SHOW CREATE DATABASE db_name;
-- 修正
ALTER DATABASE db_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

-- 檢查表
SHOW CREATE TABLE tbl_name;
-- 修正 (含欄位轉換)
ALTER TABLE tbl_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 單欄位修正
ALTER TABLE tbl_name MODIFY col_name VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

關鍵區別： ALTER TABLE ... CHARACTER SET 僅修改表預設值，**不轉換現有欄位**；CONVERT TO CHARACTER SET 會遞歸轉換所有文字欄位資料。

第三層：連線字符集 (最常被忽視)

應用程式連線時必須顯式設定：

• JDBC URL： jdbc:mysql://host:3306/db?useUnicode=true&characterEncoding=utf8mb4&zeroDateTimeBehavior=convertToNull
• Python (PyMySQL)： charset='utf8mb4'
• PHP (PDO)： charset=utf8mb4 in DSN
• C# (MySqlConnector)： Charset=utf8mb4 in connection string
• 通用 SQL： 連線建立後立即執行 SET NAMES utf8mb4;

第四層：特殊 SQL 模式差異分析 (原文案例)

-- 現象：UNION 查詢顯示正常，但單獨查詢某欄位亂碼
SELECT '工資合計', SUM(FSalary) FROM T_Employee;           -- 正常
SELECT '正式員工最高年齡', MAX(FAge) FROM T_Employee
UNION
SELECT '正式員工最低年齡', MIN(FAge) FROM T_Employee;       -- 正常

-- 分析：UNION 強制結果集字符集統一為 utf8mb4，掩蓋了欄位字符集不一致問題
-- 根因：FAge 欄位可能定義為 latin1，單獨查詢時客戶端按 latin1 解碼顯示亂碼
-- 解決：執行上述 ALTER TABLE CONVERT TO 徹底轉換表結構

第五層：客戶端工具驗證

終極排查清單 (按順序)

1. 伺服器 character_set_server = utf8mb4？
2. 資料庫/表/欄位實際字符集 = utf8mb4？(用 SHOW CREATE TABLE 看)
3. 應用連線字串含 characterEncoding=utf8mb4？
4. 客戶端工具編碼設置 = UTF-8？
5. 資料本身是否已損壞？(雙重編碼 latin1→utf8 需專門修復腳本)

為什麼推薦 utf8mb4 而非 utf8？

• MySQL utf8 偽 UTF-8，僅支援 BMP 平面 (3 字節)，**不支援 Emoji、生僻字** (需 4 字節)。
• utf8mb4 真正支援 Unicode 4 字節字符，兼容性完整。
• 索引長度限制：utf8mb4 單欄索引上限 191 字符 (767 bytes / 4)，utf8 為 255 字符。InnoDB innodb_large_prefix=ON (5.7+ 預設) 可放寬至 3072 bytes。