在编写sql语句时难免会遇到需要使用 '%xx%' 的场景,但是 '%xx' 会导致索引失效,在数据量比较大的时候会严重影响性能。全文搜索使用match() against ()语法进行,使用该方法可以有效解决上述问题,即在使用索引的情况下进行 '%xx%'。mysql全文搜索有三种模式:自然语言查找,布尔查找,带子查询扩展的自然语言查找,mysql默认模式是自然语言查找,本文也是简单讲讲自然语言查找。
前期mysql数据库全文索引只支持MyISAM,后期版本提升InnoDB也支持了全文索引,这里由于我的mysql版本足够就直接使用的InnoDB了。
DROP TABLE IF EXISTS test_match;CREATE TABLE test_match( `id` VARCHAR(32) NOT NULL COMMENT '', `name` VARCHAR(255) COMMENT '名字', PRIMARY KEY (id)) COMMENT = '测试';insert test_matchvalues (1, 'a abcd efgm'), (2, 'a abcdefgm'), (3, '啊 唐僧和尚 熏悟空啊'), (4, '啊 唐僧和尚熏悟空啊');//这个是改变数据库储存引擎的sql语句,建表之后执行即可ALTER TABLE test_match ENGINE =innodb;//这个是查看当前数据库中表的储存引擎,执行即可show table status;
由于是测试数据,表就是随便建立的,可以看见表中的name字段的值里面有空格,这是由于全文检索是对应外国英语文章的书写格式,每个单词写完后会有一个空格。
全文索引的查询规则导致的中文不适用
直接从现象阐述大家可能看的会更加直观
//创建全文索引create fulltext index ind on test_match (name);//查询name列中包含abcd的数据,即'%xx%'select namefrom test_matchwhere match(name) against('abcd');select namefrom test_matchwhere match(name) against('唐僧和尚');
首先创建一个全文索引,然后执行下面的两条查询语句,可以看见第一条语句查询结果只有id等于1的数据,第二条查询语句只有id为3的数据。
即下面红框中数据
这是因为mysql默认全文解析器是按照外国人的书写格式来进行分割的,外国人的每个单词写完都会有个空格,这符合外国人的书写习惯,但是国人的书写习惯显然不是如此。
为此mysql也内置了ngram全文解析器来解决这个问题,在创建索引时加上 WITH PARSER ngram即可解决问题。此时我们删除建立的索引,然后再重新建立索引。
//删除索引drop index ind on test_match;//重新建立使用ngram全文解析器的全文索引create fulltext index ind on test_match (name) WITH PARSER ngram;
此时我们在执行上面两条查询语句可以看到结果,第一条语句查询出id为1、2的数据,第二条查询语句查询出id为3、4的数据。说明没有使用空格为分隔符。
但是你此时执行下面的前三条语句会发现查询不出结果,但是查询后面两条会查出结果。
select namefrom test_matchwhere match(name) against('唐');select namefrom test_matchwhere match(name) against('a');select namefrom test_matchwhere match(name) against('ab');select namefrom test_matchwhere match(name) against('唐僧');select namefrom test_matchwhere match(name) against('abc');
这是由于在 InnoDB 引擎中,参数 innodb_ft_min_token_size 的默认值是3,innodb_ft_max_token_size的默认值是84。
innodb_ft_min_token_size即每个词语的最小长度
innodb_ft_max_token_size即每个词语的最大长度
将min属性修改为1后上述语句即都可以查询到。
这个配置项,需要在mysql的配置文件中修改。找到my.ini文件(Windows系统),在 [mysqld] 的后面加入一行“innodb_ft_min_token_size=1 ”:看个人需求修改即可
linux和mac,则位置为 /etc/my.cnf 然后重起mysql服务,使配置项生效。
有可能还需要重建一下索引,SQL语句:REPAIR TABLE table_name
QUICK;
本文纯属个人见解,仅供参考,如见解不同,欢迎联系我纠正文章错误
后期有空还会更新下全文搜索的三种模式;
来源地址:https://blog.csdn.net/L1511768722/article/details/128533983