mysql实现根据同音字 首字母 拼音进行模糊搜索(复刻钉钉模糊搜索)

公司新上了一款低代码平台的项目,在使用过程中用户反馈搜索功能体感不好,不如钉钉的搜索灵活则尝试复刻了一下钉钉的灵活搜索,实现方式可能不同但最终展现的效果是一致的,特此记录

待优化:

mysql自定义函数影响查询速度,添加索引也很慢

部分生僻字不支持汉字转拼音

思路:

创建自定义函数对需要查询的字段及入参进行汉字转拼音,汉字取拼音首字母,通过转译后的数据进行模糊搜索

一: 实现拼音,同音字进行搜索

1.1:先在数据库中创建拼音对照表

-- ------------------------------ Table structure for t_base_pinyin-- ----------------------------DROP TABLE IF EXISTS `t_base_pinyin`;CREATE TABLE `t_base_pinyin` (`pin_yin` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '拼音',`code` int(11) NULL DEFAULT NULL COMMENT 'code',`create_time` timestamp(0) NULL DEFAULT CURRENT_TIMESTAMP(0) COMMENT '创建时间') ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '汉字拼音对照临时表' ROW_FORMAT = Dynamic;-- ------------------------------ Records of t_base_pinyin-- ----------------------------INSERT INTO `t_base_pinyin` VALUES ('a', 20319, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('ai', 20317, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('an', 20304, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('ang', 20295, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('ao', 20292, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('ba', 20283, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('bai', 20265, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('ban', 20257, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('bang', 20242, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('bao', 0, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('bei', 1, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('ben', 6, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('beng', 2, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('bi', 20026, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('bian', 20002, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('biao', 19990, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('bie', 19986, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('bin', 19982, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('bing', 19976, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('bo', 19805, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('bu', 19784, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('ca', 19775, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('cai', 19774, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('can', 19763, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('cang', 19756, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('cao', 19751, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('ce', 19746, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('ceng', 19741, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('cha', 19739, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('chai', 19728, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('chan', 19725, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('chang', 19715, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('chao', 19540, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('che', 19531, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('chen', 19525, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('cheng', 19515, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('chi', 19500, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('chong', 19484, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('chou', 19479, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('chu', 19467, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('chuai', 19289, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('chuan', 19288, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('chuang', 19281, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('chui', 19275, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('chun', 19270, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('chuo', 19263, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('ci', 19261, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('cong', 19249, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('cou', 19243, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('cu', 19242, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('cuan', 19238, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('cui', 19235, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('cun', 19227, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('cuo', 19224, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('da', 19218, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('dai', 19212, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('dan', 19038, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('dang', 19023, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('dao', 19018, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('de', 19006, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('deng', 19003, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('di', 18996, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('dian', 18977, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('diao', 18961, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('die', 18952, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('ding', 18783, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('diu', 18774, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('dong', 18773, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('dou', 18763, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('du', 18756, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('duan', 18741, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('dui', 18735, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('dun', 18731, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('duo', 18722, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('e', 18710, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('en', 18697, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('er', 18696, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('fa', 18526, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('fan', 18518, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('fang', 18501, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('fei', 18490, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('fen', 18478, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('feng', 18463, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('fo', 18448, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('fou', 18447, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('fu', 18446, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('ga', 18239, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('gai', 18237, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('gan', 18231, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('gang', 18220, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('gao', 18211, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('ge', 18201, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('gei', 18184, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('gen', 18183, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('geng', 18181, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('gong', 18012, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('gou', 17997, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('gu', 17988, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('gua', 17970, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('guai', 17964, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('guan', 17961, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('guang', 17950, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('gui', 17947, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('gun', 17931, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('guo', 17928, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('ha', 17922, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('hai', 17759, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('han', 17752, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('hang', 17733, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('hao', 17730, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('he', 17721, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('hei', 17703, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('hen', 17701, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('heng', 17697, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('hong', 17692, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('hou', 17683, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('hu', 17676, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('hua', 17496, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('huai', 17487, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('huan', 17482, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('huang', 17468, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('hui', 17454, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('hun', 17433, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('huo', 17427, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('ji', 17417, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('jia', 17202, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('jian', 17185, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('jiang', 16983, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('jiao', 16970, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('jie', 16942, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('jin', 16915, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('jing', 16733, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('jiong', 16708, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('jiu', 16706, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('ju', 16689, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('juan', 16664, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('jue', 16657, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('jun', 16647, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('ka', 16474, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('kai', 16470, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('kan', 16465, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('kang', 16459, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('kao', 16452, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('ke', 16448, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('ken', 16433, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('keng', 16429, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('kong', 16427, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('kou', 16423, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('ku', 16419, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('kua', 16412, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('kuai', 16407, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('kuan', 16403, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('kuang', 16401, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('kui', 16393, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('kun', 16220, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('kuo', 16216, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('la', 16212, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('lai', 16205, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('lan', 16202, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('lang', 16187, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('lao', 16180, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('le', 16171, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('lei', 16169, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('leng', 16158, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('li', 16155, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('lia', 15959, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('lian', 15958, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('liang', 15944, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('liao', 15933, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('lie', 15920, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('lin', 15915, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('ling', 15903, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('liu', 15889, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('long', 15878, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('lou', 15707, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('lu', 15701, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('lv', 15681, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('luan', 15667, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('lue', 15661, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('lun', 15659, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('luo', 15652, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('ma', 15640, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('mai', 15631, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('man', 15625, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('mang', 15454, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('mao', 15448, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('me', 15436, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('mei', 15435, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('men', 15419, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('meng', 15416, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('mi', 15408, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('mian', 15394, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('miao', 15385, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('mie', 15377, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('min', 15375, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('ming', 15369, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('miu', 15363, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('mo', 15362, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('mou', 15183, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('mu', 15180, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('na', 15165, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('nai', 15158, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('nan', 15153, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('nang', 15150, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('nao', 15149, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('ne', 15144, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('nei', 15143, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('nen', 15141, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('neng', 15140, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('ni', 15139, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('nian', 15128, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('niang', 15121, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('niao', 15119, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('nie', 15117, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('nin', 15110, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('ning', 15109, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('niu', 14941, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('nong', 14937, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('nu', 14933, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('nv', 14930, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('nuan', 14929, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('nue', 14928, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('nuo', 14926, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('o', 14922, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('ou', 14921, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('pa', 14914, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('pai', 14908, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('pan', 14902, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('pang', 14894, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('pao', 14889, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('pei', 14882, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('pen', 14873, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('peng', 14871, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('pi', 14857, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('pian', 14678, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('piao', 14674, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('pie', 14670, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('pin', 14668, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('ping', 14663, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('po', 14654, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('pu', 14645, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('qi', 14630, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('qia', 14594, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('qian', 14429, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('qiang', 14407, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('qiao', 14399, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('qie', 14384, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('qin', 14379, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('qing', 14368, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('qiong', 14355, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('qiu', 14353, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('qu', 14345, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('quan', 14170, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('que', 14159, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('qun', 14151, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('ran', 14149, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('rang', 14145, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('rao', 14140, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('re', 14137, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('ren', 14135, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('reng', 14125, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('ri', 14123, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('rong', 14122, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('rou', 14112, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('ru', 14109, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('ruan', 14099, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('rui', 14097, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('run', 14094, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('ruo', 14092, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('sa', 14090, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('sai', 14087, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('san', 14083, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('sang', 13917, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('sao', 13914, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('se', 13910, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('sen', 13907, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('seng', 13906, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('sha', 13905, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('shai', 13896, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('shan', 13894, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('shang', 13878, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('shao', 13870, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('she', 13859, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('shen', 13847, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('sheng', 13831, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('shi', 13658, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('shou', 13611, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('shu', 13601, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('shua', 13406, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('shuai', 13404, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('shuan', 13400, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('shuang', 13398, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('shui', 13395, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('shun', 13391, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('shuo', 13387, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('si', 13383, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('song', 13367, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('sou', 13359, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('su', 13356, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('suan', 13343, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('sui', 13340, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('sun', 13329, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('suo', 13326, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('ta', 13318, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('tai', 13147, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('tan', 13138, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('tang', 13120, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('tao', 13107, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('te', 13096, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('teng', 13095, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('ti', 13091, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('tian', 13076, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('tiao', 13068, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('tie', 13063, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('ting', 13060, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('tong', 12888, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('tou', 12875, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('tu', 12871, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('tuan', 12860, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('tui', 12858, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('tun', 12852, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('tuo', 12849, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('wa', 12838, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('wai', 12831, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('wan', 12829, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('wang', 12812, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('wei', 12802, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('wen', 12607, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('weng', 12597, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('wo', 12594, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('wu', 12585, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('xi', 12556, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('xia', 12359, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('xian', 12346, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('xiang', 12320, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('xiao', 12300, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('xie', 12120, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('xin', 12099, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('xing', 12089, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('xiong', 12074, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('xiu', 12067, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('xu', 12058, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('xuan', 12039, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('xue', 11867, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('xun', 11861, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('ya', 11847, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('yan', 11831, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('yang', 11798, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('yao', 11781, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('ye', 11604, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('yi', 11589, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('yin', 11536, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('ying', 11358, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('yo', 11340, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('yong', 11339, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('you', 11324, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('yu', 11303, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('yuan', 11097, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('yue', 11077, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('yun', 11067, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('za', 11055, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('zai', 11052, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('zan', 11045, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('zang', 11041, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('zao', 11038, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('ze', 11024, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('zei', 11020, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('zen', 11019, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('zeng', 11018, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('zha', 11014, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('zhai', 10838, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('zhan', 10832, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('zhang', 10815, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('zhao', 10800, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('zhe', 10790, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('zhen', 10780, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('zheng', 10764, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('zhi', 10587, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('zhong', 10544, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('zhou', 10533, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('zhu', 10519, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('zhua', 10331, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('zhuai', 10329, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('zhuan', 10328, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('zhuang', 10322, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('zhui', 10315, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('zhun', 10309, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('zhuo', 10307, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('zi', 10296, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('zong', 10281, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('zou', 10274, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('zu', 10270, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('zuan', 10262, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('zui', 10260, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('zun', 10256, '-03-22 10:36:03');INSERT INTO `t_base_pinyin` VALUES ('zuo', 10254, '-03-22 10:36:03');SET FOREIGN_KEY_CHECKS = 1;

1.2:创建汉字转换拼音的自定义函数

CREATE DEFINER=`root`@`%` FUNCTION `to_pinyin`(NAME VARCHAR(255) CHARSET gbk) RETURNS varchar(255) CHARSET gbkBEGIN 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

1.3:mysql测试函数是否生效

select to_pinyin ('测试')

注:部分生僻字无法转换 转译结果都是zuo

二：实现汉字首字母查询

2.1: 创建字符串取首字母函数方法

CREATE DEFINER=`root`@`%` FUNCTION `to_frist_pinyin`(P_NAME VARCHAR(255)) RETURNS varchar(255) CHARSET utf8BEGINDECLARE V_RETURN VARCHAR(255);DECLARE V_FIRST_CHAR VARCHAR(255);#这块主要意思是假如传入的是英文串的话，只取首字母set V_FIRST_CHAR =UPPER(LEFT(CONVERT(P_NAME USING gbk),1));set V_RETURN = V_FIRST_CHAR;#如果是这些特殊符号，直接返回#IF V_FIRST_CHAR in ('（','）','《','》')THEN SET V_RETURN = '';#两个不相等只有一个情况，V_FIRST_CHAR是中文汉字或者中文符号。elseif LENGTH( V_FIRST_CHAR) <> CHARACTER_LENGTH( V_FIRST_CHAR )thenSET V_RETURN = ELT(INTERVAL(CONV(HEX(left(CONVERT(P_NAME USING gbk),1)),16,10),0xB0A1,0xB0C5,0xB2C1,0xB4EE,0xB6EA,0xB7A2,0xB8C1,0xB9FE,0xBBF7,0xBFA6,0xC0AC,0xC2E8,0xC4C3,0xC5B6,0xC5BE,0xC6DA,0xC8BB,0xC8F6,0xCBFA,0xCDDA,0xCEF4,0xD1B9,0xD4D1),'A','B','C','D','E','F','G','H','J','K','L','M','N','O','P','Q','R','S','T','W','X','Y','Z');#如果是下面的直接原样输出elseif V_FIRST_CHAR in ('A','B','C','D','E','F','G','H','J','K','L','M','N','O','P','Q','R','S','T','W','X','Y','Z','1','2','3','4','5','6','7','8','9','0','*','+','-','=','/','\\','{','}','[',']','(',')','（','）')then SET V_RETURN = V_RETURN;#其他的输出#elseSET V_RETURN = '#';END IF;#为空的话输出#RETURN IFNULL(V_RETURN,'#');END

2.3: 测试函数是否生效

select to_frist_pinyin ('测试')

2.2: 创建获取字符串每个字的首字母函数方法

CREATE DEFINER=`root`@`%` FUNCTION `to_initial_pinyin`(P_NAME VARCHAR(255)) RETURNS varchar(255) CHARSET gbkDETERMINISTICBEGINDECLARE V_COMPARE VARCHAR(255);DECLARE V_RETURN VARCHAR(255);DECLARE I INT;SET I = 1;SET V_RETURN = '';#循环截取字符while I < LENGTH(P_NAME) doSET V_COMPARE = SUBSTR(P_NAME, I, 1);IF (V_COMPARE != '') THEN#字符串拼接SET V_RETURN = CONCAT(V_RETURN, to_frist_pinyin(V_COMPARE));END IF;SET I = I + 1;end while;IF (ISNULL(V_RETURN) or V_RETURN = '') THENSET V_RETURN = P_NAME;END IF;RETURN V_RETURN;END

2.3: 测试函数是否生效

使用示例:

同音字示例:

SELECT* FROMemployee_info WHERE(employee_name LIKE CONCAT( '%', '策士', '%' ) OR to_pinyin ( employee_name ) LIKE CONCAT( '%', to_pinyin ( '策士' ), '%' ) OR to_initial_pinyin ( employee_name ) LIKE CONCAT( '%', '策士', '%' ) )

首字母示例:

SELECT* FROMemployee_info WHERE(employee_name LIKE CONCAT( '%', 'cs', '%' ) OR to_pinyin ( employee_name ) LIKE CONCAT( '%', to_pinyin ( 'cs' ), '%' ) OR to_initial_pinyin ( employee_name ) LIKE CONCAT( '%', 'cs', '%' ) )

过程中遇到的一些报错

Incorrect string value: '\xE7\xA8\x8B\xE5\xBA\x8F...' for column 'course' at row 1

检查数据库或者需查询字段的编码格式,修改为utf-8