SQL学习(全文本搜索)_sql 文本搜索

理解全文搜索

前面我们已经了解过基于LIKE关键字的搜索，它利用通配操作符匹配文本。使用LIKE，能够查找包含特殊值或部分值得行。使用正则表达式，可以编写查找所需行得非常浮渣得匹配模式。

虽然这些搜索机制非常有用，但存在几个重要的限制:

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

所有这些限制以及更多的限制都可以通过全文本搜索来解决。在使用全文本搜索时，MySQL不需要分别查看每个行，不需要分别分析和处理每个词。MySQL创建指定列中各词的一个索引，搜索可以针对这些词进行。这样，MySQL可以快速有效的决定哪些词匹配，哪些词不匹配，它们的频率，等等。

使用全文本搜索

为了进行全文本搜索，必须索引被搜索的列，而且要随着数据的改变不断地重新索引。在对表进行适当设计后，MySQL会自动进行所有的索引和重新索引。

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

启用全文本搜索支持

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

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. |
+----------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9

！传递给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.                         |
+----------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9

全文本搜索返回以文本匹配的良好程度排序的数据。两个行都包含词rabbit,但包含词rabbit作为第3个词的行的等级比作为第20个词的行高。

使用查询扩展

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

用例：你想找出所有提到anvils的注释，只有一个注释包含词anvils,但你还想找出可能与你的搜索有关的其他所有行，即使它们不包含词anvils。

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

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

mysql> 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.01 sec)

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 rows in set (0.01 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

使用扩展查查询后，第一行包含词anvils,因此等级最高。第二行与anvils无关，但因为它包含第一行中的两个词（customer和recommend），所以也被检索出来。第3行也包含这两个相同的词，但它们在文本中的位置更靠后且分开得更远。因此也包含这一行，但等级为第三。

布尔文本搜索

MySQL支持全文本搜索得另外一种形式，称为布尔方式。
布尔方式使用细节如下：

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

！即使没有FULLTEXT索引也可以引用布尔表达式，但这是一种非常缓慢的操作（其性能将随着数据量的增加而降低）

1.检索包含heavy词的所有行

mysql> 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.01 sec)
1
2
3
4
5
6
7
8
9

2.检索包含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. |
+---------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
1
2
3
4
5
6
7
8

3.检索包含词rabbit和词bait的行

mysql> 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)
1
2
3
4
5
6
7
8

4.检索至少包含rabbit和bait一个词的行

mysql> 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)
1
2
3
4
5
6
7
8
9

5.检索包含短语rabbit bait的行

mysql> 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)
1
2
3
4
5
6
7
8
9

6.匹配rabbit和carrot，提高前者的等级降低后者的等级。

mysql> 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)
1
2
3
4
5
6
7
8
9

7.匹配safe和combination， 降低后者的等级

mysql> 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)
1
2
3
4
5
6
7
8

全文搜索布尔操作符

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

全文本搜索使用说明

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