sql必知必会学习记录（十四）- 全文本搜索

刺骨的言语ヽ痛彻心扉 2022-03-09 16:50 250阅读 0赞

### sql必知必会学习记录（十四）- 全文本搜索 ###

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

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

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

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

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自动维护该索引。在增加、更新或删除行时，索引随之自动更新。

不要在导入数据时使用FULLTEXT更新索引要花时间，虽然不是很多，但毕竟要花时间。如果正在导入数据到一个新表，此时不应该启用FULLTEXT索引。应该首先导入所有数据，然后再修改表，定义FULLTEXT。这样有助于更快地导入数据（而且使索引数据的总时间小于在导入每行时分别进行索引所需的总时间）。

#### 进行全文本搜索 ####

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

SELECT note_text from productnotes WHERE MATCH(note_text) AGAINST('rabbit')

\*\*注意：\*\*传递给Match() 的值必须与FULLTEXT()定义中的相同。如果指定多个列，则必须列出它们（而且次序正确）。除非使用BINARY方式（本章中没有介绍），否则全文本搜索不区分大小写。

SELECT note_text, MATCH(note_text) AGAINST('rabbit') as rank  from productnotes where MATCH(note_text) AGAINST('rabbit')

mysql是根据匹配的等级来进行排序的，它会排除等级为0的行，然后按照等级以降序排序。

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

查询扩展用来设法放宽所返回的全文本搜索结果的范围。考虑下面的情况。你想找出所有提到anvils的注释。只有一个注释包含词anvils，但你还想找出可能与你的搜索有关的所有其他行，即使它们不包含词anvils。

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

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

SELECT note_text FROM productnotes where MATCH(note_text) AGAINST('anvils');
    
    SELECT note_text FROM productnotes where MATCH(note_text) AGAINST('anvils' WITH QUERY EXPANSION);
    两次查询的结果对比可以看出查询扩展的特点

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

在使用布尔文本搜索时需要以下要求：

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

![20190312154657.png][]

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

select note_text from productnotes where MATCH(note_text) AGAINST('heavy -rope*' in boolean MODE);

只返回一行。这匹配词heavy，但-rope*明确地指示MySQL排除包含rope*（任何以rope开始的词，包括ropes）的行。

搜索匹配包含rabbit和bait的行
    select note_text from productnotes where MATCH(note_text) AGAINST('+rabbit +bait' in boolean mode)
    没有指定操作符，这个搜索匹配包含rabbit和bait中的至少一个词的行。
    select note_text from productnotes where MATCH(note_text) AGAINST('rabbit bait' in boolean mode);
    
    搜索匹配短语rabbit bait而不是匹配两个词rabbit和bait。
    select note_text from productnotes where MATCH(note_text) AGAINST('"rabbit bait"' in boolean mode);
    
    匹配rabbit和carrot，增加前者的等级，降低后者的等级。
    select note_text from productnotes where MATCH(note_text) AGAINST('>rabbit <carrot' in boolean mode);
    
    匹配词safe和combination，降低后者的等级
    select note_text from productnotes where MATCH(note_text) AGAINST('+safe +(<combination)' in boolean mode);

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

注意：

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

[20190312154657.png]: /images/20220309/60450bad300946e580f2388009ae3843.png