MySQL - 自然语言全文搜索

MySQL：自然语言全文搜索

标准 SQL 的 LIKE 子句通常不足以在大段文本上执行有意义的搜索。MySQL 的全文搜索 (Full-Text Search) 功能提供了一种复杂而强大的方式来在文本类型列中搜索关键词。IN NATURAL LANGUAGE MODE 是默认且最直观的搜索模式，它根据相关性对结果进行排名。

MySQL 的全文搜索引擎提供三种主要模式：

• 自然语言模式 (Natural Language Mode)：将搜索字符串解释为自然人类语言中的短语，并根据相关性返回结果。
• 布尔模式 (Boolean Mode)：允许使用 +（必须包含）、-（必须不包含）和 *（通配符）等运算符进行复杂搜索。
• 查询扩展模式 (Query Expansion Mode)：一种两阶段搜索，扩展搜索范围以包含与初始搜索词常用关联的词语。

本教程重点介绍 IN NATURAL LANGUAGE MODE。

设置全文搜索

要使用全文搜索，您必须首先在您希望搜索的文本类型列（例如，CHAR、VARCHAR、TEXT）上创建一个 FULLTEXT 索引（全文索引）。这个特殊的索引允许 MySQL 高效地解析和定位文本中的关键词。

语法

自然语言全文搜索的基本语法使用 MATCH() 和 AGAINST() 函数：

SELECT column1, column2, ...
FROM table_name
WHERE MATCH(column_to_search1, column_to_search2)
AGAINST('your search phrase' IN NATURAL LANGUAGE MODE);

IN NATURAL LANGUAGE MODE 部分是可选的，因为它是默认行为，但明确地写出来可以提高清晰度。

示例

让我们创建一个 articles 表并填充一些数据。我们将在 title 和 content 列上创建 FULLTEXT 索引。

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

现在，插入一些记录：

INSERT INTO articles(title, content) VALUES
('Introduction to SQL', 'SQL is the standard language for relational database management systems.'),
('Advanced MySQL Techniques', 'This guide covers advanced topics in MySQL, including indexing and database optimization.'),
('Learning a New Programming Language', 'Java and Python are popular programming languages for backend development.'),
('The Importance of Database Backups', 'Regular database backups are crucial for disaster recovery.');

让我们搜索关于 ‘database management’ 的文章。

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

输出与相关性排名

MySQL 会为每一行计算一个相关性分数。相关性分数较高的行会首先列出。该分数基于找到的关键词数量及其在索引文档中的稀有程度等因素。

id	title	content
1	Introduction to SQL	SQL is the standard language for relational database management systems.
4	The Importance of Database Backups	Regular database backups are crucial for disaster recovery.
2	Advanced MySQL Techniques	This guide covers advanced topics in MySQL, including indexing and database optimization.

请注意，第一个结果同时包含 ‘database’ 和 ‘management’（通过 ‘systems’ 隐式包含），因此它是最相关的。其他结果包含 ‘database’ 并且也一同返回。

停用词和词长

全文引擎会忽略非常常见的词，称为停用词 (Stopwords)（例如 ‘the’、‘a’、‘is’、‘in’），以提高性能和相关性。它还会忽略短于某个长度的词。默认最小词长由 innodb_ft_min_token_size（针对 InnoDB）或 ft_min_word_len（针对 MyISAM）服务器变量控制。如果需要，您可以在 MySQL 配置中自定义停用词列表和最小词长。

示例：停用词的效果

搜索 ‘database and a language’ 可能会产生与只搜索 ‘database language’ 类似的结果，因为 ‘and’ 和 ‘a’ 是停用词，将被忽略。

SELECT id, title FROM articles
WHERE MATCH(title, content)
AGAINST('database and a language' IN NATURAL LANGUAGE MODE);

结果将根据 ‘database’ 和 ‘language’ 的存在进行排名。

通过客户端程序执行全文搜索

以下是如何从现代应用程序执行全文搜索的示例，确保安全地处理用户输入。

语法概览

Node.js
Python
Java
PHP



Using `mysql2/promise` with parameterized queries to prevent SQL injection:

const query = 'SELECT ... WHERE MATCH(title, content) AGAINST(? IN NATURAL LANGUAGE MODE)';
const [rows] = await connection.execute(query, [searchTerm]);



Using `mysql-connector-python` with parameter substitution:

query = 'SELECT ... WHERE MATCH(title, content) AGAINST(%s IN NATURAL LANGUAGE MODE)'
cursor.execute(query, (search_term,))
results = cursor.fetchall()



Using JDBC with `PreparedStatement` for security and performance:

String sql = "SELECT ... WHERE MATCH(title, content) AGAINST(? IN NATURAL LANGUAGE MODE)";
try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
    pstmt.setString(1, searchTerm);
    ResultSet rs = pstmt.executeQuery();
    // ... 处理结果
}



Using PDO with prepared statements:

$stmt = $pdo->prepare('SELECT ... WHERE MATCH(title, content) AGAINST(? IN NATURAL LANGUAGE MODE)');
$stmt->execute([$searchTerm]);
$results = $stmt->fetchAll();

完整示例 (Java)

这个 Java 示例使用 PreparedStatement 安全地执行带有用户提供的词语的全文搜索。

import java.sql.*;

public class FullTextSearchExample {
    // --- 配置 ---
    private static final String DB_URL = "jdbc:mysql://localhost:3306/your_database";
    private static final String USER = "your_user";
    private static final String PASS = "your_password";

    public static void searchArticles(String searchTerm) {
        String sql = "SELECT id, title FROM articles " +
                     "WHERE MATCH(title, content) AGAINST(? IN NATURAL LANGUAGE MODE)";

        System.out.println("\nSearching for articles matching: '" + searchTerm + "'\n");

        // 使用 try-with-resources 进行自动资源管理
        try (Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setString(1, searchTerm);

            try (ResultSet rs = pstmt.executeQuery()) {
                boolean found = false;
                while (rs.next()) {
                    found = true;
                    System.out.printf("ID: %d, Title: %s%n",
                            rs.getInt("id"),
                            rs.getString("title"));
                }
                if (!found) {
                    System.out.println("No matching articles found.");
                }
            }
        } catch (SQLException e) {
            System.err.println("Database error occurred:");
            e.printStackTrace();
        }
    }

    public static void main(String[] args) {
        // 加载 MySQL 驱动
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            System.err.println("MySQL JDBC Driver not found.");
            return;
        }
        searchArticles("database optimization");
        searchArticles("learning python");
    }
}

/*
预期输出：

Searching for articles matching: 'database optimization'

ID: 2, Title: Advanced MySQL Techniques
ID: 4, Title: The Importance of Database Backups

Searching for articles matching: 'learning python'

ID: 3, Title: Learning a New Programming Language
*/