通俗易懂解決中文亂碼 (二)：MySQL 插入 Emoji/特殊字符報錯實戰定位與修復

前言

上一篇講解了字符編碼基礎與跨平台亂碼成因。本篇聚焦實戰：MySQL 插入資料報錯 Incorrect string value: '\xF0\x9F\x94\xA5\xE5\x8C...'，如何快速定位並徹底修復。

一、精準定位錯誤 —— 最關鍵的一步

「不是所有的牛奶都叫特侖蘇」—— 同樣報錯，成因可能截然不同，盲目套用網路方案往往失效。

1. 觀察錯誤碼

報錯十六進位 \xF0\x9F\x94\xA5 對應 Unicode U+1F525 (**🔥 火焰 Emoji**)，屬於 **增補字符平面 (Supplementary Planes)**，需 **4 字節 UTF-8 編碼 (utf8mb4)**。

2. 確認問題欄位內容

透過應用端日誌或除錯工具，印出入庫前的實際字串值。本案例中 event_title 欄位包含 🔥 字符。

二、根因分析：utf8 vs utf8mb4

結論：資料庫/表/欄位定義為 utf8，無法儲存 4 字節字符，導致寫入失敗。

三、完整修復方案 (生產環境可直接執行)

方案 A：修改現有資料庫/表/欄位 (需停機維護窗口)

-- 1. 修改資料庫預設編碼
ALTER DATABASE your_db CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

-- 2. 修改表預設編碼並轉換現有欄位
ALTER TABLE your_table CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 3. 若僅需修改特定欄位
ALTER TABLE your_table MODIFY event_title VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

注意：CONVERT TO 會重建表並轉換所有字串欄位資料，**大表會鎖表較久**，建議使用 pt-online-schema-change 或 gh-ost 線上變更。

方案 B：新建表/庫直接指定 utf8mb4 (遷移數據)

CREATE DATABASE new_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE TABLE new_table (...) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

方案 C：應用層連線字符集同步修改 (必須)

資料庫改完，**應用連線字符集必須同步為 utf8mb4**，否則讀寫仍會轉碼錯誤。

• JDBC：jdbc:mysql://host:3306/db?characterEncoding=utf8mb4&useUnicode=true (MySQL Connector/J 5.1.13+ 支援)
• Python (PyMySQL/mysqlclient)：charset='utf8mb4'
• PHP (PDO)：charset=utf8mb4 in DSN
• Go (go-sql-driver/mysql)：charset=utf8mb4 in DSN

四、避坑指南：索引長度限制

InnoDB 預設索引鍵長度上限 **767 字節** (MySQL 5.6 / 5.7 預設 innodb_large_prefix=OFF)。

• utf8：VARCHAR(255) → 255×3 = 765 字節 ✅
• utf8mb4：VARCHAR(255) → 255×4 = 1020 字節 ❌ 超過 767

解決辦法

1. 縮短欄位長度：VARCHAR(191) (191×4=764) —— 最簡單。
2. 開啟大索引前綴 (需 MySQL 5.7.7+ / 5.6.20+，且 innodb_file_format=Barracuda、ROW_FORMAT=DYNAMIC/COMPRESSED)：

   SET GLOBAL innodb_large_prefix = ON;
   SET GLOBAL innodb_file_format = Barracuda;
   ALTER TABLE t ROW_FORMAT=DYNAMIC;

3. 升級 MySQL 8.0：預設 innodb_large_prefix=ON、ROW_FORMAT=DYNAMIC，索引上限 3072 字節，無此煩惱。

五、驗證修復

-- 1. 確認編碼
SHOW CREATE TABLE your_table;
SHOW VARIABLES LIKE 'character_set_%';

-- 2. 實測插入 Emoji
INSERT INTO your_table (event_title) VALUES ('測試 🔥 火焰');
SELECT * FROM your_table WHERE event_title LIKE '%🔥%';

總結

1. 定位：十六進位錯誤碼 → 確認是 4 字節字符 (Emoji/生僻字)。
2. 根因：資料庫/表/欄位為 utf8 (實為 utf8mb3)，不支援 4 字節。
3. 修復：全鏈路升級至 utf8mb4 (資料庫 → 表 → 欄位 → 應用連線)。
4. 索引：注意 VARCHAR(255) 索引長度超限，縮至 191 或開啟 innodb_large_prefix。
5. 最佳實踐：新專案**一律直接使用 utf8mb4**，避免後續遷移痛苦。