MySQL - 查询扩展全文搜索

MySQL - 带查询扩展的全文搜索

MySQL 的全文搜索（Full-Text Search，FTS）是一项强大的功能，用于对字符串数据执行复杂的基于文本的搜索。与简单的 LIKE 比较不同，FTS 理解自然语言，根据相关性对结果进行排名，并支持高级搜索模式。

MySQL 中有三种主要的全文搜索模式：

• 自然语言模式（Natural Language Mode）： 默认模式，将搜索字符串解释为自然人类语言中的短语。
• 布尔模式（Boolean Mode）： 允许使用 +（必需）、-（排除）和 *（通配符）等运算符进行复杂查询。
• 查询扩展模式（Query Expansion Mode）： 自然语言模式的一种修改，它会拓宽搜索范围以包含相关术语，这是本教程的重点。

理解查询扩展

通常，用户的搜索查询可能过于简短或具体，他们可能会错过使用同义词或紧密相关概念的相关文档。查询扩展，也称为“盲相关反馈”，通过分两个阶段执行搜索来解决这个问题：

1. 第一阶段： MySQL 首先对用户的原始查询执行标准自然语言搜索。
2. 第二阶段： 然后分析第一阶段中最相关的文档，并提取似乎与初始查询高度相关的词语。随后使用原始查询和这些新的相关词语进行第二次搜索。

这项技术有助于发现用户可能没有直接想到要搜索的结果。它通过在 AGAINST() 函数中添加 WITH QUERY EXPANSION 来激活。

现代背景：InnoDB 和专用搜索引擎

值得注意的是，全文搜索完全受默认的 InnoDB 存储引擎支持。过去，FTS 主要是 MyISAM 引擎的一项功能。对于高需求、大规模的搜索应用，许多现代系统会集成专用的搜索引擎，例如 Elasticsearch 或 OpenSearch。然而，MySQL 内置的 FTS 对于广泛的应用来说，功能强大且非常方便。

示例：搜索技术博客

首先，让我们创建一个博客文章表，并在 title 和 content 列上添加一个 FULLTEXT 索引。

CREATE TABLE articles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    content TEXT,
    FULLTEXT KEY ft_index (title, content)
) ENGINE=InnoDB;

现在，让我们插入一些文章：

INSERT INTO articles (title, content) VALUES
('Introduction to Databases', 'A database is an organized collection of data, generally stored and accessed electronically. SQL is the standard language.'),
('Learning MySQL', 'MySQL is a popular relational database management system (RDBMS). It is a key part of the LAMP stack.'),
('Web Security Fundamentals', 'Protecting your web application is crucial. A secure database prevents data breaches.'),
('Deep Dive into RDBMS', 'Relational models are the foundation for systems like MySQL and PostgreSQL.');

标准搜索与查询扩展

如果用户搜索“MySQL”，标准的自然语言搜索将找到明确提及该词的文档。

SELECT id, title FROM articles
WHERE MATCH(title, content) AGAINST('MySQL' IN NATURAL LANGUAGE MODE);

这很可能会返回文章 2 和 4。现在，让我们使用查询扩展。

SELECT id, title,
    MATCH(title, content) AGAINST('MySQL' WITH QUERY EXPANSION) AS relevance_score
FROM articles
WHERE MATCH(title, content) AGAINST('MySQL' WITH QUERY EXPANSION)
ORDER BY relevance_score DESC;

在这种情况下，结果可能如下所示：

ID	标题	相关性得分
2	Learning MySQL	1.8…
4	Deep Dive into RDBMS	0.9…
1	Introduction to Databases	0.8…

请注意，“MySQL”的搜索结果中也返回了“Introduction to Databases”。这是因为最初的搜索发现了“Learning MySQL”和“Deep Dive into RDBMS”高度相关。从这些文章中，MySQL 可能识别出“database”和“RDBMS”等相关术语。在第二次查询中，它使用这些新术语找到了文章 1，尽管文章 1 不包含“MySQL”一词。我们还选择了 relevance_score 来查看 MySQL 如何对结果进行排名。

在客户端程序中使用查询扩展

以下是如何在 Web 应用程序中实现此搜索功能的示例。

Python 示例

import mysql.connector

def expanded_search(db_config, search_term):
    query = """
        SELECT id, title, content,
               MATCH(title, content) AGAINST(%s WITH QUERY EXPANSION) AS score
        FROM articles
        WHERE MATCH(title, content) AGAINST(%s WITH QUERY EXPANSION)
        ORDER BY score DESC
    """
    try:
        with mysql.connector.connect(**db_config) as cnx:
            with cnx.cursor(dictionary=True) as cursor:
                # 为安全起见使用参数化查询
                cursor.execute(query, (search_term, search_term))
                results = cursor.fetchall()

                print(f"Expanded search results for: '{search_term}'")
                for row in results:
                    print(f"  - ID: {row['id']}, Title: {row['title']}, Score: {row['score']:.4f}")

    except mysql.connector.Error as err:
        print(f"Database Error: {err}")

# 示例用法
db_config = {'user': 'root', 'password': 'password', 'host': '127.0.0.1', 'database': 'your_db'}
expanded_search(db_config, 'MySQL')

Node.js (使用 mysql2) 示例

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

async function expandedSearch(dbConfig, searchTerm) {
    let connection;
    const query = `
        SELECT id, title,
               MATCH(title, content) AGAINST(? WITH QUERY EXPANSION) AS score
        FROM articles
        WHERE MATCH(title, content) AGAINST(? WITH QUERY EXPANSION)
        ORDER BY score DESC
    `;
    try {
        connection = await mysql.createConnection(dbConfig);
        const [rows] = await connection.execute(query, [searchTerm, searchTerm]);

        console.log(`Expanded search results for: '${searchTerm}'`);
        rows.forEach(row => {
            console.log(`  - ID: ${row.id}, Title: ${row.title}, Score: ${row.score.toFixed(4)}`);
        });

    } catch (err) {
        console.error(`Database Error: ${err.message}`);
    } finally {
        if (connection) await connection.end();
    }
}

// 示例用法
const dbConfig = { host: 'localhost', user: 'root', password: 'password', database: 'your_db' };
expandedSearch(dbConfig, 'MySQL');