如何操作 MySQL 全文搜索相关性以使一个字段比另一个字段更“有价值"? [英] How can I manipulate MySQL fulltext search relevance to make one field more 'valuable' than another?

问题描述

假设我有两列，关键字和内容.我在两者之间都有全文索引.我希望关键字中包含 foo 的行比内容中包含 foo 的行具有更高的相关性.我需要做什么才能使 MySQL 将关键字中的匹配项加权高于内容中的匹配项?

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.

解决方案:

能够以下列方式完成这项工作:

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 

推荐答案

实际上，使用 case 语句来制作一对标志可能是更好的解决方案:

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 全文搜索相关性以使一个字段比另一个字段更“有价值"?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！