MySQL根据关键词选择文本片段

Question

我有一个查询，根据与搜索词的相关性提取搜索结果:

SELECT *, 
( (1.3 * (MATCH(strTitle) AGAINST ('+john+smith' IN BOOLEAN MODE))) + (0.6 * (MATCH(txtContent) AGAINST ('+john+smith' IN BOOLEAN MODE)))) AS relevance 
FROM content 
WHERE (MATCH(strTitle,txtContent) AGAINST ('+john+smith' IN BOOLEAN MODE) ) 
ORDER BY relevance DESC

我还希望能够从描述中提取搜索匹配的第一个实例。因此，如果搜索“john smith”，我希望结果显示如下:

Page Title 1 ...snippet matching john smith...

Page Title 2 ... snippet john matching....

Page Title 3 .... snippet smith matching john...

等等等等

这怎么可能？

Answer 1

您可以使用案例陈述来做到这一点:

select *,
       ( (1.3 * (MATCH(strTitle) AGAINST ('+john+smith' IN BOOLEAN MODE))) + (0.6 * (MATCH(txtContent) AGAINST ('+john+smith' IN BOOLEAN MODE)))) AS relevance,
      (case when MATCH(strTitle) AGAINST ('+john+smith' IN BOOLEAN MODE)) > 0
            then 'snippet matching +john+smith'
            . . .
       end)

就我个人而言，我会将匹配作为单独的变量放在子查询中:

select
from (select t.*,
             MATCH(strTitle) AGAINST ('+john+smith' IN BOOLEAN MODE)) as match_join_smith,
             . . .
      from t
     ) t

然后将这些变量用于外部查询中的逻辑。