跳转到主要内容

VillageSQL is a drop-in replacement for MySQL with extensions.

All examples on this page work on VillageSQL. Install Now →
MySQL 的 LIKE '%keyword%' 对小表有效,但无法使用索引——每一行都会被扫描。全文搜索为搜索文本列提供了一种带索引、带排名的替代方案。

创建 FULLTEXT 索引

FULLTEXT 索引适用于 InnoDB(默认存储引擎)和 MyISAM 表上的 CHAR、VARCHAR 和 TEXT 列。
为现有的表添加 FULLTEXT 索引:
单个 FULLTEXT 索引可以跨越多列。针对该索引的查询会同时搜索所有被索引的列。

使用 MATCH…AGAINST 查询

全文查询使用 MATCH(columns) AGAINST(expression) 语法。MATCH 的列列表必须与 FULLTEXT 索引中的列完全匹配。
当 MATCH...AGAINST 出现在 WHERE 子句中时,MySQL 按相关性降序返回行。AGAINST 表达式返回一个相关性分数——0 表示不匹配。

搜索模式

MySQL 支持三种搜索模式:

自然语言模式

默认模式。MySQL 使用一种加权方案,实际上会忽略极其常见的词——出现在几乎所有行中的词对相关性分数的贡献很小。常见的英文停用词(the、is、at)会被直接过滤掉。当没有显式的 ORDER BY 时,结果按相关性降序返回。

布尔模式

布尔模式支持用于精确控制的运算符:
常见的布尔运算符: InnoDB 表要求 MATCH() 表达式中的所有列上都有 FULLTEXT 索引——没有它,InnoDB 上的布尔模式查询会返回错误。(MyISAM 允许在没有 FULLTEXT 索引的情况下使用布尔模式,但会扫描每一行。)

查询扩展

两遍搜索。MySQL 运行初始查询,取出最匹配的结果,从中提取额外的词,然后加上这些词重新运行搜索。
当用户用单个或含糊的词搜索时很有用。如果第一遍的最匹配结果偏离主题,可能返回令人意外的结果。

全文搜索与 LIKE 的对比

需要在小表上做简单的模式匹配、需要前缀搜索(带索引的 LIKE 'prefix%')或需要任意模式匹配时,使用 LIKE。当您在文本列中搜索词或短语,并需要相关性排名或大规模下更好的性能时,使用全文搜索。

最小词长

InnoDB 的默认最小词元长度是 3 个字符(innodb_ft_min_token_size)。短于 3 个字符的词不会被索引。对于两个字符的词,请降低该最小值:
innodb_ft_min_token_size 不是动态变量——它需要重启服务器才能生效。重启后,重建所有 FULLTEXT 索引:

常见问题

为什么表中明明有这个词,我的 MATCH…AGAINST 却没有返回任何行?

三个常见原因:(1) 该词短于 innodb_ft_min_token_size(默认 3)。(2) 该词在停用词列表中。(3) 在 MyISAM 表上,该词出现在 50% 或更多的行中——MyISAM 上的自然语言模式把这样的词视为过于常见而无用。InnoDB 没有这样的阈值。使用布尔模式可以绕过 MyISAM 的 50% 阈值。

我能把全文搜索与 WHERE 条件一起使用吗?

可以。像平常一样把 MATCH...AGAINST 与其他 WHERE 条件组合:
MySQL 可以为全文部分使用 FULLTEXT 索引,并单独对其他带索引的列进行过滤。

MySQL 全文搜索支持多种语言吗?

MySQL 为多种语言内置了停用词列表。对于生产环境的多语言搜索,您通常需要配置 innodb_ft_enable_stopword = OFF 并通过 innodb_ft_server_stopword_table 提供自己的停用词表,或者使用外部搜索引擎(Elasticsearch、MeiliSearch)。

故障排除

另请参阅