MySQL 獲取漢字拼音首字母：自定義函數完整實現

MySQL 獲取漢字拼音首字母：自定義函數完整實現

MySQL 內建函數不支援直接提取漢字拼音首字母，但可通過創建存儲函數 getPY 實現。該函數逐字符解析輸入字符串，對多字節字符（判定為漢字）查詢 GBK 編碼區間映射到對應首字母，單字節字符保持原樣，最終拼接返回。

函數完整代碼

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
        SET tmp_char = LEFT(tmp_str, 1);
        SET tmp_cc = tmp_char;

        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 ;

使用示例

SELECT getPY('北京上海'); -- 返回 'BJS'

補充：模糊查詢與 Excel 方案

MySQL LIKE 模糊查詢常用模式

• WHERE name LIKE '%明%'：包含「明」
• WHERE name LIKE '李%'：以「李」開頭
• WHERE name LIKE '%[0-9]%'：包含數字
• WHERE name LIKE '%[a-z]%'：包含小寫字母
• WHERE name LIKE '%[!0-9]%'：不包含數字

% 匹配任意長度字符，_ 或 ? 匹配單個字符。

Excel 提取首字母公式

=LOOKUP(CODE(LEFT(B1,1)), 45217+{0,36,544,1101,1609,1793,2080,2560,2902,3845,4107,4679,5154,5397,5405,5689,6170,6229,7001,7481,7763,8472,9264},
{"A","B","C","D","E","F","G","H","J","K","L","M","N","O","P","Q","R","S","T","W","X","Y","Z"})

適用於單元格 B1 為單個漢字或漢字開頭的字符串。