我如何操纵MySQL全文搜索相关性使一个字段比另一个字段更“有价值”？

问题描述

假设我有两列，关键字和内容。我有一个全文索引。我想要在关键字中使用foo的行与内容中的foo的行关联更多。我需要做什么才能使MySQL对比关键字中的匹配更高的内容进行加权？

我正在使用match against语法。

解决方案：

能够通过以下方式完成此项工作：

  SELECT *，
 CASE当'％watermelon％'等关键字1 else 0 END为关键字匹配时，
当' ％watermelon％'then 1 else 0 END as contentmatch，
 MATCH（标题，关键字，内容）AGAINST（'watermelon'）AS相关性
 FROM about_data 
匹配（标题，关键字，内容）反对（'西瓜'BOOLEAN模式）
具有相关性> 0 
 ORDER by keywordmatch desc，contentmatch desc，relevance desc 
  

解决方案

  select 
 ... 
，如'％'+ @input +'％'等关键字的情况，则1 else 0以keywordmatch结尾
，如'％'+ @input +'％'之类的内容则为1 else 0以contentmatch结尾
  - 或者其他任何检查用于从
 ... 
中匹配的
，以及其余通常的匹配查询
。 .. 
 order by keywordmatch desc，contentmatch desc 
  

同样，只有当所有关键字匹配的排名高于所有仅限内容的匹配。我还假设关键字和内容中的匹配是最高级别。

Suppose I have two columns, keywords and content. I have a fulltext index across both. I want a row with foo in the keywords to have more relevance than a row with foo in the content. What do I need to do to cause MySQL to weight the matches in keywords higher than those in content?

I'm using the "match against" syntax.

SOLUTION:

Was able to make this work in the following manner:

SELECT *, 
CASE when Keywords like '%watermelon%' then 1 else 0 END as keywordmatch, 
CASE when Content like '%watermelon%' then 1 else 0 END as contentmatch,
MATCH (Title, Keywords, Content) AGAINST ('watermelon') AS relevance 
FROM about_data  
WHERE MATCH(Title, Keywords, Content) AGAINST ('watermelon' IN BOOLEAN MODE) 
HAVING relevance > 0  
ORDER by keywordmatch desc, contentmatch desc, relevance desc 

解决方案

Actually, using a case statement to make a pair of flags might be a better solution:

select 
...
, case when keyword like '%' + @input + '%' then 1 else 0 end as keywordmatch
, case when content like '%' + @input + '%' then 1 else 0 end as contentmatch
-- or whatever check you use for the matching
from 
   ... 
   and here the rest of your usual matching query
   ... 
order by keywordmatch desc, contentmatch desc

Again, this is only if all keyword matches rank higher than all the content-only matches. I also made the assumption that a match in both keyword and content is the highest rank.

这篇关于我如何操纵MySQL全文搜索相关性使一个字段比另一个字段更“有价值”？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！