MySQL 中文漢字轉拼音完整實現：建立對照表、自定義函數、全拼轉換

MySQL 內建無拼音轉換功能，需自建對照表與自定義函數實現。本文提供完整可執行方案：建立拼音編碼對照表、插入 400+ 漢字映射、創建 to_pinyin() 函數實現全拼轉換。

步驟一：建立拼音對照表

CREATE TABLE IF NOT EXISTS `t_base_pinyin` (
    `pin_yin_` VARCHAR(255) CHARACTER SET gbk NOT NULL,
    `code_` INT(11) NOT NULL,
    PRIMARY KEY (`code_`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

設計說明：

• pin_yin_：拼音字串 (如 'zhong', 'guo')，用 gbk 字符集儲存中文
• code_：GBK 區位碼計算值 (主鍵)，用於快速查找
• 表字符集 latin1 節省空間，實際中文存於 pin_yin_ 欄位指定字符集

步驟二：插入對照數據 (節選，完整 400+ 條見附件)

INSERT INTO t_base_pinyin (pin_yin_, code_) VALUES 
("a", 20319), ("ai", 20317), ("an", 20304), ("ang", 20295), ("ao", 20292),
("ba", 20283), ("bai", 20265), ("ban", 20257), ("bang", 20242), ("bao", 20230),
("bei", 20051), ("ben", 20036), ("beng", 20032), ("bi", 20026), ("bian", 20002),
-- ... 續插入所有拼音映射 (共 400+ 條)
("zuo", 10254);

編碼規則： GBK 雙字節編碼，高位字節 0xB0-0xF7，低位字節 0xA1-0xFE。計算公式：code = 65536 - high_byte * 256 - low_byte，按遞減排序對應拼音順序。

步驟三：創建轉換函數 to_pinyin()

DROP FUNCTION IF EXISTS to_pinyin;
DELIMITER $
CREATE FUNCTION to_pinyin(NAME VARCHAR(255) CHARSET gbk)
RETURNS VARCHAR(255) CHARSET gbk
DETERMINISTIC
BEGIN
    DECLARE mycode INT;
    DECLARE tmp_lcode VARCHAR(2) CHARSET gbk;
    DECLARE lcode INT;
    DECLARE tmp_rcode VARCHAR(2) CHARSET gbk;
    DECLARE rcode INT;
    DECLARE mypy VARCHAR(255) CHARSET gbk DEFAULT '';
    DECLARE lp INT;

    SET mycode = 0;
    SET lp = 1;
    SET NAME = HEX(NAME);  -- 轉十六進制處理雙字節

    WHILE lp < LENGTH(NAME) DO
        SET tmp_lcode = SUBSTRING(NAME, lp, 2);
        SET lcode = CAST(ASCII(UNHEX(tmp_lcode)) AS UNSIGNED);
        SET tmp_rcode = SUBSTRING(NAME, lp + 2, 2);
        SET rcode = CAST(ASCII(UNHEX(tmp_rcode)) AS UNSIGNED);

        IF lcode > 128 THEN  -- 雙字節字符 (中文)
            SET mycode = 65536 - lcode * 256 - rcode;
            -- 查找對應拼音 (code_ 遞減排序，取第一個 >= 絕對值)
            SELECT CONCAT(mypy, pin_yin_) INTO mypy 
            FROM t_base_pinyin 
            WHERE CODE_ >= ABS(mycode) 
            ORDER BY CODE_ ASC LIMIT 1;
            SET lp = lp + 4;  -- 雙字節佔 4 個十六進制字符
        ELSE  -- 單字節字符 (ASCII)
            SET mypy = CONCAT(mypy, CHAR(CAST(ASCII(UNHEX(SUBSTRING(NAME, lp, 2))) AS UNSIGNED)));
            SET lp = lp + 2;
        END IF;
    END WHILE;

    RETURN LOWER(mypy);
END;
$
DELIMITER ;

步驟四：使用驗證

SELECT to_pinyin('測試');
-- 輸出: ceshi

SELECT to_pinyin('中國人民');
-- 輸出: zhongguorenmin

SELECT to_pinyin('Hello 世界');
-- 輸出: hello shijie

效能優化建議

1. 索引優化：code_ 為主鍵，查找已最優
2. 緩存熱點：常用詞彙可建立詞級緩存表 t_pinyin_cache(word, pinyin)
3. 批量轉換：應用層分批調用，避免單次大量字符串處理阻塞
4. 字符集一致性：確保連線、表、欄位、函數參數字符集統一 (建議 gbk 或 utf8mb4 配合 CONVERT)

遷移至 MySQL 8.0 現代化寫法

-- 8.0 支援 CTE 遞歸，可實現純 SQL 版本 (無需對照表)
-- 但對照表法兼容性最好，5.0+ 皆可用

-- 8.0 可添加 DETERMINISTIC 特性聲明，優化器可緩存結果
CREATE FUNCTION to_pinyin(...) RETURNS ... DETERMINISTIC ...

局限性說明

• 僅支援 GBK 編碼範圍內漢字 (約 2萬字)，生僻字、擴展區字符需擴充對照表
• 多音字無法自動斷句消歧 (如「行」xing/hang)，需結合分詞庫
• 大量文本轉換效能受限於函數逐字符循環，建議異步處理