MySQL教程
 MySQL REGEXP_SUBSTR()函数 MySQL 自然语言全文搜索 

MySQL 全文搜索(FTS)

一、MySQL 全文搜索(FTS) 介绍

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

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

例如，我们搜索了植物和树木。FTS 会检查这两个词，并可以分别返回包含搜索词的结果，这些词的顺序不同，植物和树或树和植物，或者只是植物和树。它有利于用户猜测他们想要什么并更快地返回相关结果。

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

二、MySQL全文搜索的要点

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

三、MySQL 全文搜索(FTS) 语法

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

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

在此语法中，我们将首先指定 MATCH () 函数，该函数包含要搜索的以逗号分隔的列名列表。然后，AGAINST () 函数接受一个字符串进行搜索，并带有一个可选的修饰符来指示执行哪种类型的搜索。search_modifier可以是自然语言模式或带有查询扩展的自然语言模式或布尔模式或带有查询扩展。

四、MySQL 全文搜索功能

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

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

五、MySQL 全文搜索限制

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

六、全文搜索类型

全文搜索主要分为三种：

自然语言搜索类型

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

查询扩展搜索类型

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

布尔搜索类型

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

七、MySQL 全文搜索(FTS) 示例

我们必须确保在对表的列执行全文搜索之前需要对其数据进行索引。每当列数据发生变化时，MySQL 会自动重新创建全文索引。在 MySQL 中，全文索引总是命名为 FULLTEXT。我们可以定义列数据类型为CHAR、VARCHAR和TEXT的全文索引。

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

使用 CREATE TABLE 语句创建 FULLTEXT 索引

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

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

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

下面的语句创建了一个名为Articles的新表。此表有一个 FULLTEXT 索引，其中包括文章内容列：

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 子句定义一个或多个列的全文索引。

例如，我们有一个名为book的表，其中包含 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。现在，我们可以使用以下语句为这些列创建 FULLTEXT 索引：

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子句。

例如，执行以下语句将从“ office”表中永久删除地址索引：

mysql> ALTER TABLE offices DROP INDEX address;

 

 MySQL REGEXP_SUBSTR()函数 MySQL 自然语言全文搜索 

热门文章

优秀文章

全部教程