MySQL使用MATCH AGAINST与LIKE检索含特殊字符内容方案咨询

全文检索无结果的根本原因 #

1. MySQL 默认全文索引有最小索引词长限制：InnoDB 引擎默认innodb_ft_min_token_size为4，MyISAM 引擎默认ft_min_word_len为3，小于这个长度的词不会被纳入索引，所以DN这种2字符的短语无法匹配到结果。
2. 默认分词规则会把-等特殊符号当成单词分隔符，DN-NP会被拆分为DN和NP两个独立词，再加上长度限制，自然无法匹配到DN*的查询。

包含特殊字符的短语检索解决方案 #

方案1：调整全文索引配置适配短词+特殊字符 #

• 修改MySQL配置文件，添加/修改以下参数：

  # InnoDB 引擎配置
  innodb_ft_min_token_size = 2
  innodb_ft_server_stopword_table = '' # 禁用停用词表，避免短词被过滤
  # 如果需要把-当成普通字符索引，可修改字符集的合法字符规则，把-加入到word字符列表
  

• 重启MySQL后，重建domains表的全文索引，之后就可以正常匹配2字符的短语。如果需要匹配带-的完整前缀，可以把查询短语用双引号包裹：

  SELECT `name` FROM `domains` 
  WHERE MATCH (`name`) AGAINST ('"DN-"*' IN BOOLEAN MODE);
  

方案2：使用ngram分词器实现任意位置模糊匹配 #

MySQL 5.7及以上版本自带ngram全文解析器，专门用于短词、中文等场景的全文检索，不需要调整最小词长参数，且可以匹配字符串任意位置的关键词，性能远高于带前导%的LIKE：

1. 重建全文索引指定ngram解析器：

   CREATE FULLTEXT INDEX idx_name_ngram ON domains(`name`) WITH PARSER ngram;
   

2. 直接用MATCH AGAINST查询即可：

   SELECT `name` FROM `domains` 
   WHERE MATCH (`name`) AGAINST ('DN' IN BOOLEAN MODE);
   

方案3：大数据量场景用专用搜索引擎 #

如果表数据量超过10万条，或者有更复杂的检索需求，建议接入Elasticsearch等专用检索引擎，分词规则更灵活，模糊查询性能远高于数据库原生检索。

LOCATE 函数性能说明 #

LOCATE函数和带前导%的LIKE性能基本一致，都无法利用普通索引，查询时需要逐行扫描全表执行函数计算，数据量超过1万条时延迟会明显升高，仅适合小表或者非高频查询场景使用。

补充注意 #

你当前PHP实现里直接拼接用户输入的SQL写法存在SQL注入风险，建议改用PDO或者MySQLi的预处理语句传参。

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