1、创建表：
CREATETABLE
IFNOTEXISTS`t_base_pinyin`(
`pin_yin_`VARCHAR(255)CHARACTER
SETgbkNOTNULL,
`code_`INT(11)NOTNULL,
PRIMARYKEY(`code_`)
)ENGINE=INNODBDEFAULTCHARSET=latin1;
2、插入数据：
INSERTINTOt_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",0),
("bei",1),
("ben",6),
("beng",2),
("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、创建函数：
DROPFUNCTIONIFEXISTSto_pinyin;
DELIMITER$
CREATEFUNCTIONto_pinyin(NAMEVARCHAR(255)CHARSETgbk)
RETURNSVARCHAR(255)CHARSETgbk
BEGIN
DECLAREmycodeINT;
DECLAREtmp_lcodeVARCHAR(2)CHARSETgbk;
DECLARElcodeINT;
DECLAREtmp_rcodeVARCHAR(2)CHARSETgbk;
DECLARErcodeINT;
DECLAREmypyVARCHAR(255)CHARSETgbkDEFAULT'';
DECLARElpINT;
SETmycode=0;
SETlp=1;
SETNAME=HEX(NAME);
WHILElp
SETtmp_lcode=SUBSTRING(NAME,lp,2);
SETlcode=CAST(ASCII(UNHEX(tmp_lcode))ASUNSIGNED);
SETtmp_rcode=SUBSTRING(NAME,lp+2,2);
SETrcode=CAST(ASCII(UNHEX(tmp_rcode))ASUNSIGNED);
IFlcode>128THEN
SETmycode=65536-lcode*256-rcode;
SELECTCONCAT(mypy,pin_yin_)INTOmypyFROMt_base_pinyinWHERECODE_>=ABS(mycode)ORDERBYCODE_ASCLIMIT1;
SETlp=lp+4;
ELSE
SETmypy=CONCAT(mypy,CHAR(CAST(ASCII(UNHEX(SUBSTRING(NAME,lp,2)))ASUNSIGNED)));
SETlp=lp+2;
ENDIF;
ENDWHILE;
RETURNLOWER(mypy);
END;
$
DELIMITER;
4、创建视图：
CREATEVIEWv_pinyinASSELECT
u.id,
to_pinyin(u.displayname)ASpinyin,
u.displayName
FROM
wsm_userinfou
如果觉得《mysql拼音搜索中文_mysql实现用拼音搜索中文的数据库实现》对你有帮助，请点赞、收藏，并留下你的观点哦！
