MySQL全文索引:中文语义分词检索(相似度匹配)
前言
项目需求是查询时实现分词检索,一般情况下我们一般会用ES组件,但是当前业务数据量不大、没有很高的并发要求,最终选择用MySQL内置的ngram分词器。ngram分词器支持中文分词使用全文索引,即可实现对中文语义分词检索。MySQL5.6之后版本支持中文分词检索。以 %关键字% 进行模糊查询结果的,不能查询多个列必须手动添加条件以实现,效率不高。
MATCH (col1,col2,…) AGAINST (expr [search_modifier])
说明:
1.MySQL全文索引类型是FULLTEXT
2.全文索引只能用于InnoDB或MyISAM表,并且只能为CHAR、VARCHAR或TEXT列创建。
3.MySQL5.7提供了一个内置的全文ngram解析器,支持中文,日文和韩文(CJK),以及一个可安装的MeCab日文全文解析器插件。
4.FULLTEXT索引定义可以在创建表时在CREATE TABLE语句中给出,也可以稍后使用ALTER TABLE或CREATE INDEX添加。
5.对于大型数据集,将数据加载到一个没有FULLTEXT索引的表中,然后在此之后创建索引,比将数据加载到一个已有FULLTEXT索引的表中要快得多。
官方文档:https://dev.mysql.com/doc/refman/5.7/en/fulltext-search.html
使用
创建索引
create table articles (
id int unsigned auto_increment not null primary key,
title varchar(255) character set utf8 collate utf8_general_ci not null comment ‘标题’,
text varchar(255) character set utf8 collate utf8_general_ci not null comment ‘文本’,
fulltext(title, text) with parser ngram) engine = InnoDB character set utf8 collate utf8_general_ci;
create fulltext index title on articles(title, text) with parser ngram;
查询
insert into articles(title,text) values
(‘文档是什么’,'1.文档是一种对软件系统的书面描述。 2.文档应当精确地描述软件系统。 3.软件文档是软件工程师之间用作沟通交流的一种方式。 '),
(‘如何写好文档’,'1.确定文档主题。 2.文章标题。 3.文章内容。4.文章排版。 '),
(‘文档模版’,'1.word模版。 2.excel模版。 3.ppt模版。 '),
(‘文档管理’,'1.word管理。 2.excel管理。 3.ppt管理。 '),
(‘文档基本操作’,'1.word操作。 2.excel操作。 3.ppt操作。 ');
select * from articles where match(title,text) against (‘文档’);
相关性排序
select *, match(title,text) against (‘文档’) score from articles where match(title,text) against (‘文档’) order by score desc;
ngram的分词数
可以在mysql的配置文件my.ini中修改ngram的分词数ngram_token_size(默认2)
例如: “abcd”
ngram_token_size=1 : ‘a’, ‘b’, ‘c’, ‘d’
ngram_token_size=2 : ‘ab’, ‘bc’, ‘cd’
ngram_token_size=3 : ‘abc’, ‘bcd’
ngram_token_size=4 : ‘abcd’
删除索引
drop index title on articles