MySQL - 布尔全文搜索

MySQL：布尔全文搜索

全文搜索简介

全文搜索提供了一种强大的方式，可以对数据库中基于字符的数据执行复杂搜索。与简单的 LIKE '%word%' 查询不同，全文搜索针对自然语言进行了优化，理解词边界，并能按相关性对结果进行排名。MySQL 通过 FULLTEXT 索引提供此功能。

什么是布尔全文搜索？

布尔全文搜索是一种特定模式，允许您使用特殊运算符构建高度详细和复杂的搜索查询。您不仅可以搜索短语，还可以要求某些词必须存在，某些词必须不存在，并且可以对其他词进行更高或更低的排名。

通过向 AGAINST() 表达式添加 IN BOOLEAN MODE 修饰符来激活此模式。与自然语言搜索不同，布尔模式下的结果不会自动按相关性排序，除非您也计算相关性分数并按其排序。

设置 FULLTEXT 索引

在执行全文搜索之前，您必须在要搜索的列上创建 FULLTEXT 索引。这通常在创建表时完成。我们将使用 InnoDB 存储引擎，它是现代的默认设置。

CREATE TABLE ARTICLES (
    ID INT AUTO_INCREMENT PRIMARY KEY,
    ARTICLE_TITLE VARCHAR(255),
    ARTICLE_BODY TEXT,
    FULLTEXT KEY ft_index (ARTICLE_TITLE, ARTICLE_BODY)
) ENGINE=InnoDB;

INSERT INTO ARTICLES (ARTICLE_TITLE, ARTICLE_BODY) VALUES
('Introduction to SQL', 'SQL is a powerful language for relational database management.'),
('Advanced MySQL Techniques', 'This guide covers advanced MySQL features like indexing and transactions.'),
('Learning Java Basics', 'Java is a versatile, object-oriented programming language.'),
('Data Structures in Java', 'Understanding data structures is key to efficient programming.'),
('Getting Started with Databases', 'A database allows for structured storage and retrieval of data.');

布尔模式语法

SELECT columns, MATCH(indexed_columns) AGAINST('expression' IN BOOLEAN MODE) AS relevance
FROM table_name
WHERE MATCH(indexed_columns) AGAINST('expression' IN BOOLEAN MODE);

MATCH() 子句指定要搜索的索引列，而 AGAINST() 包含您的搜索查询。

关键布尔运算符

运算符	描述	示例
+	该词必须出现在每个返回的行中。	+MySQL +advanced
-	该词不得出现在任何返回的行中。	Java -SQL
(no operator)	该词是可选的，但包含该词的行排名更高。	database language
>	增加该词对相关性分数的贡献。	>database language
<	降低该词对相关性分数的贡献。	<database language
~	抵消该词对相关性分数的贡献。有助于降低垃圾词的排名，但不排除该行。	database ~management
*	通配符运算符。必须附加在单词的末尾。	program*
” “	定义一个字面短语。这些词必须以这个确切的顺序出现。	“relational database”
()	将词分组为子表达式以实现复杂逻辑。	+(database >management)

实际搜索示例

使用我们创建的 ARTICLES 表：

示例 1：必须包含 ‘database’，不得包含 ‘SQL’

SELECT ARTICLE_TITLE, ARTICLE_BODY FROM ARTICLES
WHERE MATCH(ARTICLE_TITLE, ARTICLE_BODY)
AGAINST('+database -SQL' IN BOOLEAN MODE);

这将返回文章 ‘Getting Started with Databases’，但排除 ‘Introduction to SQL’。

示例 2：查找关于 ‘Java’ 或 ‘SQL’ 的文章

SELECT ARTICLE_TITLE FROM ARTICLES
WHERE MATCH(ARTICLE_TITLE, ARTICLE_BODY)
AGAINST('Java SQL' IN BOOLEAN MODE);

没有运算符时，这些词是可选的。这将查找包含 ‘Java’ 或 ‘SQL’（或两者）的任何文章。

示例 3：查找以 ‘program’ 开头的词

SELECT ARTICLE_TITLE, ARTICLE_BODY FROM ARTICLES
WHERE MATCH(ARTICLE_TITLE, ARTICLE_BODY)
AGAINST('program*' IN BOOLEAN MODE);

这将同时匹配 ‘programming’ 和 ‘program’。

InnoDB 的重要注意事项

• 索引： MATCH() 子句中的列强制要求 FULLTEXT 索引。
• 停用词： MySQL 维护一个常用 ‘停用词’（如 ‘a’、‘the’、‘is’）列表，这些词在索引期间被忽略。您可以自定义此列表。
• 最小词长： 默认情况下，短于 3 个字符的词（innodb_ft_min_token_size）不会被索引。您可能需要调整此设置并重建索引才能使其生效。
• 50% 阈值： 忽略在超过 50% 的行中出现的词的这条规则适用于较旧的 MyISAM 引擎。在 InnoDB 中，这不再是一个硬编码的限制。

在客户端应用程序中实现布尔搜索

要在布尔全文搜索中安全地使用用户输入，您应该对其进行清理并使用预处理语句。

Node.js（使用 mysql2/promise）

const mysql = require('mysql2/promise');

async function searchArticles(query) {
  let connection;
  try {
    connection = await mysql.createConnection({ /* connection details */ });

    // 基本清理：确保用户查询包含所需词。
    // 实际应用程序需要更健壮的清理。
    const sanitizedQuery = `+${query.replace(/\s+/g, ' +')}`;

    const sql = `SELECT ARTICLE_TITLE, MATCH(ARTICLE_TITLE, ARTICLE_BODY)
                 AGAINST(? IN BOOLEAN MODE) AS score
                 FROM ARTICLES
                 WHERE MATCH(ARTICLE_TITLE, ARTICLE_BODY)
                 AGAINST(? IN BOOLEAN MODE)
                 ORDER BY score DESC`;

    const [rows] = await connection.execute(sql, [sanitizedQuery, sanitizedQuery]);
    console.log('Search Results:');
    console.log(rows);

  } catch (error) {
    console.error('Search failed:', error);
  } finally {
    if (connection) await connection.end();
  }
}

// 示例用法：查找必须包含 'MySQL' 和 'advanced' 的文章
searchArticles('MySQL advanced');