学习使用mysql中like查询支持匹配度进行查询排序
前言
我们在使用 MySQL 进行模糊查询的时候,受到默认排序的影响,我们会发现与查询关键字相似的记录有时候并没有排到最前面。
如 :
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for qipa_user
-- ----------------------------
DROP TABLE IF EXISTS `qipa_user`;
CREATE TABLE `qipa_user` (
`id` bigint(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 9 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
-- ----------------------------
-- Records of qipa_user
-- ----------------------------
INSERT INTO `qipa_user` VALUES (1, 'F奇葩天网');
INSERT INTO `qipa_user` VALUES (2, 'E奇葩地网');
INSERT INTO `qipa_user` VALUES (3, 'D奇葩天地网');
INSERT INTO `qipa_user` VALUES (4, 'C奇葩网');
INSERT INTO `qipa_user` VALUES (5, 'B每日奇葩');
INSERT INTO `qipa_user` VALUES (6, 'A每日奇葩天地网');
INSERT INTO `qipa_user` VALUES (7, '奇葩天地网');
INSERT INTO `qipa_user` VALUES (8, '奇葩');
SET FOREIGN_KEY_CHECKS = 1;
对上述数据以 奇葩
为关键字进行模糊查询后,会发现查询结果如下:
SELECT * FROM `qipa_user` where name Like '%奇葩%'
可以看到,与 奇葩
最相似的一条记录被排序到了最后面,如果我们使用了 LIMIT语句,那么很有可能无法再第一页获取到 奇葩
这条记录。
也就引出了今天的问题,如何在模糊查询条件下,让返回结果按照匹配度(相似度)进行进行排序,从而让最匹配的那条记录排在最前面。
分析
首先,我们需要定位一下问题原因,为什么最匹配的那条记录被排到了最后面?
这其实是与SELECT 的默认排序规则有关,当我们使用 SELECT 进行查询而没有指定 ORDER BY的时候,MySQL其实是按照id 进行升序排序的,因为 id作为主键索引通过它根本不需要排序,同时每次返回的结果顺序也完全相同。也就是说id = 8 的 奇葩
记录之所以被排在最后面是因为它是在最后被插入的,即使它是最 奇葩
的 奇葩
也要讲个先来后到。
在一些情况下我们的排序是按照 name 来排序的,那么 name是如何进行排序的呢?首先 name字段的数据类型是 varchar可变长度的字符串类型,而对于字符串的排序方式则必然是字典序。
也就是当我们按照 name 进行排序后,会发现排序结果 变成了这样:
SELECT * FROM `qipa_user` where name Like '%奇葩%' order by name asc
是 A 奇葩
被排在了最前面,是由于字符A的编码在所有name的开头最小,同理 张作为name的开头编码最大,因此被排在了最后面。如果我们的记录都是以 奇葩
开头,然后结尾是字母的话,则按照 name进行排序的确可以帮助我们将 奇葩
排到最前面:
问题的关键是如何让查询结果按照匹配度进行排序。
匹配度函数
匹配度量化-字符串长度char_length()
我们可以对记录的字符长度进行排序
SELECT NAME
FROM
`qipa_user`
WHERE
NAME LIKE CONCAT( "%", "奇葩", "%" )
ORDER BY
CHAR_LENGTH( NAME ),
NAME
当我们根据查询条件 **name LIKE CONCAT("%","奇葩 ","%")
**进行模糊查询的时候,返回记录的结果有四种样式:
奇葩
奇葩%
%奇葩
%奇葩%
可以看到,除了第二种和第三种条件之外,我们能够根据字符串的长度将结果大致分开。即匹配度和字符串长度呈现相关联的关系。
匹配结果在记录中位置locate(),instr(),position()
其实使用 char_length()
就已经能够解决大部分问题了,如果感觉效果还是不够好的话还可以搭配使用locate()
,instr()
,position()
之一对排序结果进行进一步的处理。
locate(subStr,string)
:返回subStr
在string
中出现的位置
locate()
函数会返回一个字符串在另一个字符串中出现的位置。如果包含,则返回对应的索引位置(从1开始),反之则返回 0。
SELECT NAME
,
locate( '奇葩', NAME ) loc
FROM
`qipa_user`
WHERE
NAME LIKE "%奇葩%"
ORDER BY
CHAR_LENGTH( NAME ),
locate( '奇葩', NAME )
position(substr IN str)
:返回subStr
在string
中出现的位置
SELECT NAME
,
position( '奇葩' IN NAME ) loc
FROM
qipa_user
WHERE
NAME LIKE "%奇葩%"
ORDER BY
CHAR_LENGTH( NAME ),
position( '奇葩' IN NAME ) asc