mysql实现用拼音搜索中文的数据库实现
浏览数：51 /
时间：2015年06月12日
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 < 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; $ DELIMITER ;
4、创建视图：
CREATE VIEW v_pinyin AS SELECT u.id, to_pinyin (u.displayname) AS pinyin, u.displayName FROM wsm_userinfo u
郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。
