MySQL全文布尔搜索与标签

Question

我以前没有从MYSQL进行搜索，但我需要实现搜索。我有三个表：articles、articles_tags和tags。

表articles保存了我想要搜索的第一件事情，即title字段。

表articles_tags是一个关联articles和tags的中间表。 articles_tags有两个字段：articles_id和tag_id。

而表tags保存了我想要搜索的第二件事情，即name字段。

我的问题是，我需要一种方法来搜索title字段以及与该文章相关的每个标签（tags.name），并返回特定文章的相关性（或按相关性排序）。

有什么好的方法来实现这个？我相信这不能只通过一个查询来完成，因此可以使用两个查询，然后将相关性混合在一起。

谢谢。

编辑：忘记说，如果我可以给匹配标签比匹配标题中的单词更多的权重，那就太棒了。我不是真的要求任何人写这个东西，只是给我一些方向。我在PHP和MySQL方面都有点新手。

- james

Answer 1

这个时候，建议你考虑将搜索任务转移到专门为此而设计的工具上。我们的产品使用MySQL存储数据，但是通过Lucene（通过Solr实现）索引所有数据。它值得一看，因为设置相对简单，功能非常强大，而且比试图让数据库按照你的要求操作更容易。抱歉这不是直接回答问题的答案，我只是觉得在这种情况下提及这种事情总是值得一提 :)

- metdos

Answer 2

从@james.c.funk给出的答案开始，但做了一些更改。

SELECT a.id, a.title, 
  MATCH (a.title) AGAINST (?) AS relevance
FROM articles AS a
LEFT OUTER JOIN (articles_tags AS at
  JOIN tags AS t ON (t.id = at.tag_id AND t.name = ?))
  ON (a.id = at.article_id)
WHERE MATCH (a.title) AGAINST (? IN BOOLEAN MODE) 
ORDER BY IF(t.name IS NOT NULL, 1.0, relevance) DESC;

我假设你想要标签匹配与整个字符串匹配，而不是使用全文搜索。

同时只使用一个左连接而不是两个，因为如果满足到articles_tags的连接，则一定存在标签。将标签名称比较放在连接条件中，而不是在WHERE子句中。

布尔模式使得MATCH()在匹配时返回1.0，这使其作为相关性度量无用。因此，在选择列表中进行额外的比较以计算相关性。该值介于0.0和1.0之间。现在我们可以通过将其视为具有1.0相关性来提高标签匹配的排序。

- Bill Karwin

---

嗨，比尔。我在几个地方读到使用FULLTEXT连接是不好的，因为它会强制MySQL运行全表扫描并且失去宝贵的性能。我现在要对此进行测试，看看我所读的是否属实。 - TheCarver

@PaparazzoKid，这取决于首先访问哪个表。MySQL使用嵌套循环算法进行连接，因此如果您使用FULLTEXT来限制第一个表中匹配的行数，然后使用它来查找加入的表中的行，那么就不应该有问题。但是，如果您首先扫描另一个表，然后在连接条件中使用FULLTEXT，甚至更糟的是，在FULLTEXT搜索中使用第一个表的列作为模式进行搜索（我不知道这是否可能），那么它将是昂贵的。您可能需要使用STRAIGHT_JOIN。 - Bill Karwin

Answer 3

以下是我过去的做法。看起来很慢，但我认为你会发现它并不慢。

我增加了一些复杂性，以展示其他容易完成的任务。在这个例子中，文章将获得1分部分标题匹配，2分部分标签匹配，3分完全标签匹配和4分完全标题匹配。然后将它们相加并按分数排序。

SELECT
  a.*,
  SUM(
    CASE WHEN a.title LIKE '%keyword%' THEN 1 ELSE 0 END
    +
    CASE WHEN t.name LIKE '%keyword%' THEN 2 ELSE 0 END
    +
    CASE WHEN t.name = 'keyword' THEN 3 ELSE 0 END
    +
    CASE WHEN a.title = 'keyword' THEN 4 ELSE END
  ) AS score
FROM article a, articles_tags at, tags t
WHERE a.id = at.article_id
AND at.tag_id=t.id
AND (a.title LIKE '%keyword%' OR t.name LIKE '%keyword%')
GROUP BY a.id
ORDER BY score;

注意：这不会返回没有标签的文章。我使用简单的连接来减少查询中的噪音，并突出显示得分的内容。要包括没有标签的文章，只需将连接设置为左连接。

- Jackson Miller

Answer 4

你可能想要了解一下Sphinx，http://www.sphinxsearch.com/。

- jhogendorn

Answer 5

这个快速演示查询远非最优化，但应该是一个很好的起点

SELECT * FROM
(SELECT a.id, a.title, 
  MATCH (a.title) AGAINST ('$s_search_term') AS title_score,
  SUM(MATCH (t.name) AGAINST ('$s_search_term')
) AS tag_score
FROM articles AS a
LEFT JOIN articles_tags AS at
  ON a.id = at.article_id
LEFT JOIN tags AS t
  ON t.id = at.tag_id
WHERE MATCH (a.title) AGAINST ('$s_search_term') 
  OR MATCH (t.name) AGAINST ('$s_search_term')
GROUP BY a.id) AS table1
ORDER BY 2*tag_score + title_score DESC

您可能希望通过将 tag_score 除以 COUNT(t.id) 来对其进行标准化。很抱歉，但是提供查询比解释如何制作更容易。

- james.c.funk

Answer 6

很有趣，我在两天内看到了关于同样问题的第三个问题，可以查看这两篇文章：1，2

- dimus

---

我看了那两个，但没看出它们如何与我的问题相关。 - james

更有趣的是，这实际上是一条评论，而不是答案。 - TheCarver