Bob的城堡

【转】MySQL - 中文全文检索ngram使用总结

2024/02/02 MySQL 共 3829 字，约 11 分钟
Bob.Zhu

在MySQL 5.7.6之前，全文索引只支持英文全文索引，不支持中文全文索引，需要利用分词器把中文段落预处理拆分成单词，然后存入数据库。

从MySQL 5.7.6开始，MySQL内置了ngram全文解析器，用来支持中文、日文、韩文分词。

本文使用的MySQL 版本是5.7.22，InnoDB数据库引擎。

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。

启用ngram

Bob：我的macOS使用brew安装的mysql8.0，不需要手动开启设置，直接使用即可。

全局变量ngram_token_size的两种设置方法： 1、启动mysqld命令时

mysqld --ngram_token_size=2

2、修改MySQL配置文件

[mysqld]
ngram_token_size=2

创建全文索引

1、创建表的同时创建全文索引

CREATE TABLE articles (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
title VARCHAR (200),
body TEXT,
FULLTEXT (title, body) WITH PARSER ngram
) ENGINE = INNODB;

2、通过 alter table 的方式来添加

ALTER TABLE articles ADD FULLTEXT INDEX ft_index (title,body) WITH PARSER ngram;

3、直接通过create index的方式

CREATE FULLTEXT INDEX ft_index ON articles (title,body) WITH PARSER ngram;

全文检索模式

常用的全文检索模式

自然语言模式(NATURAL LANGUAGE MODE)：

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

BOOLEAN模式(BOOLEAN MODE)：

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

示例

SELECT * FROM articles
WHERE MATCH (title,body)
AGAINST ('一路 一带' IN NATURAL LANGUAGE MODE);

// 不指定模式，默认使用自然语言模式
SELECT * FROM articles
WHERE MATCH (title,body)
AGAINST ('一路 一带');

上面的示例返回结果会自动按照相关性排序，相关性高的在前面。相关性的值是一个非负浮点数，0表示无相关性。

// 获取相关性的值
SELECT id,title,
MATCH (title,body) AGAINST ('手机' IN NATURAL LANGUAGE MODE) AS score
FROM articles
ORDER BY score DESC;

示例：

// 获取匹配结果记录数
SELECT COUNT(*) FROM articles
WHERE MATCH (title,body)
AGAINST ('一路 一带' IN NATURAL LANGUAGE MODE);

// 可以使用BOOLEAN模式执行高级查询。
// 必须包含"腾讯"
SELECT * FROM articles
WHERE MATCH (title,body)
AGAINST ('+腾讯' IN BOOLEAN MODE);
// 必须包含"腾讯"，但是不能包含"通讯工具"
SELECT * FROM articles
WHERE MATCH (title,body)
AGAINST ('+腾讯 -通讯工具' IN BOOLEAN MODE);

下面的例子演示了BOOLEAN模式下运算符的使用方式：

'apple banana' 
无操作符，表示或，要么包含apple，要么包含banana

'+apple +juice'
必须同时包含两个词

'+apple macintosh'
必须包含apple，但是如果也包含macintosh的话，相关性会更高。

'+apple -macintosh'
必须包含apple，同时不能包含macintosh。

'+apple ~macintosh'
必须包含apple，但是如果也包含macintosh的话，相关性要比不包含macintosh的记录低。

'+apple +(>juice <pie)'
查询必须包含apple和juice或者apple和pie的记录，但是apple juice的相关性要比apple pie高。

'apple*'
查询包含以apple开头的单词的记录，如apple、apples、applet。
 
'"some words"'
使用双引号把要搜素的词括起来，效果类似于like '%some words%'，
例如“some words of wisdom”会被匹配到，而“some noise words”就不会被匹配。

注意

  • 只能在类型为CHAR、VARCHAR或者TEXT的字段上创建全文索引。
  • 全文索引只支持InnoDB和MyISAM引擎。
  • MATCH (columnName) AGAINST (‘keywords’)。MATCH()函数使用的字段名，必须要与创建全文索引时指定的字段名一致。如上面的示例，MATCH (title,body)使用的字段名与全文索引ft_articles(title,body)定义的字段名一致。如果要对title或者body字段分别进行查询，就需要在title和body字段上分别创建新的全文索引。
  • MATCH()函数使用的字段名只能是同一个表的字段，因为全文索引不能够跨多个表进行检索。
  • 如果要导入大数据集，使用先导入数据再在表上创建全文索引的方式要比先在表上创建全文索引再导入数据的方式快很多，所以全文索引是很影响TPS的。

全文索引变量值查询

全文索引有几个需要提前设置的变量值，可以用sql语句直接进行查询：

SHOW VARIABLES LIKE 'ft%';

执行后会出现几个变量值得具体数据，例如：

ft_boolean_syntax   + -><()~*:""&|  #改变IN BOOLEAN MODE的查询字符，不用重新启动MySQL也不用重建索引
ft_min_word_len    4                #最短的索引字符串，默认值为4，(通常改为1)修改后必须重建索引文件
ft_max_word_len    84               #最长的索引字符串，默认值为84，修改后必须重建索引文件
ft_query_expansion_limit   20       #查询括展时取最相关的几个值用作二次查询
ft_stopword_file    (built-in)      #全文索引的过滤词文件，具体可以参考：MySQL全文检索中不进行全文索引默认过滤词

关于查询字符ft_boolean_syntax，一般常用的包含下面几个：

“ ”

整体匹配，用双引号将一段字符串包裹，表示查询出来的字段要完全相符，不能分隔，例如“李明”，就是查询出带有 “李明” 这个字符串的相关字段结果，而不是返回包含“李小明”、“李大明”等的结果。

*

通配符，这个只能接在字符串后面。

MATCH (name) AGAINST (‘*小明‘) #错误，不能放前面
MATCH (name) AGAINST (‘+小明*’) #正确

~

表示拥有该字会降低相关性，只是排在较后面

+apple ~macintosh 先匹配apple，但如果同时包含macintosh，就排名会靠后面。

( )

通过括号来使用字条件，找到包含111和222和333，111和222，或者111和333,语法为：

+111 +(>222 <333)

> <

提高与降低相关性

> 提高相关性  <降低相关性
决定查询的结果排得比较靠前还是比较靠后的位置

+

放在词的前面，表示一定要包含该词，并且必须在字符的开始位置

+Apple 匹配：Apple123, “tommy, Apple”

-

不包含该词，所以不能只用「-yoursql」查不到任何row的，必须搭配其他语法使用。

MATCH (name) AGAINST (‘-林11 +张筱雨’)

匹配到：所有不包含林11，但包含张筱雨的记录

空格

表示可选的，包含该词的顺序较高

apple banana 找至少包含上面词中的一个的记录行
+apple +juice 两个词均在被包含
+apple macintosh 包含词 “apple”，但是如果同时包含 “macintosh”，它的排列更高一些
+apple -macintosh 包含 “apple” 但不包含 “macintosh”

参考资料

开始写作吧

![image-alter](/image/post/2024/02/02/01/xxx.png)

文档信息

Search

    Table of Contents