MySQL基础之全文本搜索

理解全文搜索

并非所有引擎都支持全文本搜索

MySQL支持几种基本的数据库引擎。并非所有的引擎都支持全文本搜索。两个最常使用的引擎为MYISAM和InnoDB, 前者支持全文本搜索, 而后者不支持。

LIKE关键字利用通配符操作符匹配文本(和部分文本)。使用LIKE, 能够查找包含特殊值或部分值的行(不管这些值位于列内的什么位置)

使用正则表达式, 可以编写查找所需行的非常复杂的匹配模式。

虽然这些搜索机制非常有用, 但是有几个重要的限制

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

所有这些限制以及更多的限制都可以用全文本搜索来解决，在使用全文本搜索时，不需要分别查看每个行，不需要分别分析和处理每个词，创建指定列中各词的一个索引，搜索可以针对这些词进行。

使用全文本搜索

为了进行全文本搜索，必须索引被搜索的列，而且要随着数据的改变不断地重新索引。

在索引之后, SELECT可与Match()和Against()一起使用以实际执行搜索

启用全文本搜索支持

一般在创建表时启用全文本搜索。CREATE TABLE语句接受FULLTEXT子句。它给出被索引列的一个逗号分割的列表

MariaDB [crashcourse]>  CREATE TABLE productnotes
    -> (
    -> note_id  int  NOT NULL AUTO_INCREMENT,
    -> prod_id char(10)  NOT NULL,
    -> note_date datetime  NOT NULL,
    -> note_text text  NULL,
    -> PRIMARY KEY(note_id),
    -> FULLTEXT(note_text)
    -> )ENGINE=MyISAM;

在定义之后, MySQL自动维护该索引。在增加、更新或删除行时, 索引随之自动更新

进行全文本搜索

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

MariaDB [crashcourse]> 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. |
+----------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.000 sec)

MariaDB [crashcourse]> 

使用完整的Match说明

传递给Match()的值必须与FULLTEXT()定义的相同。如果指定多个列, 则必须列出它们(而且次序正确)

搜索不区分大小写

除非使用BINARY方式, 否则全文本搜索不区分大小写

使用全文本搜索会返回以文本匹配的良好程序排序的数据。全文本排序的一个重要部分就是对结果排序。具有较高等级的行先返回。

MariaDB [crashcourse]> SELECT note_text, Match(note_text) Against('rabbit') AS rank FROM productnotes;

Match()和Against()用来建立一个计算列(别名rank), 此列包含全文本搜索计算出的等级值。等级由MySQL根据行中词的数目、唯一词的数目、整个索引中词的总数以及包含该词的行的数目计算出来。

使用查询扩展

查询扩展用来设法放宽所返回的全文本搜索结果的范围。在使用查询扩展时, MySQL对数据和索引进行两遍扫描来完成搜索:

• 首先, 进行一个基本的全文本搜索, 找出与搜索条件匹配的所有行;
• 其次, MySQL检查这些匹配行并且选择所有有用的词
• 再其次, MySQL再次进行全文本搜索, 这次不仅使用原来的条件, 而且还使用所有有用的词

MariaDB [crashcourse]> SELECT note_text FROM productnotes WHERE Match(note_text) Against('anvils' WITH QUERY EXPANSION);

查询扩展极大的增加了返回的行数, 但这样做也增加了你实际上并不想要的行的数目

布尔文本搜索

MySQL支持全文本搜索的另外一种形式, 称为布尔方式, 以布尔方式, 可以提供关于如下内容的细节:

• 要匹配的行
• 要排斥的词
• 排列提示
• 表达式分组
• 另外一些内容

布尔方式即使没有定义FULLTEXT索引。也可以使用它, 但这是一种非常缓慢的操作(其性能随着数据量的增加而降低)

MariaDB [crashcourse]> SELECT note_text FROM productnotes WHERE Match(note_text) Against('heavy' IN BOOLEAN MODE);

为了匹配包含heavy但不包含以rope开始的词的行, 可使用如下查询

MariaDB [crashcourse]> SELECT note_text FROM productnotes WHERE Match(note_text) Against('heavy -rope*' IN BOOLEAN MODE);

两个全文本搜索布尔操作符-和*, -排除一个词, 而*是截断操作符。

全文本搜索的使用说明

• 在索引全文本数据时，短词被忽略且从索引中排除，短词定义为那些具有3个或3个以下字符的词(如果需要，这个数目可以更改)
• MySQL带有一个内建的非用词(stopword)列表，这些词在索引全文本数据时总是被忽略(可以覆盖这个列表)
• 许多词出现的频率很高，搜索它们没有用处，MySQL规定了一条50%规则：如果一个词出现在50%以上的行中，则将它作为一个非用词忽略，50%规则不用于IN BOOLEAN MODE
• 如果表中的行数少于3行，则全文本搜索不返回结果
• 忽略词中的单引号，例如：don’t索引为dont
• 不具有词分隔符(包括日语和汉语)的语言不能恰当地返回全文本搜索结果