MySQL 全文搜索多列 : result confusion

Question

我有一个在数据库上执行全文搜索的搜索查询。

$sql = "SELECT
*
FROM 
`tbl_auction_listing` AS `al` 
JOIN 
`tbl_user` AS `u` ON `al`.`user_id` = `u`.`user_id` 
LEFT JOIN
`tbl_gallery_details` AS `gd` ON `al`.`user_id` = `gd`.`user_id`
LEFT JOIN
`tbl_self_represented_details` AS `sr` ON `u`.`user_id` = `sr`.`user_id`
WHERE 
`al`.`status` = '" . ACTIVE . "'
AND
`al`.`start_date` < NOW() 
AND
`al`.`end_date` > NOW()
AND
MATCH(`al`.`listing_title`,
`al`.`description`,
`al`.`provenance`,
`al`.`title`,
`al`.`artist_full_name`,
`al`.`artist_first_name`,
`al`.`artist_last_name`,
`sr`.`artist_name`,
`gd`.`gallery_name`,
`u`.`username`) AGAINST('$search_query' IN BOOLEAN MODE)";

当我搜索“Cardozo, Horacio”或“cardozo”或“horacio”时，我没有得到任何结果，但我知道数据库中有一位艺术家有 2 条记录，其中 artist_full_name = Cardozo, Horacio。

如果我删除所有 MATCH 字段并且只有 al.artist_full_name 我会得到 2 个结果。如果我添加 al.description 我会得到 1 个结果，因为描述中存在“Horacio Cardozo”。

如果在任何 MATCH 字段中满足任何条件(任何搜索查询词)，是否有办法让搜索返回所有记录？我尝试删除 IN BOOLEAN MODE，但结果相同。

Answer 1

似乎 InnoDB 表不允许在相同的 MATCH() 条件下搜索多个全文索引。

这里你的字段并不都属于同一个表，因此它们被不同的索引覆盖。请注意，如果您有这样的表格，同样的限制适用:

CREATE TABLE t (
  f1 VARCHAR(20),
  f2 VARCHAR(20),
  FULLTEXT(f1), FULLTEXT(f2)
) ENGINE=InnoDB;

SELECT * FROM t
WHERE MATCH(f1, f2) AGAINST ('something in f2'); -- likely to return no row

它看起来全文搜索可能只搜索它遇到的第一个全文索引但这只是我扣除的东西from this experience ，请不要认为这是理所当然的。

底线是您应该拆分搜索以便每个 MATCH() 子句使用一个全文索引:

SELECT * FROM auction, user, gallery, ...
WHERE
    MATCH(auction.field1, auction.field2) AGAINST ('search query' IN BOOLEAN MODE) OR
    MATCH(auction.field3) AGAINST ('search query' IN BOOLEAN MODE) OR
    MATCH(user.field1, user.field2, user.field3) AGAINST...

如果您在 auction 上有两个不同的索引，在 user 上有一个索引，这是一个可能的查询示例。您需要根据您的实际结构对其进行调整(如果您需要更多指导，请发布您的表格描述)。

注意这只适用于 InnoDB 表。有趣的是，MyISAM 表 do not seem to show the same limitation .

---

更新:事实证明这是 a bug in the InnoDB engine , 已在 5.6.13/5.7.2 中修复。上面的例子现在理所当然地失败了，“找不到与列列表匹配的 FULLTEXT 索引”。事实上，(f1, f2) 上没有索引，但是 (f1) 上有一个索引，(f2) 上有一个索引。 As the changelog advises :

Unlike MyISAM, InnoDB does not support boolean full-text searches on nonindexed columns, but this restriction was not enforced, resulting in queries that returned incorrect results.

值得注意的是，虽然此类查询使用 MyISAM 返回正确的结果集，但它们的运行速度比人们预期的要慢，如 they silently ignore existing fulltext indexes .