MySQL 全文索引

创建全文索引(FULLTEXT INDEX)

创建表的同时创建全文索引

CREATE TABLE `article` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `title` varchar(255) DEFAULT NULL COMMENT '标题',
  `body` text COMMENT '内容',
  PRIMARY KEY (`id`),
  FULLTEXT KEY `body` (`body`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

通过 ALTER TABLE 的方式来添加

ALTER TABLE `student` 
ADD FULLTEXT INDEX ft_stu_name (`name`)

ft_stu_name是索引名，可以随便起
或者

ALTER TABLE `student` 
ADD FULLTEXT ft_stu_name (`name`)

直接通过CREATE INDEX的方式

CREATE FULLTEXT INDEX ft_email_name ON `student` (`name`)

也可以在创建索引的时候指定索引的长度

CREATE FULLTEXT INDEX ft_email_name ON `student` (`name` (20))

删除全文索引

直接使用 DROP INDEX(注意：没有 DROP FULLTEXT INDEX 这种用法)

DROP INDEX full_idx_name ON tommy.girl ;
使用 ALTER TABLE 的方式

ALTER TABLE tommy.girl DROP INDEX ft_email_abcd;

使用全文索引

跟普通索引稍有不同，使用全文索引的格式： MATCH (columnName) AGAINST (‘string’)

SELECT * FROM `student` WHERE MATCH(`name`) AGAINST('聪')

当查询多列数据时：建议在此多列数据上创建一个联合的全文索引，否则使用不了索引的。

SELECT * FROM `student` WHERE MATCH(`name`,`address`) AGAINST('聪 广东')

使用全文索引需要注意的是：(基本单位是词)
分词，全文索引以词为基础的，MySQL默认的分词是所有非字母和数字的特殊符号都是分词符

MYSQL中与全文索引相关的几个变量

必须通过修改MySQL的配置文件来完成。通常修改最小搜索长度的值为2，首先打开MySQL的配置文件

在 [mysqld]的下面追加
innodb_ft_min_token_size = 2
ft_min_word_len = 2
ngram_token_size = 2
使用命令：mysql> SHOW VARIABLES LIKE 'ft%';ft就是FullText的简写

变量名字 变量值 变量说明
ft_boolean_syntax + -><()~*:""&| 改变IN BOOLEAN MODE的查询字符，不用重新启动MySQL也不用重建索引
ft_min_word_len 4 最短的索引字符串，默认值为4，(通常改为1)修改后必须重建索引文件。重新建立索引命令：repair table tablename quick
ft_max_word_len 84 最长的索引字符串，默认值为84，修改后必须重建索引文件
ft_query_expansion_limit 20 查询括展时取最相关的几个值用作二次查询
ft_stopword_file (built-IN) 全文索引的过滤词文件，具体可以参考：MySQL全文检索中不进行全文索引默认过滤词
使用命令：show variables like "%ngram%"

变量名字 变量值 变量说明
ngram_token_size 2 ngram解析器令牌长度即against()中字符串切分的最小字符长度
特别注意：50%的门坎限制(当查询结果很多，几乎所有记录都有，或者极少的数据，都有可能会返回非所期望的结果)：可用IN BOOLEAN MODE即可以避开50%的限制。
此时使用全文索引的格式就变成了：

SELECT * FROM `student` WHERE MATCH(`name`) AGAINST('聪' IN BOOLEAN MODE)

FT_BOOLEAN_SYNTAX (+ -><()~*:”“&|)使用的例子

• : 用在词的前面，表示一定要包含该词，并且必须在开始位置
  +Apple 匹配：Apple123, “tommy, Apple”
• : 不包含该词，所以不能只用-yoursql这样是查不到任何row的，必须搭配其他语法使用
  MATCH (girl_name) AGAINST (‘-林志玲 +张筱雨’)
  匹配到： 所有不包含林志玲，但包含张筱雨的记录
  空(也就是默认情况)，表示可选的，包含该词的顺序较高
  apple banana 找至少包含上面词中的一个的记录行；
  +apple +juice 两个词均在被包含；
  +apple macintosh 包含词 “apple”，但是如果同时包含 “macintosh”，它的排列将更高一些；
  +apple -macintosh 包含 “apple” 但不包含 “macintosh”。

  :提高该字的相关性，查询的结果会排在比较靠前的位置
  < :降低相关性，查询的结果会排在比较靠后的位置
  " " : 双引号内作为整体不能拆词

• : 通配符，只能接在词后面
  先不使用 ><

select * from article where match(body) against('明天晴天 ' in boolean mode);

可以看到完全匹配的排的比较靠前。

单独使用 >

select * from article where match(body) against('明天晴天 > 好好学习 ' in boolean mode);

单独使用 <

select * from article where match(body) against('明天晴天 < 出去放风筝' in boolean mode);

同时使用><

select * from article where match(body) against('明天晴天 > 阴天了吧 <好好学习 > 喝奶茶去 < 出去放风筝' in boolean mode);
image.png

可以通过括号来使用字条件

+aaa +(>bbb <ccc) : 找到有aaa和bbb和ccc，aaa和bbb，或者aaa和ccc(因为bbb，ccc前面没有+，所以表示可有可无)，然后 aaa&bbb > aaa&bbb&ccc > aaa&ccc

将其相关性由正转负

~ :将其相关性由正转负，表示拥有该字会降低相关性，但不像「-」将之排除，只是排在较后面。
+apple ~macintosh : 先匹配apple，但如果同时包含macintosh，就排名会靠后。

通配符只能接在字符串后面

MATCH (girl_name) AGAINST ('+ABC') : 错误，不能放前面
MATCH (girl_name) AGAINST ('+张筱雨*') : 正确

整体匹配，用双引号将一段句子包起来表示要完全相符，不可拆字

"tommy huang"可以匹配 tommy huang xxxxx 但是不能匹配 tommy is huang