MySQL的全文本搜索功能进行高级的数据查询和选择

理解全文本搜索

匹配搜索LIKE和正则表达式REGEXP存在的限制

  • 性能
    匹配表中所有行（极少使用表索引）。被搜索行数不断增加，这些搜索功能可能非常耗时
  • 明确控制
    很难明确的控制匹配什么和不匹配什么
  • 智能化的结果
    无法提供一种智能化的选择结果的方法

使用全文本搜索可解决以上问题，MySQL不需要分别查看每个行，不需要分别分析和处理每个词。可以快速有效地决定哪些词匹配（哪些行包含它们），哪些词不匹配，它们匹配的频率等。

MyISAM和InnoDB

MyISAM支持全文本搜索功能，但是InnoDB不支持。

使用全文本搜索

全文本搜索必须搜索被搜索的列。在对列表进行适当设计后，MySQL会自动进行所有的索引和重新索引。

启用全文本搜索支持 FULLTEXT

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

CREATE TABLE `productnotes` (
	`note_id` INT ( 11 ) NOT NULL AUTO_INCREMENT COMMENT '唯一注释ID',
	`prod_id` CHAR ( 10 ) NOT NULL COMMENT '产品ID（对应于products表中的prod_id）',
	`note_date` datetime NOT NULL COMMENT '增加注释的日期',
	`note_text` text COMMENT '注释文本',
	PRIMARY KEY ( `note_id` ),
FULLTEXT KEY `note_text` ( `note_text` ) 
) ENGINE = MyISAM AUTO_INCREMENT = 115 DEFAULT CHARSET = utf8 COMMENT = '产品相关注释表';
  • 在定义之后，MySQL自动维护该索引。在增加、更新或删除行是，索引随之自动更新。
  • 在创建表时指定FULLTEXT，或者在稍后指定

不要在导入数据时使用FULLTEXT

如果正在导入一个数据到一个新表，此时不应该启用FULLTEXT索引。应先导入所有数据，然后再修改表，定义FULLTEXT。这样有助于更快地导入数据

进行全文本搜索MATCH、AGAINST

使用MATCH()和AGAINST()执行全文本搜索。MATCH指定被搜索的列，AGAINST指定要使用的搜索表达式

SELECT
	note_text 
FROM
	productnotes 
WHERE
	MATCH ( note_text ) AGAINST ( "rabbit" );

此SELECT语句检索单个列note_text。由于WHERE子句，一个全文本搜索被执行，指定rabbit作为搜索文本。
image.png

注意

  • 使用完整的MATCH()说明
    传递给MATCH()的值必须与FULLTEXT()定义中的相同
  • 搜索不区分大小写
    除非使用BINARY方式，否则全文本搜索不区分大小写。

使用LIKE和全文本的区别

上述的例子也可以使用LIKE完成

SELECT
	note_text 
FROM
	productnotes 
WHERE
	note_text LIKE "%rabbit%";

注意其返回的行虽然都为2，但排序的次序不同
image.png
使用全文本搜索时，包含词rabbit作为第三个词的行的登记比作为第20个词的行高。
全文本搜索，具有较高等级的行先返回

SELECT
	note_text,
	MATCH ( note_text ) AGAINST ( "rabbit" ) AS rank 
FROM
	productnotes;

此SQL语句搜索所有行，使用MATCH和AGAINST用来建立一个计算列，包含全文本搜索计算出的等级值。
image.png
等级由MySQL根据行中词的数目、唯一词数目、整个索引中词的总数以及包含该词的行的数目计算出来。
全文本搜索排出那些等级为0的行，按等级以降序排序

  • 排序多个搜索项
    如果指定多个搜索项，包含多数匹配词的那些行将具有比包含较少词的那些行高的等级值。

使用查询扩展

放宽所返回的全文本搜索结果的范围。

过程

MySQL在使用扩展查询时，会对数据和索引进行俩遍扫描来完成搜索，共分为3步骤

  • 进行一个基本的全文本搜索，找出与搜索条件匹配的所有行
  • 检查这些匹配行并选择所有有用的词
  • 再次进行全文本搜索，不仅使用原来的条件，而且还使用所有有用的词
SELECT
	note_text 
FROM
	productnotes 
WHERE
	MATCH ( note_text ) AGAINST ( "anvils" WITH QUERY EXPANSION );

使用扩展查询的结果
image.png
其返回7行，第一行包含词anvils，因此等级最高。第二行与anvils无关，但包含第一行中的2个词（customer和recommend）所以也被检索出来。第三行也包含这2个相同的词，但因为在文本位置中排序靠后，所以等级为3.
扩展查询极大地增加了返回的行数，但也增加了实际上并不想要的行的数目

布尔文本搜索

即使没有定义FULLTEXT索引也可以使用，但是搜索非常缓慢
布尔方式可以提供如下内容细节:

  • 要匹配的词

  • 要排斥的词
    如果包含这个词则不返回该行，即使该行包含要匹配的词

  • 排列提示
    指定词的等级

  • 表达式分组

  • 另外一些内容

  • 例：

SELECT
	note_text 
FROM
	productnotes 
WHERE
	MATCH ( note_text ) AGAINST ( "heavy" IN BOOLEAN MODE );

因为没有指定布尔操作符，其结果与没有指定布尔方式的结果相同。
image.png

SELECT
	note_text 
FROM
	productnotes 
WHERE
	MATCH ( note_text ) AGAINST ( "heavy -rope*" IN BOOLEAN MODE );

匹配包含heavy但不包含以rope开始的词的行，其只返回一行，
image.png

全文本布尔操作符

  • + 包含,词必须存在
  • - 排出，词必须不出现
  • > 包含，增加等级值
  • < 包含，减少等级值
  • () 把词组成子表达式
  • ~ 取消一个词的排序值
  • * 词尾的通配符
  • "" 定义一个短语

全文本搜索的使用说明

  • 短词被忽略切葱索引排除（3个或3个以下字符的词，可以更改）
  • MySQL自带的内建非用词列表，这些词总是被忽略（可以覆盖）
  • 词出现的频率过高，如果词出现在50%以上的行中，则将其作为一个非用词忽略
  • 行数少于3行，则全文本搜索不返回结果（每个词或者不出现，或者至少出现在50%的行中）
  • 忽略词中的单引号(don't索引为dont)
  • 不具有词分隔符(汉语和日语)的语言不能恰当地返回全文本搜索结果
  • 仅在MyISAM数据库引擎中支持全文本搜索

这个家伙很懒，啥也没有留下😋