如何用MySQL的MATCH AGAINST替代LIKE？解决模糊子串匹配问题

嘿，我完全懂你的困扰——想用全文索引替代慢到头疼的LIKE '%part%'，结果试了好几种语句都没效果，确实挺闹心的。先给你说清楚为什么你之前的写法不行，再给你几个可行的解决方案：

为什么你的MATCH语句无效？ #

MySQL的默认全文索引是基于完整单词的，布尔模式下的*通配符只能用作前缀匹配——也就是说，你写part*能匹配以part开头的单词，但*part或者*part*这种写法是不生效的，它根本识别不了这种中间/后缀的通配符。这就是你之前的语句查不到预期结果的核心原因。

可行解决方案 #

方案1：用ngram分词器（推荐，MySQL 8.0.17+支持） #

如果你的MySQL版本够新（8.0.17及以上），可以用MySQL自带的ngram分词器，它会把文本拆成指定长度的字符片段（默认是2个字符），这样就能实现任意位置的子串匹配，完美替代LIKE '%part%'：

1. 先删除原有的全文索引（如果已经创建过）：

ALTER TABLE `table` DROP INDEX idx_title_fulltext;

2. 创建使用ngram分词器的全文索引：

ALTER TABLE `table` ADD FULLTEXT INDEX idx_title_fulltext (title) WITH PARSER ngram;

3. 直接查询即可，不需要加任何通配符：

SELECT * FROM `table` WHERE MATCH(title) AGAINST ('part' IN BOOLEAN MODE);

这个时候就能匹配所有包含part子串的记录，不管它在单词的开头、中间还是结尾，性能比LIKE '%part%'好太多。

方案2：旧版本MySQL的折中优化 #

如果你的MySQL版本太低用不了ngram，那可以试试给LIKE '%part%'做一些性能优化：

• 如果你经常需要前缀匹配（LIKE 'part%'），可以给title字段建普通索引，这样前缀匹配能用到索引，速度会快很多；
• 如果必须要中间/后缀匹配，可以考虑把title字段反转存储，再给反转后的字段建索引。比如把"apple"存成"elppa"，要查%part就变成查反转后的字段LIKE 'traP%'，这样就能用到索引了，最后再把结果反转回来就行。不过这个方法比较繁琐，适合特定场景。

方案3：第三方全文搜索工具（适合大数据量） #

如果你的数据量特别大，对搜索性能要求很高，那可以考虑用Elasticsearch这类专门的全文搜索工具，它的子串匹配能力更强，还支持更复杂的搜索规则，不过需要额外部署和维护。

内容的提问来源于stack exchange，提问作者Godfather