MySQL ─ CHARSET 與 COLLATION 設定

---

簡介

在 MySQL 中，**字元編碼（CHARSET）與校對規則（COLLATION）**是資料庫、資料表與欄位層級最基礎卻最重要的設定。
它們直接決定了資料如何儲存、檢索以及比較的行為。若在開發初期選錯編碼或校對規則，往往會在多語系、搜尋、排序或資料匯入時碰到「亂碼」或「不正確的排序」等問題，修正成本相當高。

本單元將帶你深入了解 CHARSET 與 COLLATION 的概念、常見的設定方式與實務應用，讓你在設計資料庫時能 一次到位，避免日後的字元問題。

---

核心概念

1. 什麼是 CHARSET（字元編碼）

• 定義：CHARSET 決定了資料在硬碟上以什麼位元組序列儲存。不同的編碼會使用不同的位元組長度，例如 utf8mb4 每個字元最長 4 個位元組，而 latin1 只使用單一位元組。
• 常見選項
  • utf8mb4：完整支援 Unicode（包括表情符號），是目前最推薦的預設編碼。
  • utf8：MySQL 的舊版 UTF‑8，只支援最多 3 個位元組的 Unicode，無法儲存部分表情符號。
  • latin1、ascii：僅適用於單語系、英文或簡易西歐語系。

⚠️ 小提醒：若資料庫已建立為 utf8，之後直接改為 utf8mb4 必須重新整理資料表與欄位，否則仍會受到舊編碼的限制。

2. 什麼是 COLLATION（校對規則）

• 定義：COLLATION 控制字串比較與排序的方式，包含大小寫、重音、語系特有的字母順序等。

• 結構：<charset>_<locale>_<strength>，例如 utf8mb4_unicode_ci。

  • ci → case‑insensitive（不區分大小寫）
  • cs → case‑sensitive（區分大小寫）
  • ai → accent‑insensitive（不區分重音）

• 常見校對規則

  • utf8mb4_general_ci：速度較快，但排序規則較簡單，適用於大多數英文或混合語系的應用。
  • utf8mb4_unicode_ci：依照 Unicode 標準排序，較嚴謹，適合多語系平台。
  • utf8mb4_0900_ai_ci（MySQL 8.0+）：支援最新 Unicode 9.0，並提供 accent‑insensitive、case‑insensitive 的行為。

💡 小技巧：若不確定使用哪個 COLLATION，直接選擇 utf8mb4_0900_ai_ci（MySQL 8.0）或 utf8mb4_unicode_ci（MySQL 5.7）是最保險的選擇。

3. 設定層級

MySQL 允許在三個層級指定 CHARSET 與 COLLATION：

層級	指令範例	說明
伺服器	SET @@character_set_server = 'utf8mb4';	影響未指定編碼的新資料庫。
資料庫	CREATE DATABASE blog CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;	建立資料庫時指定。
資料表/欄位	CREATE TABLE posts ( title VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci );	最細緻的控制，優先於上層設定。

⚙️ 設定優先順序：欄位 > 資料表 > 資料庫 > 伺服器。若欄位未指定，會繼承資料表的設定，依此類推。

4. 變更已存在的編碼與校對規則

-- 1. 變更資料庫的編碼與校對規則
ALTER DATABASE blog CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

-- 2. 變更整個資料表的編碼與校對規則
ALTER TABLE posts CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 3. 單一欄位變更（常用於修正特定欄位的問題）
ALTER TABLE posts MODIFY title VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL;

⚠️ 注意：CONVERT TO 會重新寫入整個表格，若表格很大，請先備份並在低流量時段執行。

---

程式碼範例

以下提供 5 個實務上常見的範例，說明如何在不同情境下使用 CHARSET 與 COLLATION。

範例 1：建立支援多語系的資料庫

-- 建立資料庫，使用最完整的 Unicode 編碼與校對規則
CREATE DATABASE ecommerce
    CHARACTER SET utf8mb4          -- 使用 4 位元組的 Unicode
    COLLATE utf8mb4_0900_ai_ci;    -- 大小寫與重音皆不敏感，支援最新 Unicode

此設定適合電商平台，需要同時儲存中文、日文、韓文及表情符號。

範例 2：資料表層級指定不同的 COLLATION

CREATE TABLE customers (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    name        VARCHAR(100) NOT NULL,
    email       VARCHAR(150) NOT NULL,
    country     VARCHAR(50)  NOT NULL
) ENGINE=InnoDB
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;   -- 以 Unicode 排序，適合多語系搜尋

若某欄位需要大小寫區分（例如密碼雜湊），可在欄位層級覆寫：

ALTER TABLE customers
    MODIFY password_hash BINARY(60) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL;

範例 3：搜尋時指定 COLLATION（避免大小寫問題）

-- 找出所有姓氏為「張」的使用者，忽略大小寫與重音
SELECT *
FROM users
WHERE last_name COLLATE utf8mb4_unicode_ci = '張';

在 MySQL 8.0 以前，若資料表使用 utf8mb4_general_ci，上述比較仍會正確執行，但排序結果可能與 Unicode 標準不同。

範例 4：使用 LIKE 搭配 COLLATION

-- 查詢所有以「test」開頭的帳號，大小寫不敏感
SELECT username
FROM accounts
WHERE username LIKE 'test%' COLLATE utf8mb4_general_ci;

若資料表本身是 utf8mb4_bin（大小寫敏感），必須在查詢中明確指定要使用的 COLLATION。

範例 5：在 MySQL 5.7 中升級至 utf8mb4

-- 1. 先檢查目前的編碼設定
SELECT @@character_set_server, @@collation_server;

-- 2. 設定全域預設為 utf8mb4（重啟後生效）
SET GLOBAL character_set_server = 'utf8mb4';
SET GLOBAL collation_server = 'utf8mb4_unicode_ci';

-- 3. 逐一轉換既有資料庫與資料表
ALTER DATABASE legacy_db CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;
ALTER TABLE legacy_table CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

升級過程中，務必使用 mysqldump --default-character-set=utf8 先備份資料，避免因編碼不一致而產生資料遺失。

---

常見陷阱與最佳實踐

陷阱	說明	解決方式
使用 utf8 而非 utf8mb4	utf8 無法儲存 4 位元組的 Unicode（如 Emoji、某些中文擴充字）	直接採用 utf8mb4，或在升級時執行 ALTER TABLE … CONVERT TO CHARACTER SET utf8mb4
校對規則不一致導致比較錯誤	例如資料表使用 utf8mb4_bin，但搜尋時未指定 COLLATION，會出現大小寫敏感的結果	在查詢時明確使用 COLLATE，或統一資料表的 COLLATION 為 utf8mb4_unicode_ci
忘記同步伺服器與資料庫層級設定	只改變 character_set_server，新建資料庫仍使用舊的預設值	同時設定 character_set_server、collation_server，並在建立資料庫時明確指定
字元長度與欄位大小的誤判	VARCHAR(255) 在 utf8mb4 下最長可佔 1020 位元組，若使用 ROW_FORMAT=COMPACT 會超過行限制	使用 ROW_FORMAT=DYNAMIC 或調整欄位長度；MySQL 5.7+ 預設已支援長度自動調整
備份與還原時編碼不匹配	mysqldump 預設使用 latin1，導致匯入時變成亂碼	加上 --default-character-set=utf8mb4 參數，或在 my.cnf 中設定 default-character-set=utf8mb4

最佳實踐清單

1. 統一使用 utf8mb4：從一開始就把資料庫、資料表與欄位的 CHARSET 設為 utf8mb4。
2. 選擇符合需求的 COLLATION：
   • 多語系平台 → utf8mb4_unicode_ci 或 utf8mb4_0900_ai_ci（MySQL 8.0+）
   • 需要精確排序（如字典） → utf8mb4_unicode_520_ci（支援 Unicode 5.2）
   • 完全大小寫區分 → utf8mb4_bin
3. 在開發環境與生產環境保持相同設定：使用 my.cnf 中的 character-set-server 與 collation-server 參數，避免環境差異。
4. 測試資料遷移：在升級或搬移資料前，先在測試庫執行 SHOW CREATE TABLE，確認編碼與校對規則。
5. 使用 ROW_FORMAT=DYNAMIC（MySQL 5.7+）避免因 VARCHAR 長度過大而觸發行大小限制。

---

實際應用場景

1. 電子商務平台的商品描述

商品名稱、說明與評論往往包含多國語言與 Emoji。若資料庫僅使用 utf8，使用者在輸入表情符號時會被截斷或出現「?」字元。解決方案：

CREATE DATABASE shop
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_0900_ai_ci;

所有文字欄位（product_name、description、review_text）皆使用 utf8mb4，確保完整儲存。

2. 會員系統的帳號與密碼

帳號通常不區分大小寫，但密碼雜湊（binary）必須完全相同。做法：

CREATE TABLE members (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    username    VARCHAR(50)  CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
    password    VARBINARY(60) NOT NULL   -- binary，不受 COLLATION 影響
);

在登入驗證時，使用 BINARY 比對確保大小寫敏感。

3. 文字搜尋與排序

在搜尋功能中，使用者可能輸入帶有重音的字母（例如法文「é」）。若採用 utf8mb4_unicode_ci，搜尋會自動忽略重音，提升使用者體驗：

SELECT name
FROM cities
WHERE name LIKE '%e%' COLLATE utf8mb4_unicode_ci;

若需要精確匹配（如密碼或代碼），改用 utf8mb4_bin。

---

總結

• CHARSET 決定資料的儲存方式；COLLATION 控制字串比較與排序。兩者缺一不可。
• 建議：從一開始就使用 utf8mb4 + utf8mb4_unicode_ci（或 utf8mb4_0900_ai_ci），可兼容多語系與 Emoji，減少未來遷移成本。
• 層級設定：欄位 > 資料表 > 資料庫 > 伺服器，設定越細緻越安全。
• 常見陷阱：錯誤的編碼、校對規則不一致、備份時編碼遺失，皆會導致亂碼或比較錯誤。
• 最佳實踐：統一編碼、明確指定 COLLATION、使用 ROW_FORMAT=DYNAMIC、在測試環境驗證遷移。

掌握了 CHARSET 與 COLLATION 的概念與操作，你就能在 MySQL 中建立 穩定、可擴充、支援全球化 的資料庫，為未來的功能開發與國際化鋪設堅實基礎。祝開發順利，資料永遠不會「亂碼」！