Mysql like 和 BOOLEAN MODE (FULLTEXT) 搜索

Question

我正在尝试编写查询以使用通配符搜索记录。

我在下面有两个有效的查询，但我想知道哪个更优化。

查询一确实给了我我要找的东西，但查询二给了我不同的结果。

我应该使用哪个。

1. 在我的查询中使用 Like。

   SELECT code, name
   FROM countryCounty
   WHERE name LIKE  '%County Down%'
   AND isActive =1
   AND countryISO2FK =  'GB'
   LIMIT 1
   

2. 然后我有 bool 模式 (FULLTEXT) 查询。

   SELECT code,name, match( name )
   AGAINST ( 'County Down' IN BOOLEAN MODE ) AS relevance
   FROM opjb_countryCounty 
   WHERE match( name ) AGAINST ( '%County Down%' IN BOOLEAN MODE ) 
       AND isActive=1 
       AND countryISO2FK='GB' 
   ORDER BY relevance DESC LIMIT 1
   

Answer 1

这两个查询之间存在显着差异。

第一个查询是在名称列中搜索出现的单个字符串 'County Down'。

第二个查询是搜索文本中出现的两个单独的单词(单独的字符串)'County' 和 'Down'。 (我不知道第二个查询中 'County' 之前的 '%' 字符的目的和效果。)

bool 模式全文搜索的相关性将为 1.0。如果您只想返回同时包含“County”和“Down”这两个词的那些行，那么您确实需要在每个词前使用 '+' 限定符，例如:

MATCH(name) AGAINST('+County +Down' IN BOOLEAN MODE)

请注意，此谓词还将“匹配”包含 'Some Down and out County' 的名称，例如，第一个查询不会匹配。

---

此外，用于获取按相关性排序的结果集的方法几乎是正确的。有一个微妙的问题:包含 IN BOOLEAN MODE 修饰符会导致表达式返回 1.0，而不是像 NATURAL LANGUAGE MODE 那样返回加权 float 。

---

回答您的问题:如果第一个查询返回您需要的结果集，则使用该查询。该查询的缺点是该查询中的 LIKE 谓词不可搜索，即 MySQL 无法使用索引范围扫描来满足该谓词。 (索引可用于其他谓词，但需要检查每一行上的 name 列。

第二种形式的查询的优点是它可以使用 FULLTEXT INDEX，如果创建了 FULLTEXT INDEX，则可以提高性能。