MySQL 中文拼音排序兩大實戰方案：CONVERT+COLLATE 簡單法與 HEX+ELT 精確首字母法

業務需求：酒店列表按中文名拼音排序。MySQL 預設 utf8_general_ci 按字符編碼排序（非拼音），需顯式指定拼音規則。本文提供兩種方案，涵蓋「極簡通用」與「精確可控」兩類場景。

方案一：CONVERT + COLLATE —— 一行搞定（推薦通用場景）

SELECT hotel_name
FROM hotel_base
ORDER BY CONVERT(hotel_name USING gbk) COLLATE gbk_chinese_ci;

• 原理：將 UTF-8 字符串轉為 GBK 編碼，GBK 內部按拼音順序排列漢字；再套用 gbk_chinese_ci 校對集，直接按拼音比較。
• 優點：SQL 簡潔、無需建輔助表/函數、支援多字符串欄位聯合排序。
• 缺點：依賴 GBK 字符集（MySQL 內建），極少數生僻字/擴展字可能排序異常；排序時隱式轉換會產生臨時表，大數據量下較慢。

方案二：HEX + ELT + INTERVAL —— 手工提取首字母，精確可控（推薦大數據/高性能場景）

SELECT hotel_id, hotel_name,
       ELT(INTERVAL(CONV(HEX(LEFT(CONVERT(hotel_name USING gbk), 1)), 16, 10),
                    0xB0A1, 0xB0C5, 0xB2C1, 0xB4EE, 0xB6EA, 0xB7A2, 0xB8C1, 0xB9FE,
                    0xBBF7, 0xBFA6, 0xC0AC, 0xC2E8, 0xC4C3, 0xC5B6, 0xC5BE, 0xC6DA,
                    0xC8BB, 0xC8F6, 0xCBFA, 0xCDDA, 0xCEF4, 0xD1B9, 0xD4D1),
           'A','B','C','D','E','F','G','H','J','K','L','M','N','O','P','Q','R','S','T','W','X','Y','Z') AS PY
FROM hotel_base
ORDER BY PY ASC;

• 核心邏輯：
  1. CONVERT(... USING gbk) → 取首字節 GBK 編碼（雙字節）。
  2. LEFT(...,1) 取首字節高位，HEX 轉十六進制，CONV(...,16,10) 轉十進制數值。
  3. INTERVAL(val, boundary1, boundary2...) 判斷落在哪個拼音區間（GBK 漢字區 0xB0A1~0xD4D1 分 24 段）。
  4. ELT(index, 'A','B'...) 映射為首字母。
• 優點：可預計算首字母存入索引列（py_initial CHAR(1)），排序直接走索引，極快；邏輯透明，可自定義多音字修正。
• 缺點：SQL 極長、維護成本高、需 MySQL 支援 GBK（部分雲托管實例已移除）。

性能對比（100 萬行測試）

最佳實踐建議

1. 數據量 < 10 萬：直接用方案一，開發效率最高。
2. 數據量 > 10 萬且高頻排序：表加 py_initial CHAR(1) 列，寫入時觸發器/應用層按方案二邏輯填充，建立索引 INDEX idx_py (py_initial, hotel_name)。
3. 多音字/人名：方案二可人工維護例外映射表（word, py_initial），方案一無法干預。
4. MySQL 8.0+：可用 ICU 校對集 utf8mb4_zh_0900_as_cs 等原生支援拼音排序，無需轉 GBK。

按業務體量選型：「小表用隱式轉換，大表用顯式首字母索引」，兼顧開發速度與查詢性能。