MySQL UTF-8 中文亂碼終極解決方案：從庫表建立到應用層全鏈路統一

MySQL UTF-8 中文亂碼終極解決方案：從庫表建立到應用層全鏈路統一

中文亂碼是 MySQL 開發中最經典、最讓人頭痛的問題。其本質是**編碼鏈路任一環節不一致**導致的解碼錯誤。本文提供從資料庫建立、表結構設計、連線配置、應用層設定到頁面輸出的全鏈路統一標準化方案，徹底根治亂碼。

一、核心原理：編碼鏈路五環節

資料流向：頁面/應用 → 連線協議 → MySQL Server → 表欄位 → 存儲引擎

鐵律：五個環節必須 100% 一致，缺一不可。

二、資料庫與表建立：源頭治理

建庫標準語句

CREATE DATABASE `dbname` 
  DEFAULT CHARACTER SET utf8mb4 
  COLLATE utf8mb4_unicode_ci;

建表標準語句（中文欄位顯式指定）

CREATE TABLE `test` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='測試表';

關鍵點：

• 必須使用 utf8mb4 而非 utf8：MySQL 的 utf8 實為 utf8mb3，**不支援 Emoji 及生僻字**（4 字節字符），會導致截斷或報錯
• COLLATE utf8mb4_unicode_ci：基於 Unicode 標準排序，支援多語言；utf8mb4_general_ci 速度略快但排序準確度較低
• 表級 DEFAULT CHARSET 為新增欄位提供預設，欄位級可覆蓋

三、應用層連線配置：關鍵橋樑

PHP (mysqli/PDO)

// mysqli 面向對象
$mysqli = new mysqli($host, $user, $pass, $db, $port);
$mysqli->set_charset('utf8mb4');  // 單一調用替代下面兩行

// PDO (推薦)
$dsn = "mysql:host=$host;port=$port;dbname=$db;charset=utf8mb4";
$pdo = new PDO($dsn, $user, $pass, [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_EMULATE_PREPARES => false
]);

Java (JDBC / Spring Boot)

# JDBC URL (8.0+ 無需 characterEncoding)
jdbc:mysql://host:3306/db?useUnicode=true&characterEncoding=utf8mb4&serverTimezone=Asia/Shanghai

# Spring Boot application.yml
spring:
  datasource:
    url: jdbc:mysql://host:3306/db?useUnicode=true&characterEncoding=utf8mb4&serverTimezone=Asia/Shanghai
    hikari:
      connection-init-sql: "SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci"

Python (PyMySQL / SQLAlchemy)

# PyMySQL
conn = pymysql.connect(host=host, user=user, password=pass, db=db, charset='utf8mb4')

# SQLAlchemy
engine = create_engine('mysql+pymysql://user:pass@host/db?charset=utf8mb4')

Node.js (mysql2)

const pool = mysql.createPool({
  host, user, password, database,
  charset: 'utf8mb4',
  // 或連線後執行
  // connection.query('SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci')
});

四、歷史專案遷移：從 GBK/UTF8 升級至 UTF8MB4

1. 修改 Server 配置 (my.cnf)：

   [mysqld]
   character_set_server = utf8mb4
   collation_server = utf8mb4_unicode_ci
   # 客戶端預設也建議設定
   [client]
   default-character-set = utf8mb4

2. 轉換現有庫表：

   -- 轉換資料庫預設
   ALTER DATABASE `dbname` CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;
   
   -- 轉換表及所有欄位（會重建表，大表謹慎）
   ALTER TABLE `tbl` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
   
   -- 僅修改表預設（不轉換現有欄位）
   ALTER TABLE `tbl` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

3. 檢查最大長度限制：utf8mb4 單字符最多 4 字節，VARCHAR(255) 索引長度可能超過 767 字節限制（5.7 預設），需開啟 innodb_large_prefix=ON + innodb_file_format=Barracuda + ROW_FORMAT=DYNAMIC（8.0 預設已解決）
4. 應用層同步修改連線字符集：同上節配置

五、驗證與排查清單

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

-- 2. 檢查表/欄位實際編碼
SHOW CREATE TABLE `tbl`\G
SELECT COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_SCHEMA='db' AND TABLE_NAME='tbl';

-- 3. 實測寫入/讀取
INSERT INTO `tbl`(`name`) VALUES('測試😀');
SELECT `name`, HEX(`name`) FROM `tbl` WHERE `name`='測試😀';
-- HEX 應顯示：E6B58BE8AF95F09F9880 (UTF-8 編碼)

六、常見亂碼現象與成因對照表

徹底解決中文亂碼沒有捷徑，唯有**全鏈路統一 utf8mb4**、**新專案起步即標準化**、**舊專案計劃遷移驗證**。將本方案落地為團隊「編碼規範文檔」，納入代碼審查清單，才能從根源杜絕亂碼困擾。

1. 上一篇文章： MySQL DDL 語句完整手冊：庫表結構管理的核心語法與細節陷阱
2. 下一篇文章： MySQL DATE_ADD 函數實戰指南：時間日期運算的完整場景覆蓋

MySQL 綜合教學

《MySQL必知必會》讀書筆記（三）：聚合函數、分組、聯結、全文搜尋與 DDL 實戰

《MySQL必知必會》讀書筆記（三）：聚合函數、分組、聯結、

MySQL 5.7 ZIP 版 Windows 安裝與配置（my.ini 範例）

MySQL 5.7 ZIP 免安裝版 Windows 部署配

MSQL v1.0 釋出：輕量級 SQL 引擎核心元件與展示

MSQL v1.0 釋出：輕量級 SQL 引擎核心元件與展示

MySQL 事務完整教程：ACID 特性、啟動提交回滾、隱式提交陷阱與轉賬實戰

事務四大特性 (ACID) 特性含義MySQL 實現