萬盛學電腦網

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

MySQL 亂碼終極解決方案:字符集配置、連接協商與最佳實踐

亂碼產生的根本原因

MySQL 涉及四層字符集轉換,任一環節不匹配即產生亂碼:

  1. 客戶端字符集(應用/驅動發送 SQL 的編碼)
  2. 連接字符集(Server 解析 SQL 時使用的編碼)
  3. 數據庫/表/列字符集(數據實際存儲編碼)
  4. 結果字符集(Server 返回結果給客戶端的編碼)

查看當前會話所有字符集變量:

SHOW VARIABLES LIKE 'character_set_%';
SHOW VARIABLES LIKE 'collation_%';

標準解決方案:全鏈路 UTF-8(推薦 utf8mb4)

1. 服務端配置 /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'  # 非超級用戶連接時自動執行
skip-character-set-client-handshake  # 強制忽略客戶端聲明,統一用服務端設置

修改後重啟 MySQL:systemctl restart mysqld。

2. 創建數據庫/表時顯式指定

CREATE DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE TABLE t1 (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

3. 應用層連接字符串顯式指定

語言/框架正確連接參數
JDBC (MySQL Connector/J 8.0+)jdbc:mysql://host:3306/db?characterEncoding=utf8mb4&useSSL=false
PHP PDOnew PDO("mysql:host=host;dbname=db;charset=utf8mb4", $user, $pass)
PHP mysqli$mysqli->set_charset("utf8mb4");
Python PyMySQLpymysql.connect(..., charset='utf8mb4')
Go (go-sql-driver/mysql)user:pass@tcp(host:3306)/db?charset=utf8mb4,utf8
Node.js (mysql2)createConnection({charset: 'utf8mb4'})

4. 避免在代碼中執行 SET NAMES

驅動程序內部已處理字符集協商,手動執行 SET NAMES utf8mb4 可能導致驅動狀態與服務端不同步,引發更隱蔽的 Bug。

歷史遺留項目:GBK 環境遷移指南

若老系統已大量使用 GBK(gbk_chinese_ci),無法立即全量遷移至 utf8mb4,可採用「雙軌制」:

  1. 服務端保持 character-set-server=gbk。
  2. 應用層連接字符串指定 charset=gbk(JDBC: characterEncoding=GBK)。
  3. 新建表/庫顯式指定 CHARSET=utf8mb4,並在應用層對該表單獨建立 utf8mb4 連接池。
  4. 逐步遷移存量數據:ALTER TABLE t CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;(會鎖表,大表用 pt-online-schema-change 或 gh-ost)。

排查亂碼的標準流程

  1. 確認數據在數據庫中是否正常:SELECT HEX(col), col FROM t WHERE id=1; 對照 Unicode 碼位。
  2. 檢查表/列定義:SHOW CREATE TABLE t\G。
  3. 檢查會話變量:SHOW SESSION VARIABLES LIKE 'character_set_%';。
  4. 檢查應用連接參數是否顯式指定 charset。
  5. 檢查中間件(連接池、代理、ORM)是否覆蓋字符集設置。

常見誤區

  • 以為 utf8 = utf8mb4:MySQL 的 utf8 實為 utf8mb3,僅支持 BMP 平面(3 字節),無法存儲 Emoji、生僻字。必須用 utf8mb4。
  • 只改服務端不改應用端:連接協商失敗,繼續按 latin1 發送/接收。
  • 用 SET NAMES 代替連接參數:驅動不知情,準備語句參數綁定可能出錯。
  • 忽略排序規則:utf8mb4_general_ci 與 utf8mb4_unicode_ci 在某些字符排序、比較上有差異,建議統一用 unicode_ci(或 0900_ai_ci)。

phpMyAdmin 設置參考

  • 語言:Chinese Traditional (zh-TW)
  • MySQL 字符集:UTF-8 Unicode (utf8mb4)
  • MySQL 連接校對:utf8mb4_unicode_ci

字符集問題 90% 發生在「客戶端以為是 UTF-8,服務端以為是 Latin1/GBK」的協商失敗上。統一全鏈路顯式聲明 utf8mb4,是一次性徹底解決的關鍵。

copyright © 萬盛學電腦網 all rights reserved