InnoDB全文索引单词部分匹配的差异化评分实现方案咨询

先帮你拆解下当前遇到的两个问题的根源：

1. 得分相同的原因：MySQL 5.6的InnoDB全文索引在布尔模式下，只会判断条目是否匹配查询条件，所有匹配成功的条目都会得到相同的基础得分，不会区分精确匹配和前缀匹配的差异。
2. 后缀匹配失败的原因：InnoDB全文索引的布尔模式只支持前缀通配符（比如battle*），不支持后缀通配符（*battle），因为索引是按词的前缀构建的，无法高效匹配后缀，所以abcbattle这类条目不会被全文索引查询返回。

接下来给你两个可行的数据库层面解决方案，不用动应用层：

方案一：自定义评分逻辑（推荐） #

通过CASE语句结合全文索引和LIKE匹配，手动给不同类型的匹配设置权重，实现差异化排序，同时覆盖后缀匹配的场景：

SELECT 
    blacklist_entry_id AS id,
    name,
    insertat,
    CASE
        -- 精确匹配给最高权重
        WHEN name = 'battle' THEN 10
        -- 前缀匹配（以battle开头）给次高权重
        WHEN name LIKE 'battle%' THEN 8
        -- 后缀匹配（以battle结尾）给基础权重
        WHEN name LIKE '%battle' THEN 5
        ELSE 0
    END AS score
FROM blacklist_entries
WHERE 
    -- 用全文索引处理前缀匹配，保证查询效率
    MATCH(name) AGAINST('battle*' IN BOOLEAN MODE)
    -- 用LIKE处理后缀匹配，覆盖全文索引无法处理的场景
    OR name LIKE '%battle'
-- 去重，避免同时满足两种匹配条件的条目重复出现
GROUP BY blacklist_entry_id, name, insertat
-- 按自定义得分降序排列，精确匹配优先
ORDER BY score DESC;

这个查询会返回所有包含battle的条目，并且得分从高到低依次是：精确匹配的battle → 前缀匹配的battleground/battlefield等 → 后缀匹配的abcbattle，完全符合你的需求。

方案二：结合自然语言模式得分+自定义权重 #

如果你想保留全文索引的相关性得分，可以结合自然语言模式（NATURAL LANGUAGE MODE）的得分，再叠加自定义权重：

SELECT 
    blacklist_entry_id AS id,
    name,
    insertat,
    -- 自然语言模式得分乘以2放大差异，再加自定义权重
    MATCH(name) AGAINST('battle' IN NATURAL LANGUAGE MODE) * 2 +
    CASE
        WHEN name = 'battle' THEN 5
        WHEN name LIKE 'battle%' THEN 3
        ELSE 0
    END AS score
FROM blacklist_entries
WHERE 
    MATCH(name) AGAINST('battle*' IN BOOLEAN MODE)
    OR name LIKE '%battle'
GROUP BY blacklist_entry_id, name, insertat
ORDER BY score DESC;

不过要注意，自然语言模式的得分受词频影响，如果你的数据里battle出现的次数和其他前缀条目差不多，得分差异可能不明显，所以方案一的自定义权重逻辑更稳定可控。

为什么不建议存储过程？ #

存储过程虽然能实现更复杂的评分逻辑，但对于这个场景来说，上面的单条SQL已经足够简洁高效，存储过程反而会增加维护成本，所以优先用SQL语句解决。

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