Mysql实现全文检索、关键词跑分的方法实例

一、前言

今天一个同事问我，如何使用 Mysql 实现类似于 ElasticSearch 的全文检索功能，并且对检索关键词跑分？我当时脑子里立马产生了疑问？为啥不直接用es呢？简单好用还贼快。但是听他说，数据量不多，客户给的时间非常有限，根本没时间去搭建es，所以还是看一下 Mysql 的全文检索功能吧！

MySQL 从 5.7.6 版本开始，MySQL就内置了ngram全文解析器，用来支持中文、日文、韩文分词。在 MySQL 5.7.6 版本之前，全文索引只支持英文全文索引，不支持中文全文索引，需要利用分词器把中文段落预处理拆分成单词，然后存入数据库。本篇文章测试的时候，采用的 Mysql 5.7.6 ，InnoDB数据库引擎。mysql全文检索

二、全文解析器ngram

ngram就是一段文字里面连续的n个字的序列。ngram全文解析器能够对文本进行分词，每个单词是连续的n个字的序列。
例如，用ngram全文解析器对“你好世界”进行分词:

n=1: '你', '好', '世', '界' 
n=2: '你好', '好世', '世界' 
n=3: '你好世', '好世界' 
n=4: '你好世界'

MySQL 中使用全局变量 ngram_token_size 来配置 ngram 中 n 的大小，它的取值范围是1到10，默认值是 2。通常ngram_token_size设置为要查询的单词的最小字数。如果需要搜索单字，就要把ngram_token_size设置为 1。在默认值是 2 的情况下，搜索单字是得不到任何结果的。因为中文单词最少是两个汉字，推荐使用默认值 2。

咱们看一下Mysql默认的ngram_token_size大小：

show variables like 'ngram_token_size'

Mysql实现全文检索、关键词跑分的方法实例

ngram_token_size 变量的两种设置方式：

1、启动mysqld命令时指定

mysqld --ngram_token_size=2

2、修改mysql配置文件

[mysqld] 
ngram_token_size=2

三、全文索引

以某文书数据为例，新建数据表 t_wenshu ，并且针对文书内容字段创建全文索引，导入10w条测试数据。

Mysql实现全文检索、关键词跑分的方法实例

1、建表时创建全文索引

CREATE TABLE `t_wenshu` (
 `province` varchar(255) DEFAULT NULL,
 `caseclass` varchar(255) DEFAULT NULL,
 `casenumber` varchar(255) DEFAULT NULL,
 `caseid` varchar(255) DEFAULT NULL,
 `types` varchar(255) DEFAULT NULL,
 `title` varchar(255) DEFAULT NULL,
 `content` longtext,
 `updatetime` varchar(255) DEFAULT NULL,
 FULLTEXT KEY `content` (`content`) WITH PARSER `ngram`
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2、通过 alter table 方式

ALTER TABLE t_wenshu ADD FULLTEXT INDEX content_index (content) WITH PARSER ngram;

3、通过 create index 方式

CREATE FULLTEXT INDEX content_index ON t_wenshu (content) WITH PARSER ngram;

四、检索模式

自然语言检索

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

布尔检索

（IN BOOLEAN MODE）剔除一半匹配行以上都有的词，例如，每行都有this这个词的话，那用this去查时，会找不到任何结果，这在记录条数特别多时很有用，原因是数据库认为把所有行都找出来是没有意义的，这时，this几乎被当作是stopword(中断词)；布尔检索模式可以使用操作符，可以支持指定关键词必须出现或者必须不能出现或者关键词的权重高还是低等复杂查询。

   ● IN BOOLEAN MODE的特色：
      ・不剔除50%以上符合的row。
      ・不自动以相关性反向排序。
      ・可以对没有FULLTEXT index的字段进行搜寻，但会非常慢。
      ・限制最长与最短的字符串。
      ・套用Stopwords。
   ● 搜索语法规则：
     +   一定要有(不含有该关键词的数据条均被忽略)。
     -   不可以有(排除指定关键词，含有该关键词的均被忽略)。
     >   提高该条匹配数据的权重值。
     <   降低该条匹配数据的权重值。
     ~   将其相关性由正转负，表示拥有该字会降低相关性(但不像-将之排除)，只是排在较后面权重值降低。
     *   万用字，不像其他语法放在前面，这个要接在字符串后面。
     " " 用双引号将一段句子包起来表示要完全相符，不可拆字。

查询扩展检索

注释：（WITH QUERY EXPANSION）由于查询扩展可能带来许多非相关性的查询，谨慎使用！

五、检索查询

1）查询 content 中包含“盗窃罪”的记录，查询语句如下

select caseid,content, MATCH ( content) AGAINST ('盗窃罪') as score from t_wenshu where MATCH ( content) AGAINST ('盗窃罪' IN NATURAL LANGUAGE MODE)

Mysql实现全文检索、关键词跑分的方法实例

2）查询 content 中包含“寻衅滋事”的记录，查询语句如下

select caseid,content, MATCH ( content) AGAINST ('寻衅滋事') as score from t_wenshu where MATCH ( content) AGAINST ('寻衅滋事' IN NATURAL LANGUAGE MODE) ;

Mysql实现全文检索、关键词跑分的方法实例

3）单个汉字，查询 content 中包含“我”的记录，查询语句如下

select caseid,content, MATCH ( content) AGAINST ('我') as score from t_wenshu where MATCH ( content) AGAINST ('我' IN NATURAL LANGUAGE MODE) ;

Mysql实现全文检索、关键词跑分的方法实例

备注：因为设置的全局变量 ngram_token_size 的值为 2。如果想查询单个汉字，需要在配置文件 my.ini 中修改 ngram_token_size = 1 ，并重启 mysqld 服务，此处不做尝试了。

4）查询字段 content 中包含 “危险驾驶”和“寻衅滋事”的语句如下：

select caseid,content, MATCH (content) AGAINST ('+危险驾驶 +寻衅滋事') as score from t_wenshu where MATCH (content) AGAINST ('+危险驾驶 +寻衅滋事' IN BOOLEAN MODE);

Mysql实现全文检索、关键词跑分的方法实例

5）查询字段 content 中包含 “危险驾驶”，但不包含“寻衅滋事”的语句如下：

select caseid,content, MATCH (content) AGAINST ('+危险驾驶 -寻衅滋事') as score from t_wenshu where MATCH (content) AGAINST ('+危险驾驶 -寻衅滋事' IN BOOLEAN MODE);

Mysql实现全文检索、关键词跑分的方法实例

6）查询字段 conent 中包含“危险驾驶”或者“寻衅滋事”的语句如下：

select caseid,content, MATCH (content) AGAINST ('危险驾驶 寻衅滋事') as score from t_wenshu where MATCH (content) AGAINST ('危险驾驶 寻衅滋事' IN BOOLEAN MODE);

Mysql实现全文检索、关键词跑分的方法实例

六、总结

1）使用 Mysql 全文索引之前，搞清楚各版本支持情况；

2）全文索引比 like + % 快 N 倍，但是可能存在精度问题；

3）如果需要全文索引的是大量数据，建议先添加数据，再创建索引；

4）对于中文，可以使用 MySQL 5.7.6 之后的版本，或者 Sphinx、Lucene 等第三方的插件；

5）MATCH()函数使用的字段名，必须要与创建全文索引时指定的字段名一致，且只能是同一个表的字段不能跨表；

Mysql实现全文检索、关键词跑分的方法实例

相关推荐