MySQL 学习笔记 —— 24、全文本搜索

理解全文本搜索

并非所有的引擎都支持全文本搜索：
MySQL 支持几种基本的数据库引擎；两个最常用的引擎是 MyISAM 和 InnoDB，前者支持全文本搜索，后者不支持

LIKE 关键字，利用通配操作符匹配文本和部分文本，使用 LIKE ，可以查找包含特殊值或者部分值的行（无论这些值位于行内什么位置）

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

但是这些搜索机制还存在几个重要的限制：

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

所有这些限制以及更多的限制，都可以使用全文本搜索来解决

在使用全文本搜索时，MySQL 不需要分别查看每个行，不需要分别分析和处理每个词

MySQL 创建指定列中各词的一个索引，搜索可以针对这些词进行

---

使用全文本搜索

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

在对表列进行适当设计后，MySQL 会自动进行所有的索引和重新索引

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

---

启用全文本搜索支持

一般在创建表时启用全文本搜索

CREATE TABLE 语句（创建和操纵表 中介绍） 接受 FULL TEXT 子句，它给出被索引列的一个逗号分隔的列表

如下示例代码：

CREATE TABLE productnotes2
(
	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;

这个 CREATE TABLE 语句定义表 productnotes2 ，并列出它所包含的列

这些列中有一个名为 note_text 的列，为了进行全文本搜索，MySQL 根据子句 FULLTEXT(note_text) 的指示对它进行索引

FULLTEXT 可以索引单个列，也可以指定多个列

在定义后，MySQL 可以自动维护该索引；在增加、更新或者删除行时，索引随之自动更新

可以在创建表时指定 FULLTEXT ，或者在稍后指定（在这种情况下，所有已有数据必须立即索引）

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

---

进行全文本搜索

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

SELECT note_text FROM productnotes
WHERE Match(note_text) Against('rabbit');

此 SELECT 语句检索单个列 note_text
由于 WHERE 子句，一个全文本搜索被执行

Match(note_text) 指示 MySQL 针对指定的列进行搜索，Against('rabbit') 指定词 rabbit 作为搜索文本
由于有两行包含词 rabbit ，这两行被返回

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

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

下面的代码可以完成和上述代码同样的功能：

SELECT note_text FROM productnotes
WHERE note_text LIKE '%rabbit%';

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

上述两条 SELECT 语句都不包含 ORDER BY 子句
使用 LIKE 以不特别有用的顺序返回数据；使用全文本搜索返回以文本匹配的良好程度排序的数据

两个行都包含词 rabbit ，但是包含词 rabbit 作为第三个词的行的等级比作为第20个词的行高

全文本搜索的一个重要部分就是对结果排序；具有较高等级的行先返回（因为这些行更有可能是想要的行）

示例：

SELECT note_text,
	Match(note_text) Against('rabbit') AS rank
FROM productnotes;

在 SELECT 而不是 WHERE 子句中使用 Match() 和 Against() ，将使得所有行都被返回

Match() 和 Against() 用来建立一个计算列（别名为 rank），此列包含全文本搜索计算出的等级值

等级由MySQL 根据行中词的数目、唯一词的数目、整个索引中词的总数以及包含该词的行的数目计算出来

不包含词 rabbit 的行等级为0；确实包含词 rabbit 的两个行每行都有一个等级值，文本中词靠前的行的等级值比词靠后的行的等级值高

排序多个搜索项：
如果指定多个搜索项，则包含多数匹配词的哪些行将具有比包含较少词（或仅有一个匹配）的那些行高的等级值

---

使用查询扩展

查询扩展用来设法放宽所返回的全文本搜索结果的范围

如果想找出所有提到 anvils 的注释，只有一个注释包含词 anvils，但是还想找出可能与这个搜索有关的所有其他行，即使它们不包含词 anvils

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

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

利用查询扩展，能找出可能相关的结果，即使它们并不精确地包含所有查找的词

没有查询扩展：

SELECT note_text FROM productnotes
WHERE Match(note_text) Against('anvils');

只有一行包含词anvils，因此只返回一行

使用查询扩展：

SELECT note_text FROM productnotes
WHERE Match(note_text) Against('anvils' WITH QUERY EXPANSION);

这次返回了7行。第一行包含词anvils，因此等级最高

第二行与anvils无关，但因为它包含第一行中的两个词（ customer 和 recommend ），所以也被检索出来

第3行也包含这两个相同的词，但它们在文本中的位置更靠后且分开得更远，因此也包含这一行，但等级为第三。第三行确实也没有涉及anvils（按它们的产品名）

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

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

---

布尔文本搜索

MySQL 支持全文本搜索的另一种形式——布尔方式（boolean mode）

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

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

没有 FULLTEXT 也可以使用：
布尔方式不同于迄今为止使用的全文本搜索语法的地方在于，即使没有定义 FULLTEXT 索引，也可以使用
但是其性能将随着数据量的增加而降低

示例1：

SELECT note_text FROM productnotes
WHERE Match(note_text) Against('heavy' IN BOOLEAN MODE);

此全文本搜索检索包含词 heavy 的所有行（有两行）。其中使用了关键字 IN BOOLEAN MODE，但实际上没有指定布尔操作符，因此，其结果与没有指定布尔方式的结果相同

示例2：

SELECT note_text FROM productnotes
WHERE Match(note_text) Against('heavy -rope*' IN BOOLEAN MODE);

这次只返回一行。这一次仍然匹配词 heavy，但 -rope* 明确地指示MySQL排除包含 rope*（任何以 rope开始的词，包括 ropes）的行，这就是为什么上一个例子中的第一行被排除的原因

两个全文本搜索布尔操作符 - 和 *， -排除一个词，而 * 是截断操作符（可想象为用于词尾的一个通配符）

更多全文本布尔操作符，如下所示

code1 :

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

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

code2 :

SELECT note_text FROM productnotes
WHERE Match(note_text) Against('rabbit bait' IN BOOLEAN MODE);

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

code3 :

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

这个搜索匹配短语 rabbit bait 而不是匹配两个词 rabbit 和bait

code4 :

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

匹配 rabbit 和 carrot ，增加前者的等级，降低后者的等级

code5 :

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

搜索匹配词 safe 和 combination，降低后者的等级

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

全文本搜索的使用说明

1. 在索引全文本数据时，短词被忽略且从索引中排除；短词定义为那些具有3个或者3个以下字符的词（如果需要，这个数目可以更改）

2. MySQL 带有一个内建的非用词（stopword）列表，这些词在索引全文本数据时总是被忽略；如果需要，可以覆盖这个列表

3. 许多词出现的频率很高，搜索它们没有用处（返回太多结果）；MySQL 规定一条 50% 规则，如果一个词出现在 50% 以上的行中，则将它作为一个非用词忽略；50%的规则不用于 IN BOOLEAN MODE

4. 如果表中的行数少于三行，则全文本搜索不反悔结果（因为每个词或者不出现，或者至少出现在 50% 的行中）

5. 忽略词中的单引号；例如，don’t 索引为 dont

6. 不具有词分隔符（包括日语和汉语）的语言不能恰当地返回全文本搜索结果

7. 仅在 MyISAM 数据库引擎中支持全文本搜索