MySQL的全文检索

• 1 简介
• 2 案例
• • 2.1 启用全文索引
  • • 2.1.1 查看并启用表的存储机制类型（Navicat）：
    • 2.1.2 查看并启用表的存储机制类型（CMD）：
    • 2.1.3 接下来的设置
  • 2.2 进行全文索引
  • • 2.2.1 传统模式(`LIKE`)：
    • 2.2.2 全文索引模式（`MATCH AGAINST` + `WHERE`）：
    • 2.2.3 全文索引模式（匹配优先级）：
    • 2.2.4 布尔全文搜索（重要）
    • • 2.2.4.1 基本应用
      • 2.2.4.2 `+` 的应用
      • 2.2.4.3 `-` 的应用
      • 2.2.4.4 `<` 和 `>` 的应用
      • 2.2.4.5 `()` 的应用
      • 2.2.4.6 其他的话

1 简介

虽然之前学过的搜索机制非常有用，但存在几个重要的限制（通配符和正则表达式，WHERE和LIKE）：

• 性能——通配符和正则表达式匹配通常要求MySQL尝试匹配表中所有行（而且这些搜索极少使用表索引）。因此，由于被搜索行数不断增加，这些搜索可能非常耗时。（大量大概可以定义为10G以上）
• 明确控制——使用通配符和正则表达式匹配，很难（而且并不总是能）明确地控制匹配什么和不匹配什么。例如，指定一个词必须匹配，一个词必须不匹配，而一个词仅在第一个词确实匹配的情况下才可以匹配或者才可以不匹配。

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

存储机制	全文索引	类型	适用于
Innodb	不支持	事务性	业务表（比如用于模糊查询）
MyISAM	支持	数据型	数据表
productnote	支持	数据型	数据表

2 案例

2.1 启用全文索引

注意：不要将业务类的主要表开启MyISAM！

2.1.1 查看并启用表的存储机制类型（Navicat）：

2.1.2 查看并启用表的存储机制类型（CMD）：

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 ;		# 在这里设置

2.1.3 接下来的设置

首先：全文索引的数据类型必须是TEXT类型！不能是 char 或者 varchar 类型的！

必须是上图 TEXT 类的数据，才可以开启下图的 FULLTEXT 的全文索引

2.2 进行全文索引

传统模糊查询方法：like 和 regex
全文索引模糊查询方法：MATCH AGAINST

2.2.1 传统模式(LIKE)：

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

2.2.2 全文索引模式（MATCH AGAINST + WHERE）：

select note_text from productnotes
where Match (note_text) AGAINST ('rabbit') ;

2.2.3 全文索引模式（匹配优先级）：

注：先出现关键字的，优先级别高
注：MATCH AGAINST 写在 from 前时， MATCH AGAINST 不作为筛选条件，而是显示为优先级（下图第二列）
注：MATCH AGAINST 写在 from 后时， MATCH AGAINST 作为筛选条件

select note_text, MATCH(note_text) AGAINST ('rabbit') from productnotes

2.2.4 布尔全文搜索（重要）

布尔操作符	含义
+	必须包含
-	排除
>	包含 增加等级
<	包含 降低等级
()	选择其一
*	词尾匹配符
""	定义短句

2.2.4.1 基本应用

在 productnotes 表中用 BOOLEAN 模式查询所有包含 heavy 的 note_text 字段：

select note_text from productnotes
where MATCH (note_text) 
AGAINST ('heavy' IN BOOLEAN MODE) ;

2.2.4.2 + 的应用

在 productnotes 表中用 BOOLEAN 模式查询所有包含 rabbit 且包含 bait 的 note_text 字段：

select note_text  from productnotes
where MATCH (note_text) 
AGAINST ('+rabbit +bait' IN BOOLEAN MODE) ;

2.2.4.3 - 的应用

在 productnotes 表中用 BOOLEAN 模式查询所有包含 heavy 且不包含 rope 的 note_text 字段：

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

2.2.4.4 < 和 > 的应用

在 productnotes 表中用 BOOLEAN 模式查询所有包含 降低优先级的 combination 的 note_text 字段：

注：MATCH AGAINST 写在 from 前时， MATCH AGAINST 不作为筛选条件，而是显示为优先级
注：MATCH AGAINST 写在 from 后时， MATCH AGAINST 作为筛选条件

select note_text, MATCH(note_text) 
AGAINST ('+safe +(<combination)' IN BOOLEAN MODE)  from productnotes	# 这里的 MATCH AGAINST 显示为结果中的优先
where MATCH(note_text) 
AGAINST ('+safe +(<combination)' IN BOOLEAN MODE) ;		#这里的 MATCH AGAINST 是筛选的作用

2.2.4.5 () 的应用

在 productnotes 表中用 BOOLEAN 模式查询所有包含 safe 且包含 (combination 或 accepted) 的 note_text 字段：

select note_text, MATCH(note_text) 
AGAINST ('+safe +(combination accepted)' IN BOOLEAN MODE)  from productnotes	# 这里的 MATCH AGAINST 显示为结果中的优先级字段
where MATCH(note_text) 
AGAINST ('+safe +(combination accepted)' IN BOOLEAN MODE) ;		#这里的 MATCH AGAINST 是筛选的作用

全文搜索结果不要优先级的话，可以简化成：

select note_text from productnotes
where MATCH(note_text) 
AGAINST ('+safe +(combination accepted)' IN BOOLEAN MODE) ;

2.2.4.6 其他的话

BOOLEAN 搜索其实也运用在百度搜索上，好奇的小伙伴可以去查查用法，很实用，推荐。