VillageSQL is a drop-in replacement for MySQL with extensions.
All examples on this page work on VillageSQL. Install Now →
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 条件组合:
FULLTEXT 索引,并单独对其他带索引的列进行过滤。
MySQL 全文搜索支持多种语言吗?
MySQL 为多种语言内置了停用词列表。对于生产环境的多语言搜索,您通常需要配置innodb_ft_enable_stopword = OFF 并通过 innodb_ft_server_stopword_table 提供自己的停用词表,或者使用外部搜索引擎(Elasticsearch、MeiliSearch)。
故障排除
另请参阅
- MySQL 索引的工作原理 — FULLTEXT 是与 B 树并列的一种专用索引类型
- MySQL 中的 JSON — 与结构化数据一起搜索 JSON 文本字段
- 多维范围查询 — 当文本搜索不合适时,跨多个数值维度进行搜索
- MySQL LIKE — 全文索引在大规模下所替代的模式运算符
- MySQL REGEXP — 读取每一行的模式匹配