MySQL 支持 Emoji 表情字符存儲：utf8mb4 字符集遷移全流程與驗證指南

MySQL 支持 Emoji 表情字符存儲：utf8mb4 字符集遷移全流程與驗證指南

MySQL 5.5.3+ 引入 utf8mb4，真正支持 4 字節 Unicode 字符（Emoji、生僻字）。舊 utf8 僅支持 3 字節，存 Emoji 會報錯或截斷。本文記錄從 utf8 遷移至 utf8mb4 的服務端配置、庫表轉換 SQL 生成、驗證查詢全步驟。

前置條件

• MySQL ≥ 5.5.3（主從均需滿足，否則復制報錯）。
• 停止服務修改配置。

1. 修改服務端配置 my.cnf

[client]
default-character-set = utf8mb4
[mysql]
default-character-set = utf8mb4
[mysqld]
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
init_connect='SET NAMES utf8mb4'

重啟 MySQL，驗證：

SHOW VARIABLES WHERE Variable_name LIKE 'character%' OR Variable_name LIKE 'collation%';

預期所有 character_set_* 均為 utf8mb4，collation_* 為 utf8mb4_unicode_ci。

2. 檢查當前庫表列字符集

-- 庫級
SELECT * FROM information_schema.SCHEMATA WHERE SCHEMA_NAME='your_db';
-- 表級
SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_COLLATION FROM information_schema.TABLES WHERE TABLE_SCHEMA='your_db';
-- 列級
SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, COLLATION_NAME FROM information_schema.COLUMNS WHERE TABLE_SCHEMA='your_db';

3. 生成批量轉換 SQL

在 information_schema 執行以下查詢，導出結果直接執行：

-- 庫轉換
SELECT CONCAT('ALTER DATABASE `', TABLE_SCHEMA, '` CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;')
FROM TABLES WHERE TABLE_SCHEMA LIKE 'your_db' GROUP BY TABLE_SCHEMA;

-- 表轉換
SELECT CONCAT('ALTER TABLE `', TABLE_SCHEMA, '`.`', TABLE_NAME, '` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;')
FROM TABLES WHERE TABLE_SCHEMA LIKE 'your_db' GROUP BY TABLE_SCHEMA, TABLE_NAME;

-- 列轉換（VARCHAR/TEXT 類型）
SELECT CONCAT('ALTER TABLE `', TABLE_SCHEMA, '`.`', TABLE_NAME, '` CHANGE `', COLUMN_NAME, '` `', COLUMN_NAME, '` ', DATA_TYPE, '(', CHARACTER_MAXIMUM_LENGTH, ') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;')
FROM COLUMNS WHERE TABLE_SCHEMA LIKE 'your_db' AND DATA_TYPE IN ('varchar','text','tinytext','mediumtext','longtext');

執行生成的 SQL 即可完成遷移。注意：CONVERT TO 會重建表，大表需規劃維護窗口或用 pt-online-schema-change。