mysql 拼音模糊搜索汉字

– 创建汉字拼音对照临时表–

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;

---- 添加数据 ----

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

----- 创建汉字转换拼音函数 ----

DROP FUNCTION IF EXISTS to_pinyin;
CREATE DEFINER=`root`@`%` 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

---- 添加函数会报错 ----
This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you might want to use the less safe log_bin_trust_function_creators variable)
执行SET GLOBAL log_bin_trust_function_creators = 1;可以暂时解决

---- 测试函数 —

select to_pinyin('张三');

---- 测试sql —

SELECT * FROM `user` where `name` LIKE CONCAT('%','zhang','%');

— 测试sql 和 函数结合用 —

SELECT * FROM `user` where (`name` LIKE CONCAT('%','zhang','%') OR to_pinyin(`name`) LIKE CONCAT('%','zhang','%'));