php 如何按相关性对MYSQL全文搜索结果进行排序
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1016804/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to sort MYSQL fulltext search results by relevancy
提问by user125591
I am relatively new to MYSQL and have had an issue that has been bugging me for a while. I've tried googling all over the place for the answer, but have unable to find an acceptable solution as of yet.
我对 MYSQL 比较陌生，并且遇到了一个困扰我一段时间的问题。我试过在谷歌上到处搜索答案，但到目前为止还没有找到可接受的解决方案。
Here is the query I am running currently to find the best possible match for a given search term:
这是我目前正在运行的查询，用于查找给定搜索词的最佳匹配项：
$query="SELECT * from `vocabulary` WHERE translation = 'word' OR translation LIKE '%word%'";
The results it returns are comprehensive in that they include all relevant rows. However, they are not sorted in any particular order, and I would like to have the ones with an exact match displayed first when I print results in PHP. Like this:
它返回的结果是全面的，因为它们包括所有相关的行。但是，它们没有按任何特定顺序排序，当我在 PHP 中打印结果时，我希望首先显示完全匹配的那些。像这样：
1 | word <-exact match
2 | crossword <- partial matches sorted alphabetically /
3 | words
4 | wordsmith
1 | word <-完全匹配
2 | 填字游戏 <- 按字母顺序排序的部分匹配 /
3 | 词
4 | 文字匠
Thank you very much in advance for your assistance.
非常感谢您的帮助。
-macspacejunkie
-macspacejunkie
采纳答案by SO User
SELECT * from vocabulary
WHERE translation like 'word'
union all
SELECT * from vocabulary
WHERE translation LIKE '%word%' and translation not like 'word'
will list exact matches first
将首先列出完全匹配
回答by instanceof me
LIKE is not fulltext search. In Fulltext search, MATCH(...) AGAINST(...)returns a matching score that can be roughly approximated as relevancy.
LIKE 不是全文搜索。在全文搜索中，MATCH(...) AGAINST(...)返回可以粗略近似为相关性的匹配分数。
回答by Rich Adams
You can get a good relevance search by creating a fulltext index and then matching against your search term.
您可以通过创建全文索引然后匹配您的搜索词来获得良好的相关性搜索。
So something like this should work.
所以这样的事情应该有效。
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
More information this can be found in the MySQL Reference Manual.
更多信息可以在MySQL 参考手册中找到。
回答by chrismacp
I have been looking at the same problem and not quite found the perfect answer for my situation yet, but this might be useful for you. I'm pretty new to full text searching also so any experts help me out too.
我一直在研究同样的问题，但还没有找到适合我情况的完美答案，但这可能对您有用。我对全文搜索也很陌生，所以任何专家也可以帮助我。
I do two MATCH() AGAINST() statements in the select and combine the score from each to form the total relevancy. Assigning different multipliers allows me to configure the importance of each set of results.
我在 select 中做了两个 MATCH() AGAINST() 语句，并将每个语句的分数组合起来形成总相关性。分配不同的乘数允许我配置每组结果的重要性。
My first MATCH() would check against the literal (or exact) search term using double quotes. My second MATCH would check normally. I apply a higher multiplier to the first match so it should have a higher relevancy value if found.
我的第一个 MATCH() 将使用双引号检查字面（或精确）搜索词。我的第二场比赛会正常检查。我将更高的乘数应用于第一个匹配项，因此如果找到它应该具有更高的相关性值。
Something like this.
像这样的东西。
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
If you use EXPLAIN to examine how the query works you should find that the extra MATCH() AGAINST() clauses don't actually add any overhead to the query due to the way MySQL works.
如果您使用 EXPLAIN 来检查查询的工作方式，您应该会发现由于 MySQL 的工作方式，额外的 MATCH() AGAINST() 子句实际上并没有给查询增加任何开销。
回答by Phistrom
Your query needs only a little modification to get the order you're looking for.
您的查询只需稍加修改即可获得您要查找的订单。
SELECT *
FROM vocabulary
WHERE translation LIKE '%word%'
ORDER BY translation <> 'word', translation;
If translationis exactly 'word', it will be at the top of the results. This is because translation <> 'word'will be 0when there is an exact matchwhich comes before the 1that will be returned for all the other results. The remaining results will be sorted alphabetically after that because of the , translation.
如果translation正好是'word'，它将位于结果的顶部。这是因为当有一个精确匹配出现在所有其他结果将返回的1之前时，translation <> 'word'它将是0。由于., translation
This query avoids making two queries like the selected answer does with its UNION. Additionally, your query does not need translation = 'word' OR translation LIKE '%word%'since the second half will always be executed and is a superset of the first part.
此查询避免了像所选答案对其UNION. 此外，您的查询不需要，translation = 'word' OR translation LIKE '%word%'因为后半部分将始终执行并且是第一部分的超集。
For those looking for an answer that uses an actualfulltext search, please see the other, more highly upvoted answers.
对于那些寻找使用实际全文搜索的答案的人，请参阅其他更受好评的答案。
