php - mysql bool 搜索加权列

Question

我在 mysql 中有三个启用了全文搜索的表:

标题(title，url)，标题(headings，url)，内容(content，url)。

我的问题是:我希望 url 列比标题更重要，标题列比标题更重要，标题比内容更重要。我可以在需要每个术语的情况下使用 bool 搜索来执行此操作吗，如下所示:

SELECT * FROM title WHERE MATCH (title)
AGAINST('+search' IN BOOLEAN MODE)

Answer 1

MATCH ...AGAINST 返回一个数字，MySQL 会记住 where 子句的结果，因此如果您在 1 个表上有 3 个单独的 FT 索引，您可以执行以下操作:

SELECT * FROM tablename WHERE 
  MATCH (title)    AGAINST('search')
  OR MATCH (headings) AGAINST('search')
  OR MATCH (content) AGAINST('search')
ORDER BY 
  (MATCH (title) AGAINST('search') * 3) + 
  (MATCH (headings) AGAINST('search') * 2) +
  MATCH (content) AGAINST('content') DESC

或者任何可能需要的权重。

或者，在 bool 模式下，同一张表，您可以使用:

SELECT * FROM tablename WHERE 
  MATCH (title)    AGAINST('>>>search'   IN BOOLEAN MODE)
  OR MATCH (headings) AGAINST('>>search' IN BOOLEAN MODE )
  OR MATCH (content) AGAINST('>search'   IN BOOLEAN MODE)
ORDER BY 
  MATCH (title) AGAINST('>>>search'   IN BOOLEAN MODE) + 
  MATCH (headings) AGAINST('>>search' IN BOOLEAN MODE) +
  MATCH (content) AGAINST('>search'   IN BOOLEAN MODE)

对于需要搜索词的搜索，同样的技巧:

 MATCH (title) AGAINST('+>>>search'   IN BOOLEAN MODE)

如果您有 3 个单独的表，则必须将权重存储在某个地方，或者在某些 JOIN 中，或者通过单独的查询(无论您现在如何做):

SELECT *, MATCH (title) AGAINST('+>>>search'   IN BOOLEAN MODE) as weight
FROM title
WHERE     MATCH (title) AGAINST('+>>>search'   IN BOOLEAN MODE)

SELECT *, MATCH (headings) AGAINST('+>>search'   IN BOOLEAN MODE) as weight
FROM headings
WHERE     MATCH (headings) AGAINST('+>>search'   IN BOOLEAN MODE)

SELECT *, MATCH (content) AGAINST('+search'   IN BOOLEAN MODE) as weight
FROM content
WHERE     MATCH (content) AGAINST('+search'   IN BOOLEAN MODE)