MySQL模糊查询 结果按匹配度 排序 |
您所在的位置:网站首页 › sql中模糊查询语句 › MySQL模糊查询 结果按匹配度 排序 |
我们在使用 MySQL 进行模糊查询的时候,受到默认排序的影响,我们会发现与查询关键字相似的记录有时候并没有排到最前面。 如 : CREATE TABLE user( id BIGINT(11) AUTO_INCREMENT PRIMARY KEY, name varchar(255) )engine=InnoDB,DEFAULT CHARSET=UTF8; INSERT INTO user(name) values("F张三"),("E张三"),("D张三"),("C张三"),("B张三"),("A张三"),("AAA张三"),("张三"),("李四"),("王五");对上述数据以张三为关键字进行模糊查询后,会发现查询结果如下: SELECT * FROM user WHERE name LIKE "%张三%";
首先,我们需要定位一下问题原因,为什么最匹配的那条记录被排到了最后面? 这其实是与SELECT 的默认排序规则有关,当我们使用 SELECT 进行查询而没有指定 ORDER BY的时候,MySQL其实是按照id 进行升序排序的,因为 id作为主键索引通过它根本不需要排序,同时每次返回的结果顺序也完全相同。也就是说id = 7 的张三记录之所以被排在最后面是因为它是在最后被插入的,即使它是最张三的张三也要讲个先来后到。 在一些情况下我们的排序是按照 name 来排序的,那么 name是如何进行排序的呢?首先 name字段的数据类型是 varchar可变长度的字符串类型,而对于字符串的排序方式则必然是字典序。 也就是当我们按照 name 进行排序后,会发现排序结果 变成了这样:
我们可以对记录的字符长度进行排序,如下: SELECT name,CHAR_LENGTH(name) len FROM user WHERE name LIKE CONCAT("%","张三","%") ORDER BY CHAR_LENGTH(name);当我们根据查询条件 name LIKE CONCAT("%","张三","%")进行模糊查询的时候,返回记录的结果有四种样式: 张三张三%%张三%张三%可以看到,当我们使用条件查询后,name 记录值越短,记录和我们预期的结果就相关,此时我们就可以使用 CHAR_LENGTH(name) ASC 实现相关度排序. CHAR_LENGTH(name) ASC 根据 name 字段的长度进行排序,此时如果有 张三,A张三,张三B 时,由于 张三 的长度最短且相关度最高,根据字段长度升序排序可以确保 张三排到最前面. 匹配度量化-匹配结果在记录中位置locate(),instr(),position()其实使用 char_length() 就已经能够解决大部分问题了,如果感觉效果还是不够好的话还可以搭配使用locate(),instr(),position()之一对排序结果进行进一步的处理。 locate(subStr,string):返回subStr在string中出现的位置locate() 函数会返回一个字符串在另一个字符串中出现的位置。如果包含,则返回对应的索引位置(从1开始),反之则返回 0。 SELECT name,locate('张三',name) loc FROM user WHERE name LIKE "%张三%" ORDER BY CHAR_LENGTH(name),locate('张三',name);
instr() 函数与 locate()函数效果基本相同. SELECT name,instr(name,'张三') instr FROM user WHERE name LIKE "%张三%" ORDER BY instr(name,'张三');
在一些场景下我们可能不一定需要都是张三的结果,可能需要李四,王五,然后要求张三排到最前面,此时我们可以通过设定排序方式来确保张三排到最前面. 这种情况下我们就不能 通过 name like 进行条件查找了,此时可以着重设计 ORDER BY 来调整排序方式. SELECT name,instr(name,'张三') loc FROM user ORDER BY instr(name,'张三')=0,CHAR_LENGTH(name),instr(name,'张三');
根据 name 中是否不包含 “张三” 进行排序,这里可以看到如果 name 字段包含张三的话则为 false(0), 如果不包含张三的话则为 true(1),用true(1)/false(0) 进行升序排序可以将包含张三的记录排到最前面. SELECT name,instr(name,'张三')=0 matchs FROM user ORDER BY instr(name,'张三')=0;CHAR_LENGTH(name) ASC 在经过上面的排序后所有包含 张三 的记录都被排到了最前面,此时我们可以对这部分记录按照之前的方式使用 CHAR_LENGTH(name)进行排序,首先最短的那条记录肯定是和张三相关度最高的,然后根据记录长度依次排名. SELECT name,CHAR_LENGTH(name) len FROM user ORDER BY instr(name,'张三')=0,CHAR_LENGTH(name);instr(name,'张三') ASC 如果根据上面的排序后我们最后可以根据 张三 在 name 字段中记录的位置做最后一步的调整,将name字段中 张三 在记录中的位置靠前的记录优先级更高. SELECT name,instr(name,'张三') inst FROM user ORDER BY instr(name,'张三')=0,CHAR_LENGTH(name),instr(name,'张三');Mysql匹配度排序 MYSQL 模糊查询按着匹配度排序 |
今日新闻 |
推荐新闻 |
CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |