字元集與排序規則：utf8 與 utf8mb4 的陷阱

最經典的陷阱：utf8 不是真正的 UTF-8

在 MySQL 與 MariaDB 中，名為 utf8 的字元集實際上是 utf8mb3，每個字元最多只用三個位元組。

真正完整的 UTF-8 是 utf8mb4，最多四個位元組。

差別在哪

• 常用中文字在三個位元組內，所以 utf8 平常看起來沒問題
• 但表情符號、部分罕用字與擴充區的字需要四個位元組
• 存入時會出現錯誤，或被截斷、變成問號

典型的症狀

• 使用者在留言或表單中輸入表情符號，資料被截斷
• 出現 Incorrect string value 錯誤
• 某些人名或罕用字無法正確儲存

新建的資料庫一律使用 utf8mb4，沒有例外。

排序規則的選擇

字元集決定「能存哪些字」，排序規則決定「怎麼比較與排序」。

實務建議

• 一般網站用不分大小寫的通用規則即可
• 整個資料庫、資料表、欄位要使用一致的排序規則
• MySQL 與 MariaDB 的可用選項不同——跨系統轉移時要注意

不一致會怎樣

JOIN 兩個排序規則不同的欄位時，可能出現：

Illegal mix of collations

或是索引無法使用，導致查詢突然變得極慢。 這是很難察覺的效能陷阱。

檢查目前的設定

-- 資料庫層級
SELECT default_character_set_name, default_collation_name
FROM information_schema.schemata
WHERE schema_name = 'your_database';

-- 資料表層級
SELECT table_name, table_collation
FROM information_schema.tables
WHERE table_schema = 'your_database';

-- 欄位層級
SELECT table_name, column_name, character_set_name, collation_name
FROM information_schema.columns
WHERE table_schema = 'your_database'
  AND character_set_name IS NOT NULL;

三個層級都要檢查。 資料庫改了但舊資料表沒改，是常見的狀況。

轉換為 utf8mb4

轉換前務必確認

1. 完整備份
2. 確認索引長度——見下段
3. 在測試環境先跑一次
4. 評估鎖表時間——大表轉換會鎖住一段時間

轉換指令

-- 資料庫預設
ALTER DATABASE dbname
  CHARACTER SET = utf8mb4
  COLLATE = utf8mb4_unicode_ci;

-- 資料表與既有資料
ALTER TABLE tablename
  CONVERT TO CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

注意 CONVERT TO 會實際轉換既有資料，而只改 DEFAULT CHARACTER SET 只影響之後新增的欄位。

索引長度的限制

這是轉換時最常遇到的障礙。

索引有位元組長度上限。從三個位元組換成四個位元組，同樣的字元數會佔用更多空間，原本可以建立的索引可能超出限制。

可能出現的錯誤

Specified key was too long

處理方式

• 縮短欄位長度——例如 VARCHAR(255) 改為 VARCHAR(191)
• 使用前綴索引——只對前 N 個字元建立索引
• 確認使用較新的列格式與檔案格式——較新的設定支援更長的索引

現代版本多數已預設支援較長的索引，但從舊版沿用的資料表可能仍是舊格式，需要一併調整。

連線層的字元集也要對

資料庫改對了，但連線時宣告的字元集不對，一樣會亂碼。

PHP 端的設定

// PDO
$dsn = 'mysql:host=localhost;dbname=test;charset=utf8mb4';

// mysqli
$mysqli->set_charset('utf8mb4');

不要用 SET NAMES 語句——某些驅動的內部狀態不會同步更新，可能造成跳脫處理的問題。應使用驅動提供的設定方式。

伺服器端的預設

[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

[client]
default-character-set = utf8mb4

亂碼的排查順序

亂碼可能發生在任何一個環節，逐一確認：

1. 資料庫、資料表、欄位的字元集
2. 連線時的字元集
3. 應用程式的內部編碼
4. 網頁輸出的宣告——HTML 的 meta 與 HTTP 標頭
5. 檔案本身的編碼——PHP 檔案應存為不帶標記的 UTF-8

判斷是哪一段出問題

直接用資料庫用戶端查詢，看資料本身是否正確：

• 資料庫中就是亂碼——寫入時的環節有問題，需要修復資料
• 資料庫正常但網頁亂碼——讀取或輸出的環節有問題

這個判斷很重要——前者需要處理資料，後者只需要調整設定。

已經存入的亂碼資料

若資料本身已經以錯誤的編碼存入，直接轉換字元集會讓情況更糟。

常見的處理思路是：先轉為二進位型別保留原始位元組，再轉為正確的字元集。但這類操作風險高，務必先備份並在測試環境驗證。

資料量不大時，從乾淨的來源重新匯入，往往比修復更可靠。

本文以 MySQL 與 MariaDB 的常見版本為例，實際的指令、預設值與可用選項可能因版本與發行版而異。執行任何變更前請確實備份，並先在測試環境驗證。