MySQL 中文亂碼根因分析與徹底解決方案

MySQL 中文亂碼本質是編碼不一致：存儲編碼、連接編碼、客戶端編碼三者任一不匹配即產生亂碼。本文通過實戰案例，演示如何診斷編碼錯位、導出修正、統一全鏈路編碼。

現象診斷：數據實際是 latin1 但庫表聲明 utf8

-- 查看庫表定義
SHOW CREATE DATABASE account;  -- DEFAULT CHARSET=utf8
SHOW CREATE TABLE user;        -- DEFAULT CHARSET=utf8

-- 查看數據（顯示亂碼）
SELECT * FROM user LIMIT 10;
-- realname: 老...

-- 系統變量顯示均為 utf8
SHOW VARIABLES LIKE '%char%';
-- character_set_client/connection/database/results/server/system = utf8

關鍵突破：用 latin1 讀取驗證假設

SET NAMES latin1;
SELECT * FROM user LIMIT 10;
-- realname: 陳、王嘉文、網家鋒...  <-- 正常顯示！

結論：數據實際按 latin1 編碼存儲，但表定義聲明 utf8，導致「雙重編碼」錯位。

修正流程：導出→轉碼→導入

1. 以 latin1 導出（匹配實際存儲編碼）

mysqldump -uroot -p --default-character-set=latin1 account user > /tmp/user.sql

2. 本地編輯器修正文件編碼聲明

• 用 EmEditor / Notepad++ / VS Code 打開 user.sql
• 將 SET NAMES latin1 改為 SET NAMES utf8
• 另存為 UTF-8 無 BOM 編碼

3. 以 utf8 導入

mysql -uroot -p -S /tmp/mysql3306.sock account < user.sql

4. 驗證

SELECT * FROM user LIMIT 10;  -- 中文正常顯示

亂碼四大根因總結

1. 服務器默認編碼非 utf8：舊版本默認 latin1，my.cnf 未配置 character-set-server=utf8
2. 庫/表/列定義編碼不一致：建庫建表未顯式指定 CHARSET=utf8
3. 連接層編碼未設置：應用連接後未執行 SET NAMES utf8 或 mysqli::set_charset('utf8')
4. 客戶端/頁面編碼不匹配：HTML <meta charset="utf-8">、終端編碼、編輯器編碼需統一

最佳實踐：全鏈路 UTF-8

1. 服務器配置 (my.cnf / my.ini)

[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_general_ci
init_connect = 'SET NAMES utf8mb4'

[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

推薦 utf8mb4：支援 Emoji 及完整 Unicode，utf8 實為 utf8mb3。

2. 建庫建表顯式指定

CREATE DATABASE mydb DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
CREATE TABLE t (c VARCHAR(100)) CHARSET=utf8mb4;

3. 應用層連接設置

-- PHP (mysqli)
$mysqli = new mysqli('localhost', 'user', 'pass', 'db');
$mysqli->set_charset('utf8mb4');

-- PHP (PDO)
$pdo = new PDO('mysql:host=localhost;dbname=db;charset=utf8mb4', 'user', 'pass');

-- Java (JDBC)
jdbc:mysql://localhost:3306/db?characterEncoding=utf8&useSSL=false

4. 遷移存量 latin1 數據的通用方法

-- 步驟 1：臨時表以二進制存儲
ALTER TABLE t MODIFY col VARBINARY(255);

-- 步驟 2：轉換為 utf8mb4
ALTER TABLE t MODIFY col VARCHAR(255) CHARACTER SET utf8mb4;

避坑指南

• 不要信任「顯示正常」：終端編碼可能掩蓋存儲錯誤
• SET NAMES 僅影響當前會話，重連失效，必須在連接池初始化/重連時執行
• mysqldump 導出時 必須指定 --default-character-set，否則按連接編碼導出，可能加重錯位
• 異構遷移（如 latin1 → utf8）務必「導出時按舊編碼、導入時按新編碼」

總結

中文亂碼的本質是編碼鏈路斷點。建立「服務器→庫表→連接→應用→前端」全鏈路 utf8mb4 標準，新項目從遷移存量數據時按「二進制中轉」法處理，可徹底告別亂碼。