内置的 MySQL 全文解析器使用单词之间的空格作为分隔符来确定单词的起始和结束位置，这在处理不使用单词分隔符的表意文字语言时是一个局限。为了解决这一局限，MySQL 提供了支持中文、日文和韩文（CJK）的 ngram 全文解析器。ngram 全文解析器可用于 InnoDB 和 MyISAM。The built-in MySQL full-text parser uses the white space between words as a delimiter to determine where words begin and end, which is a limitation when working with ideographic languages that do not use word delimiters. To address this limitation, MySQL provides an ngram full-text parser that supports Chinese, Japanese, and Korean (CJK). The ngram full-text parser is supported for use with InnoDB and MyISAM.

ngram 是从给定文本序列中连续取出的 n 个字符的序列。ngram 解析器将文本序列分词为连续的 n 个字符的序列。例如，你可以使用 ngram 全文解析器对 “abcd” 根据不同的 n 值进行分词。An ngram is a contiguous sequence of n characters from a given sequence of text. The ngram parser tokenizes a sequence of text into a contiguous sequence of n characters. For example, you can tokenize “abcd” for different values of n using the ngram full-text parser.

n=1: 'a', 'b', 'c', 'd'
n=2: 'ab', 'bc', 'cd'
n=3: 'abc', 'bcd'
n=4: 'abcd'

ngram 全文解析器是一个内置的服务器插件。与其他内置服务器插件一样，它在服务器启动时会自动加载。The ngram full-text parser is a built-in server plugin. As with other built-in server plugins, it is automatically loaded when the server is started.

在 第 14.9 节，“全文搜索函数” 中描述的全文搜索语法适用于 ngram 解析器插件。本节描述了解析行为的差异。除最小和最大词长选项（innodb_ft_min_token_size、innodb_ft_max_token_size、ft_min_word_len、ft_max_word_len）之外，全文相关的配置选项同样适用。The full-text search syntax described in Section 14.9, “Full-Text Search Functions” applies to the ngram parser plugin. Differences in parsing behavior are described in this section. Full-text-related configuration options, except for minimum and maximum word length options (innodb_ft_min_token_size, innodb_ft_max_token_size, ft_min_word_len, ft_max_word_len) are also applicable.

配置 ngram 令牌大小Configuring ngram Token Size

ngram 解析器的默认 ngram 令牌大小为 2（双字母组）。例如，当令牌大小为 2 时，ngram 解析器会将字符串 “abc def” 解析为四个令牌：“ab”、“bc”、“de” 和 “ef”。The ngram parser has a default ngram token size of 2 (bigram). For example, with a token size of 2, the ngram parser parses the string “abc def” into four tokens: “ab”, “bc”, “de” and “ef”.

ngram 令牌大小可以使用 ngram_token_size 配置选项进行配置，其最小值为 1，最大值为 10。ngram token size is configurable using the ngram_token_size configuration option, which has a minimum value of 1 and maximum value of 10.

通常，ngram_token_size 设置为你希望搜索的最大标记的大小。如果你只打算搜索单个字符，请将 ngram_token_size 设置为 1。较小的标记大小会产生较小的全文本搜索索引，同时搜索速度更快。如果你需要搜索由多个字符组成的词，请相应地设置 ngram_token_size。例如，“Happy Birthday” 在简体中文中是 “生日快乐”，其中 “生日” 是 “birthday”，而 “快乐” 翻译为 “happy”。要搜索像这样的双字符词，请将 ngram_token_size 设置为 2 或更高。Typically, ngram_token_size is set to the size of the largest token that you want to search for. If you only intend to search for single characters, set ngram_token_size to 1. A smaller token size produces a smaller full-text search index, and faster searches. If you need to search for words comprised of more than one character, set ngram_token_size accordingly. For example, “Happy Birthday” is “生日快乐” in simplified Chinese, where “生日” is “birthday”, and “快乐” translates as “happy”. To search on two-character words such as these, set ngram_token_size to a value of 2 or higher.

作为只读变量，ngram_token_size 只能在启动字符串或配置文件中设置：As a read-only variable, ngram_token_size may only be set as part of a startup string or in a configuration file:

创建使用 ngram 解析器的 FULLTEXT 索引Creating a FULLTEXT Index that Uses the ngram Parser

要创建使用 ngram 解析器的 FULLTEXT 索引，请在 CREATE TABLE、ALTER TABLE 或 CREATE INDEX 时指定 WITH PARSER ngram。To create a FULLTEXT index that uses the ngram parser, specify WITH PARSER ngram with CREATE TABLE, ALTER TABLE, or CREATE INDEX.

以下示例演示了如何创建一个带有ngram FULLTEXT索引的表、插入示例数据（简体中文文本），以及在信息模式中查看分词后的数据 INNODB_FT_INDEX_CACHE 表。The following example demonstrates creating a table with an ngram FULLTEXT index, inserting sample data (Simplified Chinese text), and viewing tokenized data in the Information Schema INNODB_FT_INDEX_CACHE table.

mysql> USE test;

mysql> 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 CHARACTER SET utf8mb4;

mysql> SET NAMES utf8mb4;

INSERT INTO articles (title,body) VALUES
    ('数据库管理','在本教程中我将向你展示如何管理数据库'),
    ('数据库应用开发','学习开发数据库应用程序');

mysql> SET GLOBAL innodb_ft_aux_table="test/articles";

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE ORDER BY doc_id, position;

要向现有表添加FULLTEXT索引，可以使用 ALTER TABLE 或 CREATE INDEX。例如：To add a FULLTEXT index to an existing table, you can use ALTER TABLE or CREATE INDEX. For example:

CREATE TABLE articles (
      id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
      title VARCHAR(200),
      body TEXT
     ) ENGINE=InnoDB CHARACTER SET utf8mb4;

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

# Or:

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

ngram 解析器空格处理ngram Parser Space Handling

ngram 解析器在解析时会去掉空格。例如：The ngram parser eliminates spaces when parsing. For example:

ngram 解析器停用词处理ngram Parser Stopword Handling

内置的 MySQL 全文解析器将单词与停用词列表中的条目进行比较。如果一个单词与停用词列表中的某个条目相等，则该单词将从索引中排除。对于 ngram 解析器，停用词的处理方式不同。它不是排除与停用词列表中的条目相等的标记，而是排除包含停用词的标记。例如，假设 ngram_token_size=2，一个包含 “a,b” 的文档将被解析为 “a,” 和 “,b”。如果逗号（“,”）被定义为停用词，则 “a,” 和 “,b” 都会因为包含逗号而从索引中排除。The built-in MySQL full-text parser compares words to entries in the stopword list. If a word is equal to an entry in the stopword list, the word is excluded from the index. For the ngram parser, stopword handling is performed differently. Instead of excluding tokens that are equal to entries in the stopword list, the ngram parser excludes tokens that contain stopwords. For example, assuming ngram_token_size=2, a document that contains “a,b” is parsed to “a,” and “,b”. If a comma (“,”) is defined as a stopword, both “a,” and “,b” are excluded from the index because they contain a comma.

默认情况下，ngram 解析器使用默认的停用词列表，其中包含英文停用词列表。对于适用于中文、日文或韩文的停用词列表，您必须自己创建。有关创建停用词列表的信息，请参见 第 14.9.4 节，“全文停用词”。By default, the ngram parser uses the default stopword list, which contains a list of English stopwords. For a stopword list applicable to Chinese, Japanese, or Korean, you must create your own. For information about creating a stopword list, see Section 14.9.4, “Full-Text Stopwords”.

长度大于 ngram_token_size 的停用词将被忽略。Stopwords greater in length than ngram_token_size are ignored.

ngram 解析器术语搜索ngram Parser Term Search

对于自然语言模式搜索，搜索词会被转换为 ngram 词的联合。例如，字符串 “abc”（假设 ngram_token_size=2）会被转换为 “ab bc”。给定两个文档，一个包含 “ab”，另一个包含 “abc”，搜索词 “ab bc” 会匹配这两个文档。For natural language mode search, the search term is converted to a union of ngram terms. For example, the string “abc” (assuming ngram_token_size=2) is converted to “ab bc”. Given two documents, one containing “ab” and the other containing “abc”, the search term “ab bc” matches both documents.

对于布尔模式搜索，搜索词会被转换为 ngram 短语搜索。例如，字符串 'abc'（假设 ngram_token_size=2）被转换为 '“ab bc”'。给定两个文档，一个包含 'ab'，另一个包含 'abc'，搜索短语 '“ab bc”' 仅匹配包含 'abc' 的文档。For boolean mode search, the search term is converted to an ngram phrase search. For example, the string 'abc' (assuming ngram_token_size=2) is converted to '“ab bc”'. Given two documents, one containing 'ab' and the other containing 'abc', the search phrase '“ab bc”' only matches the document containing 'abc'.

ngram 解析器通配符搜索ngram Parser Wildcard Search

因为 ngram FULLTEXT 索引只包含 ngram，并不包含关于词语开头的信息，通配符搜索可能会返回意想不到的结果。以下行为适用于使用 ngram FULLTEXT 搜索索引的通配符搜索：Because an ngram FULLTEXT index contains only ngrams, and does not contain information about the beginning of terms, wildcard searches may return unexpected results. The following behaviors apply to wildcard searches using ngram FULLTEXT search indexes:

ngram 解析器短语搜索ngram Parser Phrase Search

短语搜索会被转换为 ngram 短语搜索。例如，搜索短语“abc”会被转换为“ab bc”，它会返回包含“abc”和“ab bc”的文档。Phrase searches are converted to ngram phrase searches. For example, The search phrase “abc” is converted to “ab bc”, which returns documents containing “abc” and “ab bc”.

搜索短语“abc def”会被转换为“ab bc de ef”，它会返回包含“abc def”和“ab bc de ef”的文档。包含“abcdef”的文档不会被返回。The search phrase “abc def” is converted to “ab bc de ef”, which returns documents containing “abc def” and “ab bc de ef”. A document that contains “abcdef” is not returned.