MySQL可选的LEFT JOIN与MATCH

Question

我有以下查询，它在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可选的，如果没有匹配的Ids，它不会返回任何内容。有什么办法可以解决这个问题吗？

- neildt

Answer 1

我找到了一种解决方法，似乎表现良好并给出我想要的结果；

    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

- neildt

---

既然您已经使用了 HAVING (Relevance1+Relevance2) > 0，那么您是否可以简单地省略 WHERE MATCH ... OR MATCH ... 呢？结果不是一样的吗？ - Ivo Renkema

你可以在select语句中将匹配得分合并，如下：(MATCH (tb1.comment) AGAINST (+'search term' IN BOOLEAN MODE) + MATCH (tb2.comment) AGAINST (+'search term' IN BOOLEAN MODE)) AS Relevance - jim smith

Answer 2

如果多个列不在同一FULLTEXT索引中，就无法针对这些列进行匹配。根据http://dev.mysql.com/doc/refman/5.6/en/fulltext-restrictions.html的说明：

“MATCH()列列表必须与表中某些FULLTEXT索引定义中的列列表完全匹配，除非此MATCH()在MyISAM表上是以BOOLEAN MODE进行的。对于MyISAM表，可以在非索引列上进行布尔模式搜索，但这可能会很慢。”

在您的特定情况下，您没有由恰好（tb1.comment，tb2.comment）组成的索引 - 也不能有 - 因此匹配永远无法成功。

要使其正常工作，请创建第三个表与包含这两个“comment”字段的表相关联，并对两个列进行索引，并在适当的“JOIN”中针对该表执行匹配。

- Jeremy Smyth

---

那么，通过创建第三个表，我需要将评论数据复制到该表中，这样tb1和tb2的评论数据也将出现在tb3中？ - neildt

是的，除非您通过将注释放入第三个表中并从前两个表中删除它们来进行规范化。您必须更改应用程序以执行此操作。请查看sakila示例数据库中film和film_text表之间的关系。 film_text包含每部电影的描述，并用于执行FULLTEXT搜索。 - Jeremy Smyth

还是可以针对每个表创建两个全文搜索，然后将结果合并在一起进行UNION操作吗？ - neildt

你可以在WHERE子句中使用两个MATCH()AGAINST，并用OR连接起来，但这样会失去单个FULLTEXT索引在两个列上的优先排序和多个条件（例如，你不能让一个列具有+val，另一个列具有-val，因为OR将忽略负结果）。 - Jeremy Smyth