PHP8+MySQL5.7 MATCH AGAINST全文检索匹配异常问题咨询

所有异常本质是MySQL 5.7内置全文检索的规则限制，加上代码写法不规范导致：

• MySQL默认全文解析器将空格、连字符（-）判定为词边界分隔符，遇到这两个字符会自动将输入内容拆分为独立分词单元，因此搜索"Los Angeles"这类带空格的词时，会被拆成"Los"和"Angeles"两个词分别匹配，自然会返回大量仅命中单个词的无关结果。
• 布尔全文检索模式下，通配符*的语法要求是必须紧跟在有效检索词之后，之前直接拼接+*".$q."*的写法，当搜索词包含连字符被拆分后，极易出现*前无有效检索词的情况，就会触发syntax error, unexpected $end, expecting FTS_TERM or FTS_NUMB or '*'的报错。
• MySQL 5.7默认全文索引配置的最小分词长度（ft_min_word_len）为4，用户输入3个及以下字符的前缀时无法命中索引，短输入场景下联想功能直接失效。
• 之前贴出的第三版查询SQL本身存在笔误，name和from关键字之间漏了空格，执行时也会触发语法错误。
• 最初的LIKE '%$q%'写法因为前置通配符的存在，无法走B+树索引，必须全表扫描，数据量上来后性能必然不达标。

根据环境和业务要求，可二选一：

方案1：基于现有全文索引改造（无需调整表结构） #

适合不想改动现有表结构、快速修复问题的场景：

• 第一步：预处理用户输入，避免语法错误和分词匹配偏差
  不要直接把用户输入拼接到检索语句里，先过滤全文检索保留字符，拆分有效词项后再拼接检索规则，PHP参考代码：

  // 先做SQL转义，阻断注入风险
  $rawQ = $dbcon->real_escape_string($q);
  // 按空格、连字符等词边界拆分有效检索词，过滤空值
  $termList = preg_split('/[\s\-]+/', trim($rawQ), -1, PREG_SPLIT_NO_EMPTY);
  $againstStr = '';
  foreach ($termList as $singleTerm) {
      // 每个检索词都要求必须命中，加后缀通配符支持前缀匹配
      $againstStr .= '+' . $singleTerm . '* ';
  }
  $againstStr = trim($againstStr);
  

• 第二步：调整MySQL全文索引配置，支持短输入匹配
  修改MySQL配置文件my.cnf，添加配置项ft_min_word_len = 1，重启MySQL服务后重建全文索引让配置生效：

  REPAIR TABLE places QUICK;
  

• 第三步：优化查询逻辑，按匹配度排序返回结果
  修正之前的SQL笔误，加入匹配度排序逻辑，优先返回最相关的结果：

  $querySql = "SELECT name, MATCH(alternate_name) AGAINST('{$againstStr}' IN BOOLEAN MODE) AS match_score
               FROM places
               WHERE MATCH(alternate_name) AGAINST('{$againstStr}' IN BOOLEAN MODE)
               ORDER BY match_score DESC
               LIMIT 25";
  $result = $dbcon->query($querySql);
  

  该方案可覆盖绝大多数搜索场景，但对多词连续精确匹配的支持存在天然缺陷，对匹配精度要求高的场景推荐用方案2。

方案2：生产环境推荐方案（性能、准确率双优） #

绕开MySQL全文检索的分词限制，从表结构层面优化：

• 新建别名关联表places_alias，将原places表alternate_name字段中存储的多语言别名按分隔符拆分为独立条目存储，表结构参考：

  CREATE TABLE `places_alias` (
    `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `place_id` int(11) NOT NULL COMMENT '关联places表id',
    `alias_name` varchar(255) NOT NULL DEFAULT '' COMMENT '单个别名/名称',
    PRIMARY KEY (`id`),
    KEY `idx_alias` (`alias_name`),
    KEY `idx_placeid` (`place_id`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  

• 查询时直接走B+树前缀匹配，性能远高于全表扫描的LIKE写法，且不存在分词、特殊字符报错问题：

  $searchKey = $dbcon->real_escape_string($q);
  $querySql = "SELECT p.name 
               FROM places p
               INNER JOIN places_alias pa ON p.id = pa.place_id
               WHERE pa.alias_name LIKE '{$searchKey}%'
               LIMIT 25";
  $result = $dbcon->query($querySql);
  

  该写法因为是前缀匹配（没有前置%），可以完全命中idx_alias索引，百万级数据量下查询响应时间在毫秒级，完全满足实时输入联想的性能要求，同时不管搜索词带空格、连字符还是其他特殊字符，都能精确匹配。

注意：所有用户传入的参数必须做转义处理，禁止直接拼接到SQL语句中，避免SQL注入风险。

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