Mysql 汉字转拼音

Ocean@上源码

已于 2023-09-15 11:47:00 修改

阅读量1.7k

点赞数 4

分类专栏：工具文章标签： mysql

于 2023-01-01 12:31:02 首次发布

本文链接：https://blog.csdn.net/qq_34253002/article/details/128458724

版权

工具专栏收录该内容

15 篇文章 0 订阅

订阅专栏

最近工作中业务提出用户在填写推荐人时可能会出现输入错别字的情况，为了给用户更好的体验，决定用户输入做拼音匹配（测试=侧式=侧室），因为需要汉字拼音转换工具。

操作步骤
1.创建拼音Unicode对照表
2.完善对照表信息
3.创建转换方法

一.创建拼音Unicode对照表

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_`)
    );

二. 完善对照表数据

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),
	("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);

三.创建汉字转换拼音函数

# 出现错误“This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its de”
# 执行 set global log_bin_trust_function_creators=TRUE;

CREATE 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 ;