How to sort MYSQL fulltext search results by relevancy
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.
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:
1 | word <-exact match
2 | crossword <- partial matches sorted alphabetically /
3 | words
4 | wordsmith
Thank you very much in advance for your assistance.
-macspacejunkie
SELECT * from vocabulary
WHERE translation like 'word'
union all
SELECT * from vocabulary
WHERE translation LIKE '%word%' and translation not like 'word'
将首先列出完全匹配
LIKE is not fulltext search. In Fulltext search, MATCH(...) AGAINST(...) returns a matching score that can be roughly approximated as relevancy.
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.
链接地址: http://www.djcxy.com/p/75312.html