MySQL中文转拼音相关函数

/ 数据库 / 没有评论 / 367浏览

​

1.转为全拼

  新建数据库:



SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;


-- ----------------------------
-- Table structure for t_base_pinyin
-- ----------------------------
DROP TABLE IF EXISTS t_base_pinyin;
CREATE TABLE t_base_pinyin  (
code_ int(255) NOT NULL,
pin_yin_ varchar(255) CHARACTER SET gbk COLLATE gbk_chinese_ci NOT NULL,
PRIMARY KEY (code_) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Compact;


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


SET FOREIGN_KEY_CHECKS = 1;

写函数(根据你的库略作修改):

CREATE DEFINER=`wf_course_v3`@`%` FUNCTION `to_pinyin`(NAME VARCHAR(255) CHARSET gbk) RETURNS varchar(255) CHARSET gbk
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 ;

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;

ELSE

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

2.首字母:

两个函数:

CREATE DEFINER=`wf_course_v3`@`%` FUNCTION `fristPinyin`(P_NAME VARCHAR(255)) RETURNS varchar(255) CHARSET utf8
BEGIN
    DECLARE V_RETURN VARCHAR(255);
		
    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 DEFINER=`wf_course_v3`@`%` FUNCTION `pinyin`(P_NAME VARCHAR(255)) RETURNS varchar(255) CHARSET utf8
BEGIN
    DECLARE V_COMPARE VARCHAR(255);
    DECLARE V_RETURN VARCHAR(255);
    DECLARE I INT;
    SET I = 1;
    SET V_RETURN = '';
    while I < LENGTH(P_NAME) do
        SET V_COMPARE = SUBSTR(P_NAME, I, 1);
        IF (V_COMPARE != '' and length(V_COMPARE) != CHARACTER_LENGTH( V_COMPARE )) THEN
				     if(fristPinyin(V_COMPARE) is not null) then 
                SET V_RETURN = CONCAT(V_RETURN, fristPinyin(V_COMPARE));
						 END IF;
        END IF;
        SET I = I + 1;
    end while;

    IF (ISNULL(V_RETURN) or V_RETURN = '') THEN
        SET V_RETURN = P_NAME;
    END IF;

    RETURN  LOWER(V_RETURN);
END

使用第二个即可

​

本文由 GY 创作，采用 知识共享署名4.0 国际许可协议进行许可
本站文章除注明转载/出处外，均为本站原创或翻译，转载前请务必署名
最后编辑时间为: 2021/11/19 15:13

→ ← ↓ ↑