MySQL 多行全文搜索

Question

我有一个名为essays 的表，它与一个名为paragraphs 的表具有一对多的关系。因此，paragraphs 表具有指向 essay 的外键。

我当前遇到的问题是，如果我进行搜索，MySQL 将查看每个单独的段落，但不知道多个段落可以指向同一本书。

我的全文搜索如下所示:

SELECT DISTINCT *, 
 MATCH(essays.title) AGAINST('my search') as tscore,
 MATCH(paragraphs.content) AGAINST('my search') as cscore
FROM essays 
INNER JOIN paragraphs ON paragraphs.essay_id = essays.id
WHERE 
 MATCH(essays.title) AGAINST('my search')
 OR MATCH(paragraphs.content) AGAINST('my search')
ORDER BY (3 * tscore + cscore) DESC

有没有办法让 MySQL 在一篇文章的所有段落中搜索某些内容并返回该文章？

Answer 1

这样的事情对你有用吗？

SELECT essays.id, essays.title,
   MATCH(essays.title) AGAINST('my search') as tscore,
   MATCH(paragraphs.content) AGAINST('my search') as cscore
FROM essays 
   INNER JOIN paragraphs ON paragraphs.essay_id = essays.id
GROUP BY essays.id
   HAVING tscore > 0 OR cscore > 0
ORDER BY (3 * tscore + cscore) DESC;

您还可以使用 WHERE 子句代替 HAVING，但您需要:

SELECT essays.id, essays.title,
   MATCH(essays.title) AGAINST('my search') as tscore,
   MATCH(paragraphs.content) AGAINST('my search') as cscore
FROM essays 
   INNER JOIN paragraphs ON paragraphs.essay_id = essays.id
WHERE 
   MATCH(essays.title) AGAINST('my search')
   OR MATCH(paragraphs.content) AGAINST('my search')
GROUP BY essays.id
ORDER BY (3 * tscore + cscore) DESC;

请注意，在使用 GROUP BY 时不应使用 SELECT *，因为可能会出现不需要的聚合问题。