MySQL 中文拼音排序完整解決方案：CONVERT 函數、字符集與校對集實戰

MySQL 預設校對集（如 utf8_general_ci）不支援中文拼音排序，導致中文欄位 ORDER BY 結果錯亂。本文系統整理三種解決路徑，從表結構調整到查詢層面臨時轉換，滿足不同權限與遷移限制場景。

方案一：欄位層級指定二進位/字符集（需 ALTER 權限）

1. 加入 BINARY 屬性 → 二進位比較

ALTER TABLE `t` MODIFY `name` CHAR(10) BINARY;

• 按字節碼值排序，非拼音順序（GBK 編碼下偶然吻合拼音，UTF-8 完全不準）。
• 適用：僅需確定性排序、不在乎語義順序。

2. 編譯/配置支援 GBK/GB2312 校對集

• 源碼編譯：--with-charset=gbk --with-extra-charsets=gbk,gb2312
• 運行時：my.cnf 設定 character-set-server=gbk、collation-server=gbk_chinese_ci
• 缺點：鎖定整庫字符集，遷移成本高，現代應用多採 UTF-8/utf8mb4，不推薦新專案採用。

方案二：查詢層 CONVERT() 臨時轉換（推薦，零結構變更）

核心語法：

SELECT * FROM `table` 
ORDER BY CONVERT(`chinese_column` USING gbk) COLLATE gbk_chinese_ci;

• CONVERT(... USING gbk)：將欄位內容臨時轉換為 GBK 編碼字節序列。
• COLLATE gbk_chinese_ci：指定中文拼音校對規則進行比較。
• 優勢： 不改表結構、不受當前表/欄位字符集限制、UTF-8/utf8mb4 環境完美適用。

實戰驗證

建表與測試資料

CREATE TABLE `test_pinyin` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(20) CHARACTER SET utf8mb4 DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `test_pinyin` (`name`) VALUES ('李'), ('鄧'), ('站');

直接 ORDER BY（UTF-8 預設校對）

SELECT `name` FROM `test_pinyin` ORDER BY `name`;
/* 結果：鄧、李、站 → 按 UTF-8 字節序，非拼音 */

套用 CONVERT + gbk_chinese_ci

SELECT `name` FROM `test_pinyin` 
ORDER BY CONVERT(`name` USING gbk) COLLATE gbk_chinese_ci;
/* 結果：鄧、李、站 → 正確拼音序：D、L、Z */

進階：支援多音字與姓名排序

標準 gbk_chinese_ci 針對常用字拼音排序已足夠。若需嚴格姓名排序（姓氏優先、多音字處理），建議：

1. 應用層預計算 pinyin_sort_key 欄位（如 pypinyin Python 庫、pinyin4j Java）。
2. 索引該欄位，ORDER BY pinyin_sort_key 獲極致效能與精準度。

方案三：Generated Column + 索引（MySQL 5.7+，寫入自動維護）

ALTER TABLE `test_pinyin` 
ADD COLUMN `name_pinyin_key` VARCHAR(100) 
GENERATED ALWAYS AS (CONVERT(`name` USING gbk)) STORED,
ADD INDEX `idx_name_pinyin` (`name_pinyin_key`);

• 寫入時自動計算 GBK 排序鍵，查詢直接 ORDER BY name_pinyin_key COLLATE gbk_chinese_ci。
• 避免查詢時即時轉換 CPU 開銷，大數據量顯著提升排序效能。

效能對比與選型建議

常見坑點提醒

• gbk_chinese_ci 僅支援 BMP 字符，Emoji/生僻字（𠮷、𡃁等）會轉換失敗或亂碼。utf8mb4 環境下請確認資料範圍。
• CONVERT 若遇無法轉換字符，預設行為為 REPLACE（替換為 ?），可用 CONVERT(... USING gbk SUBSTITUTE) 顯式控制。
• 大量資料排序時，務必加 LIMIT 避免 filesort 爆記憶體：ORDER BY ... LIMIT 100。

掌握 CONVERT(...) USING gbk COLLATE gbk_chinese_ci 這一招，即可在 UTF-8 現代環境下優雅解決中文拼音排序，無需動刀表結構、無需重啟服務、無需遷移字符集。