全文本搜索

FULLTEXT 索引 / MATCH AGAINST / 搜索模式 / 中文搜索配置 / 与 LIKE 的区别

LIKE 搜索有什么问题？

前面学过滤时，我们用 LIKE '%关键词%' 来搜索文本。这在少量数据时够用，但数据一多就暴露问题了：

-- 搜文章标题里包含"MySQL教程"的行
SELECT * FROM articles WHERE title LIKE '%MySQL教程%';

痛点：

• 慢 — LIKE 不走索引，几百万行时全表扫描，秒级变分钟级
• 傻 — "MySQL" 和 "mysql" 可能被当成两个东西
• 不会排序 — 所有匹配行一视同仁，没法区分"高度相关"和"勉强沾边"

全文本搜索就是来解决这些问题的。

全文本搜索 vs LIKE

	LIKE	全文本搜索
性能	全表扫描，数据量大时慢	走 FULLTEXT 索引，快很多
排序	无相关性排序	自动按相关度排序
分词	不分词，纯字符串匹配	分词后搜索，忽略常见词
中英文	都一样	英文天然支持，中文需要配置

⚠️ 全文本搜索对英文开箱即用，对中文需要额外配置分词器（如 N-gram）。如果你主要处理中文数据，通常会用 Elasticsearch 或 MySQL 的 N-gram 解析器。

创建 FULLTEXT 索引

全文本搜索需要在被搜索的列上建 FULLTEXT 索引：

-- 建表时创建
CREATE TABLE articles (
    id      INT AUTO_INCREMENT PRIMARY KEY,
    title   VARCHAR(200),
    content TEXT,
    FULLTEXT (title, content)
) ENGINE=InnoDB;

-- 给已有表加索引
ALTER TABLE articles ADD FULLTEXT (title, content);

只有 InnoDB（MySQL 5.6+）和 MyISAM 引擎支持 FULLTEXT。

插入一些测试数据：

INSERT INTO articles (title, content) VALUES
('MySQL基础教程', 'MySQL是流行的开源数据库，广泛应用于Web开发。'),
('Python数据分析', 'Python在数据科学领域非常流行，配合Pandas使用。'),
('MySQL性能优化', '优化MySQL查询性能，索引、慢查询和缓存是关键。'),
('数据库选型指南', 'MySQL和PostgreSQL都是优秀的开源数据库选择。'),
('Python与MySQL集成', '使用Python连接MySQL数据库，进行增删改查操作。');

基本搜索：MATCH AGAINST

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

+----------------+--------------------------------------------------+
| title          | content                                          |
+----------------+--------------------------------------------------+
| MySQL基础教程  | MySQL是流行的开源数据库，广泛应用于Web开发。     |
| 数据库选型指南 | MySQL和PostgreSQL都是优秀的开源数据库选择。       |
| MySQL性能优化 | 优化MySQL查询性能，索引、慢查询和缓存是关键。     |
| Python与MySQL集成 | 使用Python连接MySQL数据库，进行增删改查操作。  |
+----------------+--------------------------------------------------+

和 LIKE 不同——MATCH AGAINST 的结果是按相关度排序的，"MySQL"出现次数多的排前面。

查看相关度分数

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

+----------------+-------------------+
| title          | relevance         |
+----------------+-------------------+
| MySQL基础教程  | 0.3623345792293549 |
| MySQL性能优化   | 0.2588104307651520 |
| Python与MySQL集成 | 0.1903240888118744 |
| 数据库选型指南 | 0.0903360098600388 |
+----------------+-------------------+

分数越高越相关。0 表示完全不匹配。

两种搜索模式

自然语言模式（默认）

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

MySQL 自动忽略常见词（如 the、is、a），剩余词做搜索。匹配的词越多，分数越高。

布尔模式：精确控制

布尔模式支持 + - * 等操作符，更像搜索引擎的语法：

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

+MySQL — 必须包含 MySQL -PostgreSQL — 必须不包含 PostgreSQL

常用布尔操作符：

操作符	含义	示例
+	必须包含	+MySQL
-	必须不包含	-PostgreSQL
*	前缀匹配	optim* → optimizer, optimize
""	短语匹配	"MySQL教程"
()	分组	(+MySQL +Python)
> <	提升/降低权重	+MySQL >Python

更多例子：

-- 包含 MySQL 且包含 数据库，但排除 PostgreSQL
SELECT title FROM articles
WHERE MATCH(title, content)
      AGAINST('+MySQL +数据库 -PostgreSQL' IN BOOLEAN MODE);

-- 搜索 optim 开头的词（optimize, optimizer, optimization）
SELECT title FROM articles
WHERE MATCH(title, content)
      AGAINST('optim*' IN BOOLEAN MODE);

中文全文本搜索

MySQL 8.0 内置了 N-gram 解析器来处理中文等没有空格分隔的语言：

-- 建表时指定 N-gram 解析器
CREATE TABLE articles (
    id      INT AUTO_INCREMENT PRIMARY KEY,
    title   VARCHAR(200),
    content TEXT,
    FULLTEXT (title, content) WITH PARSER ngram
) ENGINE=InnoDB;

N-gram 会把中文切成连续的 N 个字符来建索引。默认 N=2，MySQL教程 会被切成 My sq ql L教 教程 程。

💡 中文全文本搜索的配置比较繁琐，效果也不一定理想。生产环境中文搜索通常用 Elasticsearch，或者直接在应用层分词后用 LIKE 兜底。

什么时候用全文本搜索

场景	推荐
英文文章、博客的站内搜索	✅ FULLTEXT
简单的中文关键词匹配	LIKE 足够了
复杂的中文搜索	Elasticsearch 等专用工具
数据量小（万级以下）	LIKE 完全够用

小结

全文本搜索是 LIKE 的进阶替代，适合大量文本的智能搜索：

1. LIKE 的痛点 — 全表扫描慢、不分词、不会排序相关性
2. FULLTEXT 索引 + MATCH AGAINST — 走索引快得多，结果按相关度排序
3. 布尔模式精确控制 — + 必须包含、- 排除、* 前缀匹配，像搜索引擎一样
4. 中文需要 N-gram 解析器 — 英文开箱即用，中文要额外配置，效果不一定理想
5. 数据量万级以下用 LIKE 就行 — 全文本搜索是锦上添花，不是必需品

💡 英文博客站内搜索用 FULLTEXT 很舒服，中文场景量大了考虑 Elasticsearch。

自主练习

1. 创建一张 articles 表并建 FULLTEXT 索引，插入几条测试数据
2. 用 MATCH AGAINST 搜索"数据库"，查看相关度分数
3. 用布尔模式搜索包含"MySQL"但不包含"PostgreSQL"的文章
4. 对比一下 LIKE 和 MATCH AGAINST 的结果有什么不同