· 8 years ago · Apr 04, 2018, 01:10 PM
1-- 创建汉å—拼音对照临时表
2
3CREATE TABLE IF NOT EXISTS `t_base_pinyin` (
4
5 `pin_yin_` varchar(255) CHARACTER SET gbk NOT NULL,
6
7 `code_` int(11) NOT NULL,
8
9 PRIMARY KEY (`code_`)
10
11) ENGINE=InnoDB DEFAULT CHARSET=latin1;
12
13-- æ’入数æ®
14
15INSERT 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),("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);
16
17-- 建立汉å—转æ¢æ‹¼éŸ³å‡½æ•°
18
19DROP FUNCTION IF EXISTS to_pinyin;
20
21DELIMITER $
22
23CREATE FUNCTION to_pinyin(NAME VARCHAR(255) CHARSET gbk)
24
25RETURNS VARCHAR(255) CHARSET gbk
26
27BEGIN
28
29 DECLARE mycode INT;
30
31 DECLARE tmp_lcode VARCHAR(2) CHARSET gbk;
32
33 DECLARE lcode INT;
34 DECLARE tmp_rcode VARCHAR(2) CHARSET gbk;
35
36 DECLARE rcode INT;
37 DECLARE mypy VARCHAR(255) CHARSET gbk DEFAULT '';
38
39 DECLARE lp INT;
40 SET mycode = 0;
41
42 SET lp = 1;
43 SET NAME = HEX(NAME);
44 WHILE lp < LENGTH(NAME) DO
45 SET tmp_lcode = SUBSTRING(NAME, lp, 2);
46
47 SET lcode = CAST(ASCII(UNHEX(tmp_lcode)) AS UNSIGNED);
48
49 SET tmp_rcode = SUBSTRING(NAME, lp + 2, 2);
50
51 SET rcode = CAST(ASCII(UNHEX(tmp_rcode)) AS UNSIGNED);
52
53 IF lcode > 128 THEN
54
55 SET mycode =65536 - lcode * 256 - rcode ;
56
57 SELECT CONCAT(mypy,pin_yin_) INTO mypy FROM t_base_pinyin WHERE CODE_ >= ABS(mycode) ORDER BY CODE_ ASC LIMIT 1;
58
59 SET lp = lp + 4;
60
61 ELSE
62
63 SET mypy = CONCAT(mypy,CHAR(CAST(ASCII(UNHEX(SUBSTRING(NAME, lp, 2))) AS UNSIGNED)));
64
65 SET lp = lp + 2;
66
67 END IF;
68
69 END WHILE;
70
71 RETURN LOWER(mypy);
72
73END;
74
75$
76
77DELIMITER ;
78
79用法：
8010 select to_pinyin('测试')
81
82输出结果：
83 to_pinyin('测试')
84
85ceshi
86
87
88-- 汉å—è¯ç»„首å—的拼音 （ä¸å›½ ==> Z）
89
90DELIMITER //
91CREATE FUNCTION `fristPinyin`(P_NAME VARCHAR(255)) RETURNS varchar(255)
92 BEGIN
93 DECLARE V_RETURN VARCHAR(255);
94
95 SET V_RETURN = ELT(INTERVAL(CONV(HEX(left(CONVERT(P_NAME USING gbk),1)),16,10),
96 0xB0A1,0xB0C5,0xB2C1,0xB4EE,0xB6EA,0xB7A2,0xB8C1,0xB9FE,0xBBF7,
97 0xBFA6,0xC0AC,0xC2E8,0xC4C3,0xC5B6,0xC5BE,0xC6DA,0xC8BB,
98 0xC8F6,0xCBFA,0xCDDA,0xCEF4,0xD1B9,0xD4D1),
99 'A','B','C','D','E','F','G','H','J','K','L','M','N','O','P','Q','R','S','T','W','X','Y','Z');
100
101 RETURN V_RETURN;
102
103 END //
104DELIMITER ;
105
106
107
108
109
110
111-- 汉å—è¯ç»„首å—æ¯ ï¼ˆä¸å›½ ==> ZG）
112
113DELIMITER //
114CREATE FUNCTION `pinyin`(P_NAME VARCHAR(255)) RETURNS varchar(255) CHARSET utf8
115
116BEGIN
117
118 DECLARE V_COMPARE VARCHAR(255);
119
120 DECLARE V_RETURN VARCHAR(255);
121
122 DECLARE I INT;
123 SET I = 1;
124
125 SET V_RETURN = '';
126
127 while I < LENGTH(P_NAME) do
128
129 SET V_COMPARE = SUBSTR(P_NAME, I, 1);
130
131 IF (V_COMPARE != '') THEN
132
133 #SET V_RETURN = CONCAT(V_RETURN, ',', V_COMPARE);
134
135 SET V_RETURN = CONCAT(V_RETURN, fristPinyin(V_COMPARE));
136
137 #SET V_RETURN = fristPinyin(V_COMPARE);
138
139 END IF;
140
141 SET I = I + 1;
142
143 end while;
144 IF (ISNULL(V_RETURN) or V_RETURN = '') THEN
145
146 SET V_RETURN = P_NAME;
147
148 END IF;
149 RETURN V_RETURN;
150
151END //
152DELIMITER ;