MySQL中文转换成拼音的函数

也是头一次看到关于在mysql中直接把中文自动转换成拼音两个例子,看上去非常的好用我整理一下与大家分享,希望文章给大家有帮助.

汉字转拼音全拼,代码如下:

1. --创建汉字拼音对照临时表
2. CREATETABLEIFNOTEXISTS`t_base_pinyin`(
3. `pin_yin_`varchar(255)CHARACTERSETgbkNOTNULL,
4. `code_`int(11)NOTNULL,
5. PRIMARYKEY(`code_`)
6. )ENGINE=InnoDBDEFAULTCHARSET=latin1;
7. --插入数据
8. INSERTINTOt_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),("zhuai",10329),("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);

-- 建立汉字转换拼音函数,代码如下:

1. DROPFUNCTIONIFEXISTSto_pinyin;
2. DELIMITER$
3. CREATEFUNCTIONto_pinyin(NAMEVARCHAR(255)CHARSETgbk)
4. RETURNSVARCHAR(255)CHARSETgbk
5. BEGIN
6. DECLAREmycodeINT;
7. DECLAREtmp_lcodeVARCHAR(2)CHARSETgbk;
8. DECLARElcodeINT;
10. DECLAREtmp_rcodeVARCHAR(2)CHARSETgbk;
11. DECLARErcodeINT;
13. DECLAREmypyVARCHAR(255)CHARSETgbkDEFAULT'';
14. DECLARElpINT;
16. SETmycode=0;
17. SETlp=1;
19. SETNAME=HEX(NAME);
21. WHILElp<LENGTH(NAME)DO
23. SETtmp_lcode=SUBSTRING(NAME,lp,2);
24. SETlcode=CAST(ASCII(UNHEX(tmp_lcode))ASUNSIGNED);
25. SETtmp_rcode=SUBSTRING(NAME,lp+2,2);
26. SETrcode=CAST(ASCII(UNHEX(tmp_rcode))ASUNSIGNED);
27. IFlcode>128THEN
28. SETmycode=65536-lcode*256-rcode;
29. SELECTCONCAT(mypy,pin_yin_)INTOmypyFROMt_base_pinyinWHERECODE_>=ABS(mycode)ORDERBYCODE_ASCLIMIT1;
30. SETlp=lp+4;
31. ELSE
32. SETmypy=CONCAT(mypy,CHAR(CAST(ASCII(UNHEX(SUBSTRING(NAME,lp,2)))ASUNSIGNED)));
33. SETlp=lp+2;
34. ENDIF;
35. ENDWHILE;
36. RETURNLOWER(mypy);
37. END;
38. $
39. DELIMITER;

用法:select to_pinyin('测试')

输出结果:to_pinyin('测试')ceshi

例子2,中文汉字转拼音函数,fristPinyin,此函数是将一个中文字符串的第一个汉字转成拼音字母,例如,"中国人"->Z,代码如下:

1. CREATEFUNCTION`fristPinyin`(P_NAMEVARCHAR(255))RETURNSvarchar(255)CHARSETutf8
2. BEGIN
3. DECLAREV_RETURNVARCHAR(255);
4. SETV_RETURN=ELT(INTERVAL(CONV(HEX(left(CONVERT(P_NAMEUSINGgbk),1)),16,10),
5. 0xB0A1,0xB0C5,0xB2C1,0xB4EE,0xB6EA,0xB7A2,0xB8C1,0xB9FE,0xBBF7,
6. 0xBFA6,0xC0AC,0xC2E8,0xC4C3,0xC5B6,0xC5BE,0xC6DA,0xC8BB,
7. 0xC8F6,0xCBFA,0xCDDA,0xCEF4,0xD1B9,0xD4D1),
8. 'A','B','C','D','E','F','G','H','J','K','L','M','N','O','P','Q','R','S','T','W','X','Y','Z');
9. RETURNV_RETURN;
10. END

pinyin:此函数是将一个中文字符串对应拼音母的每个相连,例如,"中国人"->ZGR,代码如下:

1. CREATEFUNCTION`pinyin`(P_NAMEVARCHAR(255))RETURNSvarchar(255)CHARSETutf8
2. BEGIN
3. DECLAREV_COMPAREVARCHAR(255);
4. DECLAREV_RETURNVARCHAR(255);
5. DECLAREIINT;
7. SETI=1;
8. SETV_RETURN='';
9. whileI<LENGTH(P_NAME)do
10. SETV_COMPARE=SUBSTR(P_NAME,I,1);
11. IF(V_COMPARE!='')THEN
12. #SETV_RETURN=CONCAT(V_RETURN,',',V_COMPARE);
13. SETV_RETURN=CONCAT(V_RETURN,fristPinyin(V_COMPARE));
14. #SETV_RETURN=fristPinyin(V_COMPARE);
15. ENDIF;
16. SETI=I+1;
17. endwhile;
19. IF(ISNULL(V_RETURN)orV_RETURN='')THEN
20. SETV_RETURN=P_NAME;
21. ENDIF;
23. RETURNV_RETURN;
24. END//phpfensi.com

示例:

1. mysql>selectp.province,fristPinyin(p.province),pinyin(p.province)fromprovincep;
2. +------------------+-------------------------+--------------------+
3. |province|fristPinyin(p.province)|pinyin(p.province)|
4. +------------------+-------------------------+--------------------+
5. |北京市|B|BJS|
6. |天津市|T|TJS|
7. |河北省|H|HBS|
8. |山西省|S|SXS|
9. |内蒙古自治区|N|NMGZZQ|
10. +------------------+-------------------------+--------------------+
11. 5rowsinset