MySQL 之全文索引--fulltext

https://blog.csdn.net/mrzhouxiaofei/article/details/79940958

通过数值比较、范围过滤等就可以完成绝大多数我们需要的查询，但是，如果希望通过关键字的匹配来进行查询过滤，那么就需要基于相似度的查询，而不是原来的精确数值比较。全文索引就是为这种场景设计的。
like + % 在文本比较少时是合适的，全文索引在大量的数据面前，能比 like + % 快 N 倍，速度不是一个数量级，但是全文索引可能存在精度问题。

1、创建表的时候创建索引

// 创建全文索引
CREATE TABLE table (
	id INT(11) NOT NULL AUTO_INCREMENT,
	content TEXT NOT NULL,
	tag VARCHAR(255) NULL DEFAULT NULL,
	PRIMARY KEY (id),
	FULLTEXT INDEX contentft (content)
)

当查询多列数据时：，要在多列数据上创建一个联合的全文索引

// 创建 联合全文索引
CREATE TABLE table_b (
	id INT(11) NOT NULL AUTO_INCREMENT,
	content TEXT NOT NULL,
	tag VARCHAR(255) NULL DEFAULT NULL,
	PRIMARY KEY (id),
	FULLTEXT INDEX content_tag_fulltext (content, tag)
)

2、在已存在的表上创建索引

CREATE TABLE table_c (
	id INT(11) NOT NULL AUTO_INCREMENT,
	content TEXT NOT NULL,
	tag VARCHAR(255) NULL DEFAULT NULL,
	PRIMARY KEY (id)
)
（1）CREATE FULLTEXT INDEX fulltext_tag ON table_c(tag)
（2）ALTER TABLE table_c ADD FULLTEXT INDEX fulltext_tag(tag)

3、删除索引

（1）DROP INDEX fulltext_tag ON table_c
（2）ALTER TABLE table_c DROP INDEX fulltext_tag

4、查询

全文索引

SELECT * FROM table_a WHERE MATCH(content) AGAINST('aa')

查询不到结果，

SELECT * FROM table_a WHERE MATCH(content) AGAINST('aaa')

查询的到结果

最小搜索长度，最大搜索长度

SHOW VARIABLES LIKE '%ft%'

MyISAM

可以进行修改，
在 MySQL 的配置文件 /etc/my.cnf，在 [mysqld] 的下面追加以下内容：
[mysqld]
innodb_ft_min_token_size = 1
ft_min_word_len = 1
然后重启 MySQL 服务器，并修复全文索引。不然参数不会生效。
两种修复方式，可以使用下面的命令修复

REPAIR TABLE table_a QUICK 

或者直接删掉重新建立索引
联合全文索引查询

SELECT * FROM table_b WHERE MATCH(content,tag) AGAINST ('aa aaa')

无效词（停止词）50%的门坎限制

stopword
在全文索引中，如果一个词被认为是太普通或者太没价值，那么它将会被搜索索引和搜索查询忽略。innodb和myisam分别有两组不同的设置，控制着对应的stopword。
使用IN BOOLEAN MODE可避开50%的限制

自然语言的全文索引

在整个索引中出现次数越少的词语，匹配时的相关度就越高。相反，非常常见的单词将不会被搜索，如果一个词语的在超过 50% 的记录中都出现了，那么自然语言的搜索将不会搜索这类词语。

布尔全文索引

• 必须包含该词

• 必须不包含该词，不能只用「-yoursql」这样是查不到任何row的，必须搭配其他语法使用。
  MATCH (girl_name) AGAINST (‘-林志玲 +张筱雨’)
  匹配到： 所有不包含林志玲，但包含张筱雨的记录

提高该词的相关性，查询的结果靠前
< 降低该词的相关性，查询的结果靠后
(*)星号 通配符，只能接在词后面

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

C:\Windows\System32\cmd.exe
C:\ProgramData\MySQL\MySQL Server 5.7\my.ini
innodb_ft_min_token_size = 1
ft_min_word_len = 1