问题描述

我已经多次重温这个问题，但我从来没有真正找到正确的答案.

I have revisited this problem many times, and I have never really found a proper answer.

是否可以执行按相关性返回 ACTUAL 准确排序结果的 MySQL 搜索?

我正在尝试创建一个 ajax 搜索表单，该表单在用户输入输入字段时提出建议，但没有找到仅使用纯 MySQL 查询的合适解决方案.我知道有可用的搜索服务器，例如 ElasticSearch，我想知道如何仅使用原始 MySQL 查询来执行此操作.

I am trying to create an ajax search form which makes suggestions as the user types into an input field, and have found no decent solution to this using only pure MySQL queries. I know there are search servers available such as ElasticSearch, I want to know how to do it with a raw MySQL query only.

我有一张学校科目表.少于 1200 行，这永远不会改变.让我们执行一个基本的 FULLTEXT 搜索，用户开始输入Bio".

I have a table of school subjects. There are less than 1200 rows and this will never change. Let's perform a basic FULLTEXT search where the user starts typing "Bio".

查询(Bio...") - 全文布尔模式

SELECT name, MATCH(name) AGAINST('bio*' IN BOOLEAN MODE) AS relevance
FROM subjects
WHERE MATCH(name) AGAINST('bio*' IN BOOLEAN MODE)
ORDER BY relevance DESC
LIMIT 10

结果

name                                  |  relevance
--------------------------------------------------------
Biomechanics, Biomaterials and Prosthetics  |  1
Applied Biology                             |  1
Behavioural Biology                         |  1
Cell Biology                                |  1
Applied Cell Biology                        |  1
Developmental/Reproductive Biology          |  1
Developmental Biology                       |  1
Reproductive Biology                        |  1
Environmental Biology                       |  1
Marine/Freshwater Biology                   |  1

为了显示这些结果有多糟糕，这里与一个简单的 LIKE 查询进行了比较，该查询显示了所有未显示的更相关的结果:

To show how bad these results are, here is a comparison with a simple LIKE query which shows all the more relevant results which weren't shown:

查询(Bio...")- LIKE

SELECT id, name
WHERE name LIKE 'bio%'
ORDER BY name

结果

name                                  |  relevance
--------------------------------------------------------
Bio-organic Chemistry                       |  1
Biochemical Engineering                     |  1
Biodiversity                                |  1
Bioengineering                              |  1
Biogeography                                |  1
Biological Chemistry                        |  1
Biological Sciences                         |  1
Biology                                     |  1
Biomechanics, Biomaterials and Prosthetics  |  1
Biometry                                    |  1

并且您已经看到有多少主题没有被建议，即使这些主题更有可能是用户想要的.

And already you see how many subjects are not suggested, even though these are more likely what the user will be looking for.

然而，使用 LIKE 的问题是如何像 FULLTEXT 那样跨多个单词和单词中间进行搜索.

The problem with using LIKE however, is how to search across multiple words and in the middle of words like FULLTEXT does.

我想要实现的基本排序是这样的:

The basic ordering I would want to implement is something like:

1. 以搜索词开头的第一个词
2. 以搜索词开头的第二个词
3. 词不在词首的词
4. 如果没有进一步的相关性，所有内容通常按字母顺序排列

所以我的问题是，如何通过跨多个单词的 MySQL 搜索为用户获得合理排序的建议列表?

So my question is, how does one go about getting a sensibly sorted list of suggestions for the user with a MySQL search across multiple words?

推荐答案

你可以使用字符串函数，例如:

You could use string functions, such as:

select id, name
from subjects
where name like concat('%', @search, '%')
order by 
  name like concat(@search, '%') desc,
  ifnull(nullif(instr(name, concat(' ', @search)), 0), 99999),
  ifnull(nullif(instr(name, @search), 0), 99999),
  name;

这会为您提供包含@search 的所有条目.首先是开头的那些，然后是空格之后的那些，然后是出现的位置，然后是字母.

This gets you all entries containing @search. First those that have it at the beginning, then those that have it after a blank, then by the position of the occurrence, then alphabetical.

name like concat(@search, '%') desc 顺便用了 MySQL 的布尔逻辑.1 = 真，0 = 假，所以按降序排列会首先得到真.

name like concat(@search, '%') desc uses MySQL's boolean logic by the way. 1 = true, 0 = false, so ordering this descending gives you true first.

SQL 小提琴:http://sqlfiddle.com/#!9/c6321a/1