默认情况下或使用 IN NATURAL LANGUAGE MODE 修饰符时，MATCH() 函数会对一个 文本集合 执行自然语言搜索。集合是包含在 FULLTEXT 索引中的一个或多个列的集合。搜索字符串作为 AGAINST() 的参数提供。对于表中的每一行，MATCH() 返回一个相关性值，也就是搜索字符串与 MATCH() 列表中指定列的该行文本之间的相似度测量值。By default or with the IN NATURAL LANGUAGE MODE modifier, the MATCH() function performs a natural language search for a string against a text collection. A collection is a set of one or more columns included in a FULLTEXT index. The search string is given as the argument to AGAINST(). For each row in the table, MATCH() returns a relevance value; that is, a similarity measure between the search string and the text in that row in the columns named in the MATCH() list.

mysql> CREATE TABLE articles (
    ->   id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
    ->   title VARCHAR(200),
    ->   body TEXT,
    ->   FULLTEXT (title,body)
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.08 sec)

mysql> INSERT INTO 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 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.01 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM articles
    -> WHERE MATCH (title,body)
    -> AGAINST ('database' IN NATURAL LANGUAGE MODE);
+----+-------------------+------------------------------------------+
| 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)

默认情况下，搜索是以不区分大小写的方式进行的。要执行区分大小写的全文搜索，请对索引列使用区分大小写或二进制排序规则。例如，使用 utf8mb4 字符集的列可以分配 utf8mb4_0900_as_cs 或 utf8mb4_bin 排序规则，以使其在全文搜索中区分大小写。By default, the search is performed in case-insensitive fashion. To perform a case-sensitive full-text search, use a case-sensitive or binary collation for the indexed columns. For example, a column that uses the utf8mb4 character set of can be assigned a collation of utf8mb4_0900_as_cs or utf8mb4_bin to make it case-sensitive for full-text searches.

当 MATCH() 用于 WHERE 子句中时，如前面的示例所示，只要满足以下条件，返回的行将自动按相关性从高到低排序：When MATCH() is used in a WHERE clause, as in the example shown earlier, the rows returned are automatically sorted with the highest relevance first as long as the following conditions are met:

鉴于刚才列出的条件，当需要或希望时，通常使用 ORDER BY 指定一个明确的排序顺序会更省力。Given the conditions just listed, it is usually less effort to specify using ORDER BY an explicit sort order when one is necessary or desired.

相关性值是非负的浮点数。零相关性意味着没有相似性。相关性是基于行（文档）中的单词数量、行中的唯一单词数量、集合中的单词总数以及包含特定单词的行数来计算的。Relevance values are nonnegative floating-point numbers. Zero relevance means no similarity. Relevance is computed based on the number of words in the row (document), the number of unique words in the row, the total number of words in the collection, and the number of rows that contain a particular word.

要简单地计算匹配项，您可以使用如下查询：To simply count matches, you could use a query like this:

mysql> SELECT COUNT(*) FROM articles
    -> WHERE MATCH (title,body)
    -> AGAINST ('database' IN NATURAL LANGUAGE MODE);
+----------+
| COUNT(*) |
+----------+
|        2 |
+----------+
1 row in set (0.00 sec)

您可能会发现将查询重写如下会更快：You might find it quicker to rewrite the query as follows:

mysql> SELECT
    -> COUNT(IF(MATCH (title,body) AGAINST ('database' IN NATURAL LANGUAGE MODE), 1, NULL))
    -> AS count
    -> FROM articles;
+-------+
| count |
+-------+
|     2 |
+-------+
1 row in set (0.03 sec)

第一个查询做了一些额外的工作（按相关性对结果进行排序），但也可以使用基于WHERE子句的索引查找。如果搜索匹配的行很少，索引查找可能会使第一个查询更快。第二个查询执行全表扫描，如果搜索词出现在大多数行中，可能比索引查找更快。The first query does some extra work (sorting the results by relevance) but also can use an index lookup based on the WHERE clause. The index lookup might make the first query faster if the search matches few rows. The second query performs a full table scan, which might be faster than the index lookup if the search term was present in most rows.

对于自然语言全文搜索，MATCH() 函数中指定的列必须与表中某个 FULLTEXT 索引中包含的列相同。对于前面的查询，请注意 MATCH() 函数中指定的列（title 和 body）与 article 表的 FULLTEXT 索引定义中指定的列相同。要分别搜索 title 或 body，您需要为每一列创建单独的 FULLTEXT 索引。For natural-language full-text searches, the columns named in the MATCH() function must be the same columns included in some FULLTEXT index in your table. For the preceding query, note that the columns named in the MATCH() function (title and body) are the same as those named in the definition of the article table's FULLTEXT index. To search the title or body separately, you would create separate FULLTEXT indexes for each column.

您还可以执行布尔搜索或带查询扩展的搜索。这些搜索类型在第 14.9.2 节，“布尔全文搜索”和第 14.9.3 节，“带查询扩展的全文搜索”中进行了描述。You can also perform a boolean search or a search with query expansion. These search types are described in Section 14.9.2, “Boolean Full-Text Searches”, and Section 14.9.3, “Full-Text Searches with Query Expansion”.

使用索引的全文搜索在MATCH() 子句中只能指定单个表的列，因为索引不能跨越多个表。对于MyISAM表，即使没有索引，也可以进行布尔搜索（尽管速度较慢），在这种情况下，可以指定来自多个表的列。A full-text search that uses an index can name columns only from a single table in the MATCH() clause because an index cannot span multiple tables. For MyISAM tables, a boolean search can be done in the absence of an index (albeit more slowly), in which case it is possible to name columns from multiple tables.

前面的示例是一个基本示例，演示了如何使用 MATCH() 函数，其中返回的行按相关性递减顺序排列。下一个示例显示了如何显式检索相关性值。返回的行没有排序，因为 SELECT 语句既不包含 WHERE 也不包含 ORDER BY 子句：The preceding example is a basic illustration that shows how to use the MATCH() function where rows are returned in order of decreasing relevance. The next example shows how to retrieve the relevance values explicitly. Returned rows are not ordered because the SELECT statement includes neither WHERE nor ORDER BY clauses:

mysql> SELECT id, MATCH (title,body)
    -> AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE) AS score
    -> FROM articles;
+----+---------------------+
| id | score               |
+----+---------------------+
|  1 | 0.22764469683170319 |
|  2 |                   0 |
|  3 | 0.22764469683170319 |
|  4 |                   0 |
|  5 |                   0 |
|  6 |                   0 |
+----+---------------------+
6 rows in set (0.00 sec)

以下示例更复杂。查询返回相关性值，并且还按相关性递减顺序排序行。要实现此结果，请两次指定MATCH()：一次在SELECT 列表中，一次在WHERE 子句中。这不会增加额外开销，因为 MySQL 优化器会注意到这两个MATCH() 调用是相同的，并且只会调用一次全文搜索代码。The following example is more complex. The query returns the relevance values and it also sorts the rows in order of decreasing relevance. To achieve this result, specify MATCH() twice: once in the SELECT list and once in the WHERE clause. This causes no additional overhead, because the MySQL optimizer notices that the two MATCH() calls are identical and invokes the full-text search code only once.

mysql> SELECT id, body, MATCH (title,body)
    ->   AGAINST ('Security implications of running MySQL as root'
    ->   IN NATURAL LANGUAGE MODE) AS score
    -> FROM articles
    ->   WHERE MATCH (title,body) 
    ->   AGAINST('Security implications of running MySQL as root'
    ->   IN NATURAL LANGUAGE MODE);
+----+-------------------------------------+-----------------+
| id | body                                | score           |
+----+-------------------------------------+-----------------+
|  4 | 1. Never run mysqld as root. 2. ... | 1.5219271183014 |
|  6 | When configured properly, MySQL ... | 1.3114095926285 |
+----+-------------------------------------+-----------------+
2 rows in set (0.00 sec)

用双引号（"）括起来的短语只匹配包含该短语的行，完全按照输入的方式。全文索引引擎会将短语拆分为单词，并在 FULLTEXT 索引中搜索这些单词。非单词字符不需要精确匹配：短语搜索只要求匹配项包含与短语完全相同的单词，并且顺序相同。例如，"test phrase" 可以匹配 "test, phrase"。如果短语中没有索引中的单词，结果将为空。例如，如果所有单词都是停用词或短于索引单词的最小长度，结果将为空。A phrase that is enclosed within double quote (") characters matches only rows that contain the phrase literally, as it was typed. The full-text engine splits the phrase into words and performs a search in the FULLTEXT index for the words. Nonword characters need not be matched exactly: Phrase searching requires only that matches contain exactly the same words as the phrase and in the same order. For example, "test phrase" matches "test, phrase". If the phrase contains no words that are in the index, the result is empty. For example, if all words are either stopwords or shorter than the minimum length of indexed words, the result is empty.

MySQL 的 FULLTEXT 实现将任何由真正的单词字符（字母、数字和下划线）组成的序列视为一个单词。该序列也可以包含撇号（'），但不能连续出现多个。这意味着 aaa'bbb 被视为一个单词，而 aaa''bbb 被视为两个单词。在单词开头或结尾的撇号会被 FULLTEXT 解析器去掉；'aaa'bbb' 会被解析为 aaa'bbb。The MySQL FULLTEXT implementation regards any sequence of true word characters (letters, digits, and underscores) as a word. That sequence may also contain apostrophes ('), but not more than one in a row. This means that aaa'bbb is regarded as one word, but aaa''bbb is regarded as two words. Apostrophes at the beginning or the end of a word are stripped by the FULLTEXT parser; 'aaa'bbb' would be parsed as aaa'bbb.

内置的 FULLTEXT 解析器通过查找某些分隔符字符来确定单词的起始和结束位置；例如， （空格）、,（逗号）和 .（句号）。如果单词没有被分隔符分开（例如中文中），内置的 FULLTEXT 解析器无法确定单词的起始和结束位置。为了能够将这些语言中的单词或其他索引词添加到使用内置 FULLTEXT 解析器的 FULLTEXT 索引中，必须预处理它们，使其由某些任意分隔符分隔。或者，您可以使用 ngram 解析器插件（针对中文、日文或韩文）或 MeCab 解析器插件（针对日文）来创建 FULLTEXT 索引。The built-in FULLTEXT parser determines where words start and end by looking for certain delimiter characters; for example,   (space), , (comma), and . (period). If words are not separated by delimiters (as in, for example, Chinese), the built-in FULLTEXT parser cannot determine where a word begins or ends. To be able to add words or other indexed terms in such languages to a FULLTEXT index that uses the built-in FULLTEXT parser, you must preprocess them so that they are separated by some arbitrary delimiter. Alternatively, you can create FULLTEXT indexes using the ngram parser plugin (for Chinese, Japanese, or Korean) or the MeCab parser plugin (for Japanese).

可以编写一个插件来替换内置的全文解析器。详细信息，请参阅 MySQL 插件 API。例如解析器插件的源代码，请参阅 MySQL 源代码发行版的 plugin/fulltext 目录。It is possible to write a plugin that replaces the built-in full-text parser. For details, see The MySQL Plugin API. For example parser plugin source code, see the plugin/fulltext directory of a MySQL source distribution.

某些词在全文搜索中会被忽略：Some words are ignored in full-text searches:

请参阅第 14.9.4 节，“全文停止词”，以查看默认的停止词列表以及如何更改它们。默认的最小词长可以按照第 14.9.6 节，“微调 MySQL 全文搜索”中描述的方法进行更改。See Section 14.9.4, “Full-Text Stopwords” to view default stopword lists and how to change them. The default minimum word length can be changed as described in Section 14.9.6, “Fine-Tuning MySQL Full-Text Search”.

集合和查询中每个正确的单词都根据其在集合或查询中的重要性被赋予权重。因此，出现在许多文档中的单词权重较低，因为它在这个特定集合中的语义价值较低。相反，如果一个单词很少出现，它会获得更高的权重。单词的权重结合起来计算行的相关性。这种技术在大型集合中效果最好。Every correct word in the collection and in the query is weighted according to its significance in the collection or query. Thus, a word that is present in many documents has a lower weight, because it has lower semantic value in this particular collection. Conversely, if the word is rare, it receives a higher weight. The weights of the words are combined to compute the relevance of the row. This technique works best with large collections.

mysql> SELECT * FROM articles
    -> WHERE MATCH (title,body)
    -> AGAINST ('MySQL' IN NATURAL LANGUAGE MODE);
Empty set (0.00 sec)