MySQL 自訂函數：漢字轉拼音首字母（GBK 編碼區間對照表法）

函數用途

將中文字串轉換為拼音首字母大寫字串，如「中國」→「ZG」，用於索引、排序、模糊搜尋優化。

完整函數定義

DELIMITER ;;

CREATE FUNCTION `getPY`(in_string VARCHAR(65534)) 
RETURNS MEDIUMTEXT CHARSET utf8
DETERMINISTIC
BEGIN
    DECLARE tmp_str VARCHAR(65534) CHARSET gbk DEFAULT '';
    DECLARE tmp_len SMALLINT DEFAULT 0;
    DECLARE tmp_char VARCHAR(2) CHARSET gbk DEFAULT '';
    DECLARE tmp_rs VARCHAR(65534) CHARSET gbk DEFAULT '';
    DECLARE tmp_cc VARCHAR(2) CHARSET gbk DEFAULT '';
    
    SET tmp_str = in_string;
    SET tmp_len = LENGTH(tmp_str);
    
    WHILE tmp_len > 0 DO
        SET tmp_char = LEFT(tmp_str, 1);
        SET tmp_cc = tmp_char;
        
        -- 判斷是否為雙字節（GBK 中文佔 2 字節）
        IF LENGTH(tmp_char) > 1 THEN
            -- GBK 區間對照表 → 拼音首字母
            SELECT ELT(INTERVAL(CONV(HEX(tmp_char), 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') INTO tmp_cc;
        END IF;
        
        SET tmp_rs = CONCAT(tmp_rs, tmp_cc);
        SET tmp_str = SUBSTRING(tmp_str, 2);
        SET tmp_len = LENGTH(tmp_str);
    END WHILE;
    
    RETURN tmp_rs;
END;;

DELIMITER ;

核心原理

1. 逐字元切割字串（LEFT(str,1) + SUBSTRING(str,2)）
2. LENGTH(char) > 1 判斷為雙字節 → GBK 中文字元
3. HEX() → CONV(...,16,10) 取得數值編碼
4. INTERVAL(val, 區間下界...) 定位所在拼音區間（0=第一區間）
5. ELT(index, 'A','B'...) 取得對應首字母

GBK 拼音區間對照表（節錄）

使用範例

SELECT getPY('中國人民');  -- 'ZGRM'
SELECT getPY('北京歡迎您');  -- 'BJHYN'

-- 建立計算欄位索引加速模糊搜尋
ALTER TABLE users ADD COLUMN name_py VARCHAR(100) GENERATED ALWAYS AS (getPY(name)) STORED;
CREATE INDEX idx_name_py ON users(name_py);

-- 查詢姓「王」的用戶
SELECT * FROM users WHERE name_py LIKE 'W%';

注意事項

• **僅支援 GBK 編碼**中文字元，UTF-8 需先 CONVERT(STR USING GBK)
• 非中文字元（英文、數字、符號）原樣保留
• 多音字無法精準處理（取 GBK 編碼順序首個拼音）
• 大量資料建議離線預計算存入欄位，避免查詢時即時計算
• MySQL 8.0 可改用 COLLATE utf8mb4_zh_0900_as_cs 等中文校對規則原生排序