我有以下查询,该查询针对MySQL Innodb数据库中的同一搜索词对两个不同表中的两列执行全文搜索;
SELECT Id,
MATCH (tb1.comment, tb2.comment) AGAINST (+'search term' IN BOOLEAN MODE) AS Relevance
FROM tbl1
LEFT JOIN tb2 ON tb1.Id = tb2.Id
WHERE MATCH (tb1.comment, tb2.comment) AGAINST (+'search term' IN BOOLEAN MODE)
HAVING Relevance > 0
如果我仅在tb1.comment上执行MATCH,则工作正常,并且我获得了相关的搜索字词,但我想对两列都执行它。
但是,由于LEFT JOIN中的另一个表是可选的,因此如果没有匹配的ID,它不会返回任何内容。关于如何克服这个问题有什么想法吗?
我设法弄清了以下工作,这些工作似乎效果很好,并给出了我想要的结果;
SELECT Id,
MATCH (tb1.comment) AGAINST (+'search term' IN BOOLEAN MODE) AS Relevance1,
MATCH (tb2.comment) AGAINST (+'search term' IN BOOLEAN MODE) AS Relevance2
FROM tbl1
LEFT JOIN tb2 ON tb1.Id = tb2.Id
WHERE (MATCH (tb1.comment) AGAINST (+'search term' IN BOOLEAN MODE)
OR MATCH ( tb2.comment) AGAINST (+'search term' IN BOOLEAN MODE))
HAVING (Relevance1+Relevance2) > 0
ORDER BY (Relevance1+Relevance2) DESC
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句