MySQL - 全文搜索

MySQL - 现代全文搜索

全文搜索 (FTS) 简介

MySQL 中的全文搜索（Full-Text Search，FTS）提供了一种强大的方式，可以对数据库中存储的字符数据执行复杂的基于文本的查询。与简单的 LIKE 模式匹配不同，FTS 针对自然语言进行了优化，允许你查找与短语最相关的记录，而不仅仅是包含精确子字符串的记录。

要在一个或多个列上启用 FTS，你必须首先在它们上面创建 FULLTEXT 索引。此索引将文本内容进行分词（tokenizes），从而创建高效的查找结构以搜索单词和短语。

存储引擎：InnoDB 与 MyISAM

MySQL 支持在使用 InnoDB 或 MyISAM 存储引擎的表上创建 FULLTEXT 索引。然而，自 MySQL 5.5 以来，InnoDB 一直是默认的存储引擎，并且由于其对事务、外键和崩溃恢复的支持，几乎所有用例都推荐使用它。

• InnoDB: 现代的默认选择。自 MySQL 5.6 起支持全文搜索。它是一个事务性的、崩溃安全的引擎，应作为你的首选。
• MyISAM: 一个遗留的存储引擎。虽然它在历史上以 FTS 闻名，但它缺乏 InnoDB 的可靠性和功能。你只有在有特定遗留原因时才应使用它。

本教程将专注于使用 InnoDB 存储引擎进行 FTS。

创建 FULLTEXT 索引

你可以在创建表时添加 FULLTEXT 索引，或者将其添加到现有表。对于大型数据集，先加载数据再创建索引会显著更快。

1. 表创建期间 (CREATE TABLE)

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

2. 在现有表上 (ALTER TABLE)

ALTER TABLE articles
ADD FULLTEXT KEY ft_title_content (title, content);

3. 使用 CREATE INDEX

CREATE FULLTEXT INDEX ft_title_content
ON articles(title, content);

执行全文搜索

搜索使用 WHERE 或 ORDER BY 子句中的 MATCH() 和 AGAINST() 函数执行。MATCH() 指定要搜索的索引列，AGAINST() 提供搜索查询。

SELECT title, content, MATCH(title, content) AGAINST('search query') AS relevance_score
FROM articles
WHERE MATCH(title, content) AGAINST('search query');

让我们填充 articles 表并探索不同的搜索模式。

INSERT INTO articles (title, content)
VALUES
('Introduction to SQL', 'SQL is a powerful language for database management.'),
('Modern JavaScript Features', 'This article covers modern features like async/await in JavaScript.'),
('Database Indexing Explained', 'Indexing is crucial for database performance and fast queries.'),
('A Guide to SQL Joins', 'Learn how to combine rows from two or more tables using SQL.');

自然语言模式（默认）

此模式将搜索字符串解释为自然短语。它查找与查询相关的行。结果会自动按相关性排序，相关性得分越高，排名越靠前。

-- Search for articles about 'SQL database'
SELECT title, MATCH(title, content) AGAINST('SQL database' IN NATURAL LANGUAGE MODE) AS score
FROM articles
WHERE MATCH(title, content) AGAINST('SQL database' IN NATURAL LANGUAGE MODE);

输出

title	score
Introduction to SQL	1.312…
Database Indexing Explained	0.678…
A Guide to SQL Joins	0.634…

布尔模式

布尔模式允许使用特殊运算符进行更复杂的搜索。此模式不会自动按相关性排序。

• +: 该词必须存在。+SQL -Joins
• -: 该词必须不存在。database -performance
• >: 增加该词对相关性得分的贡献。<: 降低该词的贡献。
• *: 用于以特定前缀开头的单词的通配符。data* 匹配 ‘database’, ‘data’。

-- Find articles containing 'SQL' but NOT 'Joins'
SELECT title
FROM articles
WHERE MATCH(title, content) AGAINST('+SQL -Joins' IN BOOLEAN MODE);

输出

title
Introduction to SQL

查询扩展模式

这是一种高级模式，可以扩大搜索范围。它首先执行自然语言搜索，然后将初始结果中最相关的词添加到搜索字符串中并再次执行搜索。它对于查找相关概念很有用。

-- Search for 'database' and related terms
SELECT title
FROM articles
WHERE MATCH(title, content) AGAINST('database' WITH QUERY EXPANSION);

删除 FULLTEXT 索引

要删除 FULLTEXT 索引，你需要使用 DROP INDEX 命令和 ALTER TABLE。你必须知道索引的名称。

-- Find the index name first if you don't know it
SHOW CREATE TABLE articles;

-- Drop the index
ALTER TABLE articles DROP INDEX ft_title_content;

性能考虑和最佳实践

• 索引策略： 只索引你实际会搜索的列。包含不必要的列会使索引膨胀并降低写入速度。
• 停用词（Stopwords）： MySQL 会忽略非常常见的词（如 ‘the’、‘is’、‘a’），这些词被称为停用词。如果需要，你可以自定义停用词列表。
• 最小单词长度： 默认情况下，InnoDB 只索引长度为 3 个或更多字符的单词（innodb_ft_min_token_size）。如果你需要搜索更短的单词，你必须更改此服务器配置并重建你的索引。
• 替代解决方案： 对于非常大规模或复杂的搜索需求（例如，分面搜索、地理搜索、聚合），专门的搜索引擎如 Elasticsearch 或 OpenSearch 性能更优。MySQL FTS 非常适合中等规模、集成式的搜索需求。

常见错误和调试

• 错误 1191: Can’t find FULLTEXT index…：这意味着你正在尝试在没有定义 FULLTEXT 索引的列上使用 MATCH() ... AGAINST()，或者 MATCH() 中的列与索引定义中的列不完全匹配。
• 未返回结果： 检查最小单词长度。如果你搜索 ‘js’ 且最小长度为 3，你将不会得到任何结果。此外，在超过 50% 的行中出现的单词在自然语言搜索中默认被视为停用词并被忽略。

使用客户端应用程序连接（现代示例）

以下是如何从现代编程环境执行全文搜索，使用异步操作和预处理语句等最佳实践的示例。

Node.js
Python
PHP
Java



// Using mysql2/promise for async/await
// npm install mysql2

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

async function searchArticles(searchTerm) {
  let connection;
  try {
    connection = await mysql.createConnection({
      host: 'localhost',
      user: 'root',
      password: 'password',
      database: 'TUTORIALS'
    });

    const query = `
      SELECT title, MATCH(title, content) AGAINST(? IN NATURAL LANGUAGE MODE) AS score
      FROM articles
      WHERE MATCH(title, content) AGAINST(? IN NATURAL LANGUAGE MODE)
      ORDER BY score DESC;
    `;

    const [rows] = await connection.execute(query, [searchTerm, searchTerm]);
    console.log(`Search results for '${searchTerm}':`);
    console.log(rows);
    return rows;
  } catch (error) {
    console.error('An error occurred:', error);
  } finally {
    if (connection) await connection.end();
  }
}

searchArticles('SQL database');




# Using mysql-connector-python and context managers
# pip install mysql-connector-python

import mysql.connector
from mysql.connector import errorcode

def search_articles(search_term):
    config = {
        'user': 'root',
        'password': 'password',
        'host': 'localhost',
        'database': 'TUTORIALS'
    }
    query = ("""
        SELECT title, MATCH(title, content) AGAINST(%s IN NATURAL LANGUAGE MODE) AS score
        FROM articles
        WHERE MATCH(title, content) AGAINST(%s IN NATURAL LANGUAGE MODE)
        ORDER BY score DESC
    """)

    try:
        with mysql.connector.connect(**config) as connection:
            with connection.cursor(dictionary=True) as cursor:
                cursor.execute(query, (search_term, search_term))
                results = cursor.fetchall()
                print(f"Search results for '{search_term}':")
                for row in results:
                    print(row)
                return results
    except mysql.connector.Error as err:
        print(f"An error occurred: {err}")

search_articles('SQL database')




// Using PDO for secure, prepared statements

<?php
$host = 'localhost';
$db   = 'TUTORIALS';
$user = 'root';
$pass = 'password';
$charset = 'utf8mb4';

$dsn = "mysql:host=$host;dbname=$db;charset=$charset";
$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
];

function searchArticles(string $searchTerm, PDO $pdo): array
{
    $query = <<<'SQL'
        SELECT title, MATCH(title, content) AGAINST(? IN NATURAL LANGUAGE MODE) AS score
        FROM articles
        WHERE MATCH(title, content) AGAINST(? IN NATURAL LANGUAGE MODE)
        ORDER BY score DESC
    SQL;

    try {
        $stmt = $pdo->prepare($query);
        $stmt->execute([$searchTerm, $searchTerm]);
        return $stmt->fetchAll();
    } catch (PDOException $e) {
        throw new PDOException($e->getMessage(), (int)$e->getCode());
    }
}

try {
    $pdo = new PDO($dsn, $user, $pass, $options);
    $results = searchArticles('SQL database', $pdo);
    echo "Search results for 'SQL database':\n";
    print_r($results);
} catch (PDOException $e) {
     die("DB ERROR: " . $e->getMessage());
}




// Using modern JDBC with try-with-resources

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class FullTextSearchExample {
    private static final String URL = "jdbc:mysql://localhost:3306/TUTORIALS";
    private static final String USER = "root";
    private static final String PASSWORD = "password";

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

        try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setString(1, searchTerm);
            pstmt.setString(2, searchTerm);

            try (ResultSet rs = pstmt.executeQuery()) {
                System.out.println("Search results for '" + searchTerm + "':");
                while (rs.next()) {
                    System.out.printf("Title: %s, Score: %f%n",
                            rs.getString("title"),
                            rs.getFloat("score"));
                }
            }
        } catch (SQLException e) {
            System.err.println("SQL Error: " + e.getMessage());
            e.printStackTrace();
        }
    }

    public static void main(String[] args) {
        searchArticles("SQL database");
    }
}