[MySQL全文搜索教程2]中文语义分割搜索、配置ngram解析器ngram_token_size、innodb_ft_min_token_size、innodb_ft_min_token_size

MySQL全文索引：中文语义分词检索（相似度匹配）

原文链接：https://blog.****.net/qq_39702981/article/details/125141024

通常情况下，全文检索引擎我们一般会用ES组件（传送门：SpringBoot系列——ElasticSearch），但不是所有业务都有那么大的数据量、那么大的并发要求，MySQL5.7之后内置了ngram分词器，支持中文分词，使用全文索引，即可实现对中文语义分词检索

全文索引说明

MySQL支持全文索引和搜索：

• MySQL中的全文索引是FULLTEXT类型的索引。

• 全文索引只能用于InnoDB或MyISAM表，并且只能为CHAR、VARCHAR或TEXT列创建。

• MySQL5.7提供了一个内置的全文ngram解析器，支持中文，日文和韩文(CJK)，以及一个可安装的MeCab日文全文解析器插件。

  • FULLTEXT索引定义可以在创建表时在CREATE TABLE语句中给出，
  • 也可以稍后使用ALTER TABLE或CREATE INDEX添加。

• 对于大型数据集，将数据加载到一个没有FULLTEXT索引的表中，然后在此之后创建索引，

  • 比将数据加载到一个已有FULLTEXT索引的表中要快得多。(每次加数据 都会变动索引，不如先加数据，在创建索引)

MySQL全文检索官方文档介绍：https://dev.mysql.com/doc/refman/5.7/en/fulltext-search.html

查看MySQL版本

-- 查看mysql版本
select VERSION()

创建表 并插入数据

CREATE TABLE `game`  (
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '游戏名'
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

插入

-- 关闭自动提交，mysql每次执行一条语句都默认自动提交，去掉自动提交可大大提高批量操作性能
SET autocommit = 0;
 
INSERT INTO `game`(`name`) VALUES ('空穴');
INSERT INTO `game`(`name`) VALUES ('全面战争传奇：大不列颠王座');
INSERT INTO `game`(`name`) VALUES ('仙剑奇侠传2');
INSERT INTO `game`(`name`) VALUES ('阿提拉：全面战争');
INSERT INTO `game`(`name`) VALUES ('正当防卫1-4');
INSERT INTO `game`(`name`) VALUES ('苍翼默示录：交叉组队战');
INSERT INTO `game`(`name`) VALUES ('羊肚菌：狩猎游戏');
INSERT INTO `game`(`name`) VALUES ('孤岛危机3');
INSERT INTO `game`(`name`) VALUES ('刀剑神域：虚空断章');
INSERT INTO `game`(`name`) VALUES ('狙击手1-3');
INSERT INTO `game`(`name`) VALUES ('鬼武者HD复刻版');
INSERT INTO `game`(`name`) VALUES ('突袭4/3/2/1');
INSERT INTO `game`(`name`) VALUES ('露露亚的工作室：雅兰德的炼金术士4');
INSERT INTO `game`(`name`) VALUES ('网吧模拟器');
INSERT INTO `game`(`name`) VALUES ('战地3/战地风云3/BF3');
INSERT INTO `game`(`name`) VALUES ('口袋妖怪合集/精灵宝可梦合集/PC模拟器版');
INSERT INTO `game`(`name`) VALUES ('使命召唤9');
INSERT INTO `game`(`name`) VALUES ('轩辕剑7');
INSERT INTO `game`(`name`) VALUES ('遗迹：灰烬重生');
INSERT INTO `game`(`name`) VALUES ('逃生1/2 两部');
INSERT INTO `game`(`name`) VALUES ('暗黑破坏神2重制版');
INSERT INTO `game`(`name`) VALUES ('不义联盟2传奇版/单人.多人同屏');
INSERT INTO `game`(`name`) VALUES ('狙击精英：纳粹僵尸部队2');
INSERT INTO `game`(`name`) VALUES ('异星探险家：网络联机版/星球探索者网络联机');
INSERT INTO `game`(`name`) VALUES ('噬血代码');
INSERT INTO `game`(`name`) VALUES ('拳皇14');
INSERT INTO `game`(`name`) VALUES ('*飞车17：最高通缉 ');
INSERT INTO `game`(`name`) VALUES ('*飞车11：街道争霸 中文版');
INSERT INTO `game`(`name`) VALUES ('古墓丽影10：崛起/古墓丽影崛起');
INSERT INTO `game`(`name`) VALUES ('英雄无敌2');
INSERT INTO `game`(`name`) VALUES ('流浪汉模拟器/Bum Simulator');
INSERT INTO `game`(`name`) VALUES ('逃离塔科夫');
INSERT INTO `game`(`name`) VALUES ('战地5（战地风云5／BF5＋1）');
INSERT INTO `game`(`name`) VALUES ('浮岛物语');
INSERT INTO `game`(`name`) VALUES ('尘埃3/DiRT 3');
INSERT INTO `game`(`name`) VALUES ('古剑奇谭1');
INSERT INTO `game`(`name`) VALUES ('剑侠情缘单机版合集');

 
-- 开启自动提交
SET autocommit = 1;
 
insert into插入数据

全文索引

可以在mysql的配置文件my.ini中修改ngram的分词数（默认2）

ngram_token_size=2

show variables like'%token%';

-- 在mysqld下面添加 ngram_token_size=1

repair table table_name quick; -- 重新构建索引

,当标记大小为2时,ngram解析器将字符串 “abc def” 解析为四个标记:“ab”,“bc”,“de” 和“ef”

需要重启服务器并重新构建FULLTEXT索引

SHOW VARIABLES LIKE 'ft%'; #ft就是FullText的简写。这个也改为1

[mysqld]，在其下方加入ft_min_word_len=1，这样就把最小长度改为了1
ft_min_word_len=1 最短索引字符串，也要修改为1

ft_boolean_syntax    + -><()~*:""&|         
#改变IN BOOLEAN MODE的查询字符，不用重新启动MySQL也不用重建索引
ft_min_word_len    4                                   
#最短的索引字符串，默认值为4，(通常改为1)修改后必须重建索引文件
重新建立索引命令：repair table tablename quick 

ft_max_word_len    84                                
#最长的索引字符串，默认值为84，修改后必须重建索引文件

ft_query_expansion_limit   20                      
#查询括展时取最相关的几个值用作二次查询

ft_stopword_file    (built-in)                      
#全文索引的过滤词文件，具体可以参考：MySQL全文检索中不进行全文索引默认过滤词

创建
示例：为game表的name字段创建全文索引

-- 创建全文索引
CREATE FULLTEXT INDEX ft_index ON game (name) WITH PARSER ngram

使用

• match XX against

MATCH (col1,col2,...) AGAINST (expr [search_modifier])
 
search_modifier:
  {
       IN NATURAL LANGUAGE MODE
     | IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION
     | IN BOOLEAN MODE
     | WITH QUERY EXPANSION
  }

-- 默认使用自然语言模式：IN NATURAL LANGUAGE MODE -- '刀剑危机'，分词结果：'刀剑'、'危机' 

select name from game WHERE MATCH (name) AGAINST ('刀剑危机')

生化危机5：黄金版
孤岛危机弹头
水晶危机

-- 使用布尔模式：IN BOOLEAN MODE，
-- +和-操作符分别指示一个单词必须存在或不存在，才能进行匹配，'危机*'，则表示匹配危机开头的记录
-- +危机* 和 上面的结果一样

select name from game WHERE MATCH (name) AGAINST ('+危机 -孤岛' IN BOOLEAN MODE)

魔铁危机/钢铁危机
生化危机启示录2
生化危机4：终极高清版
生化危机：浣熊市行动

删除

-- 查询某个表中的索引
show index from game

-- 删除索引
drop index ft_index on game

后记

通过全文索引、配合ngram全文解析器，可以实现对中文语义分词检索，在数据量不大、并发要求不高的情况下足够满足我们业务需要，无需上ES全文检索引擎

整理

• 修改了 mysql my.ini配置后

[mysqld]
ft_min_word_len=1
ngram_token_size=1

• 创建表

CREATE TABLE `game2` (
  `name` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL COMMENT '游戏名',
  FULLTEXT KEY `ft_index` (`name`) /*!50100 WITH PARSER `ngram` */ 
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 ROW_FORMAT=COMPACT;

• 插入数据

1
2
1,2
1,2,3

• 即可实现，全文检索查询。多选，模糊查询，任选其一 即可查出结果

select name from game2 WHERE MATCH (name) AGAINST ('1,2')

教程2

https://c4ys.com/archives/2098

数据库配置

• innodb_ft_min_token_size 也可以设置为1

[mysqld]
ngram_token_size=1 # 如果需要搜索一个字，需要改为1

innodb_ft_min_token_size

默认3，表示最小3个字符作为一个关键词，增大该值可减少全文索引的大小

innodb_ft_max_token_size

默认84，表示最大84个字符作为一个关键词，限制该值可减少全文索引的大小

ngram_token_size

默认2，表示2个字符作为内置分词解析器的一个关键词，如对“abcd”建立全文索引，关键词为’ab’，’bc’，’cd’

一般来说，查询正好等于ngram_token_size的词，速度会更快，但是查询比它更长的词或短语，则会变慢

如果需要搜索一个字，需要改为1

注意 这三个参数均不可动态修改，修改了这些参数，需重启MySQL服务，并重新建立全文索引

建立数据库

CREATE TABLE `article` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(200) DEFAULT NULL,
  `body` text,
  PRIMARY KEY (`id`),
  FULLTEXT KEY `ft_idx` (`title`,`body`) WITH PARSER ngram
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

INSERT INTO `test`.`article` (`id`, `title`, `body`) VALUES ('1', 'MySQL数据库权威指南', '非常不错的书籍，值得一看');
INSERT INTO `test`.`article` (`id`, `title`, `body`) VALUES ('2', 'Oracle数据库精选', '不妨看看');
INSERT INTO `test`.`article` (`id`, `title`, `body`) VALUES ('3', 'SQL Servr 数据库进阶', '不容错过');
INSERT INTO `test`.`article` (`id`, `title`, `body`) VALUES ('4', 'postgreq 数据库进阶', '知道了吗');

开始搜索

SELECT * FROM article WHERE MATCH(title,body) AGAINST ('精选 值得')
SELECT * FROM article WHERE MATCH(title,body) AGAINST ('精选 值得' IN NATURAL LANGUAGE MODE))
SELECT * FROM article WHERE MATCH(title,body) AGAINST ('精选 值得' IN BOOLEAN MODE))

1、自然语言模式(NATURAL LANGUAGE MODE)

自然语言模式是MySQL 默认的全文检索模式。自然语言模式不能使用操作符，不能指定关键词必须出现或者必须不能出现等复杂查询。

2、BOOLEAN模式(BOOLEAN MODE)

BOOLEAN模式可以使用操作符，可以支持指定关键词必须出现或者必须不能出现或者关键词的权重高还是低等复杂查询。

游戏保存

-- 关闭自动提交，mysql每次执行一条语句都默认自动提交，去掉自动提交可大大提高批量操作性能
SET autocommit = 0;
 
INSERT INTO `game`(`name`) VALUES ('空穴');
INSERT INTO `game`(`name`) VALUES ('全面战争传奇：大不列颠王座');
INSERT INTO `game`(`name`) VALUES ('仙剑奇侠传2');
INSERT INTO `game`(`name`) VALUES ('阿提拉：全面战争');
INSERT INTO `game`(`name`) VALUES ('正当防卫1-4');
INSERT INTO `game`(`name`) VALUES ('苍翼默示录：交叉组队战');
INSERT INTO `game`(`name`) VALUES ('羊肚菌：狩猎游戏');
INSERT INTO `game`(`name`) VALUES ('孤岛危机3');
INSERT INTO `game`(`name`) VALUES ('刀剑神域：虚空断章');
INSERT INTO `game`(`name`) VALUES ('狙击手1-3');
INSERT INTO `game`(`name`) VALUES ('鬼武者HD复刻版');
INSERT INTO `game`(`name`) VALUES ('突袭4/3/2/1');
INSERT INTO `game`(`name`) VALUES ('露露亚的工作室：雅兰德的炼金术士4');
INSERT INTO `game`(`name`) VALUES ('网吧模拟器');
INSERT INTO `game`(`name`) VALUES ('战地3/战地风云3/BF3');
INSERT INTO `game`(`name`) VALUES ('口袋妖怪合集/精灵宝可梦合集/PC模拟器版');
INSERT INTO `game`(`name`) VALUES ('使命召唤9');
INSERT INTO `game`(`name`) VALUES ('轩辕剑7');
INSERT INTO `game`(`name`) VALUES ('遗迹：灰烬重生');
INSERT INTO `game`(`name`) VALUES ('逃生1/2 两部');
INSERT INTO `game`(`name`) VALUES ('暗黑破坏神2重制版');
INSERT INTO `game`(`name`) VALUES ('不义联盟2传奇版/单人.多人同屏');
INSERT INTO `game`(`name`) VALUES ('狙击精英：纳粹僵尸部队2');
INSERT INTO `game`(`name`) VALUES ('异星探险家：网络联机版/星球探索者网络联机');
INSERT INTO `game`(`name`) VALUES (