MySQL 全文搜索 相关度,在mysql全文搜索中给一些字段更多的相关性和相关性排序...

I have two fields in posts table - post_title and post_content. Now I use standard full text search to match some keywords against both fields. I need to give the title field more relevance than the content field and than order the results by relevance...

What would the mysql syntax look like to achieve this goal? I use mysql 5.1

解决方案

First, create three FULLTEXT indexes:

* one on the title column

* one on the body column

* one on both title and body columns

Then, build your query in the following manner:

SELECT field1, field2, field3, title, body,

MATCH (title) AGAINST ('word_to_search') AS rel_title,

MATCH (body) AGAINST ('word_to_search') AS rel_body

FROM table_to_use

WHERE MATCH (title,body) AGAINST ('word_to_search')

ORDER BY (rel_title*2)+(rel_body)

This will give the title 2 times more relevance than the body.

This is quite handy when you need to allow the content to be sorted, for instance, by tags (which are not viewed by the users) because it allows you to tweak the results from behind the scenes.