全文本搜索
本章将学习如何使用MySQL的全文本搜索功能进行高级的数据查询和选择。
理解全文本搜索
并非所有引擎都支持全文本搜索 MySQL支持几种基本的数据库引擎。并非所有的引擎都支持全文本搜索。两个最常使用的引擎为 MyISAM 和 InnoDB ,前者支持全文本搜索,而后者不支持。这就是为什么虽然本书中创建的多数样例表使用 InnoDB ,而有一个样例表( productnotes 表)却使用 MyISAM 的原因。如果你的应用中需要全文本搜索功能,应该记住这一点。
前面介绍了 LIKE 关键字,它利用通配操作符匹配文本(和部分文本)。使用 LIKE ,能够查找包含特殊值或部分值的行(不管这些值位于列内什么位置)。
还学习了,用基于文本的搜索作为正则表达式匹配列值的更进一步的介绍。使用正则表达式,可以编写查找所需行的非常复杂的匹配模式。
虽然这些搜索机制非常有用,但存在几个重要的限制。
- 性能——通配符和正则表达式匹配通常要求MySQL尝试匹配表中所有行(而且这些搜索极少使用表索引)。因此,由于被搜索行数不断增加,这些搜索可能非常耗时。
- 明确控制——使用通配符和正则表达式匹配,很难(而且并不总是能)明确地控制匹配什么和不匹配什么。例如,指定一个词必须匹配,一个词必须不匹配,而一个词仅在第一个词确实匹配的情况下才可以匹配或者才可以不匹配。
- 智能化的结果——虽然基于通配符和正则表达式的搜索提供了非常灵活的搜索,但它们都不能提供一种智能化的选择结果的方法。例如,一个特殊词的搜索将会返回包含该词的所有行,而不区分包含单个匹配的行和包含多个匹配的行(按照可能是更好的匹配来排列它们)。类似,一个特殊词的搜索将不会找出不包含该词但包含其他相关词的行。
所有这些限制以及更多的限制都可以用全文本搜索来解决。在使用全文本搜索时,MySQL不需要分别查看每个行,不需要分别分析和处理每个词。MySQL创建指定列中各词的一个索引,搜索可以针对这些词进行。这样,MySQL可以快速有效地决定哪些词匹配(哪些行包含它们),哪些词不匹配,它们匹配的频率,等等。
使用全文本搜索
为了进行全文本搜索,必须索引被搜索的列,而且要随着数据的改变不断地重新索引。在对表列进行适当设计后,MySQL会自动进行所有的索引和重新索引。
在索引之后, SELECT 可与 Match() 和 Against() 一起使用以实际执行搜索。
1.启用全文本搜索支持
一般在创建表时启用全文本搜索。 CREATE TABLE 语句接受 FULLTEXT 子句,它给出被索引列的一个逗号分隔的列表。
下面的 CREATE 语句演示了 FULLTEXT 子句的使用:
MariaDB [test]> create table productnotes1 (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;
Query OK, 0 rows affected (0.37 sec)
MariaDB [test]> show table status where name='productnotes1'\G;
*************************** 1. row ***************************
Name: productnotes1
Engine: MyISAM
Version: 10
Row_format: Dynamic
Rows: 0
Avg_row_length: 0
Data_length: 0
Max_data_length: 281474976710655
Index_length: 1024
Data_free: 0
Auto_increment: 1
Create_time: 2016-09-20 15:20:19
Update_time: 2016-09-20 15:20:19
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)后面将详细考察 CREATE TABLE 语句。现在,只需知道这条CREATE TABLE 语句定义表 productnotes1 并列出它所包含的列即可。这些列中有一个名为 note_text 的列,为了进行全文本搜索,MySQL根据子句 FULLTEXT(note_text) 的指示对它进行索引。这里的FULLTEXT 索引单个列,如果需要也可以指定多个列。
在定义之后,MySQL自动维护该索引。在增加、更新或删除行时,索引随之自动更新。可以在创建表时指定 FULLTEXT ,或者在稍后指定(在这种情况下所有已有数据必须立即索引)。
不要在导入数据时使用 FULLTEXT 更新索引要花时间,虽然不是很多,但毕竟要花时间。如果正在导入数据到一个新表,此时不应该启用 FULLTEXT 索引。应该首先导入所有数据,然后再修改表,定义 FULLTEXT 。这样有助于更快地导入数据(而且使索引数据的总时间小于在导入每行时分别进行索引所需的总时间)
2.进行全文本搜索
在索引之后,使用两个函数 Match() 和 Against() 执行全文本搜索,其中 Match() 指定被搜索的列, Against() 指定要使用的搜索表达式。
下面举一个例子:
MariaDB [test]> select * from productnotes;
+---------+---------+---------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
| note_id | prod_id | note_date | note_text |
+---------+---------+---------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
| 101 | TNT2 | 2005-08-17 00:00:00 | Customer complaint:
Sticks not individually wrapped, too easy to mistakenly detonate all at once.
Recommend individual wrapping. |
| 102 | OL1 | 2005-08-18 00:00:00 | Can shipped full, refills not available.
Need to order new can if refill needed. |
| 103 | SAFE | 2005-08-18 00:00:00 | Safe is combination locked, combination not provided with safe.
This is rarely a problem as safes are typically blown up or dropped by customers. |
| 104 | FC | 2005-08-19 00:00:00 | Quantity varies, sold by the sack load.
All guaranteed to be bright and orange, and suitable for use as rabbit bait. |
| 105 | TNT2 | 2005-08-20 00:00:00 | Included fuses are short and have been known to detonate too quickly for some customers.
Longer fuses are available (item FU1) and should be recommended. |
| 106 | TNT2 | 2005-08-22 00:00:00 | Matches not included, recommend purchase of matches or detonator (item DTNTR). |
| 107 | SAFE | 2005-08-23 00:00:00 | Please note that no returns will be accepted if safe opened using explosives. |
| 108 | ANV01 | 2005-08-25 00:00:00 | Multiple customer returns, anvils failing to drop fast enough or falling backwards on purchaser. Recommend that customer considers using heavier anvils. |
| 109 | ANV03 | 2005-09-01 00:00:00 | Item is extremely heavy. Designed for dropping, not recommended for use with slings, ropes, pulleys, or tightropes. |
| 110 | FC | 2005-09-01 00:00:00 | Customer complaint: rabbit has been able to detect trap, food apparently less effective now. |
| 111 | SLING | 2005-09-02 00:00:00 | Shipped unassembled, requires common tools (including oversized hammer). |
| 112 | SAFE | 2005-09-02 00:00:00 | Customer complaint:
Circular hole in safe floor can apparently be easily cut with handsaw. |
| 113 | ANV01 | 2005-09-05 00:00:00 | Customer complaint:
Not heavy enough to generate flying stars around head of victim. If being purchased for dropping, recommend ANV02 or ANV03 instead. |
| 114 | SAFE | 2005-09-07 00:00:00 | Call from individual trapped in safe plummeting to the ground, suggests an escape hatch be added.
Comment forwarded to vendor. |
+---------+---------+---------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
14 rows in set (0.00 sec)
MariaDB [test]> select note_text from productnotes where note_text regexp '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. |
+----------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
MariaDB [test]> 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.00 sec)此 SELECT 语句检索单个列 note_text 。由于 WHERE 子句,一个全文本搜索被执行。 Match(note_text) 指示MySQL针对指定的列进行搜索, Against('rabbit') 指定词 rabbit 作为搜索文本。由于有两行包含词 rabbit ,这两个行被返回。
使用完整的Match()说明 传递给 Match() 的值必须与FULLTEXT() 定义中的相同。如果指定多个列,则必须列出它们(而且次序正确)。
搜索不区分大小写 除非使用 BINARY 方式(本章中没有介绍),否则全文本搜索不区分大小写。
事实是刚才的搜索可以简单地用 LIKE 子句完成,如下所示:
MariaDB [test]> 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. |
+----------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)这条 SELECT 语句同样检索出两行,但次序不同(虽然并不总是出现这种情况)。
上述两条 SELECT 语句都不包含 ORDER BY 子句。后者(使用 LIKE )以不特别有用的顺序返回数据。前者(使用全文本搜索)返回以文本匹配的良好程度排序的数据。两个行都包含词 rabbit ,但包含词 rabbit 作为第3个词的行的等级比作为第20个词的行高。这很重要。全文本搜索的一个重要部分就是对结果排序。具有较高等级的行先返回(因为这些行很可能是你真正想要的行)。
为演示排序如何工作,请看以下例子:
MariaDB [test]> select note_text,match(note_text) against('rabbit') as rank from productnotes;
+-----------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------+
| note_text | rank |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------+
| Customer complaint:
Sticks not individually wrapped, too easy to mistakenly detonate all at once.
Recommend individual wrapping. | 0 |
| Can shipped full, refills not available.
Need to order new can if refill needed. | 0 |
| Safe is combination locked, combination not provided with safe.
This is rarely a problem as safes are typically blown up or dropped by customers. | 0 |
| Quantity varies, sold by the sack load.
All guaranteed to be bright and orange, and suitable for use as rabbit bait. | 1.5905543565750122 |
| Included fuses are short and have been known to detonate too quickly for some customers.
Longer fuses are available (item FU1) and should be recommended. | 0 |
| Matches not included, recommend purchase of matches or detonator (item DTNTR). | 0 |
| Please note that no returns will be accepted if safe opened using explosives. | 0 |
| Multiple customer returns, anvils failing to drop fast enough or falling backwards on purchaser. Recommend that customer considers using heavier anvils. | 0 |
| Item is extremely heavy. Designed for dropping, not recommended for use with slings, ropes, pulleys, or tightropes. | 0 |
| Customer complaint: rabbit has been able to detect trap, food apparently less effective now. | 1.6408053636550903 |
| Shipped unassembled, requires common tools (including oversized hammer). | 0 |
| Customer complaint:
Circular hole in safe floor can apparently be easily cut with handsaw. | 0 |
| Customer complaint:
Not heavy enough to generate flying stars around head of victim. If being purchased for dropping, recommend ANV02 or ANV03 instead. | 0 |
| Call from individual trapped in safe plummeting to the ground, suggests an escape hatch be added.
Comment forwarded to vendor. | 0 |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------+
14 rows in set (0.00 sec)这里,在 SELECT 而不是 WHERE 子句中使用 Match() 和 Against() 。这使所有行都被返回(因为没有 WHERE 子句)。 Match() 和 Against()用来建立一个计算列(别名为 rank ),此列包含全文本搜索计算出的等级值。等级由MySQL根据行中词的数目、唯一词的数目、整个索引中词的总数以及包含该词的行的数目计算出来。正如所见,不包含词 rabbit 的行等级为0(因此不被前一例子中的 WHERE 子句选择)。确实包含词 rabbit的两个行每行都有一个等级值,文本中词靠前的行的等级值比词靠后的行的等级值高。
这个例子有助于说明全文本搜索如何排除行(排除那些等级为0的行),如何排序结果(按等级以降序排序)。
排序多个搜索项 如果指定多个搜索项,则包含多数匹配词的那些行将具有比包含较少词(或仅有一个匹配)的那些行高的等级值。
正如所见,全文本搜索提供了简单 LIKE 搜索不能提供的功能。而且,由于数据是索引的,全文本搜索还相当快。
3.使用查询扩展
查询扩展用来设法放宽所返回的全文本搜索结果的范围。考虑下面的情况。你想找出所有提到 anvils 的注释。只有一个注释包含词 anvils ,但你还想找出可能与你的搜索有关的所有其他行,即使它们不包含词anvils 。
这也是查询扩展的一项任务。在使用查询扩展时,MySQL对数据和索引进行两遍扫描来完成搜索:
- 首先,进行一个基本的全文本搜索,找出与搜索条件匹配的所有行;
- 其次,MySQL检查这些匹配行并选择所有有用的词(我们将会简要地解释MySQL如何断定什么有用,什么无用)。
- 再其次, MySQL再次进行全文本搜索,这次不仅使用原来的条件,而且还使用所有有用的词。
利用查询扩展,能找出可能相关的结果,即使它们并不精确包含所查找的词。
只用于MySQL版本4.1.1或更高级的版本 查询扩展功能是在MySQL 4.1.1中引入的,因此不能用于之前的版本。
下面举一个例子,首先进行一个简单的全文本搜索,没有查询扩展:
MariaDB [test]> select note_text from productnotes where match(note_text) against('anvils');
+----------------------------------------------------------------------------------------------------------------------------------------------------------+
| note_text |
+----------------------------------------------------------------------------------------------------------------------------------------------------------+
| Multiple customer returns, anvils failing to drop fast enough or falling backwards on purchaser. Recommend that customer considers using heavier anvils. |
+----------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)只有一行包含词 anvils ,因此只返回一行。
下面是相同的搜索,这次使用查询扩展:
MariaDB [test]> 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 rows in set (0.00 sec)这次返回了7行。第一行包含词 anvils ,因此等级最高。第二行与 anvils 无关,但因为它包含第一行中的两个词( customer和 recommend ),所以也被检索出来。第3行也包含这两个相同的词,但它们在文本中的位置更靠后且分开得更远,因此也包含这一行,但等级为第三。第三行确实也没有涉及 anvils (按它们的产品名)。
正如所见,查询扩展极大地增加了返回的行数,但这样做也增加了你实际上并不想要的行的数目。
行越多越好 表中的行越多(这些行中的文本就越多),使用查询扩展返回的结果越好。
4.布尔文本搜索
MySQL支持全文本搜索的另外一种形式,称为布尔方式(boolean mode)。
以布尔方式,可以提供关于如下内容的细节:
- 要匹配的词;
- 要排斥的词(如果某行包含这个词,则不返回该行,即使它包含其他指定的词也是如此);
- 排列提示(指定某些词比其他词更重要,更重要的词等级更高);
- 表达式分组;
- 另外一些内容。
即使没有 FULLTEXT 索引也可以使用 布尔方式不同于迄今为止使用的全文本搜索语法的地方在于,即使没有定义FULLTEXT 索引,也可以使用它。但这是一种非常缓慢的操作(其性能将随着数据量的增加而降低)。
为演示 IN BOOLEAN MODE 的作用,举一个简单的例子:
MariaDB [test]> select note_text from productnotes where match(note_text) against('heavy' in boolean mode);
+---------------------------------------------------------------------------------------------------------------------------------------------------------+
| note_text |
+---------------------------------------------------------------------------------------------------------------------------------------------------------+
| Item is extremely heavy. Designed for dropping, not recommended for use with slings, ropes, pulleys, or tightropes. |
| Customer complaint:
Not heavy enough to generate flying stars around head of victim. If being purchased for dropping, recommend ANV02 or ANV03 instead. |
+---------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)此全文本搜索检索包含词 heavy 的所有行(有两行)。其中使用了关键字 IN BOOLEAN MODE ,但实际上没有指定布尔操作符,因此,其结果与没有指定布尔方式的结果相同。
IN BOOLEAN MODE 的行为差异 虽然这个例子的结果与没有IN BOOLEAN MODE 的相同,但其行为有一个重要的差别(即使在这个特殊的例子没有表现出来)。
为了匹配包含 heavy 但不包含任意以 rope 开始的词的行,可使用以下查询:
MariaDB [test]> 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. |
+---------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)这次只返回一行。这一次仍然匹配词 heavy ,但 -rope* 明确地指示 MySQL排除 包含 rope* (任何以 rope 开始 的词,包 括ropes )的行,这就是为什么上一个例子中的第一行被排除的原因。
在MySQL 4.x中所需的代码更改 如果你使用的是MySQL4.x,则上面的例子可能不返回任何行。这是 * 操作符处理中的一个错误。为在MySQL 4.x中使用这个例子,使用 -ropes 而不是 -rope* (排除 ropes 而不是排除任何以 rope 开始的词)。
全文本布尔操作符 | 说明 |
+ | 包含,词必须存在 |
- | 排除,词必须不出现 |
> | 包含,而且增加等级值 |
< | 包含,且减少等级值 |
() | 把词组成子表达式(允许这些子表达式作为一个组被包含、排除、排列等) |
~ | 取消一个词的排序值 |
* | 词尾的通配符 |
“” | 定义一个短语(与单个词的列表不一样,它匹配整个短语以便包含或排除这个短语) |
下面举几个例子,说明某些操作符如何使用:
搜索匹配包含词 rabbit 和 bait 的行。
MariaDB [test]> select note_text from productnotes where match(note_text) against('+rabbit +bait' in boolean mode);
+----------------------------------------------------------------------------------------------------------------------+
| note_text |
+----------------------------------------------------------------------------------------------------------------------+
| Quantity varies, sold by the sack load.
All guaranteed to be bright and orange, and suitable for use as rabbit bait. |
+----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)搜索匹配包含 rabbit 和 bait 中的至少一个词的行。
MariaDB [test]> select note_text from productnotes where match(note_text) against('rabbit bait' in boolean mode);
+----------------------------------------------------------------------------------------------------------------------+
| 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. |
+----------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)搜索匹配短语 rabbit bait 而不是匹配两个词 rabbit 和 bait 。
MariaDB [test]> select note_text from productnotes where match(note_text) against('"rabbit bait"' in boolean mode);
+----------------------------------------------------------------------------------------------------------------------+
| note_text |
+----------------------------------------------------------------------------------------------------------------------+
| Quantity varies, sold by the sack load.
All guaranteed to be bright and orange, and suitable for use as rabbit bait. |
+----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)搜索匹配 rabbit 和 carrot ,增加前者的等级,降低后者的等级。
MariaDB [test]> select note_text from productnotes where match(note_text) against('>rabbit <carrot' in boolean mode);
+----------------------------------------------------------------------------------------------------------------------+
| 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. |
+----------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)搜索匹配词 safe 和 combination ,降低后者的等级。
MariaDB [test]> select note_text from productnotes where match(note_text) against('+safe +(<combination)' in boolean mode);
+---------------------------------------------------------------------------------------------------------------------------------------------------+
| note_text |
+---------------------------------------------------------------------------------------------------------------------------------------------------+
| Safe is combination locked, combination not provided with safe.
This is rarely a problem as safes are typically blown up or dropped by customers. |
+---------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)排列而不排序 在布尔方式中,不按等级值降序排序返回的行。
5.全文本搜索的使用说明
在结束本章之前,给出关于全文本搜索的某些重要的说明。
- 在索引全文本数据时,短词被忽略且从索引中排除。短词定义为那些具有3个或3个以下字符的词(如果需要,这个数目可以更改)。
- MySQL带有一个内建的非用词(stopword)列表,这些词在索引全文本数据时总是被忽略。如果需要,可以覆盖这个列表(请参阅MySQL文档以了解如何完成此工作)。
- 许多词出现的频率很高,搜索它们没有用处(返回太多的结果)。因此,MySQL规定了一条50%规则,如果一个词出现在50%以上的行中,则将它作为一个非用词忽略。 50%规则不用于 IN BOOLEANMODE 。
- 如果表中的行数少于3行,则全文本搜索不返回结果(因为每个词或者不出现,或者至少出现在50%的行中)。
- 忽略词中的单引号。例如, don’t 索引为 dont 。
- 不具有词分隔符(包括日语和汉语)的语言不能恰当地返回全文本搜索结果。
- 如前所述,仅在 MyISAM 数据库引擎中支持全文本搜索。
没有邻近操作符 邻近搜索是许多全文本搜索支持的一个特性,它能搜索相邻的词(在相同的句子中、相同的段落中或者在特定数目的词的部分中,等等)。MySQL全文本搜索现在还不支持邻近操作符,不过未来的版本有支持这种操作符的计划。
小结
本章介绍了为什么要使用全文本搜索,以及如何使用MySQL的Match() 和 Against() 函数进行全文本搜索。我们还学习了查询扩展(它能增加找到相关匹配的机会)和如何使用布尔方式进行更细致的查找控制。
