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 "%张三%";

+----+---------+
| id |  name   |
+----+---------+
|  1 |  F张三   |
|  2 |  E张三   |
|  3 |  D张三   |
|  4 |  C张三   |
|  5 |  B张三   |
|  6 |  A张三   |
|  7 |   张三   |

可以看到，与张三最相似的一条记录被排序到了最后面，如果使用了 LIMIT语句，那么很有可能无法再第一页获取到张三这条记录。
也就引出了今天的问题，如何在模糊查询条件下，让返回结果按照匹配度(相似度)进行进行排序，从而让最匹配的那条记录排在最前面。

问题分析

首先，需要定位一下问题原因，为什么最匹配的那条记录被排到了最后面?

这其实是与SELECT的默认排序规则有关，当使用SELECT进行查询而没有指定ORDER BY的时候，MySQL其实是按照id进行升序排序的，因为id作为主键索引通过它根本不需要排序，同时每次返回的结果顺序也完全相同。也就是说id = 7的张三记录之所以被排在最后面是因为它是在最后被插入的。
在一些情况下我们的排序是按照 name 来排序的，那么 name是如何进行排序的呢？首先 name字段的数据类型是 varchar可变长度的字符串类型，而对于字符串的排序方式则必然是字典序。
也就是当我们按照 name 进行排序后，会发现排序结果变成了这样：

+----+---------+
| id |  name   |
+----+---------+
|  6 |  A张三   |
|  5 |  B张三   |
|  4 |  C张三   |
|  3 |  D张三   |
|  2 |  E张三   |
|  1 |  F张三   |
|  7 |   张三   |

是 A张三被排在了最前面，是由于字符A的编码在所有name的开头最小，同理 张作为name的开头编码最大，因此被排在了最后面。如果我们的记录都是以张三开头，然后结尾是字母的话，则按照 name进行排序的确可以帮助我们将 张三 排到最前面：
SELECT * FROM user WHERE ORDER BY name;

+----+---------+
| id |  name   |
+----+---------+
|  7 |   张三   |
|  6 |  A张三   |
|  5 |  B张三   |
|  4 |  C张三   |
|  3 |  D张三   |
|  2 |  E张三   |
|  1 |  F张三   |

问题的关键是如何让查询结果按照匹配度进行排序。下面介绍一些可以用来当做匹配度的函数：

匹配度量化-字符串长度char_length()

我们可以对记录的字符长度进行排序，如下：

SELECT name,CHAR_LENGTH(name) len FROM user
WHERE name LIKE CONCAT("%","张三","%")
ORDER BY CHAR_LENGTH(name);

+--------+------+
| name   | len  |
+--------+------+
| 张三    |  2   |
| F张三   |  3   |
| E张三   |  3   |
| D张三   |  3   |
| C张三   |  3   |
| B张三   |  3   |
| A张三   |  3   |
| AAA张三 |  5   |

当我们根据查询条件 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);

+--------+------+
| name   | loc  |
+--------+------+
| 张三    |  1   |
| F张三   |  2   |
| E张三   |  2   |
| D张三   |  2   |
| C张三   |  2   |
| B张三   |  2   |
| A张三   |  2   |
| AAA张三 |  4   |

从查询结果可以看到,name值为张三这条记录通过 locate('张三',name) 计算后得到的结果为 1,其他值分别根据 张三 在 name 值中的位置依次增长,因此可以根据该函数进行排序,将和张三相关的记录排到最前面.

instr(str,substr):返回subStr在string中出现的位置

SELECT name,instr(name,'张三') instr FROM user 
WHERE name LIKE "%张三%" 
ORDER BY instr(name,'张三');

+--------+------+
| name   | instr|
+--------+------+
| 张三    |  1   |
| F张三   |  2   |
| E张三   |  2   |
| D张三   |  2   |
| C张三   |  2   |
| B张三   |  2   |
| A张三   |  2   |

position(substr IN str):返回subStr在string中出现的位置

SELECT name,position('张三' IN name) position FROM user 
WHERE name LIKE "%张三%" 
ORDER BY position('张三' IN name) ASC;

+--------+---------+
| name   | position|
+--------+---------+
| 张三    |     1   |
| F张三   |     2   |
| E张三   |     2   |
| D张三   |     2   |
| C张三   |     2   |
| B张三   |     2   |
| A张三   |     2   |
| AAA张三 |     4   |

position('张三' IN name) ASC
根据 张三 在 name 字段中的位置排序,此时如果有 A张三,张三B 时,由于 张三B 中 张三 的位置更靠前,因此会排到最前面.

相关度排序思路:让张三排到最前面

在一些场景下我们可能不一定需要都是张三的结果，可能需要李四，王五，然后要求张三排到最前面，此时我们可以通过设定排序方式来确保张三排到最前面.

这种情况下我们就不能 通过 name like 进行条件查找了,此时可以着重设计 ORDER BY 来调整排序方式.

SELECT name,instr(name,'张三') loc FROM user 
ORDER BY instr(name,'张三')=0,CHAR_LENGTH(name),instr(name,'张三');

+--------+---------+
| name   |    loc  |
+--------+---------+
| 张三    |     1   |
| F张三   |     2   |
| E张三   |     2   |
| D张三   |     2   |
| C张三   |     2   |
| B张三   |     2   |
| A张三   |     2   |
| 李四    |     0   |
| 王五    |     0   |

instr(name,'张三')=0 ASC
根据 name 中是否不包含 “张三” 进行排序,这里可以看到如果 name 字段包含张三的话则为 false(0), 如果不包含张三的话则为 true(1),用true(1)/false(0) 进行升序排序可以将包含张三的记录排到最前面.

SELECT name,instr(name,'张三')=0 matchs FROM user 
ORDER BY instr(name,'张三')=0;

+--------+---------+
| name   | matchs  |
+--------+---------+
| F张三   |     0   |
| E张三   |     0   |
| D张三   |     0   |
| C张三   |     0   |
| B张三   |     0   |
| A张三   |     0   |
| AAA张三 |     0   |
| 张三    |     0   |
| 李四    |     1   |
| 王五    |     1   |

CHAR_LENGTH(name) ASC
在经过上面的排序后所有包含 张三 的记录都被排到了最前面,此时我们可以对这部分记录按照之前的方式使用 CHAR_LENGTH(name)进行排序,首先最短的那条记录肯定是和张三相关度最高的,然后根据记录长度依次排名.

SELECT name,CHAR_LENGTH(name) len FROM user 
ORDER BY instr(name,'张三')=0,CHAR_LENGTH(name);

+--------+---------+
| name   |    len  |
+--------+---------+
| 张三    |     2   |
| F张三   |     3   |
| E张三   |     3   |
| D张三   |     3   |
| C张三   |     3   |
| B张三   |     3   |
| A张三   |     3   |
| AAA张三 |     5   |
| 李四    |     2   |
| 王五    |     2   |

instr(name,'张三') ASC
如果根据上面的排序后我们最后可以根据 张三 在 name 字段中记录的位置做最后一步的调整,将name字段中 张三 在记录中的位置靠前的记录优先级更高.

SELECT name,instr(name,'张三') inst FROM user 
ORDER BY instr(name,'张三')=0,CHAR_LENGTH(name),instr(name,'张三');

+--------+---------+
| name   |   inst  |
+--------+---------+
| 张三    |     1   |
| F张三   |     2   |
| E张三   |     2   |
| D张三   |     2   |
| C张三   |     2   |
| B张三   |     2   |
| A张三   |     2   |
| AAA张三 |     4   |
| 李四    |     0   |
| 王五    |     0   |