MySQL5.7 建立全文索引

1、ngram and MeCab full-text parser plugins

全文检索在MySQL里面很早就支持了，只不过一直以来只支持英文。缘由是他从来都使用空格来作为分词的分隔符，而对于中文来讲，显然用空格就不合适，需要针对中文语义进行分词。但从MySQL 5.7开始，MySQL内置了ngram全文检索插件，用来支持中文分词，并且对MyISAM和InnoDB引擎有效。

2、必要的参数设置

在使用中文检索分词插件ngram之前，先得在MySQL配置文件里面设置他的分词大小（默认是2），比如，

[mysqld]
ngram_token_size=2

分词的SIZE越小，索引的体积就越大，所以要根据自身情况来设置合适的大小。

3、添加全文索引

alter方式

alter table student add fulltext index full_student(clumn1,clumn2) with parser ngram;

或者 

ALTER TABLE `student` ADD FULLTEXT ft_stu_name  (`name`) with parser ngram;

create方式

CREATE FULLTEXT INDEX ft_email_name ON `student` (`name`)  with parser ngram;

也可以在创建索引的时候指定索引的长度：

CREATE FULLTEXT INDEX ft_email_name ON `student` (`name`(20)) with parser ngram;

当然也可以在建表时

CREATE TABLE articles (
id INTUNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
title VARCHAR(200),
body TEXT,
FULLTEXT (title,body) WITH PARSER ngram
) ENGINE=InnoDB CHARACTER SET utf8mb4;

4、查询索引

• 按自然语言搜索模式查询

SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('关键词' IN NATURAL LANGUAGE MODE);

• 按布尔全文搜索模式查询

2.1 匹配既有管理又有数据库的记录

SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('+数据库 +管理' IN BOOLEAN MODE);

2.2匹配有数据库，但是没有管理的记录

SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('+数据库 -管理' IN BOOLEAN MODE);

2.3匹配MySQL，但是把数据库的相关性降低

SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('>数据库 +MySQL' INBOOLEAN MODE);

OK，就不展开讲了，想进一步了解，自行解决。

---

中文分词与全文索引

InnoDB默认的全文索引parser非常合适于Latin，因为Latin是通过空格来分词的。但对于像中文，日文和韩文来说，没有这样的分隔符。一个词可以由多个字来组成，所以我们需要用不同的方式来处理。在MySQL 5.7.6中我们能使用一个新的全文索引插件来处理它们：n-gram parser.

什么是N-gram？

在全文索引中，n-gram就是一段文字里面连续的n个字的序列。例如，用n-gram来对”信息系统”来进行分词，得到的结果如下：

N-gram 例子

1	N=1 : ‘信’, ‘息’, ‘系’, ‘统’;
2	N=2 : ‘信息’, ‘息系’, ‘系统’;
3	N=3 : ‘信息系’, ‘息系统’;
4	N=4 : ‘信息系统’;

如何在InnoDB中使用N-gram Parser？

N-gram parser是默认加载到MySQL中并可以直接使用的。我们只需要在DDL中创建全文索引时使用WITH PARSER ngram。比如，下面的SQL语句在MySQL 5.7.6及更高版本上可以运行。

N-gram DDL示例

mysql > CREATE TABLE articles
(
        FTS_DOC_ID BIGINT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
        title VARCHAR(100),
        FULLTEXT INDEX ngram_idx(title) WITH PARSER ngram
) Engine=InnoDB CHARACTER SET utf8mb4;

Query OK, 0 rows affected (0.06 sec)

mysql> # ALTER TABLE articles ADD FULLTEXT INDEX ngram_idx(title) WITH PARSER ngram;
mysql> # CREATE FULLTEXT INDEX ngram_idx ON articles(title) WITH PARSER ngram;

我们引入了一个新的全局变量叫ngram_token_size。由它来决定n-gram中n的大小，也就是词的大小。它的默认值是2，这个时候，我们使用的是bigram。它的合法的取值范围是1到10。现在，我们很自然会想到一个问题：实际应用中应该如何设置ngram_token_size值的大小呢？当然，我们推荐使用2。但是你也可以通过如下这个简单的规则来可以选择任何合法的值：设置到你希望能查询到的最小的词的大小。如果你想查询到单个字，那么我们需要设置为1。 ngram_token_size的值设置的越小，全文索引占用的空间也越小。一般来说，查询正好等于ngram_token_size的词，速度会更快，但是查询比它更长的词或短语，则会变慢。

N-gram分词处理

N-gram parser和系统默认的全文索引parser有如下不同点：

词大小检查：因为有了ngram_token_size，所以innodb_ft_min_token_size和innodb_ft_max_token_size将不适用于n-gram。
无用词（stopword）处理：通常，对于一个新的词，我们会查找stopwords表，看是否有匹配的词。如果有，这个词就不会加入到全文索引中。但是在n-gram中，我们会查找stopwords表，看是否包含里面的词。这样处理的原因是，在中日韩的文本中，有很多没有意义的字符，词语和标点符号。比如，如果我们把‘的’加入到stopwords表中，那么对于句子‘信息的系统’，在默认情况下我们分词结果为‘信息’，‘系统’。其中‘息的’和‘的系’被过滤掉了。
我们可以通过查询INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE和INFORMATION_SCHEMA.INNODB_FT_TABLE_TABLE来查询哪些词在全文索引里面。这是一个非常有用的调试工具。如果我们发现一个包含某个词的文档，没有如我们所期望的那样出现在查询结果中，那么这个词可能是因为某些原因不在全文索引里面。比如，它含有stopword，或者它的大小小于ngram_token_size等等。这个时候我们就可以通过查询这两个表来确认。下面是一个简单的例子：

简单的调试示例

mysql> INSERT INTO articles (title) VALUES ('信息系统');
Query OK, 1 row affected (0.01 sec)
 

mysql> SET GLOBAL innodb_ft_aux_table="test/articles";
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE;
+--------+--------------+-------------+-----------+--------+----------+
| WORD   | FIRST_DOC_ID | LAST_DOC_ID | DOC_COUNT | DOC_ID | POSITION |
+--------+--------------+-------------+-----------+--------+----------+
| 信息   |            1 |           1 |         1 |      1 |        0 |
| 息系   |            1 |           1 |         1 |      1 |        3 |
| 系统   |            1 |           1 |         1 |      1 |        6 |
+--------+--------------+-------------+-----------+--------+----------+
3 rows in set (0.00 sec)

N-gram查询处理

文本查询（Text Searches）

• 在自然语言模式（NATURAL LANGUAGE MODE下，文本的查询被转换为n-gram分词查询的并集。例如，(‘信息系统’)转换为(‘信息 息系 系统’)。下面一个例子：

自然语言模式示例

mysql> INSERT INTO articles (title) VALUES ('信息系统'), ('信息 系统'), ('信息的系统'), ('信息'), ('系统'), ('息系');
Query OK, 6 rows affected (0.01 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM articles WHERE MATCH (title) AGAINST ('信息系统' IN NATURAL LANGUAGE MODE);
+------------+-----------------+
| FTS_DOC_ID | title           |
+------------+-----------------+
|          1 | 信息系统        |
|          6 | 息系            |
|          2 | 信息 系统       |
|          3 | 信息的系统      |
|          4 | 信息            |
|          5 | 系统            |
+------------+-----------------+
6 rows in set (0.01 sec)

• 在布尔模式（BOOLEAN MODE)，文本查询被转化为n-gram分词的短语查询。例如，(‘信息系统’)转换为(“‘信息 息系 系统’”)。

布尔模式示例

mysql> SELECT * FROM articles WHERE MATCH(title) AGAINST('信息系统' IN BOOLEAN MODE);
+------------+--------------+
| FTS_DOC_ID | title        |
+------------+--------------+
|          1 | 信息系统     |
+------------+--------------+
1 row in set (0.00 sec)

• 通配符查询（Wildcard Searches）
  如果前缀的长度比ngram_token_size小，那么查询结果将返回在全文索引中所有以这个词作为前缀的n-gram的词。

通配符查询示例-1

mysql> SELECT * FROM articles WHERE MATCH (title) AGAINST ('信*' IN BOOLEAN MODE);
+------------+-----------------+
| FTS_DOC_ID | title           |
+------------+-----------------+
|     1      | 信息系统        |
|     2      | 信息 系统       |
|     3      | 信息的系统      |
|     4      | 信息            |
+------------+-----------------+
4 rows in set (0.00 sec)

如果前缀的长度大于等于ngam_token_size，那么这个查询则转换为一个短语（phrase search），通配符则被忽略。例如，(‘信息*’)转换为(‘”信息”‘）,(‘信息系*’)转换为(‘”信息 息系”‘）。

通配符查询示例-2

mysql> SELECT * FROM articles WHERE MATCH (title) AGAINST ('信息*' IN BOOLEAN MODE);
+------------+-----------------+
| FTS_DOC_ID | title           |
+------------+-----------------+
|      1     | 信息系统        |
|      2     | 信息 系统       |
|      3     | 信息的系统      |
|      4     | 信息           |
+------------+-----------------+
4 rows in set (0.00 sec) 

mysql> SELECT * FROM articles WHERE MATCH (title) AGAINST ('信息系*' IN BOOLEAN MODE);
+------------+--------------+
| FTS_DOC_ID | title        |
+------------+--------------+
|          1 | 信息系统     |
+------------+--------------+
1 row in set (0.00 sec)

• 短语查询（Phrase Searches）
  短语查询则被转换为n-gram分词的短语查询。比如，(‘信息系统’)转换为(‘”信息 息系 系统”‘）。

短语查询示例

mysql> SELECT * FROM articles WHERE MATCH (title) AGAINST('"信息系统"' IN BOOLEAN MODE);
+------------+--------------+
| FTS_DOC_ID | title        |
+------------+--------------+
|          1 | 信息系统     |
+------------+--------------+
1 row in set (0.00 sec) 

mysql> SELECT * FROM articles WHERE MATCH (title) AGAINST ('"信息 系统"' IN BOOLEAN MODE);
+------------+---------------+
| FTS_DOC_ID | title         |
+------------+---------------+
|          2 | 信息 系统     |
+------------+---------------+
1 row in set (0.01 sec)

如果您想了解更多关于InnoDB全文索引的详细内容，可以参考用户手册中InnoDB全文索引的部分，还有Jimmy在Dr. Dobb上的精彩文章。如果您想了解更多关于n-gram的详细内容，则可以参考用户手册中n-gram parser的部分。

我们很高兴在MySQL 5.7全文索引中增强对中日韩文的支持，这也是我们工作中很重要的部分，希望这个功能对大家有帮助。如果您有任何问题，可以在本blog中进行评论，提交一个服务需求，或者提交一个bug报告。

以上用例未做完整验证，仅做参考

测试用例：使用 "修改密码"关键字的查询的结果

参考

1： https://blog.csdn.net/yygg329405/article/details/97110984

2： https://blog.csdn.net/qq_33663251/article/details/69612619