MySQL 字符集編碼設置與查看命令完全手冊

MySQL 字符集體系涉及服務器、庫、表、列、連接五個層次，配置不當是亂碼的主因。本文系統整理查看命令、修改方法、轉換流程、最佳實踐與避坑指南。

核心概念

• 字符集：字符→編碼映射集合 (如 utf8mb4)
• 校對規則：字符比較/排序規則 (如 utf8mb4_general_ci，_ci=大小寫不敏感)
• 引入器：_utf8mb4 '字符串' 指定字面量編碼，避免轉換損耗

五層字符集變量

查看默認字符集

SHOW VARIABLES LIKE 'character%';
SHOW VARIABLES LIKE 'collation%';

-- 輸出示例 (已優化配置)
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8mb4                    |
| character_set_connection | utf8mb4                    |
| character_set_database   | utf8mb4                    |
| character_set_filesystem | binary                     |
| character_set_results    | utf8mb4                    |
| character_set_server     | utf8mb4                    |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

修改字符集配置

1. 配置文件永久生效 (my.cnf / my.ini)

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

[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

重啟 MySQL 生效：systemctl restart mysqld

2. 會話級臨時修改

SET NAMES utf8mb4;  -- 等價於下面三句
SET character_set_client = utf8mb4;
SET character_set_results = utf8mb4;
SET character_set_connection = utf8mb4;

-- 單獨設置
SET character_set_database = utf8mb4;
SET collation_connection = utf8mb4_general_ci;

3. 建庫建表顯式指定 (推薦)

CREATE DATABASE mydb DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
CREATE TABLE t (c VARCHAR(100)) CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
ALTER TABLE t MODIFY c VARCHAR(100) CHARACTER SET utf8mb4;

字符集轉換流程 (MySQL 內部)

1. 請求到達：character_set_client → character_set_connection
2. 內部操作：按優先級確定 內部操作字符集：
① 列定義 CHARACTER SET ② 表默認 DEFAULT CHARACTER SET ③ 庫默認 DEFAULT CHARACTER SET ④ character_set_server
3. 結果返回：內部字符集 → character_set_results

亂碼產生的典型斷點

檢測字符集問題的 SQL 函數

-- 查看實際存儲字節
SELECT HEX(col), CHAR_LENGTH(col), LENGTH(col) FROM t;

-- 查看列字符集與校對
SELECT CHARSET(col), COLLATION(col) FROM t LIMIT 1;

-- 列出所有字符集/校對
SHOW CHARACTER SET;
SHOW COLLATION;

最佳實踐清單

1. 全鏈路 utf8mb4：my.cnf、建庫、建表、連接、頁面 <meta charset="utf-8">
2. 顯式優於隱式：建庫建表寫 CHARSET=utf8mb4，不依賴默認值
3. 連接池初始化：獲取連接後立即 SET NAMES utf8mb4，重連亦同
4. CAPI 設置：mysql_options(mysql, MYSQL_SET_CHARSET_NAME, "utf8mb4")
5. 避免裸字符串比較：WHERE col = _utf8mb4 '值' 使用引入器
6. 備份指定編碼：mysqldump --default-character-set=utf8mb4

常見誤區辨析

總結

MySQL 字符集配置的核心是五層統一。採用 utf8mb4 + 顯式指定 + 連接初始化 SET NAMES 模式，配合 HEX()/CHARSET() 診斷工具，可構建零亂碼的數據庫環境。新項目直接上 utf8mb4，舊項目按「二進制中轉」遷移。