MySQL 自定義函數：提取漢字字段拼音首字母

函數完整代碼

DROP FUNCTION IF EXISTS `getPY`;
DELIMITER ;;
CREATE FUNCTION `getPY`(in_string VARCHAR(65534))
RETURNS MEDIUMTEXT CHARSET utf8
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
        -- 取首字符（可能為多字節漢字或單字節 ASCII）
        SET tmp_char = LEFT(tmp_str, 1);
        SET tmp_cc = tmp_char;  -- 默認非漢字直接保留

        -- 判斷是否為雙字節字符（GBK 編碼下漢字佔 2 字節）
        IF LENGTH(tmp_char) > 1 THEN
            -- 通過區位碼範圍映射拼音首字母
            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. GBK 編碼與區位碼映射

函數強制使用 CHARSET gbk 變量，因為 GBK 編碼下：

• 漢字佔 2 字節，區位碼範圍 0xB0A1 ~ 0xD7FE
• 拼音首字母按區位碼順序分佈在 24 個區間

CONV(HEX(tmp_char), 16, 10) 將字符轉為十進制區位碼，INTERVAL(...) 判斷落在哪個區間，ELT(...) 返回對應首字母。

2. 逐字符迭代邏輯

• LEFT(tmp_str, 1) 取首字符（GBK 下自動識別雙字節）
• SUBSTRING(tmp_str, 2) 移除首字符（按字節移除，漢字移 2 位）
• LENGTH() 返回字節長度，作為循環終止條件

3. 非漢字透傳機制

ASCII 字符（字母、數字、符號）長度為 1，直接拼接保留，不進行拼音轉換。

使用示例

SELECT getPY('北京歡迎您');  -- 返回 'BJHYN'
SELECT getPY('Hello世界123');  -- 返回 'HelloSJ123'

注意事項與局限

• 依賴 GBK 字符集，數據庫連接字符集需兼容
• 不支持生僻字、繁體字、標點符號的拼音映射
• 大數據量查詢中調用可能影響性能，建議預計算存儲
• MySQL 8.0+ 建議使用 COLLATE 規則或應用層處理

優化建議

1. 將映射邏輯遷移到應用層（Java/Python/Go）處理，靈活性更高
2. 若必須在 DB 層，考慮創建映射表 JOIN 查詢替代 ELT/INTERVAL
3. 添加緩存表存儲常用詞彙首字母，減少重複計算