MySQL 中文亂碼終極解決：UTF-8 全鏈路配置、導入避坑與網頁顯示修復

亂碼三大根因

1. Server 級字符集未設為 UTF-8 (舊版默認 latin1)
2. 表/列級字符集與排序規則不一致
3. 客戶端連接字符集 (PHP/JAVA/Python) 未正確設置

UTF-8 全鏈路配置清單

1. 建庫建表指定字符集

CREATE DATABASE `test` CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';

CREATE TABLE `user` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2. 檢查當前字符集狀態

SHOW VARIABLES LIKE '%char%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
+--------------------------+----------------------------+

關鍵三項：client、connection、results 必須一致。

3. 臨時設置連接字符集

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

4. 永久配置 my.cnf

[mysqld]
character-set-server = utf8
collation-server = utf8_general_ci
init_connect = 'SET NAMES utf8'

[client]
default-character-set = utf8

[mysql]
default-character-set = utf8

導入數據避坑指南

標準導入流程

1. 文件另存為 UTF-8 無 BOM
2. 文件首行添加 /*!40101 SET NAMES utf8 */;
3. 命令行執行：

mysql -u user -p --default-character-set=utf8 db_name < backup.sql

網頁端修復

1. 頁面聲明：<meta charset="UTF-8"> (HTML5) 或 <meta http-equiv="Content-Type" content="text/html; charset=utf-8">
2. HTTP Header：Content-Type: text/html; charset=utf-8
3. 數據庫連接池配置：

// JDBC URL
jdbc:mysql://host:3306/db?useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull

// PHP PDO
$pdo = new PDO("mysql:host=localhost;dbname=test;charset=utf8", $user, $pass);

GBK vs UTF-8 選型建議

建議：新項目統一 utf8mb4，舊項目 GBK 可維持但遷移規劃中納入。