MySQL带标签的全文布尔搜索

从@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,使其排序更高。

在这个时候,建议你考虑将搜索工作转移到真正为此目的编写的东西上,值得吗?

值得一看,因为它的设置相对简单,功能强大,而且比试图操纵数据库做你想做的事情容易得多。

以下是我过去是如何做到这一点的。它看起来很慢,但我想你会发现它不是。

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;

你可能想看看斯芬克斯, http://www.sphinxsearch.com/

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)来对其进行规范化。很抱歉,给出查询比解释如何进行查询更容易。

有趣的是,这是我在两天内看到的关于几乎相同问题的第三个问题,看看这两篇帖子: 1 2