MySQL 排序規則詳解：COLLATE 子句、校對集後綴含義與實戰選型

MySQL 排序規則深度解析：COLLATE、後綴語義、選型指南

COLLATE 定義字符串比較、排序的規則集，影響 ORDER BY、GROUP BY、DISTINCT、WHERE col = 'val'、索引順序等所有字符串操作。

一、COLLATE 語法位置

• 庫級：CREATE DATABASE db CHARSET utf8mb4 COLLATE utf8mb4_general_ci;
• 表級：CREATE TABLE t (c VARCHAR(20)) CHARSET utf8mb4 COLLATE utf8mb4_bin;
• 列級：CREATE TABLE t (c VARCHAR(20) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci);
• 表達式級：SELECT 'a' = 'A' COLLATE utf8mb4_bin; -- 0 (區分大小寫)

二、校對集命名結構與後綴含義

格式：__，如 utf8mb4_general_ci、utf8mb4_unicode_520_ci、utf8mb4_zh_0900_ai_ci。

三、常用校對集對比

四、實戰選型建議

1. 新項目：庫/表/列統一 utf8mb4_0900_ai_ci（8.0）或 utf8mb4_unicode_ci（5.7）。
2. 需區分大小寫：列級指定 COLLATE utf8mb4_bin 或 _cs 後綴。
3. 中文排序按拼音/筆畫：8.0+ 可用 utf8mb4_zh_0900_ai_ci（拼音）或 utf8mb4_zh_0900_as_cs（筆畫）。
4. 避免混用：同一庫內不同表/列使用不同 COLLATE 會導致隱式轉換、索引失效、比較報錯（Illegal mix of collations）。

五、查看支援的校對集

SHOW COLLATION LIKE 'utf8mb4%';
SHOW COLLATION WHERE Charset = 'gbk';

總結

COLLATE 決定「字符串如何比較與排序」。選型原則：統一字符集 utf8mb4，校對集按業務語言需求選 _ci/_ai/_bin，全庫全表保持一致，避免隱式轉換帶來的性能損耗與邏輯錯誤。