MySQL-如何获得准确相关的搜索结果

Question

我已经多次重温了这个问题，但我从未真正找到合适的答案。

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

我试图创建一个ajax搜索表单，当用户在输入字段中键入内容时提出建议，并且仅使用纯MySQL查询还没有找到合适的解决方案。我知道有可用的搜索服务器，例如ElasticSearch，我想知道如何仅使用原始MySQL查询来实现。

我有一张学校科目表。少于1200行，这将永远不会改变。让我们执行一个基本的FULLTEXT搜索，其中用户开始键入“ 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查询的比较，该查询显示了所有未显示的更相关的结果：

查询（“生物…”）-喜欢

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

而且您已经看到没有建议多少主题，即使这些主题更可能是用户要寻找的主题。

LIKE但是，使用的问题是如何像在跨字词中那样在多个字词之间进行搜索FULLTEXT。

我想要实现的基本排序如下：

1. 以搜索词开头的第一个单词
2. 以搜索词开头的第二个单词
3. 单词不在单词开头的单词
4. 一切都按字母顺序排列，如果没有进一步相关的话

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

Answer 1

您可以使用字符串函数，例如：

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的条目。首先是那些在开始处具有它的对象，然后是那些在空白处具有它的对象，然后是出现位置，然后是字母顺序。

name like concat(@search, '%') desc顺便说一下，使用MySQL的布尔逻辑。1 = true，0 =
false，因此按降序排列将使您先获得true。

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