MySQL 全文搜索功能

简介

在这个实验中，你将探索 MySQL 的全文搜索功能。首先，你要创建一个数据库和一个名为 articles 的表，该表包含 id、title 和 content 列，然后用示例数据填充该表。

接下来，你将使用 ALTER TABLE 语句为 articles 表添加一个名为 article_index 的全文索引，该索引特别针对 title 和 content 列。最后，你将使用 SHOW INDEXES 命令验证索引是否创建成功，确认 Index_type 为 FULLTEXT。这样的设置能让你在 MySQL 数据库中进行高效的文本搜索。

Skills Graph

为表添加全文索引

在这一步中，我们将为 MySQL 数据库中的一个表添加全文索引。全文索引用于对基于字符的数据进行高效的文本搜索。

在开始之前，让我们创建一个名为 articles 的简单表来进行操作。打开你的终端，使用以下命令连接到 MySQL 服务器：

mysql -u root -p

系统会提示你输入 root 密码。输入密码以完成连接。

现在，让我们创建一个名为 search_db 的数据库并切换到该数据库：

CREATE DATABASE IF NOT EXISTS search_db;
USE search_db;

接下来，创建包含 id、title 和 content 列的 articles 表：

CREATE TABLE articles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    content TEXT
);

现在，让我们向 articles 表中插入一些示例数据：

INSERT INTO articles (title, content) VALUES
('MySQL Full-Text Search', 'This article explains how to use full-text search in MySQL.'),
('Indexing in MySQL', 'Learn about different types of indexes in MySQL, including full-text indexes.'),
('Optimizing MySQL Queries', 'Tips and tricks for optimizing your MySQL queries for better performance.');

要为 articles 表添加全文索引，我们使用 ALTER TABLE 语句。我们将在 title 和 content 列上创建一个全文索引。执行以下 SQL 命令：

ALTER TABLE articles ADD FULLTEXT INDEX article_index (title, content);

此命令创建了一个名为 article_index 的全文索引，该索引包含 title 和 content 列。现在，MySQL 可以使用全文搜索功能对这些列进行高效搜索。

你可以使用 SHOW INDEXES 命令验证索引是否已创建：

SHOW INDEXES FROM articles;

输出结果应包含 article_index 全文索引的行。该索引的 Index_type 列将显示 FULLTEXT。

+----------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table    | Non_unique | Key_name       | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+----------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| articles |          0 | PRIMARY        |            1 | id          | A         |           3 |     NULL | NULL   |      | BTREE      |         |               | YES     | NULL       |
| articles |          1 | article_index  |            1 | title       | NULL      |           3 |     NULL | NULL   | YES  | FULLTEXT   |         |               | YES     | NULL       |
| articles |          1 | article_index  |            2 | content     | NULL      |           3 |     NULL | NULL   | YES  | FULLTEXT   |         |               | YES     | NULL       |
+----------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
3 rows in set (0.00 sec)

现在，你已成功为表添加了全文索引。在接下来的步骤中，你将学习如何使用此索引进行全文搜索。

使用 MATCH AGAINST 进行搜索

在这一步中，我们将使用 MATCH AGAINST 子句对在上一步中创建的 articles 表执行全文搜索。

首先，确保你已连接到 MySQL 服务器并使用 search_db 数据库。如果你关闭了连接，请使用以下命令重新连接：

mysql -u root -p

系统提示时输入 root 密码。然后，选择数据库：

USE search_db;

MATCH AGAINST 子句用于 SELECT 语句的 WHERE 子句中，以执行全文搜索。基本语法如下：

SELECT * FROM table_name WHERE MATCH (column1, column2, ...) AGAINST ('search_term');

这里，column1、column2 等是全文索引的一部分列，'search_term' 是你要搜索的文本。

让我们对包含 "MySQL" 一词的文章进行简单搜索：

SELECT * FROM articles WHERE MATCH (title, content) AGAINST ('MySQL');

此查询将返回 articles 表中 title 或 content 列包含 "MySQL" 一词的所有行。

你应该会看到以下输出：

+----+--------------------------+---------------------------------------------------------------------+
| id | title                    | content                                                             |
+----+--------------------------+---------------------------------------------------------------------+
|  1 | MySQL Full-Text Search   | This article explains how to use full-text search in MySQL.         |
|  2 | Indexing in MySQL        | Learn about different types of indexes in MySQL, including full-text indexes. |
|  3 | Optimizing MySQL Queries | Tips and tricks for optimizing your MySQL queries for better performance. |
+----+--------------------------+---------------------------------------------------------------------+
3 rows in set (0.00 sec)

注意，返回了所有三篇文章，因为它们的标题或内容中都包含 "MySQL" 一词。

MATCH AGAINST 子句还可用于根据相关性对结果进行排序。MySQL 根据每行与搜索词的匹配程度为其分配一个相关性得分。你可以在 SELECT 列表中使用 MATCH AGAINST 子句来检索此得分：

SELECT id, title, content, MATCH (title, content) AGAINST ('MySQL') AS relevance FROM articles;

此查询返回每篇文章的 id、title、content 和 relevance 得分。相关性得分越高，匹配度越好。

+----+--------------------------+---------------------------------------------------------------------+--------------------+
| id | title                    | content                                                             | relevance          |
+----+--------------------------+---------------------------------------------------------------------+--------------------+
|  1 | MySQL Full-Text Search   | This article explains how to use full-text search in MySQL.         | 1.34832763671875   |
|  2 | Indexing in MySQL        | Learn about different types of indexes in MySQL, including full-text indexes. | 0.5215404033660889 |
|  3 | Optimizing MySQL Queries | Tips and tricks for optimizing your MySQL queries for better performance. | 0.5215404033660889 |
+----+--------------------------+---------------------------------------------------------------------+--------------------+
3 rows in set (0.00 sec)

然后，你可以按相关性对结果进行排序，以优先显示最匹配的结果：

SELECT id, title, content, MATCH (title, content) AGAINST ('MySQL') AS relevance FROM articles ORDER BY relevance DESC;

此查询将返回与之前相同的结果，但按 relevance 得分降序排列。

+----+--------------------------+---------------------------------------------------------------------+--------------------+
| id | title                    | content                                                             | relevance          |
+----+--------------------------+---------------------------------------------------------------------+--------------------+
|  1 | MySQL Full-Text Search   | This article explains how to use full-text search in MySQL.         | 1.34832763671875   |
|  2 | Indexing in MySQL        | Learn about different types of indexes in MySQL, including full-text indexes. | 0.5215404033660889 |
|  3 | Optimizing MySQL Queries | Tips and tricks for optimizing your MySQL queries for better performance. | 0.5215404033660889 |
+----+--------------------------+---------------------------------------------------------------------+--------------------+
3 rows in set (0.00 sec)

在这一步中，你学习了如何使用 MATCH AGAINST 子句执行基本的全文搜索，以及如何按相关性对结果进行排序。在下一步中，你将学习如何使用布尔模式实现更高级的搜索功能。

使用布尔模式进行高级搜索

在这一步中，我们将探索 MATCH AGAINST 子句的布尔模式，它允许进行更精确、更灵活的全文搜索。布尔模式提供了一些操作符，可用于在搜索查询中指定必需、禁止和可选的词汇。

首先，确保你已连接到 MySQL 服务器并使用 search_db 数据库。如果你关闭了连接，请使用以下命令重新连接：

mysql -u root -p

系统提示时输入 root 密码。然后，选择数据库：

USE search_db;

要使用布尔模式，你需要在 MATCH AGAINST 语句中添加 IN BOOLEAN MODE 子句。语法如下：

SELECT * FROM table_name WHERE MATCH (column1, column2, ...) AGAINST ('search_term' IN BOOLEAN MODE);

以下是布尔模式中可用的一些操作符：

• +：返回的每一行都必须包含该词汇。
• -：返回的任何一行都不能包含该词汇。
• >：该词汇会增加行的相关性。
• <：该词汇会降低行的相关性。
• ~：该词汇对行的相关性有负面贡献。
• *：通配符操作符。
• ""：定义一个短语。

让我们从一个简单的例子开始。假设我们要查找 必须 包含 "MySQL" 一词且 不能 包含 "optimizing" 一词的文章。我们可以使用以下查询：

SELECT * FROM articles WHERE MATCH (title, content) AGAINST ('+MySQL -optimizing' IN BOOLEAN MODE);

此查询将返回包含 "MySQL" 但不包含 "optimizing" 的行。

你应该会看到以下输出：

+----+------------------------+---------------------------------------------------------------------+
| id | title                  | content                                                             |
+----+------------------------+---------------------------------------------------------------------+
|  1 | MySQL Full-Text Search | This article explains how to use full-text search in MySQL.         |
|  2 | Indexing in MySQL      | Learn about different types of indexes in MySQL, including full-text indexes. |
+----+------------------------+---------------------------------------------------------------------+
2 rows in set (0.00 sec)

注意，标题为 "Optimizing MySQL Queries" 的文章没有被返回，因为它包含 "optimizing" 一词。

现在，让我们尝试一个需要 "MySQL" 且优先考虑 "indexing" 的查询：

SELECT * FROM articles WHERE MATCH (title, content) AGAINST ('+MySQL indexing' IN BOOLEAN MODE);

在布尔模式中，没有任何操作符时，词汇被视为可选的。此查询要求包含 "MySQL"，并可选择包含 "indexing"。

输出将是：

+----+--------------------------+---------------------------------------------------------------------+
| id | title                    | content                                                             |
+----+--------------------------+---------------------------------------------------------------------+
|  1 | MySQL Full-Text Search   | This article explains how to use full-text search in MySQL.         |
|  2 | Indexing in MySQL        | Learn about different types of indexes in MySQL, including full-text indexes. |
|  3 | Optimizing MySQL Queries | Tips and tricks for optimizing your MySQL queries for better performance. |
+----+--------------------------+---------------------------------------------------------------------+
3 rows in set (0.00 sec)

所有三篇文章都被返回，因为它们都包含 "MySQL"。"indexing" 的存在是可选的。

让我们使用 > 操作符来提高包含 "indexing" 的行的相关性：

SELECT * FROM articles WHERE MATCH (title, content) AGAINST ('+MySQL >indexing' IN BOOLEAN MODE);

此查询仍然要求包含 "MySQL"，但现在包含 "indexing" 的行将被赋予更高的排名（尽管在这个例子中我们没有按相关性排序）。输出保持不变，但内部的相关性得分会受到影响。

最后，让我们使用双引号来搜索一个短语：

SELECT * FROM articles WHERE MATCH (title, content) AGAINST ('"full-text search"' IN BOOLEAN MODE);

此查询将搜索确切的短语 "full-text search"。

输出将是：

+----+------------------------+---------------------------------------------------------------------+
| id | title                  | content                                                             |
+----+------------------------+---------------------------------------------------------------------+
|  1 | MySQL Full-Text Search | This article explains how to use full-text search in MySQL.         |
|  2 | Indexing in MySQL      | Learn about different types of indexes in MySQL, including full-text indexes. |
+----+------------------------+---------------------------------------------------------------------+
2 rows in set (0.00 sec)

在这一步中，你学习了如何在 MATCH AGAINST 子句中使用布尔模式，通过 +、-、>、< 和 "" 等操作符进行更高级、更精确的全文搜索。在下一步中，你将测试并优化你的全文查询。

测试全文查询结果

在这一步中，我们将测试并优化全文查询，以确保它们能返回预期的结果。我们会尝试不同的搜索词和布尔运算符，以实现尽可能高的搜索准确性。

首先，确保你已连接到 MySQL 服务器并使用 search_db 数据库。如果你关闭了连接，请使用以下命令重新连接：

mysql -u root -p

系统提示时输入 root 密码。然后，选择数据库：

USE search_db;

让我们先查看 articles 表中的数据：

SELECT * FROM articles;

这将显示表的当前内容：

+----+--------------------------+---------------------------------------------------------------------+
| id | title                    | content                                                             |
+----+--------------------------+---------------------------------------------------------------------+
|  1 | MySQL Full-Text Search   | This article explains how to use full-text search in MySQL.         |
|  2 | Indexing in MySQL        | Learn about different types of indexes in MySQL, including full-text indexes. |
|  3 | Optimizing MySQL Queries | Tips and tricks for optimizing your MySQL queries for better performance. |
+----+--------------------------+---------------------------------------------------------------------+
3 rows in set (0.00 sec)

现在，让我们尝试一个更复杂的布尔查询。假设我们要查找包含 "MySQL" 以及 "indexing" 或 "optimizing" 的文章。我们可以使用括号对可选术语进行分组来实现这一点：

SELECT * FROM articles WHERE MATCH (title, content) AGAINST ('+MySQL +(indexing optimizing)' IN BOOLEAN MODE);

此查询要求包含 "MySQL"，并且要求包含 "indexing" 或 "optimizing" 中的任意一个。

输出将是：

+----+--------------------------+---------------------------------------------------------------------+
| id | title                    | content                                                             |
+----+--------------------------+---------------------------------------------------------------------+
|  2 | Indexing in MySQL        | Learn about different types of indexes in MySQL, including full-text indexes. |
|  3 | Optimizing MySQL Queries | Tips and tricks for optimizing your MySQL queries for better performance. |
+----+--------------------------+---------------------------------------------------------------------+
2 rows in set (0.00 sec)

第 1 篇文章被排除在外，因为它只包含 "MySQL"，而不包含 "indexing" 或 "optimizing"。

让我们尝试另一种情况。假设我们要查找关于 "MySQL" 但特别 不 关于 "queries" 的文章。

SELECT * FROM articles WHERE MATCH (title, content) AGAINST ('+MySQL -queries' IN BOOLEAN MODE);

此查询要求包含 "MySQL"，并排除包含 "queries" 的文章。

输出将是：

+----+------------------------+---------------------------------------------------------------------+
| id | title                  | content                                                             |
+----+------------------------+---------------------------------------------------------------------+
|  1 | MySQL Full-Text Search | This article explains how to use full-text search in MySQL.         |
|  2 | Indexing in MySQL      | Learn about different types of indexes in MySQL, including full-text indexes. |
+----+------------------------+---------------------------------------------------------------------+
2 rows in set (0.00 sec)

关于 "MySQL Queries" 的第 3 篇文章被排除在外。

现在，让我们尝试对结果进行排序。我们可以使用 > 和 < 运算符来影响相关性得分。假设我们要查找关于 "MySQL" 的文章，但我们希望优先显示也提到 "indexing" 的文章：

SELECT id, title, content, MATCH (title, content) AGAINST ('+MySQL >indexing' IN BOOLEAN MODE) AS relevance FROM articles WHERE MATCH (title, content) AGAINST ('+MySQL >indexing' IN BOOLEAN MODE) ORDER BY relevance DESC;

此查询要求包含 "MySQL"，并提高包含 "indexing" 的文章的相关性。然后，结果将按相关性降序排列。

输出将是：

+----+--------------------------+---------------------------------------------------------------------+-----------+
| id | title                    | content                                                             | relevance |
+----+--------------------------+---------------------------------------------------------------------+-----------+
|  2 | Indexing in MySQL        | Learn about different types of indexes in MySQL, including full-text indexes. |  1.6931  |
|  1 | MySQL Full-Text Search   | This article explains how to use full-text search in MySQL.         |  0.3068  |
|  3 | Optimizing MySQL Queries | Tips and tricks for optimizing your MySQL queries for better performance. |  0.3068  |
+----+--------------------------+---------------------------------------------------------------------+-----------+
3 rows in set (0.00 sec)

注意，"Indexing in MySQL" 文章现在排在第一位，因为由于 >indexing 条件，它的相关性得分更高。

通过尝试不同的搜索词和布尔运算符，你可以微调全文查询以获得预期的结果。请记住，始终要彻底测试你的查询，以确保它们按预期工作。

至此，关于 MySQL 全文搜索的实验就结束了。你已经学习了如何添加全文索引、使用 MATCH AGAINST 执行基本搜索、使用布尔模式进行高级搜索，以及测试查询结果。

总结

在本次实验中，我们首先创建了一个名为 search_db 的数据库，以及一个包含 id、title 和 content 列的 articles 表。随后，我们向该表中插入了示例数据，以填充文章内容。

此步骤的核心是使用 ALTER TABLE 语句为 articles 表添加一个名为 article_index 的全文索引，该索引特别针对 title 和 content 列。这使得文本搜索能够高效进行。最后，我们使用 SHOW INDEXES 命令验证了索引的创建，确认 Index_type 为 FULLTEXT。