Mysql——使用ngram解析器创建全文索引模拟搜索引擎查询

开始之前，先说一下全文索引的版本、存储引擎、数据类型的支持情况 MySQL 5.6 以前的版本，只有 MyISAM 存储引擎支持全文索引； MySQL 5.6 及以后的版本，MyISAM 和 InnoDB 存储引擎均支持全文索引; 只有字段的数据类型为 char、varchar、text 及其系列才可以建全文索引。 为什么要使用ngram解析器？ 内置的MySQL全文解析器使用单词之间的空格作为分隔符来确定单词的开始和结束位置，这在处理不使用单词分隔符的表意文字语言时是一个限制。为了解决这个限制，MySQL提供了一个ngram全文解析器，它支持中文、日文和韩文(CJK)。InnoDB和MyISAM支持ngram全文解析器。 索引需要了解的参数 # 参数名称 默认值 最小值 最大值 作用 1 ft_min_word_len 4 1 3600 MyISAM 引擎表全文索引包含的最小词长度 2 ft_query_expansion_limit 20 0 1000 MyISAM引擎表使用 with query expansion 进行全文搜索的最大匹配数 3 innodb_ft_min_token_size 3 0 16 InnoDB 引擎表全文索引包含的最小词长度 4 innodb_ft_max_token_size 84 10 84 InnoDB 引擎表全文索引包含的最大词长度 5 ngram_token_size 2 1 10 使用ngram解析器创建的索引时，以上四种参数不生效。如果你想通过单个词匹配，将该值设置为1 6 INNODB_FT_DEFAULT_STOPWORD 在InnoDB表上创建FULLTEXT索引所使用的默认停止字表。通俗点就是用里面的词进行索引查询直接返回空 如何理解ngram_token_sizez这个参数？ 例词 '信息系统' 在设置为不同值时的分词情况 　　　　ngram_token_size=1 : '信', '息', '系', '统' 　　　　ngram_token_size=2 : '信息', '息系', '系统'; 　　　　ngram_token_size=3 : '信息系', '息系统'; 　　　　ngram_token_size=4 : '信息系统'; 创建方式全文索引方式 ALTER TABLE user_message ADD FULLTEXT INDEX msg_content_idx (content); //常规创建
ALTER TABLE user_message ADD FULLTEXT INDEX msg_content_idx (content) with PARSER ngram; //ngram创建 使用全文索引查询（非ngram解析器同样适用，不过结果不同，达不到我们想要的效果-例如搜索引擎） select * from user_message where MATCH(content) AGAINST('+服' IN BOOLEAN MODE) //通配符搜索模式

select * from user_message where MATCH(content) AGAINST('服* 生日' IN NATURAL LANGUAGE MODE ) //自然语言搜索模式
 对比下效果,为了方便测试索引参数均设置为1 常规分析器 select * from user_message where MATCH(content) AGAINST('今天你生日' IN NATURAL LANGUAGE MODE) //无法匹配数据
 select * from user_message where MATCH(content) AGAINST('今天你生日啊' IN NATURAL LANGUAGE MODE) //常规解析器支持全文匹配 select * from user_message where MATCH(content) AGAINST('[email protected]' IN NATURAL LANGUAGE MODE)  //支持非中文自然语言匹配/通配符匹配 ngram分析器 select * from user_message where MATCH(content) AGAINST('生日' IN NATURAL LANGUAGE MODE)
select * from user_message where MATCH(content) AGAINST('日生' IN NATURAL LANGUAGE MODE) 以上查询结果一样 部分测试代码 ALTER TABLE user_message ADD FULLTEXT INDEX msg_content_idx (content)  ;


select  * from user_message where content like '生日';

select * from user_message where MATCH(content) AGAINST('dd fff geek996' IN NATURAL LANGUAGE MODE)
select * from user_message where MATCH(content) AGAINST('日 生 别' IN NATURAL LANGUAGE MODE)

drop index msg_content_idx on user_message ; 

SELECT * FROM information_schema.`INNODB_FT_INDEX_TABLE`; 总结:了解ngram之后不经意看到ElsaticSearch ，技术互通性还是挺大的。