MySQL 字符集修改與查看全攻略：服務器、數據庫、表、列、連接五層級配置

MySQL 字符集修改與查看全攻略：服務器、數據庫、表、列、連接五層級配置

MySQL 字符集配置分為五個層級，層層繼承又可單獨覆蓋。本文系統梳理查看命令、修改方法、生效範圍與最佳實踐，構建全鏈路 utf8mb4 體系。

一、五層級字符集架構

二、查看字符集配置

-- 1. 服務器級變量
SHOW VARIABLES LIKE 'character_set_%';
SHOW VARIABLES LIKE 'collation_%';

-- 2. 數據庫級
SHOW CREATE DATABASE db_name;
SELECT DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME 
FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME='db_name';

-- 3. 表級
SHOW CREATE TABLE tbl_name;
SELECT TABLE_COLLATION FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='db' AND TABLE_NAME='tbl';

-- 4. 列級
SHOW FULL COLUMNS FROM tbl_name;
SELECT COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME 
FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA='db' AND TABLE_NAME='tbl';

-- 5. 連接級 (當前會話)
SHOW VARIABLES LIKE 'character_set_client';
SHOW VARIABLES LIKE 'character_set_connection';
SHOW VARIABLES LIKE 'character_set_results';
-- 或
STATUS;  -- mysql 客戶端命令

三、修改字符集命令大全

1. 服務器級 (需重啟)

# my.cnf / my.ini
[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

# 臨時生效 (重啟失效)
SET GLOBAL character_set_server = utf8mb4;
SET GLOBAL collation_server = utf8mb4_unicode_ci;

2. 數據庫級 (僅影響新建表)

-- 臨時
SET GLOBAL character_set_database = utf8mb4;  -- 不推薦，易混淆

-- 永久 (實際修改庫默認屬性)
ALTER DATABASE db_name DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

3. 表級 (僅影響新建列)

-- 修改表默認字符集 (不轉換現有列)
ALTER TABLE tbl_name DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 完整轉換表及所有列 (重建表、鎖表、耗時)
ALTER TABLE tbl_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

4. 列級 (精確控制、重建表)

-- 單列修改
ALTER TABLE products 
MODIFY products_model VARCHAR(20) 
CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci 
NULL DEFAULT NULL;

-- 批量生成腳本
SELECT CONCAT('ALTER TABLE `', TABLE_NAME, '` MODIFY `', COLUMN_NAME, '` ', 
       COLUMN_TYPE, ' CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci',
       IF(IS_NULLABLE='NO', ' NOT NULL', ''), ';')
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA='db' AND CHARACTER_SET_NAME != 'utf8mb4';

5. 連接級 (應用層關鍵)

-- 臨時 (當前會話)
SET NAMES utf8mb4;
-- 等價於
SET character_set_client = utf8mb4;
SET character_set_connection = utf8mb4;
SET character_set_results = utf8mb4;
SET collation_connection = utf8mb4_unicode_ci;

-- 永久 (驅動/連接池配置)
# JDBC
jdbc:mysql://host:3306/db?characterEncoding=utf8mb4&useSSL=false

# Python (PyMySQL)
conn = pymysql.connect(charset='utf8mb4')

# PHP (PDO)
$pdo = new PDO("mysql:host=host;dbname=db;charset=utf8mb4", user, pass);

# 連接池初始化 SQL (HikariCP/Druid)
connectionInitSqls: ["SET NAMES utf8mb4"]

四、utf8 vs utf8mb4 關鍵決策

結論：全鏈路強制 utf8mb4，無理由使用 utf8mb3。

五、C 語言 API 操作字符集

// 獲取當前連接字符集信息
MY_CHARSET_INFO charset;
mysql_get_character_set_info(mysql, &charset);
printf("charset: %s, collation: %s\n", charset.name, charset.csname);

// 設置連接字符集
int ret = mysql_set_character_set(mysql, "utf8mb4");
if (ret != 0) {
    fprintf(stderr, "Set charset failed: %s\n", mysql_error(mysql));
}

六、遷移實施路線圖

1. 基線掃描：導出所有非 utf8mb4 對象 (庫/表/列)
2. 配置固化：my.cnf 服務器級 + 客端級配置
3. 分批轉換：低峰期、小表優先、pt-osc 大表零停機
4. 驗證機制：Checksum 對比、應用端插入/查詢 Emoji/生僻字測試
5. 監控告警：定期掃描非 utf8mb4 新增對象、字符集相關錯誤率

七、總結

字符集配置是「五層級架構 + 連接協商 + 全鏈路一致性」的系統工程。服務器級 utf8mb4 為基、連接級 SET NAMES 為本、列級 CONVERT TO 為實，三位一體才能徹底杜絕亂碼、截斷、報錯等字符集顽疾。