如何启用全文索引
配置文件加入如下内容, 重启数据库
ngram_token_size=2
innodb_ft_max_token_size=8
innodb_ft_min_token_size=1
boolean 搜索操作符
- 包含, 词必须存在
- 排除, 词必须不出现
包含, 而且增加等级值
< 包含, 且减少等级值
() 把词组成子表达式(运行这些子表达式作为一个组被包含, 排除, 排列等)
- 取消一个词的排序值
- 词尾的通配符
“” 定义一个短语(与单词的列表不一样, 它匹配整个短语以便包含或排除这个词语)
实验案例
create table test(
id int(11) not null primary key auto_increment,
name varchar(100) not null comment '工商名',
brand varchar(100) default null comment '省份',
en varchar(100) default null comment '英文名',
fulltext key (name,brand,en) with parser ngram
)engine=innodb default charset=utf8mb4 collate=utf8mb4_bin;
insert into test (name,brand,en) values ('江苏苏州大漠孤烟直有限公司','js','dmgyz');
insert into test (name,brand,en) values ('江苏苏州长河落日圆电子商务有限公司','js','chlry');
insert into test (name,brand,en) values ('白日依山尽 (苏州)科技有限公司','js','brysj');
insert into test (name,brand,en) values ('黄河入海流(苏州)有限公司','js','hhrhl');
insert into test (name,brand,en) values ('江苏欲穷千里目通讯有限公司','js','yqqlm');
insert into test (name,brand,en) values ('更上一层楼江苏畅捷通支付技术有限公司','js','gsycl');
insert into test (name,brand,en) values ('万里悲秋常作客通信息技术股份有限公司','js','wlbqczk');
insert into test (name,brand,en) values ('江苏风急天高猿啸哀科技有限公司','js','fjtgyxa');
insert into test (name,brand,en) values ('中国渚清沙白鸟飞回咨询有限公司','js','zqsbnfh');
insert into test (name,brand,en) values ('南京·无边落木萧萧下有限公司','aa','wblmxxx');
insert into test (name,brand,en) values ('不尽长江滚滚来','js','bjcjggl');
测试
select * from test where match (name,brand,en) against ('电子 信息' IN BOOLEAN MODE); -- 要搜索包含两个单词中至少一个的行
select * from test where match (name,brand,en) against ('+电子 +商务' IN BOOLEAN MODE); -- 要搜索包含两个单词的行
select * from test where match (name,brand,en) against ('+江苏 -科技' IN BOOLEAN MODE); -- 要搜索包含单词1但不包含2的行
select * from test where match (name,brand,en) against ('+江苏 科技' IN BOOLEAN MODE); -- 要搜索包含单词1的行, 但为包含2的行添加更高的排名
select * from test where match (name,brand,en) against ('+江苏 >科技' IN BOOLEAN MODE); -- 要搜索包含单词1的行, 如果它包含单词2则将行排名更高
select * from test where match (name,brand,en) against ('+江苏 <科技' IN BOOLEAN MODE); -- 要搜索包含单词1的行, 如果它包含单词2则将行排名更低
select * from test where match (name,brand,en) against ('+江苏 ~科技' IN BOOLEAN MODE); -- 要搜索包含单词1的行,取消单词2的排序
select * from test where match (name,brand,en) against ('+江苏 +(通讯 电子 支付)' IN BOOLEAN MODE); -- 要搜索包含单词1的行, 子组合里必须包含1个
select * from test where match (name,brand,en) against ('+江苏 -(商务 通讯)' IN BOOLEAN MODE); -- 要搜索包含单词1的行, 子组合里必须不包含
速度测试
select count(*) from ARTICLE; -- 表共 72924 rows 500MB
-- 正则表达式查询 1.997s
select count(*) from ARTICLE
WHERE ((INFO_TITLE LIKE '%长三角%' AND INFO_TITLE REGEXP '一体化|协同发展|经济|贸易|工业|转型'))
OR ((INFO_TEXT LIKE '%长三角%' AND INFO_TEXT REGEXP '一体化|协同发展|经济|贸易|工业|转型'));
-- 正则表达式修改 2.327s
update ARTICLE
set REFERENCE_LABEL = CONCAT(REFERENCE_LABEL, ',长三角一体化')
WHERE ((INFO_TITLE LIKE '%长三角%' AND INFO_TITLE REGEXP '一体化|协同发展|经济|贸易|工业|转型'))
OR ((INFO_TEXT LIKE '%长三角%' AND INFO_TEXT REGEXP '一体化|协同发展|经济|贸易|工业|转型'));
-- 加全文索引
create fulltext index ARTICLE on ARTICLE(INFO_TITLE,INFO_TEXT) with parser ngram;
-- 全文索引查询 0.357s
select count(*) from ARTICLE
where match (INFO_TITLE,INFO_TEXT) AGAINST ('+长三角 +(一体化 协同发展 经济 贸易 工业 转型)' IN BOOLEAN MODE)
-- 全文索引修改 1.076s
update ARTICLE
set REFERENCE_LABEL = CONCAT(REFERENCE_LABEL, ',长三角一体化')
where match (INFO_TITLE,INFO_TEXT) AGAINST ('+长三角 +(一体化 协同发展 经济 贸易 工业 转型)' IN BOOLEAN MODE)
可见: 全文索引速度比正则表达式快的多