MySQL必知必会学习笔记第十八章全文本搜索

电玩女神 2022-12-28 11:19 200阅读 0赞

并非所有引擎都支持全文本搜索，MyISAM支持。

更新：  
1.MySQL 5.6 以前的版本，只有 MyISAM 存储引擎支持全文索引；  
2.MySQL 5.6 及以后的版本，MyISAM 和 InnoDB 存储引擎均支持全文索引;  
3.只有字段的数据类型为char、varchar、text的列才可以建全文索引。

LIKE和REGEXP都能搜索一个文本，但存在以下限制：  
1.性能：它们会匹配表中所有行且这些搜索极少使用索引。  
2.明确控制：它们很难明确匹配什么和不匹配什么，如指定一个词必须匹配、一个词必须不匹配、而一个词仅在第一个词确实匹配的情况下才能匹配或才能不匹配。  
3.智能化的结果：如一个特殊词搜索会返回所有包含该词的行，而不区分包含单次匹配的行和包含多次匹配的行。

使用全文本搜索时，MySQL不需要分别查看每个行，不需要分别分析和处理每个词。MySQL会创建指定列中各词的一个索引，搜索可以针对这些词进行，这样MySQL可以快速有效地决定哪些词匹配、哪些词不匹配、它们的匹配频率等。

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

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

CREATE TABLE productnotes
    (
        note_id       int          NOT NULL AUTO_INCREMENT,
        prod_id       char(10)     NOT NULL,
        note_data     datetime     NOT NULL,
        note_text     text         NULL,                         # 接受NULL值
        PRIMARY KEY(note_id),
        FULLTEXT(note_text)
    ) ENGINE = MyISAM;

上表中FULLTEXT索引单个列，也可以指定多个列。定义后MySQL自动维护该索引，增删改时索引随之更新。

更新索引需要的时间不多，但毕竟花时间，如果正在导入数据到一个新表，此时不应启用FULLTEXT，应在导完数据后再定义FULLSET，从而更快地导入数据（这样也可以使索引数据的总时间小于在导入每行时分别进行索引所需的总时间）。

进行全文本搜索：

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

以上SQL语句从note\_text列中搜索表达式`'rabbit'`。

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

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

全文本搜索的结果是以匹配的良好程序排序的，一个规则是匹配的串在整个文本的位置（即作为第几个词出现的），越靠前优先级越高。排序优先级查看：

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

运行它：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3R1czAwMDAw_size_16_color_FFFFFF_t_70]  
以上rank列由词数、位置等因素计算，不包含词rabbit的行值为0，不会被WHERE子句选出来，而选出来的行以rank降序排序。

查询扩展可放宽返回的全本文搜索的范围，如除了想找出含anvils的行，还想找出可能与搜索有关的行，即使它不包含anvils。

使用查询扩展时，会对数据和索引进行两遍扫描完成搜索：  
1.进行基本的全文本搜索，找出与搜索条件匹配的所有行。  
2.检查这些匹配行，并选择所有有用的词。  
3.再次进行全文本搜索，不仅用原来的条件，还使用所有有用的词。

查询扩展在MySQL 4.1.1中引入。

使用查询扩展前的SQL：

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

使用查询扩展前的输出：  
![在这里插入图片描述][20201218173915574.png]  
可见只有一行存在anvils。

使用查询扩展：

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

使用查询扩展的输出：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3R1czAwMDAw_size_16_color_FFFFFF_t_70 1]  
返回了七行，第一行包含词anvils，等级最高。第二行含第一行中的两个词（customer和recommend），也被检索出来。第三行也包含这两个词，但它们在文本中的位置更靠后，且分开地更远。

表中行越多，使用查询扩展返回的结果越好。

布尔文本搜索可提供以下内容的细节：  
1.要匹配的词。  
2.要排斥的词（如果某行包含这个词，不返回该行，即使它包含其他指定的词）。  
3.排序提示（指定词的优先级）。  
4.表达式分组。  
5.另外一些内容。

布尔文本搜索没有FULLTEXT索引也能使用，此操作非常缓慢。

使用布尔文本搜索：

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

运行它，得到两行：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3R1czAwMDAw_size_16_color_FFFFFF_t_70 2]

以上SQL搜索包含词heavy的所有行，虽然它使用了IN BOOLEAN MODE，但没有使用布尔操作符，因此其结果与没有指定布尔方式的结果相同。

匹配包含heavy但不包含以rope开始的词的行：

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

运行它：  
![在这里插入图片描述][20201219132948362.png]  
可见结果中只剩一行了，因为另一行包含以rope开头的词ropes。

MySQL 4.x中上例SQL可能不返回任何行，这是\*操作符处理时的一个错误。

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3R1czAwMDAw_size_16_color_FFFFFF_t_70 3]  
匹配包含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而不是这两个词：

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);

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

关于全文本搜索的说明：  
1.索引全文本数据时，三个及以下字符的短词行被忽略且从索引中删除。三个字符的限制可更改。  
2.MySQL内建一个非用词列表，这些词在索引全文本数据时总被忽略，非用词列表可被覆盖。  
3.许多词出现频率很高，搜索它们没有用处，如果一个词出现在50%以上的行中，则将它作为一个非用词忽略。50%规则不适用于IN BOOLEAN MODE。  
4.如果表中行数小于三行，则全文本搜索不返回结果（每个词或者不出现，或者至少出现在50%的行中）。  
5.忽略词中的单引号，如don’t索引为dont。  
6.不具有词分隔符的语言（中文、日文等）不能恰当地返回全文本搜索结果。

MySQL还不支持邻近操作符，邻近搜索是许多全文本搜索支持的一个特性，能用于规定命中的检索词在记录中的间隔距离（或是否在相同句子中、相同段落中等）。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3R1czAwMDAw_size_16_color_FFFFFF_t_70]: /images/20221120/1e842794e4fd408aa2c7147aa9acbb27.png
[20201218173915574.png]: /images/20221120/9887b06894344eb789d6ddc5ffa9b30c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3R1czAwMDAw_size_16_color_FFFFFF_t_70 1]: /images/20221120/e3438ac4b3b145179bfa0ba45f87916a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3R1czAwMDAw_size_16_color_FFFFFF_t_70 2]: /images/20221120/2f5d3dea9e2c4cc39bfafc71f0eb98f3.png
[20201219132948362.png]: /images/20221120/9c33b18a27b64939921cf5f13b5b6d07.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3R1czAwMDAw_size_16_color_FFFFFF_t_70 3]: /images/20221120/ba4d8ea1cc064ac1a900baf651377282.png