MySQL 預設字元集校對規則(如 utf8_general_ci)不支援中文拼音排序。本文深度解析 CONVERT(... USING gbk) 跨字元集排序原理,提供不修改表結構的通用解決方案。
utf8_general_ci:按 Unicode 碼點排序,中文按區位碼順序(非拼音)gbk_chinese_ci / gb2312_chinese_ci:按拼音/筆畫排序,符合中文習慣utf8mb4_zh_0900_as_cs (MySQL 8.0+):ICU 庫支援拼音排序,但需明確指定-- 核心語法:強制將欄位按 GBK 解碼後再按 gbk_chinese_ci 排序
SELECT * FROM `core_vender_info`
ORDER BY CONVERT(`vender_abbrev` USING gbk) COLLATE gbk_chinese_ci;
CONVERT(col USING gbk):將欄位內容(無論原編碼 UTF-8/GBK/Latin1)重新解釋為 GBK 位元組序列COLLATE gbk_chinese_ci:套用 GBK 中文校對規則進行比較排序-- 建立測試表(欄位指定 GBK)
CREATE TABLE `test111cnnet` (
`bid` INT(4) UNSIGNED NOT NULL AUTO_INCREMENT,
`namerean` VARCHAR(20) CHARACTER SET gbk DEFAULT NULL,
PRIMARY KEY (`bid`)
);
-- 插入測試資料
INSERT INTO `test111cnnet` (`namerean`) VALUES ('李'), ('鄧'), ('站');
-- 1. 原生 GBK 欄位直接排序(正確)
SELECT `namerean` FROM `test111cnnet` ORDER BY `namerean`;
-- 輸出: 鄧(d), 李(l), 站(z) ✅
-- 2. UTF-8 欄位直接排序(錯誤 - 按碼點)
-- 假設同結構表欄位為 UTF-8
SELECT `namerean` FROM `test_utf8` ORDER BY `namerean`;
-- 輸出: 站, 鄧, 李 ❌
-- 3. CONVERT 強制 GBK 排序(修正)
SELECT `namerean` FROM `test_utf8`
ORDER BY CONVERT(`namerean` USING gb2312) ASC;
-- 輸出: 鄧(d), 李(l), 站(z) ✅
-- 修改欄位屬性為 BINARY(二進位比較,按位元組序)
ALTER TABLE `t1` MODIFY `name` VARCHAR(20) CHARACTER SET gbk COLLATE gbk_chinese_ci;
-- 或
ALTER TABLE `t1` MODIFY `name` VARCHAR(20) BINARY; -- 按位元組值排序
⚠️ 需 ALTER TABLE 權限,大表執行會鎖表重建,生產環境需評估影響。
# 編譯時指定
./configure --with-charset=gbk --with-extra-charsets=gb2312,gbk
# 或啟動配置
[mysqld]
character-set-server = gbk
collation-server = gbk_chinese_ci
影響全實例新建物件預設字元集,遷移成本高,不推薦現有系統採用。
-- 多欄位混合排序:姓名拼音 + 建立時間倒序
SELECT * FROM `users`
ORDER BY
CONVERT(`name` USING gbk) COLLATE gbk_chinese_ci ASC,
`created_at` DESC;
-- 效能優化:建立函數索引 (MySQL 8.0.13+)
ALTER TABLE `users`
ADD INDEX `idx_name_pinyin` ((CONVERT(`name` USING gbk) COLLATE gbk_chinese_ci));
-- 或建立衍生欄位 + 觸發器維護 (5.7 相容)
ALTER TABLE `users` ADD `name_pinyin` VARCHAR(100) CHARACTER SET gbk COLLATE gbk_chinese_ci;
-- BEFORE INSERT/UPDATE TRIGGER 設定 NEW.name_pinyin = CONVERT(NEW.name USING gbk)
| 字元集 | 校對規則 | 排序依據 | 支援範圍 | 適用場景 |
|---|---|---|---|---|
| gbk | gbk_chinese_ci | 拼音 | 中文簡體+部分繁體 | 純簡體中文業務 |
| gb2312 | gb2312_chinese_ci | 拼音 | GB2312 標準字符集 | 老系統相容 |
| big5 | big5_chinese_ci | 筆畫/拼音 | 繁體中文 | 繁體中文業務 |
| utf8mb4 | utf8mb4_zh_0900_as_cs | 拼音 (ICU) | 全 Unicode | MySQL 8.0+ 現代應用 |
ORDER BY CONVERT(name USING gbk) 不加 COLLATE → 仍按二進位值排序中文排序的本質是「字元集編碼順序與語言邏輯順序的不一致」。CONVERT ... USING gbk COLLATE gbk_chinese_ci 是不修改存儲層前提下的最優妥協方案;長期演進應遷移至 MySQL 8.0+ 原生 ICU 校對規則。