萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL 中文亂碼徹底解決:從編碼鏈路診斷到生產環境修復實戰

編碼鏈路全景圖

MySQL 字符集流經 4 個關鍵節點:**Client → Connection → Server/Storage → Result**。任一節點不一致即產生亂碼。

Client (應用/終端編碼)
    │  SET NAMES / connection charset
    ▼
Connection (character_set_client/connection/results)
    │  隱式轉換
    ▼
Server/Storage (character_set_database/table/column)
    │  物理存儲位元組
    ▼
Result (character_set_results) → Client 顯示

診斷三步法

-- 1. 檢視表/欄位實際存儲編碼
SHOW CREATE TABLE `user`;
SHOW FULL COLUMNS FROM `user`;

-- 2. 檢視當前會話所有字符集變數
SHOW VARIABLES LIKE 'char%';
-- 關鍵看:character_set_client, connection, database, results, server

-- 3. 實測:同一數據在不同 SET NAMES 下顯示
SET NAMES utf8mb4; SELECT realname FROM user LIMIT 3;
SET NAMES latin1;  SELECT realname FROM user LIMIT 3;
SET NAMES gbk;     SELECT realname FROM user LIMIT 3;

常見亂碼場景與修復

場景 1:偽 latin1(表定義 latin1,實存 UTF-8 位元組)

現象:SET NAMES latin1 顯示正常,SET NAMES utf8mb4 顯示亂碼。

修復:

  1. 導出原始位元組:mysqldump --default-character-set=latin1 --skip-set-charset db > raw.sql
  2. 修改腳本:將 CHARSET=latin1 → CHARSET=utf8mb4,移除 SET NAMES latin1。
  3. 以 utf8mb4 導入:mysql --default-character-set=utf8mb4 new_db < fixed.sql

場景 2:雙重編碼(UTF-8 被當 latin1 存入,再被當 latin1 讀出再存成 UTF-8)

現象:中文變為多字節亂碼(如 涓婃捣),HEX(col) 顯示雙倍字節。

修復:同場景 1,但需確認中間無其他轉換層(應用層、代理層)。

場景 3:Emoji/補充字符截斷或報錯

原因:utf8 僅支援 BMP(3 字節),Emoji 需 4 字節 utf8mb4。

修復:

ALTER DATABASE db_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;
ALTER TABLE tbl_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- 修改連接字串 charset=utf8mb4

場景 4:命令列客戶端顯示正常,應用層亂碼

排查:應用連接字串缺少 charset、JDBC characterEncoding、PHP SET NAMES、Python charset 參數。

生產環境標準化配置

服務端 my.cnf

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

[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

應用層連接最佳實踐

語言/框架 正確配置
PHP (PDO) charset=utf8mb4 in DSN + MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8mb4"
PHP (mysqli) mysqli_set_charset($link, 'utf8mb4')
Java (JDBC) useUnicode=true&characterEncoding=utf8mb4&serverTimezone=UTC (8.0+ 驅動自動識別)
Python (PyMySQL) charset='utf8mb4' in connect()
Go (go-sql-driver/mysql) charset=utf8mb4,utf8 in DSN
Node.js (mysql2) charset: 'utf8mb4' in createConnection()

快速修復腳本(僅對當前會話生效)

-- 臨時修正讀寫編碼(斷開失效)
SET NAMES utf8mb4;              -- 等同於設定 client/connection/results
SET CHARACTER SET utf8mb4;      -- 等同於 SET NAMES

-- 精確控制單一變數
SET character_set_client = utf8mb4;
SET character_set_connection = utf8mb4;
SET character_set_results = utf8mb4;

驗證清單

  1. SHOW VARIABLES LIKE 'char%'; 全為 utf8mb4(除 filesystem=binary、system=utf8)。
  2. SHOW CREATE TABLE 表/欄位 CHARSET=utf8mb4。
  3. 寫入 Emoji 😀 → SELECT HEX(col) 為 F09F9880(4 字節)。
  4. 應用讀取顯示正常、無 INSERT 報 ERROR 1366 (HY000): Incorrect string value。

避坑提醒

  • utf8 ≠ utf8mb4,**永遠用 utf8mb4**。
  • SET NAMES 僅對當前會話生效,持久化必須改 my.cnf + 重啟或應用層連接參數。
  • mysqldump 預設 --default-character-set=utf8mb4(8.0+),舊版本需顯式指定。
  • 舊系統 GBK 遷移:導出 --default-character-set=gbk,導入 --default-character-set=utf8mb4,中間無需手工轉碼,MySQL 自動轉換。
copyright © 萬盛學電腦網 all rights reserved