如何按相关性排序MYSQL全文搜索结果

Question

我对MYSQL相对较新，有一个问题一直困扰着我。我已经尝试在各个地方搜索答案，但是目前还没有找到可接受的解决方案。

这是我目前正在运行的查询，以查找给定搜索词的最佳匹配：

$query="SELECT * from `vocabulary` WHERE translation = 'word' OR translation LIKE '%word%'";

它返回的结果是全面的，因为它们包括所有相关行。然而，它们没有按任何特定顺序排序，当我在PHP中打印结果时，我希望首先显示具有精确匹配的那些。就像这样：

---

1 | 单词 <- 精确匹配
2 | 纵横填字游戏 <- 按字母顺序排序的部分匹配 /
3 | 单词
4 | 词人

---

非常感谢您的帮助。

-macspacejunkie

- user125591

---

更新：感谢大家的帮助！正是我所需要的。祝好， -macspacejunkie - user125591

如果有人帮助了你，请检查他们的答案。 - Jason

Answer 1

LIKE不是全文搜索。在全文搜索中，MATCH(...) AGAINST(...)返回一个匹配分数，可以粗略地近似为相关性。

- instanceof me

Answer 2

通过创建全文索引，然后与您的搜索词匹配，您可以获得良好的相关性搜索。

因此，像这样的东西应该可以工作。

ALTER TABLE `vocabulary` ADD FULLTEXT INDEX `SEARCH`(`translation`);

SELECT *, MATCH(translation) AGAINST ('+word' IN BOOLEAN MODE) AS relevance 
FROM `vocabulary`
WHERE MATCH(translation) AGAINST ('+word' IN BOOLEAN MODE)
ORDER BY relevance DESC

更多信息可以在MySQL参考手册中找到。

- Rich Adams

---

当在WHERE子句中使用MATCH命令时，MySQL会自动按最高到最低的相关性对行进行排序。 - ejunker

感谢Rich Adams和ejunker提供的帮助。两位都提出了非常好的观点。我的客户一直在催促我提高搜索相关性，这对我来说是一个很大的帮助。 - Volomike

@ejunker 那太好了。你能给我指出一个相关的参考吗？这个也适用于 BOOLEAN MODE 吗？谢谢。 - Havok

@RichAdams 这个查询是否会返回像 words、wordsmith 等 OP 所要求的结果？我认为你需要使用 word*。 - HopeKing

Answer 3

SELECT * from vocabulary 
WHERE translation like 'word'  
union all
SELECT * from vocabulary 
WHERE translation LIKE '%word%' and translation not like 'word'  

将首先列出精确匹配项

- Rashmi Pandit

---

这是在大表中处理事情的非常糟糕的方式。考虑使用“FULLTEXT”搜索以获得更快的查询。 - OverCoder

Answer 4

我一直在研究同一个问题，但还没有找到适合我的完美答案，但这可能对你有用。我也是全文搜索的新手，所以希望专家能帮助我。

我在选择中执行了两个MATCH() AGAINST()语句，并将每个语句的得分组合起来形成总相关度。分配不同的乘数可以配置每组结果的重要性。

我的第一个MATCH()会使用双引号检查字面（或精确）搜索词。我的第二个MATCH()则会正常检查。我对第一个匹配应用了更高的乘数，因此如果找到，它应该具有更高的相关度值。

类似于以下内容：

SELECT *, ((MATCH(indexes) AGAINST ('"search_terms"' IN BOOLEAN MODE) * 10)  
           + (MATCH(indexes) AGAINST ('search_terms' IN BOOLEAN MODE) * 1.5)) AS relevance  
FROM ...
WHERE ...  
      AND (MATCH (indexes) AGAINST ('"search_terms"' IN BOOLEAN MODE) > 0  
           OR MATCH (indexes) AGAINST ('search_terms' IN BOOLEAN MODE) > 0)  
      ...
ORDER BY relevance DESC

如果您使用EXPLAIN来检查查询的工作方式，您应该会发现由于MySQL的工作方式，额外的MATCH() AGAINST()子句实际上不会给查询增加任何开销。

- chrismacp

Answer 5

您的查询只需要进行一点修改就可以得到您想要的顺序。

SELECT * 
FROM vocabulary
WHERE translation LIKE '%word%'
ORDER BY translation <> 'word', translation;

如果translation恰好是'word'，它将位于结果的顶部。这是因为当存在一个完全匹配时，translation <> 'word'将为0，而其他结果将返回1。剩下的结果将按字母顺序排序，因为有, translation。

这个查询避免了像所选答案那样进行两次查询的情况。此外，您的查询不需要translation = 'word' OR translation LIKE '%word%'，因为后半部分总是会执行，并且是第一部分的超集。

对于那些寻找使用实际全文搜索的答案的人，请参阅其他得到更高投票的答案。

- Phistrom