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