MySQL 中文漢字轉拼音完整實現：建表、數據、函數一站式腳本

方案概覽

基於 **GBK 編碼區位碼反查** 原理，構建「漢字區位碼 → 拼音」映射表，編寫存儲函數 to_pinyin(str) 實現漢字轉全拼（不帶聲調）。適用於 MySQL 5.5+ / MariaDB 10.0+。

第一步：創建映射表 t_base_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;

第二步：插入標準拼音映射數據 (節選，全量約 600+ 行)

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),
("biao", 19990), ("bie", 19986), ("bin", 19982), ("bing", 19976), ("bo", 19805),
("bu", 19784), ("ca", 19775), ("cai", 19774), ("can", 19763), ("cang", 19756),
("cao", 19751), ("ce", 19746), ("ceng", 19741), ("cha", 19739), ("chai", 19728),
("chan", 19725), ("chang", 19715), ("chao", 19540), ("che", 19531), ("chen", 19525),
("cheng", 19515), ("chi", 19500), ("chong", 19484), ("chou", 19479), ("chu", 19467),
("chuai", 19289), ("chuan", 19288), ("chuang", 19281), ("chui", 19275), ("chun", 19270),
("chuo", 19263), ("ci", 19261), ("cong", 19249), ("cou", 19243), ("cu", 19242),
("cuan", 19238), ("cui", 19235), ("cun", 19227), ("cuo", 19224), ("da", 19218),
("dai", 19212), ("dan", 19038), ("dang", 19023), ("dao", 19018), ("de", 19006),
("deng", 19003), ("di", 18996), ("dian", 18977), ("diao", 18961), ("die", 18952),
("ding", 18783), ("diu", 18774), ("dong", 18773), ("dou", 18763), ("du", 18756),
("duan", 18741), ("dui", 18735), ("dun", 18731), ("duo", 18722), ("e", 18710),
("en", 18697), ("er", 18696), ("fa", 18526), ("fan", 18518), ("fang", 18501),
("fei", 18490), ("fen", 18478), ("feng", 18463), ("fo", 18448), ("fou", 18447),
("fu", 18446), ("ga", 18239), ("gai", 18237), ("gan", 18231), ("gang", 18220),
("gao", 18211), ("ge", 18201), ("gei", 18184), ("gen", 18183), ("geng", 18181),
("gong", 18012), ("gou", 17997), ("gu", 17988), ("gua", 17970), ("guai", 17964),
("guan", 17961), ("guang", 17950), ("gui", 17947), ("gun", 17931), ("guo", 17928),
("ha", 17922), ("hai", 17759), ("han", 17752), ("hang", 17733), ("hao", 17730),
("he", 17721), ("hei", 17703), ("hen", 17701), ("heng", 17697), ("hong", 17692),
("hou", 17683), ("hu", 17676), ("hua", 17496), ("huai", 17487), ("huan", 17482),
("huang", 17468), ("hui", 17454), ("hun", 17433), ("huo", 17427), ("ji", 17417),
("jia", 17202), ("jian", 17185), ("jiang", 16983), ("jiao", 16970), ("jie", 16942),
("jin", 16915), ("jing", 16733), ("jiong", 16708), ("jiu", 16706), ("ju", 16689),
("juan", 16664), ("jue", 16657), ("jun", 16647), ("ka", 16474), ("kai", 16470),
("kan", 16465), ("kang", 16459), ("kao", 16452), ("ke", 16448), ("ken", 16433),
("keng", 16429), ("kong", 16427), ("kou", 16423), ("ku", 16419), ("kua", 16412),
("kuai", 16407), ("kuan", 16403), ("kuang", 16401), ("kui", 16393), ("kun", 16220),
("kuo", 16216), ("la", 16212), ("lai", 16205), ("lan", 16202), ("lang", 16187),
("lao", 16180), ("le", 16171), ("lei", 16169), ("leng", 16158), ("li", 16155),
("lia", 15959), ("lian", 15958), ("liang", 15944), ("liao", 15933), ("lie", 15920),
("lin", 15915), ("ling", 15903), ("liu", 15889), ("long", 15878), ("lou", 15707),
("lu", 15701), ("lv", 15681), ("luan", 15667), ("lue", 15661), ("lun", 15659),
("luo", 15652), ("ma", 15640), ("mai", 15631), ("man", 15625), ("mang", 15454),
("mao", 15448), ("me", 15436), ("mei", 15435), ("men", 15419), ("meng", 15416),
("mi", 15408), ("mian", 15394), ("miao", 15385), ("mie", 15377), ("min", 15375),
("ming", 15369), ("miu", 15363), ("mo", 15362), ("mou", 15183), ("mu", 15180),
("na", 15165), ("nai", 15158), ("nan", 15153), ("nang", 15150), ("nao", 15149),
("ne", 15144), ("nei", 15143), ("nen", 15141), ("neng", 15140), ("ni", 15139),
("nian", 15128), ("niang", 15121), ("niao", 15119), ("nie", 15117), ("nin", 15110),
("ning", 15109), ("niu", 14941), ("nong", 14937), ("nu", 14933), ("nv", 14930),
("nuan", 14929), ("nue", 14928), ("nuo", 14926), ("o", 14922), ("ou", 14921),
("pa", 14914), ("pai", 14908), ("pan", 14902), ("pang", 14894), ("pao", 14889),
("pei", 14882), ("pen", 14873), ("peng", 14871), ("pi", 14857), ("pian", 14678),
("piao", 14674), ("pie", 14670), ("pin", 14668), ("ping", 14663), ("po", 14654),
("pu", 14645), ("qi", 14630), ("qia", 14594), ("qian", 14429), ("qiang", 14407),
("qiao", 14399), ("qie", 14384), ("qin", 14379), ("qing", 14368), ("qiong", 14355),
("qiu", 14353), ("qu", 14345), ("quan", 14170), ("que", 14159), ("qun", 14151),
("ran", 14149), ("rang", 14145), ("rao", 14140), ("re", 14137), ("ren", 14135),
("reng", 14125), ("ri", 14123), ("rong", 14122), ("rou", 14112), ("ru", 14109),
("ruan", 14099), ("rui", 14097), ("run", 14094), ("ruo", 14092), ("sa", 14090),
("sai", 14087), ("san", 14083), ("sang", 13917), ("sao", 13914), ("se", 13910),
("sen", 13907), ("seng", 13906), ("sha", 13905), ("shai", 13896), ("shan", 13894),
("shang", 13878), ("shao", 13870), ("she", 13859), ("shen", 13847), ("sheng", 13831),
("shi", 13658), ("shou", 13611), ("shu", 13601), ("shua", 13406), ("shuai", 13404),
("shuan", 13400), ("shuang", 13398), ("shui", 13395), ("shun", 13391), ("shuo", 13387),
("si", 13383), ("song", 13367), ("sou", 13359), ("su", 13356), ("suan", 13343),
("sui", 13340), ("sun", 13329), ("suo", 13326), ("ta", 13318), ("tai", 13147),
("tan", 13138), ("tang", 13120), ("tao", 13107), ("te", 13096), ("teng", 13095),
("ti", 13091), ("tian", 13076), ("tiao", 13068), ("tie", 13063), ("ting", 13060),
("tong", 12888), ("tou", 12875), ("tu", 12871), ("tuan", 12860), ("tui", 12858),
("tun", 12852), ("tuo", 12849), ("wa", 12838), ("wai", 12831), ("wan", 12829),
("wang", 12812), ("wei", 12802), ("wen", 12607), ("weng", 12597), ("wo", 12594),
("wu", 12585), ("xi", 12556), ("xia", 12359), ("xian", 12346), ("xiang", 12320),
("xiao", 12300), ("xie", 12120), ("xin", 12099), ("xing", 12089), ("xiong", 12074),
("xiu", 12067), ("xu", 12058), ("xuan", 12039), ("xue", 11867), ("xun", 11861),
("ya", 11847), ("yan", 11831), ("yang", 11798), ("yao", 11781), ("ye", 11604),
("yi", 11589), ("yin", 11536), ("ying", 11358), ("yo", 11340), ("yong", 11339),
("you", 11324), ("yu", 11303), ("yuan", 11097), ("yue", 11077), ("yun", 11067),
("za", 11055), ("zai", 11052), ("zan", 11045), ("zang", 11041), ("zao", 11038),
("ze", 11024), ("zei", 11020), ("zen", 11019), ("zeng", 11018), ("zha", 11014),
("zhai", 10838), ("zhan", 10832), ("zhang", 10815), ("zhao", 10800), ("zhe", 10790),
("zhen", 10780), ("zheng", 10764), ("zhi", 10587), ("zhong", 10544), ("zhou", 10533),
("zhu", 10519), ("zhua", 10331), ("zhuan", 10328), ("zhuang", 10322), ("zhui", 10315),
("zhun", 10309), ("zhuo", 10307), ("zi", 10296), ("zong", 10281), ("zou", 10274),
("zu", 10270), ("zuan", 10262), ("zui", 10260), ("zun", 10256), ("zuo", 10254);

第三步：創建轉換函數 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
            -- 雙字節字符 (GBK 漢字範圍)
            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('你好世界'); -- 輸出: nihaoshijie
SELECT to_pinyin('MySQL數據庫'); -- 輸出: mysqlshujuku

原理解析

1. GBK 編碼特性：漢字佔 2 字節，高字節 0x81-0xFE，低字節 0x40-0xFE (除 0x7F)。
2. 區位碼計算：code = 65536 - (高字節 * 256 + 低字節)，與標準區位碼表對應。
3. 映射表設計：code_ 為區位碼邊界值，pin_yin_ 為該區塊起始拼音。查找 CODE_ >= 目標碼 最近的一條。
4. HEX 處理：將二進制字符串轉十六進制，便於用 SUBSTRING 逐字節解析，避免字符集轉換陷阱。

局限與優化方向

• 不支持多音字：按首拼音輸出（如「重」輸出 chong 而非 zhong）。
• 不帶聲調：純字母拼音。
• 性能：逐字符循環，長字符串較慢。大批量轉換建議應用層處理或引入 C UDF。
• 字符集依賴：輸入參數需聲明 CHARSET gbk，若數據庫為 utf8mb4，調用前需 CONVERT(str USING gbk)。

現代替代方案 (MySQL 8.0+)

考慮使用 ICU 庫或應用層成熟庫（如 Python pypinyin、Java pinyin4j、Go pinyin），功能更全、性能更高、維護更易。