MySQL - 使用部分词汇匹配和相关度得分进行高效搜索（FULLTEXT） [英] MySQL - Efficient search with partial word match and relevancy score (FULLTEXT)

问题描述

  SELECT name，MATCH 
 
如何才能执行MySQL搜索以匹配部分字词，并提供准确的相关性排序？ （姓名）反对（'数学*'在BOOLEAN模式中）相关性
 FROM主体
匹配（姓名）反对（'数学*'在BOOLEAN模式中）
  

布尔模式的问题是相关性总是返回1，所以结果的排序不是很好。例如，如果我在搜索结果上限制了5，那么返回的那些有时似乎并不是最相关的。

如果我在自然语言模式下搜索，我的理解是，相关性分数是有用的，但我无法匹配部分单词。

有没有办法执行满足所有这些条件的查询：

• 可匹配部分字词


• 结果以准确的相关性返回


• 有效率

迄今为止，我所得到的最好结果是：

  SELECT name 
 FROM subjects 
 WHERE name LIKE'mat％'
 UNION ALL 
 SELECT name 
 FROM subjects 
 WHERE name LIKE'％mat％'AND name not LIKE'mat％'
  

但是我宁愿不要使用 LIKE 。

解决方案

在一年后的这个（有点）重复问题上的解决方案：

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

How can I do a MySQL search which will match partial words but also provide accurate relevancy sorting?

SELECT name, MATCH(name) AGAINST ('math*' IN BOOLEAN MODE) AS relevance
FROM subjects
WHERE MATCH(name) AGAINST ('math*' IN BOOLEAN MODE)

The problem with boolean mode is the relevancy always returns 1, so the sorting of results isn't very good. For example, if I put a limit of 5 on the search results the ones returned don't seem to be the most relevant sometimes.

If I search in natural language mode, my understanding is that the relevancy score is useful but I can't match partial words.

Is there a way to perform a query which fulfils all of these criteria:

• Can match partial words
• Results are returned with accurate relevancy
• Is efficient

The best I've got so far is:

SELECT name
FROM subjects
WHERE name LIKE 'mat%'
UNION ALL
SELECT name
FROM subjects
WHERE name LIKE '%mat%' AND name NOT LIKE 'mat%'

But I would prefer not to be using LIKE.

解决方案

I obtained a good solution in this (somewhat) dupliate question a year later:

MySQL - How to get search results with accurate relevance

这篇关于MySQL - 使用部分词汇匹配和相关度得分进行高效搜索（FULLTEXT）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！