如何修改使用MATCH的SQL存储过程 实现非精确字符串模糊匹配

核心问题原因 #

• 你混淆了LIKE语句的通配符和全文检索的通配符规则：%仅适用于LIKE模糊匹配，在全文检索的NATURAL LANGUAGE MODE（自然语言模式）下不会被识别为通配符
• 自然语言模式本身不支持前缀/后缀匹配，要实现包含查询词的模糊匹配，需要切换到BOOLEAN MODE（布尔全文模式），且该模式下的通配符为*，仅支持加在查询词尾部实现前缀匹配

修改方案 #

不需要更换整体实现，仅做小幅调整即可满足需求：

1. 将所有AGAINST的模式从IN NATURAL LANGUAGE MODE改为IN BOOLEAN MODE
2. 把通配符拼接逻辑从CONCAT('%', _query, '%')改为CONCAT(_query, '*')
3. 可选优化：提取重复的匹配得分计算逻辑，减少代码冗余

修改后的完整存储过程 #

CREATE DEFINER=`database` PROCEDURE `search_terms`(
  IN _query varchar(60)
)
BEGIN
SET NAMES 'utf8mb4';
-- 预处理搜索词，避免重复拼接
SET @search_term = CONCAT(_query, '*');
SELECT
    dictionary.id,
    dictionary.`term`,
    dictionary.`definition`,
    links.id AS `linkId`,
    links.`name`,
    links.`url`,
    links.term_id,
    -- 提前计算匹配得分，避免重复执行计算逻辑
    MATCH(dictionary.term, dictionary.definition) AGAINST(@search_term IN BOOLEAN MODE) AS dict_score,
    MATCH(links.name) AGAINST(@search_term IN BOOLEAN MODE) AS link_score
FROM ItDictionary dictionary
LEFT JOIN ItRelatedLinks links ON links.term_id = dictionary.id
WHERE 
    dict_score > 0 
    OR link_score > 0
ORDER BY (dict_score + link_score) DESC;
END

补充说明 #

• 布尔模式的*通配符仅支持后缀匹配，也就是可以匹配asset开头的assets、asset_manage等内容，如果需要同时匹配中间包含查询词的内容（比如fixed_asset），可以在WHERE条件中补充LIKE CONCAT('%', _query, '%')规则，不过该操作会跳过全文索引，性能会有所下降
• 操作前请确认你已经为对应字段创建了全文索引，否则检索效率会很低：

  -- 给字典表创建term、definition的联合全文索引
  CREATE FULLTEXT INDEX idx_dict_term_def ON ItDictionary(term, definition);
  -- 给链接表创建name字段的全文索引
  CREATE FULLTEXT INDEX idx_link_name ON ItRelatedLinks(name);
  

• 如果需要支持3个字符以下的短词搜索，需要修改MySQL配置里的ft_min_word_len（MyISAM引擎）或者innodb_ft_min_token_size（InnoDB引擎）参数，重启后重建全文索引生效。

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