MySQL 全文搜索

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

全文搜索技术通常由搜索引擎如Google、Mozilla或Bing等使用。所有这些搜索引擎将网站数据收集到数据库中，并根据关键字进行搜索。它使得像博客、新闻、电子商务等网站上的搜索结果更加强大。

例如 ，我们已经搜索了 Plant 和 Trees 。全文搜索检查两个单词并返回包含这两个搜索词的结果，可以是以不同顺序出现的单词，如Plant和Tree或Tree和Plant，或者只是Plant和Tree。这使得用户更容易猜测他们想要的内容，并更快地返回相关结果。

从技术上讲，MySQL通常通过使用 LIKE 和 正则表达式操作符 来支持部分文本查找。然而，这些请求在大型数据集上有一些限制，即当文本列较大或行数增加时。当用户的输入不完全匹配或可能没有结果时，它的性能也不佳，尽管文档包含与此输入相关的信息。

MySQL全文搜索的重要要点：

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

语法

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

MATCH (col1, col2, col3, and so on) AGAINST (expression [search_modifier])

在这个语法中，我们将首先指定包含由逗号分隔的列名列表的 MATCH ()函数来进行搜索。然后， AGAINST ()函数采用一个字符串进行搜索，可选地指示所执行的搜索类型的修饰符。 search_modifier 可以是IN NATURAL LANGUAGE MODE或IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION或IN BOOLEAN MODE或WITH QUERY EXPANSION。

MySQL FULLTEXT搜索功能

下面是MySQL全文搜索的一些重要功能：

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

FULLTEXT限制

• 全文仅支持InnoDB和MyISAM表。
• 它也不支持分区表。
• MATCH（）函数参数必须是FULLTEXT索引定义中与表中列相同的列列表，除非MATCH（）在MyISAM表上以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语句创建表时定义全文索引，如下所示：

CREATE TABLE table_name(
    column_name1 data type,
    column_name2 data type,
    .....,
    FULLTEXT (column1, column2,..)
);

在这里 表名 是一个新表的名称， 列名 是一个列的名称，并且 FULLTEXT 表示包含以逗号分隔的列名列表的全文索引。

下面的语句创建一个名为 Articles 的新表。该表包含一个包含文章内容列的全文索引：

mysql> CREATE TABLE Articles (
    id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
    title VARCHAR(220),
    body TEXT,
    FULLTEXT (title, body)
);

接下来，我们将把记录填入这个表格中：

mysql> INSERT INTO Articles VALUES
('0', 'MySQL Tutorial', 'MySQL tutorial provides basic and advanced concepts for beginners ...'),
('0', 'How To Install MySQL', 'Here we will learn how we to download and install MySQL ...'),
('0', 'Optimising MySQL', 'In this tutorial we will show ...'),
('0', 'MySQL vs. MongoDB', 'In the following database comparison ...'),
('0', 'MySQL Versions', 'This section shows all MySQL versions ...');

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

mysql> SELECT * FROM articles 
WHERE MATCH (title, body) AGAINST ('tutorial');

这里是我们针对一个字符串进行全文搜索的输出结果 “tutorial” :

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

mysql> SELECT id, MATCH (title, body) AGAINST ('tutorial') FROM Articles;

这里是输出，返回的行没有排序：

使用ALTER TABLE语句创建FULLTEXT索引

MySQL还提供了ALTER TABLE语句来在现有表上创建全文索引。下面是更清晰的语法示例：

ALTER TABLE table_name  
ADD FULLTEXT (column1, column2,?)

在这个语法中，我们首先需要指定要创建索引的表名。其次，使用 ADD FULLTEXT clause 来为一个或多个列定义全文索引。

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

mysql> ALTER TABLE books ADD FULLTEXT (content, author)

使用CREATE INDEX语句创建FULLTEXT索引

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

CREATE FULLTEXT INDEX index_name
ON table_name (idx_column1, idx_column2,...)

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

mysql> CREATE FULLTEXT INDEX address
ON office (address_line1, address_line2)

如何删除FULLTEXT索引？

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

ALTER TABLE table_name DROP INDEX index_name;

在这个语法中，我们首先指定了一个表的名称，然后是全文索引名称，然后是 DROP INDEX 子句。

例如 ，执行下面的语句将 永久移除地址索引 从名为” offices” 的表中：

mysql> ALTER TABLE offices DROP INDEX address;