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.
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
