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

问题描述

我在帖子列表中有两个字段 - post_title 和 post_content 。现在我使用标准的全文搜索来匹配两个字段的一些关键字。我需要给title字段比content字段更重要，并且要按照相关性排序结果......

mysql语法看起来像达到这个目标是什么样的？我使用mysql 5.1首先，创建三个FULLTEXT索引：

<$>

标题栏中的一个
*正文栏中的一个
*标题栏和正文栏中的一个

/ pre>



然后，按以下方式建立您的查询：



  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 
 ORDER BY（rel_title * 2）+（rel_body）
  

这会让标题的关联性比主体高2倍。

当您需要允许内容进行排序，例如，标签（用户不会查看），因为它允许您调整幕后结果。

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.

这篇关于在mysql全文搜索中给一些字段更多的相关性和相关性排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！