MySQL 中文轉拼音自定義函數：首字母/全拼雙模式實現

方案一：首字母模式 (基於 GBK 編碼區間判斷，極快)

原理**：GBK 編碼中漢字按拼音順序排列，通過首字節區間映射到聲母。

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;

方案二：全拼模式 (逐字符調用首字母函數，近似)

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

    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 full_pinyin
FROM province;

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

局限性與避坑指南

1. 僅支持 GBK 覆蓋的常用漢字**：生僻字、擴展字符集 (UTF-8 mb4) 字符會回退返回原字符
2. 多音字不準**：如「行」只能映射到一個聲母 (H/X 二選一)
3. 非漢字字符**：字母、數字、標點會原樣返回或空
4. 性能**：逐字符調用函數，大批量查詢慢。建議：
• 應用層 (Python/Java) 用成熟庫 (pypinyin、jpinyin) 轉換後存庫
• 或建立 hanzi -> pinyin 映射表，JOIN 查詢
• MySQL 8.0 可考慮 CREATE FUNCTION ... LANGUAGE PYTHON (實驗特性)
5. 排序/分組用**：ORDER BY fristPinyin(name) 可實現按拼音排序

進階方案：映射表 + 精確全拼 (生產推薦)

-- 1. 建表
CREATE TABLE hanzi_pinyin (
    hanzi CHAR(1) NOT NULL PRIMARY KEY,
    pinyin VARCHAR(10) NOT NULL,
    first_letter CHAR(1) GENERATED ALWAYS AS (LEFT(pinyin, 1)) STORED
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 2. 導入數據 (從開源詞庫如 pypinyin 導出)
INSERT INTO hanzi_pinyin (hanzi, pinyin) VALUES 
('北','bei'),('京','jing'),('市','shi'), ...;

-- 3. 查詢函數 (集合操作，極快)
CREATE FUNCTION `get_pinyin`(str VARCHAR(255)) RETURNS VARCHAR(255)
DETERMINISTIC
BEGIN
    DECLARE result VARCHAR(255) DEFAULT '';
    DECLARE i INT DEFAULT 1;
    DECLARE ch CHAR(1);
    DECLARE py VARCHAR(10);
    WHILE i <= CHAR_LENGTH(str) DO
        SET ch = SUBSTRING(str, i, 1);
        SELECT pinyin INTO py FROM hanzi_pinyin WHERE hanzi = ch LIMIT 1;
        SET result = CONCAT(result, COALESCE(py, ch));
        SET i = i + 1;
    END WHILE;
    RETURN result;
END;