MySQL 中文轉拼音首字母函數：fristPinyin 與 pinyin 實現

針對「僅需首字母」或「每字首字母」的輕量級場景，無需完整拼音對照表，可利用 GBK 編碼區間直接映射。本文提供兩個高效自定義函數：fristPinyin() (首字首字母) 與 pinyin() (全串首字母)。

核心原理：GBK 區位碼區間映射

GBK 編碼中，漢字按拼音順序排列。首字母對應區間：

A: B0A1-B0C4   B: B0C5-B2C0   C: B2C1-B4ED
D: B4EE-B6E9   E: B6EA-B7A1   F: B7A2-B8C0
G: B8C1-B9FD   H: B9FE-BBF6   J: BBF7-BFA5
K: BFA6-C0AB   L: C0AC-C2E7   M: C2E8-C4C2
N: C4C3-C5B5   O: C5B6-C5BD   P: C5BE-C6D9
Q: C6DA-C8BA   R: C8BB-C8F5   S: C8F6-CBFA
T: CBFA-CDDA   W: CDDA-CEF3   X: CEF4-D1B8
Y: D1B9-D4D0   Z: D4D1-D7FA

函數一：fristPinyin() - 取首字首字母

CREATE FUNCTION `fristPinyin`(P_NAME VARCHAR(255)) 
RETURNS VARCHAR(255) CHARSET utf8
DETERMINISTIC
BEGIN
    DECLARE V_RETURN VARCHAR(255);
    -- 將首字轉 GBK 十六進制，取高位字節區間判斷
    SET V_RETURN = ELT(INTERVAL(CONV(HEX(LEFT(CONVERT(P_NAME USING gbk), 1)), 16, 10),
        0xB0A1, 0xB0C5, 0xB2C1, 0xB4EE, 0xB6EA, 0xB7A2, 0xB8C1, 0xB9FE, 0xBBF7,
        0xBFA6, 0xC0AC, 0xC2E8, 0xC4C3, 0xC5B6, 0xC5BE, 0xC6DA, 0xC8BB,
        0xC8F6, 0xCBFA, 0xCDDA, 0xCEF4, 0xD1B9, 0xD4D1),
        'A','B','C','D','E','F','G','H','J','K','L','M','N','O','P','Q','R','S','T','W','X','Y','Z');
    RETURN V_RETURN;
END

關鍵函數解析：

• CONVERT(P_NAME USING gbk)：轉 GBK 編碼
• LEFT(..., 1)：取首字 (GBK 雙字節)
• HEX(...)：轉十六進制字串 (如 'D6D0')
• CONV(..., 16, 10)：十六進制轉十進制數值
• INTERVAL(val, n1, n2, n3...)：返回 val 所在區間索引 (0-based)
• ELT(index+1, 'A','B'...)：取對應字母

函數二：pinyin() - 取字串每字首字母

CREATE FUNCTION `pinyin`(P_NAME VARCHAR(255)) 
RETURNS VARCHAR(255) CHARSET utf8
DETERMINISTIC
BEGIN
    DECLARE V_COMPARE VARCHAR(255);
    DECLARE V_RETURN VARCHAR(255);
    DECLARE I INT;

    SET I = 1;
    SET V_RETURN = '';
    
    WHILE I <= CHAR_LENGTH(P_NAME) DO
        SET V_COMPARE = SUBSTRING(P_NAME, I, 1);
        IF (V_COMPARE != '') THEN
            SET V_RETURN = CONCAT(V_RETURN, fristPinyin(V_COMPARE));
        END IF;
        SET I = I + 1;
    END WHILE;

    IF (ISNULL(V_RETURN) OR V_RETURN = '') THEN
        SET V_RETURN = P_NAME;
    END IF;

    RETURN V_RETURN;
END

使用範例與驗證

SELECT 
    province,
    fristPinyin(province) AS first_letter,
    pinyin(province) AS all_first_letters
FROM province;

-- 結果示例
+------------------+--------------+------------------+
| province         | first_letter | all_first_letters|
+------------------+--------------+------------------+
| 北京市           | B            | BJS              |
| 天津市           | T            | TJS              |
| 河北省           | H            | HBS              |
| 山西省           | S            | SXS              |
| 內蒙古自治區     | N            | NMGZZQ           |
+------------------+--------------+------------------+

效能與適用場景對比

最佳實踐建議

1. 索引優化：在需按拼音排序/檢索的欄位添加 VIRTUAL COLUMN (5.7+) 或觸發器維護首字母欄位，建立索引
2. 字符集一致：確保連線、表、函數均使用 gbk 或統一 utf8mb4 配合 CONVERT
3. 邊界測試：測試標點、數字、英文、空字串、NULL 輸入行為
4. 版本相容：CONVERT(... USING gbk) 5.0+ 支援，DETERMINISTIC 5.7+ 建議加上