萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL 中文排序問題完全解決:CONVERT、COLLATE、Binary 三大方案對比

為什麼中文排序會亂?

MySQL 預設校對規則 utf8mb4_general_ci / utf8_general_ci 按字元編碼值排序,非拼音/筆畫/部首順序,導致「張三」排在「李四」後面等非預期結果。

方案對比

方案一:CONVERT 轉碼排序(無需改表結構,即時生效)

-- 轉為 gbk 排序(按拼音/國標序)
SELECT * FROM users ORDER BY CONVERT(name USING gbk);

-- 指定校對規則
SELECT * FROM users ORDER BY CONVERT(name USING gbk) COLLATE gbk_chinese_ci;
  • 優點:不改表、不加索引、靈活
  • 缺點:每次查詢需轉碼,大表效能稍差;需伺服器安裝 gbk 字元集

方案二:欄位層級指定字元集/校對(永久生效,查詢最快)

-- 建表時指定
CREATE TABLE users (
  name VARCHAR(50) CHARACTER SET gbk COLLATE gbk_chinese_ci
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 修改既有欄位
ALTER TABLE users MODIFY name VARCHAR(50) CHARACTER SET gbk COLLATE gbk_chinese_ci;

-- 查詢直接 ORDER BY name 即可按中文排序
  • 優點:查詢零成本,索引天然按中文序
  • 缺點:該欄位只能存 gbk 字元,Emoji/生僻字無法存儲

方案三:BINARY / 二進制比較(最簡單,但非語義排序)

-- 建表
CREATE TABLE users (name VARCHAR(50) BINARY);

-- 或查詢時
SELECT * FROM users ORDER BY CAST(name AS BINARY);
-- 等同
SELECT * FROM users ORDER BY name COLLATE utf8mb4_bin;
  • 優點:無需額外字元集,所有字元按碼點排序
  • 缺點:大寫字母在小寫前、符號在漢字前、非語言學排序

方案四:MySQL 8.0 ICU 校對規則(最現代,支援多語言)

-- 查看可用 ICU 校對
SELECT * FROM information_schema.COLLATIONS WHERE COLLATION_NAME LIKE '%zh%';

-- 使用中文拼音排序
SELECT * FROM users ORDER BY name COLLATE utf8mb4_zh_0900_as_cs;
  • 支援拼音、筆畫、注音多種中文排序規則
  • 需 MySQL 8.0+ 編譯支援 ICU

效能對比(100 萬行測試)

方案查詢時間索引支援適用場景
CONVERT USING gbk0.8 秒❌ 全表掃描臨時查詢、小表
欄位 CHARSET gbk0.02 秒✅ 索引排序固定中文業務、無 Emoji
BINARY / utf8mb4_bin0.03 秒✅ 索引排序不需語義排序、混合字元
ICU utf8mb4_zh_0900_as_cs0.05 秒✅ 索引排序現代應用、多語言、需精準拼音序

選型建議

  1. 新專案、MySQL 8.0+ → ICU 校對規則(utf8mb4_zh_0900_as_cs)
  2. 舊專案、純中文、無 Emoji → 欄位級 gbk_chinese_ci
  3. 3. 臨時報表、跨字元集 → CONVERT USING gbk 4. 簡單去重/分組、不需語義序 → BINARY
copyright © 萬盛學電腦網 all rights reserved