MySQL 全文搜索 (FTS)

全文搜索是一种搜索技术，用于检查文档中可能不完全匹配搜索条件的单词。这些记录包含文本数据，如产品描述、博客文章、文章等。MySQL 从 3.23.23 版本开始支持全文索引和搜索。在本节中，我们将学习 FULLTEXT 搜索的介绍、语法、优缺点、特性及其类型。

FTS 搜索技术通常由搜索引擎使用，例如Google、Mozilla或Bing。所有这些搜索引擎都会将网站数据收集到数据库中，并根据关键字进行搜索。它可以在博客、新闻、电子商务等网站上产生强大的搜索结果。

例如，我们搜索了Plant和Trees。FTS 会检查这两个词，并返回包含单独搜索的词、不同顺序的词（Plant 和 Tree 或 Tree 和 Plant），或者仅包含 Plant 和 Tree 的结果。这使得用户能够猜测他们想要什么，并更快地返回相关结果。

从技术上讲，MySQL 通常通过使用LIKE和正则表达式运算符支持部分文本查找。但是，这些请求在大型数据集上存在一些限制，即当文本列很大或行数增加时。当用户输入不完全匹配时，它的性能也会下降，甚至可能在文档包含相关信息但输入不匹配时产生零结果。

关于 MySQL 全文搜索的要点

• 性能：MySQL 需要扫描整个表才能根据 LIKE 或正则表达式语句中指定的模式找到精确匹配。
• 灵活搜索：MySQL 没有灵活的搜索查询，因为 LIKE 和正则表达式基于模式匹配来检查搜索。例如，要查找描述包含 bike 但不包含 classic 的产品。
• 相关性排名：它没有特定的方法来指定结果集中哪一行与搜索关键字更相关。
• 存储引擎：全文搜索不支持所有存储引擎。它仅支持 MySQL 中的MyISAM和InnoDB存储引擎。

语法

MySQL 使用以下语法执行全文搜索

在此语法中，我们首先指定 MATCH() 函数，其中包含要搜索的列名列表，用逗号分隔。然后，AGAINST() 函数接受一个要搜索的字符串，并带有可选的修饰符，以指示执行哪种类型的搜索。search_modifier 可以是 IN NATURAL LANGUAGE MODE、IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION、IN BOOLEAN MODE 或 WITH QUERY EXPANSION。

MySQL 全文搜索特性

以下是 MySQL 全文搜索的一些重要特性：

• 原生 SQL 风格的接口：MySQL 使用类似 SQL 的语句执行全文搜索。
• 完全动态索引：每当列中发生数据修改时，MySQL 都会自动更新该文本列的索引。
• 适中的索引大小：FULLTEXT 索引的大小相对较小。
• 速度：全文搜索技术速度很快，可以根据复杂的搜索查询来检查数据。

FULLTEXT 限制

• 全文搜索仅支持 InnoDB 和 MyISAM 表。
• 它也不支持分区表。
• MATCH() 函数参数必须是表的全文字索引定义中包含的相同列列表，除非 MATCH() 是 MyISAM 表上的 IN BOOLEAN MODE。
• 我们不能为全文搜索使用 '%' 通配符。
• 在查询执行期间，AGAINST() 的参数应为常量字符串值。
• 全文索引对所有列使用相同的字符集和排序规则。
• 我们只能为 CHAR、VARCHAR 或 TEXT 列创建全文索引。

FULLTEXT 搜索类型

全文搜索主要分为三种类型：

自然语言搜索类型

此搜索模式将搜索字符串解释为自然语言的字面短语。它不支持特殊字符。如果没有指定修饰符，或者指定了 IN NATURAL LANGUAGE MODE 修饰符，则默认启用。

查询扩展搜索类型

它是自然语言搜索类型的一种修改，它执行两次搜索。它首先执行包含一些最相关文档的自然语言搜索，然后再次搜索以返回结果。通过使用 WITH QUERY EXPANSION 修饰符启用。

布尔搜索类型

此搜索模式解释搜索字符串以进行复杂查询，其中可以包含布尔运算符，如小于 (<) 或大于 (>) 运算符、加号 (+) 和减号 (-)、子表达式（"(" 和 ")"）、双引号（""）、降低搜索结果贡献值的运算符（~）以及通配符。通过使用 IN BOOLEAN MODE 修饰符启用。

让我们学习如何定义全文索引以在 MySQL 中执行全文搜索.

在我们对表列执行全文搜索之前，必须确保已为其创建索引。每当列数据发生更改时，MySQL 都会自动重新创建全文索引。在 MySQL 中，全文索引的名称始终是 FULLTEXT。我们可以定义列数据类型为 CHAR、VARCHAR 和 TEXT 的全文索引。

我们可以使用 CREATE TABLE 语句在创建表时定义全文索引，或者使用 ALTER TABLE 或 CREATE INDEX 语句为现有表定义全文索引。

使用 CREATE TABLE 语句创建 FULLTEXT 索引

使用 CREATE TABLE 语句在创建新表时定义全文索引，如下所示：

这里 table_name 是新表的名称，column_name 是列的名称，而 FULLTEXT 表示全文索引，其中包含括号内用逗号分隔的列名列表。

以下语句创建了一个名为 Articles 的新表。此表具有一个包含 article content 列的 FULLTEXT 索引：

接下来，我们将向此表中填充记录：

我们现在将使用 MATCH() 函数执行全文搜索，该函数在 AGAINST() 参数中包含搜索字符串。它以不区分大小写的方式执行搜索。MATCH() 函数为表中的每一行返回一个相关值。如果我们在 WHERE 子句中使用 MATCH() 函数，则返回的行将自动按最高相关值排序。相关性是根据单词数量、唯一单词数量、集合中的总单词数以及包含特定单词的行数来计算的。

以下是我们针对字符串 "tutorial" 执行全文搜索的输出：

让我们看另一个例子，展示如何显式检索相关值：

以下是返回的行未排序的输出：

使用 ALTER TABLE 语句创建 FULLTEXT 索引

MySQL 也提供了 ALTER TABLE 语句来在现有表上创建全文索引。以下语法更清楚地说明了这一点：

在此语法中，我们需要首先指定表名以创建索引。其次，使用 ADD FULLTEXT 子句为一列或多列定义全文索引。

例如，我们有一个名为 books 的表，其中包含 id、title、content 和 author 列。现在，我们可以为 content 和 author 列定义全文索引，如下所示：

使用 CREATE INDEX 语句创建 FULLTEXT 索引

我们也可以使用 CREATE INDEX 语句为现有表创建全文索引。以下语法更清楚地说明了这一点：

例如，我们有一个名为 office 的表，其中包含 address_line1 和 address_line2 列。现在，我们可以使用以下语句为这些列创建 FULLTEXT 索引：

如何删除 FULLTEXT 索引？

MySQL 提供了一个命令来从表中删除全文索引。我们可以使用以下 ALTER TABLE DROP INDEX 语句来实现：

在此语法中，我们首先指定表名，然后指定全文索引名，后跟 DROP INDEX 子句。

例如，执行以下语句将永久删除 "offices" 表中的 address 索引：