【MySQL】全文本搜索

快来打我* 2022-10-27 06:20 251阅读 0赞

### 目录 ###

*   *  理解全文本搜索
     *  使用全文本搜索
     *   *  启用全文本搜索支持
         *  进行全文本搜索
         *  使用查询扩展
         *  布尔文本搜索
         *  全文本搜索的使用说明

## 理解全文本搜索 ##

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

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

用基于文本的搜索作为正则表达式匹配列值的更进一步的介绍。使用正则表达式，可以编写查找所需行的非常复杂的匹配模式。

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

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

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

## 使用全文本搜索 ##

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

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

### 启用全文本搜索支持 ###

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

create table productnotes
        (
         note_id int not null auto_increment,
         prod_id char(10) not null,
         note_date date not null,
         note_text text null,
         primary key(note_id),
         fulltext(note_text)
         )engine=MyISAM;

现在，只需知道这条CREATE TABLE语句定义表productnotes并列出它所包含的列即可。这些列中有一个名为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，这两个行被返回。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3hpYW5nanVueWVz_size_16_color_FFFFFF_t_70]  
**使 用 完 整 的 Match() 说 明**  
**传 递 给 Match() 的 值 必 须 与FULLTEXT()定义中的相同。如果指定多个列，则必须列出它们（而且次序正确）。**

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

事实是刚才的搜索可以简单地用LIKE子句完成，如下所示：

select note_text from productnotes 
    where note_text like '%rabbit%';

这条SELECT语句同样检索出两行，但次序不同（虽然并不总是出现这种情况）。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3hpYW5nanVueWVz_size_16_color_FFFFFF_t_70 1]上述两条SELECT语句都不包含ORDER BY子句。后者（使用LIKE）以不特别有用的顺序返回数据。前者（使用全文本搜索）返回以文本匹配的良好程度排序的数据。两个行都包含词rabbit，但包含词rabbit作为第3个词的行的等级比作为第20个词的行高。这很重要。**全文本搜索的一个重要部分就是对结果排序**。具有较高等级的行先返回（因为这些行很可能是你真正想要的行）。

演示排序如何工作，请看以下例子：

select note_text, 
     match(note_text) against('rabbit') as 'rank' from productnotes;

这里， 在SELECT而不是WHERE子句中使用Match()和Against()。这使所有行都被返回（因为没有WHERE子句）。 Match()和Against()用来建立一个计算列（别名为rank），此列包含全文本搜索计算出的等级值。**等级由MySQL根据行中词的数目、唯一词的数目、整个索引中词的总数以及包含该词的行的数目计算出来**。正如所见，不包含词rabbit的行等级为0（因此不被前一例子中的WHERE子句选择）。确实包含词rabbit的两个行每行都有一个等级值，文本中词靠前的行的等级值比词靠后的行的等级值高  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3hpYW5nanVueWVz_size_16_color_FFFFFF_t_70 2]这个例子有助于说明全文本搜索如何排除行（排除那些等级为0的行），如何排序结果（按等级以降序排序）。

**排序多个搜索项**  
如果指定多个搜索项，则包含多数匹配词的那些行将具有比包含较少词（或仅有一个匹配）的那些行高的等级值。  
正如所见，全文本搜索提供了简单LIKE搜索不能提供的功能。而且，由于数据是索引的，全文本搜索还相当快。

### 使用查询扩展 ###

查询扩展用来设法放宽所返回的全文本搜索结果的范围。考虑下面的情况。你想找出所有提到anvils的注释。只有一个注释包含词anvils，但你还想找出可能与你的搜索有关的所有其他行，即使它们不包含词anvils。  
这也是查询扩展的一项任务。在使用查询扩展时， MySQL对数据和索引进行两遍扫描来完成搜索：

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

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

只用于MySQL版本4.1.1或更高级的版本 查询扩展功能是在MySQL 4.1.1中引入的，因此不能用于之前的版本。

下面举一个例子，首先进行一个简单的全文本搜索，没有查询扩展：

select note_text from productnotes where match(note_text) against('anvils');

只有一行包含词anvils，因此只返回一行。  
![在这里插入图片描述][20210205210502335.png]

下面是相同的搜索，这次使用查询扩展：

select note_text from productnotes 
    where match(note_text) against('anvils');

这次返回了7行。第一行包含词anvils，因此等级最高。第二行与anvils无关，但因为它包含第一行中的两个词（ customer和recommend），所以也被检索出来。第3行也包含这两个相同的词，但它们在文本中的位置更靠后且分开得更远，因此也包含这一行，但等级为第三。第三行确实也没有涉及anvils（按它们的产品名）。![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3hpYW5nanVueWVz_size_16_color_FFFFFF_t_70 3]  
正如所见，查询扩展极大地增加了返回的行数，但这样做也增加了你实际上并不想要的行的数目。

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

### 布尔文本搜索 ###

MySQL支持全文本搜索的另外一种形式，称为布尔方式（ boolean mode）。以布尔方式，可以提供关于如下内容的细节：

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

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

为演示IN BOOLEAN MODE的作用，举一个简单的例子：

select note_text from productnotes 
    where match(note_text) against('heavy'in boolean mode );

此全文本搜索检索包含词heavy的所有行（有两行）。其中使用了关键字IN BOOLEAN MODE，但实际上没有指定布尔操作符，因此，其结果与没有指定布尔方式的结果相同。  
![在这里插入图片描述][20210205212046902.png]  
为了匹配包含heavy但不包含任意以rope开始的词的行， 可使用以下查询：

select note_text from productnotes 
    where match(note_text) against('heavy -rope*' in boolean mode);

这次只返回一行。这一次仍然匹配词heavy，但-rope*明确地指示MySQL排除包含rope*（任何以rope开始的词，包括ropes）的行，这就是为什么上一个例子中的第一行被排除的原因。  
![在这里插入图片描述][20210205212645284.png]  
我们已经看到了两个全文本搜索布尔操作符-和\*， -排除一个词，而\*是截断操作符（可想象为用于词尾的一个通配符）。下表列出支持的所有布尔操作符。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3hpYW5nanVueWVz_size_16_color_FFFFFF_t_70 4]  
下面举几个例子，说明某些操作符如何使用：

select note_text from productnotes 
    where match(note_text) against('+rabbit +bait' in boolean mode);

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

![在这里插入图片描述][20210205213353987.png]

select note_text from productnotes where match(note_text) against('rabbit bait' in boolean mode );

没有指定操作符，这个搜索匹配包含rabbit和bait中的至少一个词的行。  
![在这里插入图片描述][20210205213533165.png]

select note_text from productnotes where
      match(note_text) against('"rabbit bait"' in boolean mode );

这个搜索匹配短语rabbit bait而不是匹配两个词rabbit和bait。  
![在这里插入图片描述][20210205213652929.png]

select note_text from productnotes 
     where match(note_text) against('>rabbit <carrot' in boolean mode );

匹配rabbit和carrot，增加前者的等级，降低后者的等级。  
![在这里插入图片描述][20210205213801373.png]

select note_text from productnotes 
     where match(note_text) against('+safe+(<combination)' in boolean mode );

这个搜索匹配词safe和combination，降低后者的等级。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3hpYW5nanVueWVz_size_16_color_FFFFFF_t_70 5]  
**排列而不排序 在布尔方式中，不按等级值降序排序返回的行。**

### 全文本搜索的使用说明 ###

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

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3hpYW5nanVueWVz_size_16_color_FFFFFF_t_70]: /images/20221024/1d15235f2f8d48a784c3b33af31c5ec0.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3hpYW5nanVueWVz_size_16_color_FFFFFF_t_70 1]: /images/20221024/b9b791ac87284731b39172455a589d3d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3hpYW5nanVueWVz_size_16_color_FFFFFF_t_70 2]: /images/20221024/9ee5a62b1cd44a6491f4c6a5db7b5673.png
[20210205210502335.png]: /images/20221024/3b434f1754f94faf83badecce1df95ab.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3hpYW5nanVueWVz_size_16_color_FFFFFF_t_70 3]: /images/20221024/63824c5fba8c4dca9c15495874b9e471.png
[20210205212046902.png]: /images/20221024/a5eaa7988075480d81fc3bce5352a651.png
[20210205212645284.png]: /images/20221024/b50ebef1148644d0b179365c6b8523f7.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3hpYW5nanVueWVz_size_16_color_FFFFFF_t_70 4]: /images/20221024/696ad9baa37c444b959c21abf08a3db4.png
[20210205213353987.png]: /images/20221024/c0105f112fc84e2da4a7edb938a20809.png
[20210205213533165.png]: /images/20221024/a74e8e6a534f43a088703e7e7138c96a.png
[20210205213652929.png]: /images/20221024/19e9ba7f0b3b49b7a7b13557547ccf9a.png
[20210205213801373.png]: /images/20221024/78a4c437a7074f5d9dba0aadd6e33a54.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3hpYW5nanVueWVz_size_16_color_FFFFFF_t_70 5]: /images/20221024/f17b9800e8044f7c9af8063de1c606ef.png