MySQL 中文亂碼根因與解決：字符集三層傳輸鏈路深度解析

MySQL 中文亂碼根因與解決：字符集三層傳輸鏈路深度解析

項目全鏈路 UTF-8，Hibernate 讀寫正常，但 MySQL 命令行顯示亂碼、手動 INSERT 報錯 1406。本文從字符集傳輸鏈路原理、DOS 窗口編碼限制、配置檔誤區、最佳實踐四個維度徹底解決。

一、字符集傳輸鏈路原理

輸入鏈路：Client → Connection → Server
輸出鏈路：Server → Connection → Results → Client 顯示

關鍵系統變量：
character_set_client     -- 客戶端發送編碼
character_set_connection -- 連接層轉換目標編碼
character_set_results    -- 結果集返回編碼
character_set_server     -- 服務端默認編碼
character_set_database   -- 當前庫默認編碼
character_set_system     -- 系統標識符編碼 (固定 utf8)

二、SET NAMES 的本質

SET NAMES 'utf8';
-- 等價於：
SET character_set_client = utf8;
SET character_set_connection = utf8;
SET character_set_results = utf8;

三、亂碼產生機制

四、ERROR 1406 根因

Data too long for column 實際是字符集轉換導致字節長度超限或非法序列。DOS 窗口默認 GBK，輸入中文按 GBK 編碼（2 字節/字），傳給期望 UTF-8 的 Server，被視為非法 UTF-8 序列或長度異常。

五、解決方案矩陣

六、最佳實踐：標準化全鏈路 UTF-8

1. 數據庫層：

   CREATE DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
   ALTER DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

2. 表/列層：

   CREATE TABLE t (c VARCHAR(100)) CHARSET=utf8mb4;
   ALTER TABLE t CONVERT TO CHARACTER SET utf8mb4;

3. 連接層（應用配置）：

   # JDBC
   jdbc:mysql://host:3306/db?characterEncoding=utf8&useSSL=false&serverTimezone=UTC
   
   # PHP PDO
   $pdo = new PDO('mysql:host=...;dbname=...;charset=utf8mb4', ...);
   
   # Python PyMySQL
   conn = pymysql.connect(..., charset='utf8mb4')

4. MySQL 配置 (/etc/my.cnf)：

   [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

5. DOS 窗口臨時方案： 僅在命令行交互時執行 SET NAMES GBK;，結束後 SET NAMES utf8mb4; 恢復。長期建議使用圖形化工具或 Linux 終端。

七、診斷腳本

-- 1. 檢查當前會話編碼
SHOW VARIABLES LIKE 'character_set_%';
SHOW VARIABLES LIKE 'collation_%';

-- 2. 檢查庫/表/列編碼
SELECT DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME 
FROM information_schema.SCHEMATA WHERE SCHEMA_NAME='mydb';

SELECT TABLE_NAME, TABLE_COLLATION 
FROM information_schema.TABLES WHERE TABLE_SCHEMA='mydb';

SELECT COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME 
FROM information_schema.COLUMNS WHERE TABLE_SCHEMA='mydb' AND TABLE_NAME='t';

-- 3. 檢查數據實際編碼
SELECT HEX(column), CHAR_LENGTH(column), LENGTH(column) FROM t;

八、關鍵知識點補充

• VARCHAR vs NVARCHAR：MySQL 無 NVARCHAR，VARCHAR 配合 utf8mb4 完美支援 Unicode
• SCHEMA 在 MySQL 中等同於 DATABASE
• ERROR 1406 90% 由字符集不匹配導致，而非真正「數據過長」
• utf8mb4 支援 Emoji (4 字節)，utf8 僅支援 BMP (3 字節)，**生產環境強制 utf8mb4**