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

MySQL 中文拼音排序解決方案：PHP 字典類與資料庫層索引優化完整指南

編輯：MySQL 資料庫相關知識

問題本質與解決路徑

MySQL 預設校對規則 (`utf8mb4_general_ci`, `utf8_general_ci`) **不支援拼音排序**，中文按字符編碼順序排列 (如「啊」U+554A 在「我」U+6211 之前)，導致業務查詢結果不符合用戶預期。

方案一：應用層預計算 + 索引欄位 (推薦、效能最佳、版本無關)

核心思想

寫入時由應用計算拼音首字母/全拼，存入專用索引欄位，查詢時直接 `ORDER BY pinyin_col` 走 B+ 樹索引，零運算開銷。

PHP 高效能拼音轉換類 (支援 2 萬+ 漢字、多音字策略、Composer 可用)

 'yi', '丁' => 'ding', ...]
        self::$loaded = true;
    }

    // 獲取首字母 (大寫 A-Z，非漢字原樣返回)
    public static function firstLetter(string $str): string {
        self::loadDict();
        $len = mb_strlen($str, 'UTF-8');
        for ($i = 0; $i < $len; $i++) {
            $ch = mb_substr($str, $i, 1, 'UTF-8');
            $py = self::$dict[$ch] ?? null;
            if ($py !== null) {
                return strtoupper($py[0]);
            }
            // 非漢字 (字母、數字、符號) 直接返回
            if (preg_match('/^[A-Za-z0-9]$/', $ch)) return strtoupper($ch);
        }
        return '#';  // 無法識別字符歸類
    }

    // 獲取全拼 (空格分隔，多音字取首個)
    public static function fullPinyin(string $str, string $sep = ' '): string {
        self::loadDict();
        $result = [];
        $len = mb_strlen($str, 'UTF-8');
        for ($i = 0; $i < $len; $i++) {
            $ch = mb_substr($str, $i, 1, 'UTF-8');
            $py = self::$dict[$ch] ?? null;
            if ($py !== null) {
                $result[] = $py;
            } else {
                $result[] = $ch;  // 保留原字符
            }
        }
        return implode($sep, $result);
    }
}

// 字典檔案結構 (pinyin_dict.php) - 仅示例片段
return [
    '一' => 'yi', '丁' => 'ding', '七' => 'qi', '万' => 'wan', '丈' => 'zhang',
    '三' => 'san', '上' => 'shang', '下' => 'xia', '不' => 'bu', '与' => 'yu',
    // ... 20000+ 條目，建議從 overtrue/pinyin 導出或使用 unicode 範圍算法
];

資料庫結構設計

CREATE TABLE tags (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL COMMENT '標籤名稱',
    pinyin_first CHAR(1) NOT NULL COMMENT '首字母 A-Z/#',
    pinyin_full VARCHAR(500) NOT NULL COMMENT '全拼 空格分隔',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uk_name (name),
    KEY idx_pinyin_first (pinyin_first),  -- 首字母索引
    KEY idx_pinyin_full (pinyin_full)     -- 全拼索引 (可選)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 寫入時自動填充 (觸發器或應用層)
CREATE TRIGGER bi_tags_pinyin BEFORE INSERT ON tags
FOR EACH ROW
BEGIN
    -- 需在 MySQL 中實現拼音函數 (見方案二) 或由應用層計算
    SET NEW.pinyin_first = UPPER(SUBSTRING(pinyin_full(NEW.name), 1, 1));
    SET NEW.pinyin_full = pinyin_full(NEW.name);
END;

查詢範例

-- 按首字母分組顯示 (A-Z 排序)
SELECT pinyin_first, GROUP_CONCAT(name) 
FROM tags 
GROUP BY pinyin_first 
ORDER BY pinyin_first;

-- 精確前綴搜尋 (利用全拼索引)
SELECT name FROM tags 
WHERE pinyin_full LIKE 'zhang san%' 
ORDER BY pinyin_full LIMIT 20;

方案二：資料庫層自定義函數 (MySQL 5.7+、無應用改造、效能次之)

建立拼音映射表 (約 2 萬行，一次性初始化)

CREATE TABLE `pinyin_dict` (
    `ch` CHAR(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL PRIMARY KEY,
    `py` VARCHAR(20) NOT NULL,
    `py_first` CHAR(1) GENERATED ALWAYS AS (UPPER(SUBSTRING(py,1,1))) STORED
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

-- 批次匯入 (從 CSV/JSON 導入)
LOAD DATA LOCAL INFILE 'pinyin_dict.csv' INTO TABLE pinyin_dict
FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n'
IGNORE 1 LINES (ch, py);

建立高效能標量函數 (MySQL 8.0 推薦 DETERMINISTIC)

DELIMITER //

CREATE FUNCTION `pinyin_first`(p_str VARCHAR(500))
RETURNS CHAR(1)
DETERMINISTIC
READS SQL DATA
BEGIN
    DECLARE v_len INT DEFAULT CHAR_LENGTH(p_str);
    DECLARE v_i INT DEFAULT 1;
    DECLARE v_ch CHAR(1);
    DECLARE v_py CHAR(1);

    WHILE v_i <= v_len DO
        SET v_ch = SUBSTRING(p_str, v_i, 1);
        -- 查詢字典 (utf8mb4_bin 精確匹配)
        SELECT py_first INTO v_py FROM pinyin_dict WHERE ch = v_ch LIMIT 1;
        IF v_py IS NOT NULL THEN
            RETURN v_py;
        END IF;
        -- 非漢字直接返回大寫
        IF v_ch REGEXP '^[A-Za-z0-9]$' THEN
            RETURN UPPER(v_ch);
        END IF;
        SET v_i = v_i + 1;
    END WHILE;
    RETURN '#';
END //

CREATE FUNCTION `pinyin_full`(p_str VARCHAR(500))
RETURNS VARCHAR(2000)
DETERMINISTIC
READS SQL DATA
BEGIN
    DECLARE v_len INT DEFAULT CHAR_LENGTH(p_str);
    DECLARE v_i INT DEFAULT 1;
    DECLARE v_ch CHAR(1);
    DECLARE v_py VARCHAR(20);
    DECLARE v_result VARCHAR(2000) DEFAULT '';

    WHILE v_i <= v_len DO
        SET v_ch = SUBSTRING(p_str, v_i, 1);
        SELECT py INTO v_py FROM pinyin_dict WHERE ch = v_ch LIMIT 1;
        IF v_py IS NOT NULL THEN
            SET v_result = CONCAT(v_result, IF(v_result='','',' '), v_py);
        ELSE
            SET v_result = CONCAT(v_result, v_ch);
        END IF;
        SET v_i = v_i + 1;
    END WHILE;
    RETURN v_result;
END //

DELIMITER ;

建立虛擬欄位索引 (8.0+ 免存儲、自動維護)

ALTER TABLE tags
ADD COLUMN pinyin_first_v CHAR(1) GENERATED ALWAYS AS (pinyin_first(name)) VIRTUAL,
ADD COLUMN pinyin_full_v VARCHAR(2000) GENERATED ALWAYS AS (pinyin_full(name)) VIRTUAL,
ADD INDEX idx_pinyin_first_v (pinyin_first_v),
ADD INDEX idx_pinyin_full_v (pinyin_full_v);

方案三：CONVERT + COLLATE 臨時方案 (無需結構變更、效能最差、不推薦生產)

-- 強制按 GBK 拼音排序 (僅支援 BMP 字符、不支援 Emoji/生僻字)
SELECT * FROM tags 
ORDER BY CONVERT(name USING gbk) COLLATE gbk_chinese_ci;

-- 原理：gbk_chinese_ci 校對規則內建拼音排序權重
-- 缺點：全表掃描、無法使用索引、字符集轉換開銷大、不支援 utf8mb4 完整字符集

方案對比與選型建議

維度	方案一：應用層預計算	方案二：資料庫函數+虛擬欄位	方案三：CONVERT COLLATE
查詢效能	⭐⭐⭐⭐⭐ 索引掃描	⭐⭐⭐ 虛擬欄位索引 / 函數調用開銷	⭐ 全表掃描 + 轉換
寫入開銷	應用層計算 (極低)	觸發器/虛擬欄位自動 (低)	無額外寫入
維護成本	需應用部署字典庫	純 SQL 維護、遷移友善	零維護
多音字處理	靈活 (可配置策略)	固定取首個	校對規則內建
Emoji/生僻字	支援 (字典含擴展區)	需字典含擴展區	不支援 (GBK 限制)
推薦場景	高併發、大數據量、微服務架構	單體應用、遷移專案、DBA 主導	臨時查詢、一次性報表、極小表