MySQL实现用拼音搜索中文的数据库实现
ec3y
9年前
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