MySQL-创建和使用全文索引（FullText）

MySQL5.6后，除了 MyISAM 存储引擎，事务型的 Innodb 存储引擎也支持创建和使用全文索引了。

以下为测试过程：

--创建测试表

CREATE TABLE article (
id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
title VARCHAR(200),
body TEXT
) engine=innodb;

--插入测试值：

insert into article values(null,'MySQL数据库权威指南','非常不错的书籍，值得一看');
insert into article values(null,'Oracle数据库精选','不妨看看');
insert into article values(null,'SQL Servr 数据库进阶','不容错过');
insert into article values(null,'postgreq 数据库进阶','知道了吗');

--创建复合键title,body全文索引（当然也可以创建单键）

create fulltext index ft_idx on article(title,body) with parser ngram; --使用 ngram 解释器

--测试是否能走上全文索引的执行计划

以上所示，确认可以走上全文索引

--执行使用全文索引的 SQL，看是否能搜索到相关数据：

mysql> select * from article where match(title,body) against('精妨');

Empty set (0.00 sec)

如上所示，结果是，搜索不到记录，怎么回呢？

往下看：

因为， mysql 5.7.9 中默认 ngram_token_size 参数的默认值为2，如下：

mysql> show variables like '%token%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| innodb_ft_max_token_size | 84 |
| innodb_ft_min_token_size | 3 |
| ngram_token_size | 2 |
+--------------------------+-------+

ngram_token_size 参数指的是，以ngram解释器搜索全文索引时，指定的搜索字符数最小为2个字符（非字节），但以上SQL

select * from article where match(title,body) against('精妨'); 表示 title like '%精%' and body like '%妨%' 各只有一个字符，所以搜索不到。（意思是和用like 一样，但不能以like方式书写，这样是走不到全文索引的）。

解决方法：

1、修改 ngram_token_size 参数值：

ngram_token_size是静态（只读）参数，必须重启mysql服务，在启动mysql时，指定 --ngram_toke_size=1 的方式启动mysql服务：

[root@dg-st tmp]#mysqld_safe --user=mysql --ngram_token_size=1 &

2、必须要重建全文索引：

create fulltext index ft_idx on article(title,body) with parser ngram;

这时，再执行以上SQL，就可以查到了：

另外一种创建全文索引的方式，可以不使用 with ngram 子句，如下：

create fulltext index ft_idx on article(title,body);

这样创建的全文索引最小和最大搜索字符数（分词）受 ft_min_word_len 和 ft_max_word_len 参数的影响，这两个参数也是静态参数，设置后，也必须要重启 mysq 服务才能生效，如下：

mysql>show variables like 'ft_%';
+--------------------------+----------------+
| Variable_name | Value |
+--------------------------+----------------+
| ft_boolean_syntax | + -><()~*:""&| | --该选项用于全文搜索特殊匹配的表达式
| ft_max_word_len | 84 |
| ft_min_word_len | 4 |
| ft_query_expansion_limit | 20 |
| ft_stopword_file | (built-in) |
+--------------------------+----------------+

所以，搜索时，全文索引的各个字段必须最小包含4个字符才能搜索到，如下：

（完）

秒客网

MySQL-创建和使用全文索引（FullText）

相关文章