mysql 拼音查询_mysql实现用拼音搜索中文的数据库实现
1、创建表：
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;
2、插入数据：
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);
3、创建函数：
DROP FUNCTION IF EXISTS to_pinyin;
DELIMITER $
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
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;
$
DELIMITER ;
4、创建视图：
CREATE VIEW v_pinyin AS SELECT
u.id,
to_pinyin (u.displayname) AS pinyin,
u.displayName
FROM
wsm_userinfo u
本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！