mysql fulltext 排序,如何根據相關性對MYSQL全文搜索結果進行排序

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

2 | crossword

3 | words

4 | wordsmith

1 |字

Thank you very much in advance for your assistance.

非常感謝您的幫助。

-macspacejunkie

-macspacejunkie

5 个解决方案

#1

15

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

會先列出精確的匹配嗎

#2

29

LIKE is not fulltext search. In Fulltext search, MATCH(...) AGAINST(...) returns a matching score that can be roughly approximated as relevancy.

LIKE不是全文搜索。在全文搜索、匹配(…)AGAINST(…)返回一個匹配的分數，這個分數可以粗略地近似為相關度。

#3

22

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參考手冊中找到。

#4

5

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 importnace of each set of results.

我在select中對()語句執行兩個MATCH()語句，並將每個語句的分數合並成總相關度。分配不同的乘數可以讓我配置每一組結果的重要性。

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 run use the EXPLAIN function to show how the query works you should find that the extra MATCH() AGAINST() clauses dont actually add any overhead to the query due to the way MySQL works.

如果您運行EXPLAIN函數來顯示查詢是如何工作的，那么您應該會發現，由於MySQL的工作方式，額外的MATCH() AGAINST()子句實際上不會給查詢增加任何開銷。

#5

2

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 translation is exactly 'word', it will be at the top of the results. This is because translation <> 'word' will be 0 when there is an exact match which comes before the 1 that will be returned for all the other results. The remaining results will be sorted alphabetically after that because of the , translation.

如果翻譯確實是“word”，那么它將是結果的頂部。這是因為翻譯<> 'word'將是0，當有一個確切的匹配出現在所有其他結果返回的1之前時。由於翻譯的原因，剩下的結果將按字母順序排序。

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.

該查詢避免像所選答案那樣對其聯合執行兩個查詢。此外，查詢不需要translation = 'word'或'%word%'之類的翻譯，因為后半部分將始終執行，並且是第一部分的超集。

For those looking for an answer that uses an actual fulltext search, please see the other, more highly upvoted answers.

如果你想要尋找一個真正的全文搜索的答案，請查看另一個高度向上的答案。