MySQL必知必会十八：全文本搜索神薯片-

18.1 理解全文本搜索

并非所有引擎都支持全文本搜索 MySQL支持几种基本的数据库引擎。并非所有的引擎都支持本书所描述的全文本搜索。两个最常使用的引擎为MyISAM和InnoDB，前者支持全文本搜索，而后者不支持。这就是为什么虽然本书中 创 建 的 多 数 样 例 表 使 用 InnoDB ， 而 有 一 个 样 例 表（ productnotes表）却使用MyISAM的原因。如果你的应用中需要全文本搜索功能，应该记住这一点。

LIKE关键字和正则表达式虽然这些搜索机制非常有用，但存在几个重要的限制。

• 性能——通配符和正则表达式匹配通常要求MySQL尝试匹配表中所有行（而且这些搜索极少使用表索引）。因此，由于被搜索行数不断增加，这些搜索可能非常耗时。
• 明确控制——使用通配符和正则表达式匹配，很难（而且并不总是能）明确地控制匹配什么和不匹配什么。例如，指定一个词必须匹配，一个词必须不匹配，而一个词仅在第一个词确实匹配的情况下才可以匹配或者才可以不匹配。
• 智能化的结果——虽然基于通配符和正则表达式的搜索提供了非常灵活的搜索，但它们都不能提供一种智能化的选择结果的方法。例如，一个特殊词的搜索将会返回包含该词的所有行，而不区分包含单个匹配的行和包含多个匹配的行（按照可能是更好的匹配来排列它们）。类似，一个特殊词的搜索将不会找出不包含该词但包含其他相关词的行。

MySQL创建指定列中各词的一个索引，搜索可以针对这些词进行。这样， MySQL可以快速有效地决定哪些词匹配（哪些行包含它们），哪些词不匹配，它们匹配的频率，等等。

18.2 使用全文本搜索

18.2.1 启用全文本搜索支持

一般在创建表时启用全文本搜索。 CREATE TABLE语句接受FULLTEXT子句，它给出被索引列的一个逗号分隔的列表。
下面的CREATE语句演示了FULLTEXT子句的使用：

• 分析：CREATE TABLE语句定义表productnotes并列出它所包含的列即可。这些列中有一个名为note_text的列，为了进行全文本搜索，MySQL根据子句FULLTEXT(note_text)的指示对它进行索引。这里的FULLTEXT索引单个列，如果需要也可以指定多个列。

可以在创建表时指定FULLTEXT，或者在稍后指定。

不要在导入数据时使用FULLTEXT 更新索引要花时间，虽然不是很多，但毕竟要花时间。如果正在导入数据到一个新表，此时不应该启用FULLTEXT索引。应该首先导入所有数据，然后再修改表， 定义FULLTEXT。 这样有助于更快地导入数据

18.2.2 进行全文本搜索

在索引之后，使用两个函数Match()和Against()执行全文本搜索，其中Match()指定被搜索的列， Against()指定要使用的搜索表达式。

mysql> SELECT note_text FROM productnotes WHERE Match(note_text) Against('rabbit'); +-----------------------------------------------------------------------------------------------------------------------+ | note_text                                                                                                             | +-----------------------------------------------------------------------------------------------------------------------+ | Customer complaint: rabbit has been able to detect trap, food apparently less effective now. | | Quantity varies, sold by the sack load. All guaranteed to be bright and orange, and suitable for use as rabbit bait. | +-----------------------------------------------------------------------------------------------------------------------+ 

• 分析：此SELECT语句检索单个列note_text。由于WHERE子句，一个全文本搜索被执行。 Match(note_text)指示MySQL针对指定的列进行搜索， Against(‘rabbit’)指定词rabbit作为搜索文本。由于有两行包含词rabbit，这两个行被返回。

使用完整的Match() 说明传递给 Match() 的值必须与FULLTEXT()定义中的相同。如果指定多个列，则必须列出它们（而且次序正确）。
搜索不区分大小写 除非使用BINARY方式（本章中没有介绍），否则全文本搜索不区分大小写。
排序多个搜索项 如果指定多个搜索项，则包含多数匹配词的那些行将具有比包含较少词（或仅有一个匹配）的那些行高的等级值。

mysql> SELECT note_text FROM productnotes WHERE note_text LIKE '%rabbit%'; +-----------------------------------------------------------------------------------------------------------------------+ | note_text                                                                                                             | +-----------------------------------------------------------------------------------------------------------------------+ | Quantity varies, sold by the sack load. All guaranteed to be bright and orange, and suitable for use as rabbit bait. | | Customer complaint: rabbit has been able to detect trap, food apparently less effective now. | +-----------------------------------------------------------------------------------------------------------------------+ 

• 分析：这条SELECT语句同样检索出两行，但次序不同（虽然并不总是
  出现这种情况）。

上述两条SELECT语句都不包含ORDER BY子句。后者（使用LIKE）以不特别有用的顺序返回数据。前者（使用全文本搜索）返回以文本匹配的良好程度排序的数据。两个行都包含词rabbit，但包含词rabbit作为第3个词的行的等级比作为第20个词的行高。这很重要。全文本搜索的一个重要部分就是对结果排序。具有较高等级的行先返回（因为这些行很可能是你真正想要的行）。

18.2.3 使用查询扩展

在使用查询扩展时， MySQL对数据和索引进行两遍扫描来完成搜索：

• 首先，进行一个基本的全文本搜索，找出与搜索条件匹配的所有
  行；
• 其次， MySQL检查这些匹配行并选择所有有用的词（我们将会简
  要地解释MySQL如何断定什么有用，什么无用）。
• 再其次， MySQL再次进行全文本搜索，这次不仅使用原来的条件，
  而且还使用所有有用的词。

mysql> SELECT note_text FROM productnotes WHERE Match(note_text) Against('anvils' WITH QUERY EXPANSION); +----------------------------------------------------------------------------------------------------------------------------------------------------------+ | note_text                                                                                                                                                | +----------------------------------------------------------------------------------------------------------------------------------------------------------+ | Multiple customer returns, anvils failing to drop fast enough or falling backwards on purchaser. Recommend that customer considers using heavier anvils. | | Customer complaint: Sticks not individually wrapped, too easy to mistakenly detonate all at once. Recommend individual wrapping. | | Customer complaint: Not heavy enough to generate flying stars around head of victim. If being purchased for dropping, recommend ANV02 or ANV03 instead. | | Please note that no returns will be accepted if safe opened using explosives. | | Customer complaint: rabbit has been able to detect trap, food apparently less effective now. | | Customer complaint: Circular hole in safe floor can apparently be easily cut with handsaw. | | Matches not included, recommend purchase of matches or detonator (item DTNTR). | +----------------------------------------------------------------------------------------------------------------------------------------------------------+ 

• 分析：这次返回了7行。第一行包含词anvils，因此等级最高。第二行与anvils无关，但因为它包含第一行中的两个词（ customer和recommend），所以也被检索出来。第3行也包含这两个相同的词，但它们在文本中的位置更靠后且分开得更远，因此也包含这一行，但等级为第三。第三行确实也没有涉及anvils（按它们的产品名）。

行越多越好 表中的行越多（这些行中的文本就越多），使用查询扩展返回的结果越好。

18.2.4 布尔文本搜索

MySQL支持全文本搜索的另外一种形式，称为布尔方式（ boolean mode）。

即使没有FULLTEXT索引也可以使用 布尔方式不同于迄今为止使用的全文本搜索语法的地方在于， 即使没有定义FULLTEXT索引，也可以使用它。但这是一种非常缓慢的操作（其性能将随着数据量的增加而降低）。

以布尔方式，可以提供关于如下内容的细节：

• 要匹配的词；
• 要排斥的词（如果某行包含这个词，则不返回该行，即使它包含
  其他指定的词也是如此）；
• 排列提示（指定某些词比其他词更重要，更重要的词等级更高）；
• 表达式分组；
• 另外一些内容。

为了匹配包含heavy但不包含任意以rope开始的词的行， 可使用以下查询：

mysql> SELECT note_text FROM productnotes WHERE Match(note_text) Against('heavy -rope*' IN BOOLEAN MODE); +----------------------------------------------------------------------------------------------------------------------------------------------------------+ | note_text                                                                                                                                                | +----------------------------------------------------------------------------------------------------------------------------------------------------------+ | Customer complaint: Not heavy enough to generate flying stars around head of victim. If being purchased for dropping, recommend ANV02 or ANV03 instead. | +----------------------------------------------------------------------------------------------------------------------------------------------------------+ 

• 分析：这次只返回一行。这一次仍然匹配词heavy，但-rope*明确地指示MySQL排除包含rope*（任何以rope开始的词，包括ropes）的行。

便包含或排除这个短语）

排列而不排序 在布尔方式中，不按等级值降序排序返回的行。

mysql> SELECT note_text FROM productnotes WHERE Match(note_text) Against('+rabbit +bbit' IN BOOLEAN MODE); 

这个搜索匹配包含词rabbit和bait的行。

mysql> SELECT note_text FROM productnotes WHERE Match(note_text) Against('rabbit bbit' IN BOOLEAN MODE); 

• 分析：没有指定操作符，这个搜索匹配包含rabbit和bait中的至少一
  个词的行。

mysql> SELECT note_text FROM productnotes WHERE Match(note_text) Against('"rabbit bbit"' IN BOOLEAN MODE); 

• 分析：这个搜索匹配短语rabbit bait而不是匹配两个词rabbit和bait。

mysql> SELECT note_text FROM productnotes WHERE Match(note_text) Against('>rabbit <carrot' IN BOOLEAN MODE); 

• 分析：匹配rabbit和carrot，增加前者的等级，降低后者的等级。

mysql> SELECT note_text FROM productnotes WHERE Match(note_text) Against('+safe +(<combination)' IN BOOLEAN MODE); 

• 分析：匹配rabbit和carrot，增加前者的等级，降低后者的等级。

18.2.5 全文本搜索的使用说明

• 在索引全文本数据时，短词被忽略且从索引中排除。短词定义为那些具有3个或3个以下字符的词（如果需要，这个数目可以更改）。
• MySQL带有一个内建的非用词（ stopword）列表，这些词在索引全文本据时总是被忽略。如果需要，可以覆盖这个列表（请参阅MySQL文档以了解如何完成此工作）。
• 许多词出现的频率很高，搜索它们没有用处（返回太多的结果）。因此， MySQL规定了一条50%规则，如果一个词出现在50%以上的行中，则将它作为一个非用词忽略。 50%规则不用于IN BOOLEANMODE。
• 如果表中的行数少于3行，则全文本搜索不返回结果（因为每个词或者不出现，或者至少出现在50%的行中）。
• 忽略词中的单引号。例如， don’t索引为dont。
• 不具有词分隔符（包括日语和汉语）的语言不能恰当地返回全文本搜索结果。
• 如前所述，仅在MyISAM数据库引擎中支持全文本搜索。