MySQL :: MySQL 8.4 参考手册 :: 14.9 全文检索功能

8.4 中文镜像

MySQL 8.4 参考手册 · 中文镜像

正文为中文，英文原文默认隐藏。点击任意中文段落即可展开 / 收起对应英文原文。

打开官方英文原文 ↗

译文由机器翻译生成，仅供理解参考；有疑问请以官方英文原文为准。

MySQL 8.4 参考手册 / 14 函数和运算符 / 14.9 全文检索功能

中文点击中文段落可显示 / 隐藏英文原文本页译文覆盖 97%

14.9 全文检索函数14.9 Full-Text Search Functions

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
  }

MySQL 支持全文索引和搜索：MySQL has support for full-text indexing and searching:

MySQL 中的全文索引是一种类型为 FULLTEXT 的索引。A full-text index in MySQL is an index of type FULLTEXT.
全文索引只能用于 InnoDB 或 MyISAM 表，并且只能为 CHAR、VARCHAR 或 TEXT 列创建。Full-text indexes can be used only with InnoDB or MyISAM tables, and can be created only for CHAR, VARCHAR, or TEXT columns.
MySQL 提供了一个内置的全文本 ngram 解析器，支持中文、日文和韩文（CJK），以及一个可安装的用于日文的 MeCab 全文本解析器插件。解析差异概述见第 14.9.8 节，“ngram 全文本解析器”，以及第 14.9.9 节，“MeCab 全文本解析器插件”。MySQL provides a built-in full-text ngram parser that supports Chinese, Japanese, and Korean (CJK), and an installable MeCab full-text parser plugin for Japanese. Parsing differences are outlined in Section 14.9.8, “ngram Full-Text Parser”, and Section 14.9.9, “MeCab Full-Text Parser Plugin”.
在创建表时，可以在 CREATE TABLE 语句中给出 FULLTEXT 索引定义，也可以稍后使用 ALTER TABLE 或 CREATE INDEX 添加。A FULLTEXT index definition can be given in the CREATE TABLE statement when a table is created, or added later using ALTER TABLE or CREATE INDEX.
对于大型数据集，将数据加载到没有 FULLTEXT 索引的表中，然后再创建索引，比将数据加载到已有 FULLTEXT 索引的表中要快得多。For large data sets, it is much faster to load your data into a table that has no FULLTEXT index and then create the index after that, than to load data into a table that has an existing FULLTEXT index.

全文搜索是使用 MATCH() AGAINST() 语法执行的。MATCH() 接受一个逗号分隔的列表，列出要搜索的列。AGAINST 接受一个要搜索的字符串，以及一个可选的修饰符，用于指示执行哪种类型的搜索。搜索字符串必须是在查询评估期间保持不变的字符串值。这排除了例如表列，因为它可以因每一行而不同。Full-text searching is performed using MATCH() AGAINST() syntax. MATCH() takes a comma-separated list that names the columns to be searched. AGAINST takes a string to search for, and an optional modifier that indicates what type of search to perform. The search string must be a string value that is constant during query evaluation. This rules out, for example, a table column because that can differ for each row.

MySQL 不允许在 MATCH() 中使用汇总列；更具体地说，任何匹配此处列出的所有条件的查询都会被拒绝，并返回 ER_FULLTEXT_WITH_ROLLUP 错误：MySQL does not permit the use of a rollup column with MATCH(); more specifically, any query matching all of the criteria listed here is rejected with ER_FULLTEXT_WITH_ROLLUP:

MATCH() 出现在查询块的 SELECT 列表、GROUP BY 子句、HAVING 子句或 ORDER BY 子句中。MATCH() appears in the SELECT list, GROUP BY clause, HAVING clause, or ORDER BY clause of a query block.
查询块中包含 GROUP BY ... WITH ROLLUP 子句。The query block contains a GROUP BY ... WITH ROLLUP clause.
对 MATCH() 函数的调用参数是分组列之一。The argument of the call to the MATCH() function is one of the grouping columns.

这里显示了一些此类查询的示例：Some examples of such queries are shown here:

# MATCH() in SELECT list...
SELECT MATCH (a) AGAINST ('abc') FROM t GROUP BY a WITH ROLLUP;
SELECT 1 FROM t GROUP BY a, MATCH (a) AGAINST ('abc') WITH ROLLUP;

# ...in HAVING clause...
SELECT 1 FROM t GROUP BY a WITH ROLLUP HAVING MATCH (a) AGAINST ('abc');

# ...and in ORDER BY clause
SELECT 1 FROM t GROUP BY a WITH ROLLUP ORDER BY MATCH (a) AGAINST ('abc');

在 WHERE 子句中允许将 MATCH() 用于汇总列。The use of MATCH() with a rollup column in the WHERE clause is permitted.

全文搜索有三种类型：There are three types of full-text searches:

自然语言搜索将搜索字符串解释为自然人类语言中的短语（自由文本中的短语）。没有特殊操作符，唯一的例外是双引号 (") 字符。停用词列表适用。有关停用词列表的更多信息，请参阅第 14.9.4 节，“全文停用词”。A natural language search interprets the search string as a phrase in natural human language (a phrase in free text). There are no special operators, with the exception of double quote (") characters. The stopword list applies. For more information about stopword lists, see Section 14.9.4, “Full-Text Stopwords”.
如果给出了 IN NATURAL LANGUAGE MODE 修饰符，或者没有给出修饰符，全文搜索就是自然语言搜索。更多信息，请参见第 14.9.1 节，“自然语言全文搜索”。Full-text searches are natural language searches if the IN NATURAL LANGUAGE MODE modifier is given or if no modifier is given. For more information, see Section 14.9.1, “Natural Language Full-Text Searches”.
布尔搜索使用特殊查询语言的规则来解释搜索字符串。字符串包含要搜索的单词。它还可以包含指定要求的运算符，例如某个单词必须存在或不存在于匹配行中，或者它的权重应该比通常更高或更低。某些常见词（停用词）会从搜索索引中省略，如果出现在搜索字符串中，它们不会匹配。IN BOOLEAN MODE 修饰符指定布尔搜索。更多信息，请参见第 14.9.2 节，“布尔全文搜索”。A boolean search interprets the search string using the rules of a special query language. The string contains the words to search for. It can also contain operators that specify requirements such that a word must be present or absent in matching rows, or that it should be weighted higher or lower than usual. Certain common words (stopwords) are omitted from the search index and do not match if present in the search string. The IN BOOLEAN MODE modifier specifies a boolean search. For more information, see Section 14.9.2, “Boolean Full-Text Searches”.
查询扩展搜索是对自然语言搜索的一种修改。搜索字符串用于执行自然语言搜索。然后，将搜索返回的最相关行中的词添加到搜索字符串中，并再次进行搜索。查询返回第二次搜索的行。IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION 或 WITH QUERY EXPANSION 修饰符指定查询扩展搜索。有关更多信息，请参见第 14.9.3 节，“全文搜索与查询扩展”。A query expansion search is a modification of a natural language search. The search string is used to perform a natural language search. Then words from the most relevant rows returned by the search are added to the search string and the search is done again. The query returns the rows from the second search. The IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION or WITH QUERY EXPANSION modifier specifies a query expansion search. For more information, see Section 14.9.3, “Full-Text Searches with Query Expansion”.

关于 FULLTEXT 查询性能的信息，请参见第 10.3.5 节，“列索引”。For information about FULLTEXT query performance, see Section 10.3.5, “Column Indexes”.

关于 InnoDB FULLTEXT 索引的更多信息，请参见第 17.6.2.4 节，“InnoDB 全文索引”。For more information about InnoDB FULLTEXT indexes, see Section 17.6.2.4, “InnoDB Full-Text Indexes”.

全文搜索的限制列在第 14.9.5 节，“全文限制”。Constraints on full-text searching are listed in Section 14.9.5, “Full-Text Restrictions”.

myisam_ftdump 工具会导出 MyISAM 全文索引的内容。这对于调试全文查询可能会有帮助。参见第 6.6.3 节，“myisam_ftdump — 显示全文索引信息”。The myisam_ftdump utility dumps the contents of a MyISAM full-text index. This may be helpful for debugging full-text queries. See Section 6.6.3, “myisam_ftdump — Display Full-Text Index information”.

首页上一级下一节