您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 資料庫相關知識

MySQL UTF8MB4 下中文拼音首字母排序：CONVERT+COLLATE 臨時方案與最佳實踐對比

編輯：MySQL 資料庫相關知識

問題背景

資料庫字符集 `utf8mb4`、校對規則 `utf8mb4_general_ci` (或 `0900_ai_ci`)，直接 `ORDER BY name` 依 Unicode 碼點排序，中文順序混亂 (如「劉」U+5283 在「張」U+5F20 之前)。

臨時方案：CONVERT + COLLATE 強制 GBK 排序

語法

SELECT * FROM tags
ORDER BY CONVERT(name USING gbk) COLLATE gbk_chinese_ci
LIMIT 100;

原理

`CONVERT(name USING gbk)`：將 UTF-8 字串轉換為 GBK 編碼位元組序列
`COLLATE gbk_chinese_ci`：套用 GBK 中文校對規則，內建拼音權重排序
MySQL 臨時建立內部表進行轉換與排序，**無法使用原表索引**

嚴重缺陷 (生產環境禁用)

全表掃描 + 檔案排序：`EXPLAIN` 顯示 `Using filesort`、`Using temporary`，百萬行表查詢秒級甚至分鐘級
字符集覆蓋不全：GBK 僅支援 2 萬餘漢字，**不支援 Emoji (U+1F600+)、生僻字 (擴展區 B/C/D)、少數民族字符** → 轉換報錯 `Incorrect string value` 或靜默截斷
隱含轉換開銷**：每行每查詢轉換，CPU 佔用高，併發下系統負載飆升
排序結果不穩定**：同音字相對順序不保證 (GBK 權重相同)

根治方案對比 (同 90206 文章，此處精簡)

方案	實現層級	查詢效能	字符覆蓋	維護成本	推薦等級
應用層預計算 + 索引欄位	Application	⭐⭐⭐⭐⭐ 索引範圍掃描	完整 (字典可擴展)	低 (Composer 套件)	★★★★★ 生產首選
資料庫虛擬欄位 + 函數 (8.0+)	Database	⭐⭐⭐ 虛擬欄位索引	取決於字典表	中 (純 SQL 維護)	★★★★ 遷移/單體首選
資料庫觸發器 + 實體欄位 (5.7)	Database	⭐⭐⭐ 實體索引	取決於字典表	中	★★★ 5.7 環境
CONVERT + COLLATE	Query	⭐ 全表掃描	不完整 (GBK 限制)	零	☆ 僅臨時/小表

UTF8MB4 環境下的最佳實踐清單

字符集統一：資料庫、表、欄位、連線、應用、備份全鏈路 `utf8mb4` + `utf8mb4_unicode_ci` (或 `0900_ai_ci`)
拼音欄位型別：`pinyin_first CHAR(1) CHARSET ascii` (極小、比較快)、`pinyin_full VARCHAR(500) CHARSET ascii`
索引策略：首字母 `INDEX (pinyin_first)`、全拼 `INDEX (pinyin_full)` (前綴搜尋)
查詢改寫：`ORDER BY pinyin_first, pinyin_full` 替代 `ORDER BY CONVERT(...)`
歷史資料回填**：分批 `UPDATE` (每批 5000 行、`SLEEP(0.1)`) 避免長鎖表
新增資料保證**：應用層寫入前計算、或 8.0 虛擬欄位自動維護
監控指標**：`Handler_read_rnd_next` (檔案排序計數) 應歸零

遷移腳本範例 (從 CONVERT 方案遷移至索引欄位)

-- 1. 新增欄位
ALTER TABLE tags ADD COLUMN pinyin_first CHAR(1) CHARSET ascii NOT NULL DEFAULT '#',
ADD COLUMN pinyin_full VARCHAR(500) CHARSET ascii NOT NULL DEFAULT '',
ADD INDEX idx_pinyin_first (pinyin_first),
ADD INDEX idx_pinyin_full (pinyin_full);

-- 2. 分批回填 (假設 500 萬行)
DELIMITER //
CREATE PROCEDURE backfill_pinyin()
BEGIN
    DECLARE v_max_id BIGINT DEFAULT (SELECT MAX(id) FROM tags);
    DECLARE v_batch_start BIGINT DEFAULT 0;
    DECLARE v_batch_size INT DEFAULT 5000;
    
    WHILE v_batch_start <= v_max_id DO
        UPDATE tags t
        JOIN (
            SELECT id, 
                   UPPER(SUBSTRING(pinyin_full(name),1,1)) AS pf,
                   pinyin_full(name) AS pfull
            FROM tags
            WHERE id > v_batch_start AND id <= v_batch_start + v_batch_size
        ) src ON t.id = src.id
        SET t.pinyin_first = src.pf, t.pinyin_full = src.pfull;
        
        SET v_batch_start = v_batch_start + v_batch_size;
        DO SLEEP(0.1);  -- 降低主從延遲、減輕主庫壓力
    END WHILE;
END //
DELIMITER ;

CALL backfill_pinyin();
DROP PROCEDURE backfill_pinyin;

驗證腳本

-- 1. 確認無 NULL/預設值殘留
SELECT COUNT(*) FROM tags WHERE pinyin_first = '#' OR pinyin_full = '';

-- 2. 抽樣對比排序結果
SELECT name, pinyin_first FROM tags ORDER BY pinyin_first, pinyin_full LIMIT 50;
SELECT name FROM tags ORDER BY CONVERT(name USING gbk) COLLATE gbk_chinese_ci LIMIT 50;
-- 兩結果應一致 (除 GBK 不支援字符)

-- 3. EXPLAIN 確認走索引
EXPLAIN SELECT * FROM tags ORDER BY pinyin_first, pinyin_full LIMIT 100;
-- type=index, key=idx_pinyin_first, Extra=Using index