MySQL-如何获得具有准确相关性的搜索结果 [英] MySQL - How to get search results with accurate relevance
问题描述
我已经重新研究了这个问题很多次，但我从未真正找到合适的答案.
I have revisited this problem many times, and I have never really found a proper answer.
是否可以执行MySQL搜索，以按相关性返回ACTUAL准确排序的结果?
我试图创建一个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:
查询(生物...")-喜欢
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:
- 以搜索词开头的第一个单词
- 以搜索词开头的第二个单词
- 单词不在单词开头的单词
- 所有与字母无关的字母
所以我的问题是，如何通过多个单词的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 = true，0 = false，因此按降序排列将使您先获得true.
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
这篇关于MySQL-如何获得具有准确相关性的搜索结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
