项目数据库中遇到一个情况，有一个字段存储的是经销厂商在工商局注册时的（官方）全称，但是用户在查询这个字段时，很多时候都会使用到各种各样的简称别称。于是，对于该字段，希望能够实现类似于搜索引擎的查询。这种情况下模糊查询和正则表达式有点捉襟见肘了，只能寄希望于其他特性。我们用的数据库是MySQL，后来查了查文档，经过测试，借助MySQL的全文检索（Full-Text Search）和相应的中文解析插件ngram解决这一问题。
在具体介绍MySQL的Full-Text Search和中文解析插件ngram之前，先聊一聊关于自然语言的话题，MySQL在实现解析时将其分成两大语系：第一种是use word delimiter language，这类语言单词与单词之间有天然的分隔符，例如英语；第二种是ideographic language，字词之间没有分隔符，其代表是汉语、日语和韩语（MySQL官方将其统称为CJK：Chinese、Japanese、Korean）。因为个人只懂中文，学习过英语和日语，所以不能拿其他语言来作例证，这一部分关于自然语言划分的内容只是看了MySQL的Reference Manual后受到启发而自己设想的结论。
对于第一种语系，MySQL解析起来非常简单，只要以空格（white space）作为界定符（delimiter）来进行分词（tokenize）操作即可。这也是MySQL最初内置的全文检索。MySQL5.6以前只能用于MyISAM表，5.6版本时InnoDB存储引擎也实现并扩展了这种全文检索。第二种CJK语系没有分隔符，即MySQL原始无法自动分词，所以需要一种新的实现来进行Full-Text Search，这就是前文提到的ngram插件，由MySQL5.7.6开始引入。
Full-Text Search全文检索是通过对字段添加全文索引（FULLTEXT INDEX），然后将索引的每条记录（document）的文本内容进行分词，并将分好的单词（word）记入辅助表（auxiliary table）来实现的。auxiliary table记录了单词与FULLTEXT index记录行的映射。`INFORMATION_SCHEMA`.`INNODB_FT_INDEX_CACHE`系统表记录了相关分词信息。设置参数“innodb_ft_aux_table”可以查看该表中的记录。
mysql> CREATE TABLE `player`.`articles` (
-> `FTS_DOC_ID` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
-> `title` VARCHAR(200) NULL DEFAULT NULL,
-> `body` TEXT NULL,
-> PRIMARY KEY (`FTS_DOC_ID`),
-> FULLTEXT INDEX `title` (`title`, `body`)
-> )
-> ENGINE=InnoDB
-> ;
Query OK, 0 rows affected (0.25 sec)
mysql> INSERT INTO `player`.`articles` (`title`, `body`) VALUES
-> ('MySQL Tutorial','DBMS stands for DataBase ...'),
-> ('How To Use MySQL Well','After you went through a ...'),
-> ('Optimizing MySQL','In this tutorial we will show ...'),
-> ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
-> ('MySQL vs. YourSQL','In the following database comparison ...'),
-> ('MySQL Security','When configured properly, MySQL ...');
Query OK, 6 rows affected (0.02 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql> SET GLOBAL innodb_ft_aux_table="player/articles";
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM `INFORMATION_SCHEMA`.`INNODB_FT_INDEX_CACHE` ORDER BY `doc_id`, `position` LIMIT 8;
+----------+--------------+-------------+-----------+--------+----------+
| WORD | FIRST_DOC_ID | LAST_DOC_ID | DOC_COUNT | DOC_ID | POSITION |
+----------+--------------+-------------+-----------+--------+----------+
| mysql | 1 | 6 | 6 | 1 | 0 |
| tutorial | 1 | 3 | 2 | 1 | 6 |
| dbms | 1 | 1 | 1 | 1 | 15 |
| stands | 1 | 1 | 1 | 1 | 20 |
| database | 1 | 5 | 2 | 1 | 31 |
| use | 2 | 2 | 1 | 2 | 7 |
| mysql | 1 | 6 | 6 | 2 | 11 |
| well | 2 | 2 | 1 | 2 | 17 |
+----------+--------------+-------------+-----------+--------+----------+
8 rows in set (0.01 sec)在以上DDL建表SQL中，有一个特殊的字段“FTS_DOC_ID”。InnoDB存储引擎固定使用该字段与记录行中的每个单词（一对多）映射，类型必须为“BIGINT UNSIGNED NOT NULL”，并且为其主动加上一个UNIQUE INDEX。所以，InnoDB存储引擎的Full-Text Search表一定会有一个“FTS_DOC_ID”字段，如果在建表时没有定义该字段，MySQL会隐式地主动添加该字段。因此，全文检索表中的“FTS_DOC_ID”字段直接对应`INNODB_FT_INDEX_CACHE`系统表中的“DOC_ID”列。
- WORD：分词出来的单词。
- FIRST_DOC_ID：该“WORD”首次出现的“DOC_ID”。
- LAST_DOC_ID：该“WORD”最后一次次出现的“DOC_ID”。
- DOC_COUNT：该“WORD”在多少行记录（多少个“DOC_ID”）中存在。
- DOC_ID：对应全文检索表中的“FTS_DOC_ID”字段（显式或隐式创建），映射该“WORD”属于哪行记录。
- POSITION。该“WORD”在该“DOC_ID”行的位置（字节）。
上面的查询为了简约展示只返回了8条记录，包含DOC_ID=1，即第一行记录的“完整”分词，而第二行记录的信息只显示了部分。但是对比发现，第一行中的单词“for”并没有记入分词，如果展示完整的查询结果，会发现几乎每行记录都存在这种“弃词”的情况。这里涉及到stopword list，在tokenize时，stopword list中的word会直接忽略。在英语中，常见的像“a”、“an”和“the”等词语并没有实际的涵义，因此将其记入分词并没有多大的意义，只是增加了索引的大小而降低了查询的性能。InnoDB存储引擎默认的stopword记录在`INFORMATION_SCHEMA`.`INNODB_FT_DEFAULT_STOPWORD`系统表，默认包含36个stopword。除此之外，MySQL系统参数“innodb_ft_min_token_size”、“innodb_ft_max_token_size”和“ft_min_word_len”、“ft_max_word_len”用来控制进行tokenize时word的长度区间，不在参数设置区间段内的word也无法全文检索。前两个参数控制InnoDB表，后两个参数控制MyISAM表，它们都是静态参数（需重启MySQL服务器来修改）。
Full-Text Search的查询语句如下：MATCH()指定被查询的字段（全文索引列）；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
}①NATURAL LANGUAGE MODE
查询带有指定word的文档。因“NATURAL LANGUAGE MODE”为默认全文检索模式，所以查询语句中可以省略该关键字。
mysql> SELECT * FROM `player`.`articles`
-> WHERE MATCH (`title`, `body`)
-> AGAINST ('database' IN NATURAL LANGUAGE MODE);
+------------+-------------------+------------------------------------------+
| FTS_DOC_ID | title | body |
+------------+-------------------+------------------------------------------+
| 1 | MySQL Tutorial | DBMS stands for DataBase ... |
| 5 | MySQL vs. YourSQL | In the following database comparison ... |
+------------+-------------------+------------------------------------------+
2 rows in set (0.00 sec)
mysql> SELECT * FROM `player`.`articles`
-> WHERE MATCH (`title`, `body`)
-> AGAINST ('database');
+------------+-------------------+------------------------------------------+
| FTS_DOC_ID | title | body |
+------------+-------------------+------------------------------------------+
| 1 | MySQL Tutorial | DBMS stands for DataBase ... |
| 5 | MySQL vs. YourSQL | In the following database comparison ... |
+------------+-------------------+------------------------------------------+
2 rows in set (0.00 sec)MATCH()返回一个相关系数（relevance value），查询结果会按relevance value值降序排列，即相关性最高的结果置于首位。relevance value根据以下条件计算。
- word是否在记录中出现
- word在记录中出现的次数
- word在索引字段中的数量
- 多少行记录包含该word
以下查询可以查看relevance value。
mysql> SELECT `pa`.`FTS_DOC_ID`, `pa`.`title`, `pa`.`body`,
-> MATCH (`pa`.`title`, `pa`.`body`) AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE) AS `score`
-> FROM `player`.`articles` AS `pa`
-> ORDER BY `score` DESC
-> LIMIT 4;
+------------+-----------------------+-------------------------------------+---------------------+
| FTS_DOC_ID | title | body | score |
+------------+-----------------------+-------------------------------------+---------------------+
| 1 | MySQL Tutorial | DBMS stands for DataBase ... | 0.22764469683170319 |
| 3 | Optimizing MySQL | In this tutorial we will show ... | 0.22764469683170319 |
| 2 | How To Use MySQL Well | After you went through a ... | 0 |
| 4 | 1001 MySQL Tricks | 1. Never run mysqld as root. 2. ... | 0 |
+------------+-----------------------+-------------------------------------+---------------------+
4 rows in set (0.00 sec)②BOOLEAN MODE
AGAINST()中关键字的前后字符会有特殊含义。BOOLEAN MODE全文检索支持以下操作符。
- +
word必须存在。
- -
word必须不存在。
- (no operator)
该word可选，如果出现relevance value更高。
- @distance
仅用于InnoDB表。查询多个单词之间的距离是否在distance（字节）内。
- > <
分别表示出现该word时增加和降低relevance value。
- ~
出现该word时relevance value变负值，用于制造噪音词（“noise” word）。
- *
表示以该字符串开头的word。
- ''
''中的内容视作一个短语（整体）
mysql> /* 查询包含“tutorial”但不包含“dbms”的记录 */
mysql> SELECT * FROM `player`.`articles`
-> WHERE MATCH (`title`, `body`) AGAINST ('+tutorial -dbms' IN BOOLEAN MODE);
+------------+------------------+-----------------------------------+
| FTS_DOC_ID | title | body |
+------------+------------------+-----------------------------------+
| 3 | Optimizing MySQL | In this tutorial we will show ... |
+------------+------------------+-----------------------------------+
1 row in set (0.01 sec)
mysql> /* 查询“mysql”与“tutorial”之间的距离在2个字节以内的记录 */
mysql> SELECT * FROM `player`.`articles`
-> WHERE MATCH (`title`, `body`) AGAINST ('"mysql tutorial" @2' IN BOOLEAN MODE);
+------------+----------------+------------------------------+
| FTS_DOC_ID | title | body |
+------------+----------------+------------------------------+
| 1 | MySQL Tutorial | DBMS stands for DataBase ... |
+------------+----------------+------------------------------+
1 row in set (0.01 sec)
mysql> SELECT * FROM `player`.`articles`
-> WHERE MATCH (`title`, `body`) AGAINST ('"mysql tutorial" @4' IN BOOLEAN MODE);
+------------+------------------+-----------------------------------+
| FTS_DOC_ID | title | body |
+------------+------------------+-----------------------------------+
| 1 | MySQL Tutorial | DBMS stands for DataBase ... |
| 3 | Optimizing MySQL | In this tutorial we will show ... |
+------------+------------------+-----------------------------------+
2 rows in set (0.00 sec)
mysql> /* 根据单词“tutorial”和“optimizing”统计relevance value：其中包含“optimizing”的记录增加relevance value */
mysql> SELECT `pa`.`FTS_DOC_ID`, `pa`.`title`, `pa`.`body`,
-> MATCH (`pa`.`title`, `pa`.`body`) AGAINST ('tutorial' IN BOOLEAN MODE) AS `score`
-> FROM `player`.`articles` AS `pa`
-> ORDER BY `score` DESC
-> LIMIT 2;
+------------+------------------+-----------------------------------+---------------------+
| FTS_DOC_ID | title | body | score |
+------------+------------------+-----------------------------------+---------------------+
| 1 | MySQL Tutorial | DBMS stands for DataBase ... | 0.22764469683170319 |
| 3 | Optimizing MySQL | In this tutorial we will show ... | 0.22764469683170319 |
+------------+------------------+-----------------------------------+---------------------+
2 rows in set (0.00 sec)
mysql> SELECT `pa`.`FTS_DOC_ID`, `pa`.`title`, `pa`.`body`,
-> MATCH (`pa`.`title`, `pa`.`body`) AGAINST ('tutorial >optimizing' IN BOOLEAN MODE) AS `score`
-> FROM `player`.`articles` AS `pa`
-> ORDER BY `score` DESC
-> LIMIT 2;
+------------+------------------+-----------------------------------+---------------------+
| FTS_DOC_ID | title | body | score |
+------------+------------------+-----------------------------------+---------------------+
| 3 | Optimizing MySQL | In this tutorial we will show ... | 1.8331639766693115 |
| 1 | MySQL Tutorial | DBMS stands for DataBase ... | 0.22764469683170319 |
+------------+------------------+-----------------------------------+---------------------+
2 rows in set (0.00 sec)
mysql> /* 查询包含“tutorial”的记录：其中包含“optimizing”的记录relevance value置为负值 */
mysql> SELECT `pa`.`FTS_DOC_ID`, `pa`.`title`, `pa`.`body`,
-> MATCH (`pa`.`title`, `pa`.`body`) AGAINST ('tutorial ~optimizing' IN BOOLEAN MODE) AS `score`
-> FROM `player`.`articles` AS `pa`
-> ORDER BY `score` DESC
-> LIMIT 2;
+------------+------------------+-----------------------------------+---------------------+
| FTS_DOC_ID | title | body | score |
+------------+------------------+-----------------------------------+---------------------+
| 1 | MySQL Tutorial | DBMS stands for DataBase ... | 0.22764469683170319 |
| 3 | Optimizing MySQL | In this tutorial we will show ... | -0.1668359637260437 |
+------------+------------------+-----------------------------------+---------------------+
2 rows in set (0.00 sec)
mysql> /* 查询包含以字母“c”开头的单词的记录 */
mysql> SELECT * FROM `player`.`articles`
-> WHERE MATCH (`title`, `body`) AGAINST ('c*' IN BOOLEAN MODE);
+------------+-------------------+------------------------------------------+
| FTS_DOC_ID | title | body |
+------------+-------------------+------------------------------------------+
| 5 | MySQL vs. YourSQL | In the following database comparison ... |
| 6 | MySQL Security | When configured properly, MySQL ... |
+------------+-------------------+------------------------------------------+
2 rows in set (0.00 sec)
mysql> /* 查询包含“mysql”或者“tutorial”的记录 */
mysql> SELECT * FROM `player`.`articles`
-> WHERE MATCH (`title`, `body`) AGAINST ('mysql tutorial' IN BOOLEAN MODE);
+------------+-----------------------+------------------------------------------+
| FTS_DOC_ID | title | body |
+------------+-----------------------+------------------------------------------+
| 1 | MySQL Tutorial | DBMS stands for DataBase ... |
| 3 | Optimizing MySQL | In this tutorial we will show ... |
| 6 | MySQL Security | When configured properly, MySQL ... |
| 2 | How To Use MySQL Well | After you went through a ... |
| 4 | 1001 MySQL Tricks | 1. Never run mysqld as root. 2. ... |
| 5 | MySQL vs. YourSQL | In the following database comparison ... |
+------------+-----------------------+------------------------------------------+
6 rows in set (0.00 sec)
mysql> /* 查询包含短语“mysql tutorial”的记录 */
mysql> SELECT * FROM `player`.`articles`
-> WHERE MATCH (`title`, `body`) AGAINST ('"mysql tutorial"' IN BOOLEAN MODE);
+------------+----------------+------------------------------+
| FTS_DOC_ID | title | body |
+------------+----------------+------------------------------+
| 1 | MySQL Tutorial | DBMS stands for DataBase ... |
+------------+----------------+------------------------------+
1 row in set (0.00 sec)③（NATURAL LANGUAGE MODE）WITH QUERY EXPANSION
全文检索扩展查询。当查询的关键词太短，需要隐含信息（implied knowledge）时比较有用。例如，当查询“database”时可能意味着“MySQL”、“Oracle”、“DB2”以及“RDBMS”等词语都应该匹配和返回。在查询语句中添加WITH QUERY EXPANSION或者IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION启用“blind query expansion”（也称“automatic relevance feedback”）。扩展查询分两步执行。
- 根据原始的查询关键字表达式进行全文检索
- 根据第一步查询的返回结果分词后再进行一次全文检索
因此，如果有记录包含“database”和“MySQL”，第二步查询也会找出那些包含“MySQL”的记录，即便这些记录没有包含“database”。
mysql> SELECT * FROM `player`.`articles`
-> WHERE MATCH (`title`, `body`)
-> AGAINST ('database' IN NATURAL LANGUAGE MODE);
+------------+-------------------+------------------------------------------+
| FTS_DOC_ID | title | body |
+------------+-------------------+------------------------------------------+
| 1 | MySQL Tutorial | DBMS stands for DataBase ... |
| 5 | MySQL vs. YourSQL | In the following database comparison ... |
+------------+-------------------+------------------------------------------+
2 rows in set (0.00 sec)
mysql> SELECT * FROM `player`.`articles`
-> WHERE MATCH (`title`, `body`)
-> AGAINST ('database' WITH QUERY EXPANSION);
+------------+-----------------------+------------------------------------------+
| FTS_DOC_ID | title | body |
+------------+-----------------------+------------------------------------------+
| 5 | MySQL vs. YourSQL | In the following database comparison ... |
| 1 | MySQL Tutorial | DBMS stands for DataBase ... |
| 3 | Optimizing MySQL | In this tutorial we will show ... |
| 6 | MySQL Security | When configured properly, MySQL ... |
| 2 | How To Use MySQL Well | After you went through a ... |
| 4 | 1001 MySQL Tricks | 1. Never run mysqld as root. 2. ... |
+------------+-----------------------+------------------------------------------+
6 rows in set (0.00 sec)blind query expansion通常会返回不相关的记录而极大地增加查询结果的“噪音”（noise），因此仅当查询语句表达式简短的情况下使用。
在CJK语系中，为了解决word与word之间不使用分隔符而原始无法分词的问题，MySQL引入ngram全文解析器（ngram full-text parser）。ngram的核心思想是用户自定义分词长度。一个ngram指的是给定文本序列中n个字符的连续序列。ngram解析器将文本序列分词成n个字符的连续序列。例如，通过ngram full-text parser可以以不同的n值将“abcd”分词。
- n=1: 'a', 'b', 'c', 'd'
- n=2: 'ab', 'bc', 'cd'
- n=3: 'abc', 'bcd'
- n=4: 'abcd'
ngram parser默认的分词大小（token size）是2（bigram）。在此默认值下，字符串“abc def”会被解析成四个分词：“ab”、“bc”、“de”和“ef”。该值大小由参数“ngram_token_size”控制，值域为1 ~ 10。通常将其设置为你想要查询的token的最大size。如果只想要搜索单字符，将其设为1。越小的token size生成越小的full-text search索引和更快的查询性能。如果需要搜索由多于一个的字符组成的word，以此相应地设置“ngram_token_size”。例如，“Happy Birthday”在简体中文里是“生日快乐”，其中“生日”是“birthday”、“快乐”译作“happy”。要搜索类似的双字符word，设置“ngram_token_size”的值为2或者更大。该参数为只读变量，只能在启动行或者配置文件里面设置。
ngram parser在解析时会消除空格。
- “ab cd”解析为：“ab”、“cd”
- “a bc”解析为：“bc”
在以空格作为分隔符来分词的第一语系，stopword list是相同剔除（word与stopword相同时不会FULLTEXT索引）；CJK语系则与此不同，stopword list为包含剔除。例如，假设ngram_token_size=2，一个包含“a,b”的记录会被解析为“a,”和“,b”，如果逗号（“,”）被定义为stopword，那么“a,”和“,b”都会被FULLTEXT索引排除，因为它们包含逗号。默认情况下，ngram parser使用MySQL默认的stopword list，它包含一列英文stopword。对于适用于CJK语系的stopword list，则必须自己创建（通过系统参数“innodb_ft_server_stopword_table”指定）。宽度超过“ngram_token_size”的stopword会被忽略。
要创建启用ngram parser的FULLTEXT index，在建表的DDL中指定“WITH PARSER ngram”。
mysql> create database `player`;
Query OK, 1 row affected (0.00 sec)
mysql> CREATE TABLE `player`.`articles_ngram` (
-> `FTS_DOC_ID` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
-> `title` VARCHAR(200) NULL DEFAULT NULL,
-> `body` TEXT NULL,
-> PRIMARY KEY (`FTS_DOC_ID`),
-> FULLTEXT INDEX `title` (`title`, `body`) WITH PARSER ngram
-> )
-> ENGINE=InnoDB CHARACTER SET utf8mb4
-> ;
Query OK, 0 rows affected, 1 warning (0.05 sec)
mysql> SET NAMES utf8mb4;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO `player`.`articles_ngram` (`title`, `body`) VALUES
-> ('程序员', '从Hello World到exit 0'),
-> ('程序猿', '（男）程序员'),
-> ('程序媛', '妹纸程序员'),
-> ('软件开发工程师', '就是程序员咯'),
-> ('软件工程', '阔以说是最好的专业了从毕业到喵喵喵'),
-> ('DBA', '专注于数据库 管理员的工作是主要负责设计、开发和维护数据库架构'),
-> ('数据库管理员', '一般大家简称DBA'),
-> ('MySQL', '国内一般读作吗哎瑟叩，最流行的开源关系型数据库'),
-> ('Python', 'Python是工程不是艺术')
-> ;
Query OK, 9 rows affected (0.03 sec)
Records: 9 Duplicates: 0 Warnings: 0
mysql> SET GLOBAL innodb_ft_aux_table="player/articles_ngram";
Query OK, 0 rows affected (0.01 sec)
mysql> SHOW GLOBAL VARIABLES LIKE 'ngram_token_size';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| ngram_token_size | 2 |
+------------------+-------+
1 row in set (0.01 sec)
mysql> SELECT * FROM `INFORMATION_SCHEMA`.`INNODB_FT_INDEX_CACHE` AS `ii` WHERE `ii`.`DOC_ID` = 1 ORDER BY `ii`.`POSITION`;
+--------+--------------+-------------+-----------+--------+----------+
| WORD | FIRST_DOC_ID | LAST_DOC_ID | DOC_COUNT | DOC_ID | POSITION |
+--------+--------------+-------------+-----------+--------+----------+
| 程序 | 1 | 4 | 4 | 1 | 0 |
| 序员 | 1 | 4 | 4 | 1 | 3 |
| 从h | 1 | 1 | 1 | 1 | 10 |
| he | 1 | 1 | 1 | 1 | 13 |
| el | 1 | 1 | 1 | 1 | 14 |
| ll | 1 | 1 | 1 | 1 | 15 |
| lo | 1 | 1 | 1 | 1 | 16 |
| wo | 1 | 1 | 1 | 1 | 19 |
| rl | 1 | 1 | 1 | 1 | 21 |
| ld | 1 | 1 | 1 | 1 | 22 |
| d到 | 1 | 1 | 1 | 1 | 23 |
| 到e | 1 | 1 | 1 | 1 | 24 |
| ex | 1 | 1 | 1 | 1 | 27 |
+--------+--------------+-------------+-----------+--------+----------+
13 rows in set (0.01 sec)全文检索的SQL查询语法对于ngram解析器插件同样适用，但解析方式存在一些不同之处。
①ngram Parser Term Search
- 在NATURAL LANGUAGE MODE检索模式下，查询关键字表达式被转换为若干个ngram词语的联合。例如，字符串“abc”（假设ngram_token_size=2）会被转换为“ab bc”。给出两行记录，一行包含“ab”而另一行包含“abc”，这两行记录都匹配检索词语“ab bc”。即匹配其一即可返回。
mysql> SELECT * FROM `player`.`articles_ngram`
-> WHERE MATCH (`title`, `body`)
-> AGAINST ('软件工程' IN NATURAL LANGUAGE MODE);
+------------+-----------------------+-----------------------------------------------------+
| FTS_DOC_ID | title | body |
+------------+-----------------------+-----------------------------------------------------+
| 5 | 软件工程 | 阔以说是最好的专业了从毕业到喵喵喵 |
| 4 | 软件开发工程师 | 就是程序员咯 |
| 9 | Python | Python是工程不是艺术 |
+------------+-----------------------+-----------------------------------------------------+
3 rows in set (0.01 sec)- 在BOOLEAN MODE检索模式下，查询关键字表达式被转换为一个ngram短语检索。例如，字符串“abc”（假设ngram_token_size=2）会被转换为'"ab bc"'。给出两行记录，一行包含“ab”而另一行包含“abc”，只有包含“abc”的记录行匹配检索短语'"ab bc"'。即完全匹配才可返回。
mysql> SELECT * FROM `player`.`articles_ngram`
-> WHERE MATCH (`title`, `body`)
-> AGAINST ('软件工程' IN BOOLEAN MODE);
+------------+--------------+-----------------------------------------------------+
| FTS_DOC_ID | title | body |
+------------+--------------+-----------------------------------------------------+
| 5 | 软件工程 | 阔以说是最好的专业了从毕业到喵喵喵 |
+------------+--------------+-----------------------------------------------------+
1 row in set (0.00 sec)②ngram Parser Wildcard Search
由于ngram FULLTEXT index仅包含ngram分词，而不包含词语的词首信息，所以通配符检索可能返回预想之外的结果。
- 如果通配符检索的前缀词语小于“ngram_token_size”，查询会返回包含以该前缀词语开头的ngram分词的所有索引行。例如，假设ngram_token_size=2，对于“a*”的检索会返回所有以“a”开头的记录。
mysql> SELECT * FROM `player`.`articles_ngram`
-> WHERE MATCH (`title`, `body`)
-> AGAINST ('开发*' IN BOOLEAN MODE);
+------------+-----------------------+---------------------------------------------------------------------------------------------+
| FTS_DOC_ID | title | body |
+------------+-----------------------+---------------------------------------------------------------------------------------------+
| 4 | 软件开发工程师 | 就是程序员咯 |
| 6 | DBA | 专注于数据库 管理员的工作是主要负责设计、开发和维护数据库架构 |
+------------+-----------------------+---------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)- 如果通配符检索的前缀词语大于“ngram_token_size”，该前缀词语会被转换为一个ngram短语，与此同时，通配符符号会被忽略。例如，假设ngram_token_size=2，“abc*”通配符检索会被转换为“ab bc”（“ngram Parser Term Search”的BOOLEAN MODE）。
mysql> SELECT * FROM `player`.`articles_ngram`
-> WHERE MATCH (`title`, `body`)
-> AGAINST ('开发工程师*' IN BOOLEAN MODE);
+------------+-----------------------+--------------------+
| FTS_DOC_ID | title | body |
+------------+-----------------------+--------------------+
| 4 | 软件开发工程师 | 就是程序员咯 |
+------------+-----------------------+--------------------+
1 row in set (0.00 sec)③ngram Parser Phrase Search
短语检索会被转换为ngram短语检索。例如，检索短语“abc”会被转换为“ab bc”，包含“abc”和“ab bc”的记录都会被返回。检索短语“abc def”会被转换为“ab bc de ef”，包含“abcdef”的记录不会被返回。
mysql> SELECT * FROM `player`.`articles_ngram`
-> WHERE MATCH (`title`, `body`)
-> AGAINST ('"数据库管理员"' IN BOOLEAN MODE);
+------------+--------------------+-----------------------+
| FTS_DOC_ID | title | body |
+------------+--------------------+-----------------------+
| 7 | 数据库管理员 | 一般大家简称DBA |
+------------+--------------------+-----------------------+
1 row in set (0.00 sec)
mysql> SELECT * FROM `player`.`articles_ngram`
-> WHERE MATCH (`title`, `body`)
-> AGAINST ('"数据库 管理员"' IN BOOLEAN MODE);
+------------+-------+---------------------------------------------------------------------------------------------+
| FTS_DOC_ID | title | body |
+------------+-------+---------------------------------------------------------------------------------------------+
| 6 | DBA | 专注于数据库 管理员的工作是主要负责设计、开发和维护数据库架构 |
+------------+-------+---------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)query expansion扩展查询同样适用于ngram parser。
mysql> SELECT * FROM `player`.`articles_ngram`
-> WHERE MATCH (`title`, `body`)
-> AGAINST ('吗哎');
+------------+-------+-----------------------------------------------------------------------+
| FTS_DOC_ID | title | body |
+------------+-------+-----------------------------------------------------------------------+
| 8 | MySQL | 国内一般读作吗哎瑟叩，最流行的开源关系型数据库 |
+------------+-------+-----------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT * FROM `player`.`articles_ngram`
-> WHERE MATCH (`title`, `body`)
-> AGAINST ('吗哎' WITH QUERY EXPANSION);
+------------+--------------------+---------------------------------------------------------------------------------------------+
| FTS_DOC_ID | title | body |
+------------+--------------------+---------------------------------------------------------------------------------------------+
| 8 | MySQL | 国内一般读作吗哎瑟叩，最流行的开源关系型数据库 |
| 6 | DBA | 专注于数据库 管理员的工作是主要负责设计、开发和维护数据库架构 |
| 7 | 数据库管理员 | 一般大家简称DBA |
+------------+--------------------+---------------------------------------------------------------------------------------------+
3 rows in set (0.01 sec)除了上文第一语系解析时提到的用于InnoDB的“innodb_ft_min_token_size”和“innodb_ft_max_token_size”以及用于MyISAM的“ft_min_word_len”、“ft_max_word_len”（ngram parser会自动忽略其值设置），其他内置的Full-Text Search配置参数同样适用于ngram parser。
MySQL同时提供了一个专用于日语的MeCab full-text parser（MySQL全文检索fulltext日语解析插件MeCab学习笔记_蛙鳜鸡鹳狸猿的博客-CSDN博客），将记录行tokenize为有实际意义的词语。
参考：
MySQL :: MySQL 5.7 Reference Manual :: 12.10 Full-Text Search Functions
https://dev.mysql.com/doc/refman/5.7/en/innodb-ft-index-cache-table.html
《MySQL技术内幕 InnoDB存储引擎》（MySQL技术内幕 (豆瓣) ）
更新说明
2022-11-18
这篇博文2018年写的，起初的一段时间没有问题处于公开状态。但是后面举得查询例子不太恰当后来不知道什么时候开始就被下线了。本次重新全量修改了文末的查询语句例子并提交发布。
7437
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
