MySQL 存儲 Emoji 表情字符：utf8mb4 字符集遷移全流程指南

MySQL 5.5.3+ 引入 utf8mb4，真正支援 4 字節 Unicode（含 Emoji、生僻字）。本文詳述從 utf8 遷移至 utf8mb4 的服務端配置、對象級轉換 SQL 生成、驗證清單，助你徹底解決「Incorrect string value」報錯。

版本前置條件

• 主庫 ≥ 5.5.3
• 從庫 ≥ 5.5.3（否則復制報錯）
• 應用端驅動 ≥ 5.1.13 (Connector/J) 或啟用 characterEncoding=utf8mb4

服務端配置 my.cnf

[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

[mysqld]
character-set-client-handshake = FALSE  # 強制忽略客戶端聲明，統一 utf8mb4
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
init_connect = 'SET NAMES utf8mb4'

重啟後驗證：

SHOW VARIABLES LIKE 'character%';
-- 期望：client/connection/database/results/server 均為 utf8mb4
-- system 仍為 utf8（內部元數據固定，不影響業務）

對象級字符集檢查

-- 數據庫級
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' AND DATA_TYPE IN ('varchar','text','char');

一鍵生成轉換 SQL（資料庫/表/列三級）

USE information_schema;

-- 1. 數據庫默認字符集
SELECT CONCAT('ALTER DATABASE `', TABLE_SCHEMA, '` CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;') AS _sql
FROM TABLES WHERE TABLE_SCHEMA = 'your_db' GROUP BY TABLE_SCHEMA;

-- 2. 表默認字符集（不轉換現有列）
SELECT CONCAT('ALTER TABLE `', TABLE_SCHEMA, '`.`', TABLE_NAME, '` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;') AS _sql
FROM TABLES WHERE TABLE_SCHEMA = 'your_db' GROUP BY TABLE_SCHEMA, TABLE_NAME;

-- 3. 列級顯式轉換（針對 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;') AS _sql
FROM COLUMNS WHERE TABLE_SCHEMA = 'your_db' AND DATA_TYPE IN ('varchar','char');

SELECT CONCAT('ALTER TABLE `', TABLE_SCHEMA, '`.`', TABLE_NAME, '` CHANGE `', COLUMN_NAME, '` `', COLUMN_NAME, '` ',
              DATA_TYPE, ' CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;') AS _sql
FROM COLUMNS WHERE TABLE_SCHEMA = 'your_db' AND DATA_TYPE IN ('text','tinytext','mediumtext','longtext');

執行生成的 SQL 即可完成遷移。大表建議使用 pt-online-schema-change 或 gh-ost 在線變更，避免鎖表。

驗證 Emoji 寫入

INSERT INTO test_emoji (content) VALUES ('😀🎉🚀');
SELECT content, HEX(content) FROM test_emoji;
-- HEX 應顯示 F09F9880 F09F8E89 F09F9A80（4 字節/字符）

常見坑點避雷

1. 連接池未重建：舊連接仍持有 utf8 字符集，需重啟應用或配置 connectionInitSql=SET NAMES utf8mb4。
2. JDBC URL 缺參數：jdbc:mysql://host:3306/db?characterEncoding=utf8mb4&useUnicode=true。
3. 索引長度超限：utf8mb4 單字符 4 字節，VARCHAR(255) 索引長度 1020 字節 < InnoDB 767 字節限制（5.7.7+ innodb_large_prefix=ON 可放寬至 3072）。解決：縮短列長度或升級版本開啟大前綴。
4. 從庫版本過低：5.5.3 以下從庫復制報錯，必須同步升級。