MySQL全文检索查询报错与前缀匹配失效问题求助

直接将用户输入拼接到SQL语句中不仅存在SQL注入风险，还会因为不符合MySQL布尔全文搜索语法的输入（如孤立的+/-、开头/结尾的运算符）触发语法错误。解决方法如下：

1. 强制使用预处理语句
   永远不要直接将用户输入拼接进SQL，改用预处理语句避免注入风险，同时隔离输入与SQL语法：

   // 先清理输入，再用PDO预处理
   $stmt = $pdo->prepare("
       SELECT *, MATCH(title, beskrivelse) AGAINST(? IN BOOLEAN MODE) as score
       FROM search_posts
       WHERE MATCH(title, beskrivelse) AGAINST(? IN BOOLEAN MODE)
       GROUP BY title, beskrivelse
       ORDER BY score DESC
   ");
   

2. 清洗不符合语法的输入
   用正则表达式过滤掉孤立的布尔运算符（前后无有效关键词的+/-），保留合法的运算符使用：

   $input = "gold +"; // 示例输入
   // 清理开头/结尾的孤立运算符、空格前后的孤立运算符
   $cleanInput = preg_replace('/(^[+-]+)|([+-]+$)|(\s[+-]+)|([+-]+\s)/', ' ', $input);
   $cleanInput = trim($cleanInput); // 去除首尾空格
   
   // 执行预处理语句
   $stmt->execute([$cleanInput, $cleanInput]);
   

   这样既保留了用户合法的布尔运算符使用（如+gold -silver），又避免了孤立运算符导致的语法错误。

你已经调整了全文索引的最小token长度，但还需要结合MySQL布尔搜索的前缀语法才能实现预期的前缀匹配效果，具体步骤：

1. 确认索引重建生效
   修改my.cnf/my.ini配置后，必须重启MySQL服务，然后手动重建全文索引：

   -- 假设你的全文索引名为ft_search_posts
   ALTER TABLE search_posts DROP INDEX ft_search_posts;
   ALTER TABLE search_posts ADD FULLTEXT INDEX ft_search_posts(title, beskrivelse);
   

   或者使用OPTIMIZE TABLE来重建索引：

   OPTIMIZE TABLE search_posts;
   

2. 修改查询为前缀匹配模式
   MySQL布尔全文搜索中，关键词后加*表示前缀匹配。需要将用户输入的每个关键词自动添加*，确保匹配所有以该关键词开头的词汇：

   $input = "go"; // 示例输入
   $cleanInput = preg_replace('/(^[+-]+)|([+-]+$)|(\s[+-]+)|([+-]+\s)/', ' ', $input);
   $cleanInput = trim($cleanInput);
   
   // 拆分关键词并添加前缀通配符
   $keywords = array_filter(explode(' ', $cleanInput)); // 过滤空关键词
   $prefixKeywords = array_map(function($word) {
       // 如果关键词本身带+/-运算符，要把*加在运算符后面（如+go*）
       if (preg_match('/^([+-])(.*)$/', $word, $matches)) {
           return $matches[1] . $matches[2] . '*';
       }
       return $word . '*';
   }, $keywords);
   $searchQuery = implode(' ', $prefixKeywords);
   
   // 执行预处理查询
   $stmt = $pdo->prepare("
       SELECT *, MATCH(title, beskrivelse) AGAINST(? IN BOOLEAN MODE) as score
       FROM search_posts
       WHERE MATCH(title, beskrivelse) AGAINST(? IN BOOLEAN MODE)
       ORDER BY score DESC
   ");
   $stmt->execute([$searchQuery, $searchQuery]);
   

   注：原查询中的SUM(...)如果不是为了聚合重复的title/beskrivelse行，可以直接去掉，因为MATCH本身返回的就是当前行的匹配得分。

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