MySQL 字符集亂碼徹底解決：從臨時修復到服務器級永久配置

問題現象與根因分析

部署至阿里雲後出現兩大問題：

1. 中文顯示亂碼
2. json_encode() 失敗：GBK 編碼數據無法直接 JSON 序列化（JSON 標準要求 UTF-8）

根因：雖然資料庫建立時指定了 UTF-8，但服務器級系統變量 character_set_server 仍為 gbk，導致新建表/列繼承錯誤編碼。

診斷命令

SHOW VARIABLES LIKE 'character%';

典型錯誤輸出：

+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | gbk          <-- 核心問題  |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

方案對比：從臨時到永久

❌ 方案一：運行時 SET（重啟失效）

SET character_set_server = utf8;

僅對當前會話有效，服務重啟後恢復預設值。

⚠️ 方案二：連線層強制指定（每次查詢額外開銷）

$conn->query("SET character_set_client = utf8");
$conn->query("SET character_set_results = utf8");
$conn->query("SET character_set_connection = utf8");

或使用 mysqli 封裝：

$mysqli->set_charset("utf8mb4"); // 推薦 utf8mb4 支援 emoji

✅ 方案三：配置文件永久生效（推薦生產環境）

編輯 /etc/my.cnf（或 /etc/mysql/my.cnf）：

[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

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

關鍵點：

• 使用 utf8mb4 而非 utf8（MySQL 的 utf8 實為 utf8mb3，不支援 4 字節字符）
• collation-server 指定排序規則，utf8mb4_unicode_ci 準確性優於 general_ci
• 修改後必須 systemctl restart mysqld 重啟服務

驗證修復結果

SHOW VARIABLES LIKE 'character_set_server';
+----------------------+---------+
| Variable_name        | Value   |
+----------------------+---------+
| character_set_server | utf8mb4 |
+----------------------+---------+

遷移存量數據的完整流程

1. 備份：mysqldump -u root -p --default-character-set=gbk db_name > backup.sql
2. 修改備份文件：將 CHARSET=gbk 替換為 CHARSET=utf8mb4，COLLATE=gbk_chinese_ci 替換為 COLLATE=utf8mb4_unicode_ci
3. 刪除舊庫：DROP DATABASE db_name;
4. 建立新庫：CREATE DATABASE db_name CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
5. 恢復：mysql -u root -p db_name < backup.sql

應用層最佳實踐

總結

字符集問題的核心在於全鏈路一致性：服務器配置 → 資料庫/表定義 → 連線參數 → 應用層處理。採用 utf8mb4 作為統一標準，配置文件固化服務端設置，應用層顯式聲明連線編碼，可徹底杜絕亂碼與 JSON 序列化失敗。