如何修改MySQL排序算法，按行内词匹配而非词频排序？

MySQL默认的全文搜索评分（MATCH...AGAINST返回的score）是基于全表词频统计的，这就是为什么完全匹配'TJ CNC 1 ADSP'的记录会因为关键词在全表中出现频次低，被排到结果末尾。要改成按单条记录内的匹配情况排序，可以试试以下几种方案：

1. 直接置顶完全匹配的记录 #

把完全等于搜索词的结果放在最前面，剩下的再按默认评分排序：

SELECT name, MATCH(name) AGAINST('TJ CNC 1 ADSP' IN BOOLEAN MODE) as score
FROM templates 
WHERE MATCH(name) AGAINST('TJ CNC 1 ADSP' IN BOOLEAN MODE)
ORDER BY 
    CASE WHEN name = 'TJ CNC 1 ADSP' THEN 0 ELSE 1 END,
    score DESC;

2. 按匹配的关键词数量排序 #

统计每条记录匹配到的搜索关键词数量，按数量从多到少排序，完全匹配的记录会拿到最高的匹配数：

SELECT 
    name,
    MATCH(name) AGAINST('TJ CNC 1 ADSP' IN BOOLEAN MODE) as score,
    (
        IF(MATCH(name) AGAINST('+TJ' IN BOOLEAN MODE), 1, 0) +
        IF(MATCH(name) AGAINST('+CNC' IN BOOLEAN MODE), 1, 0) +
        IF(MATCH(name) AGAINST('+1' IN BOOLEAN MODE), 1, 0) +
        IF(MATCH(name) AGAINST('+ADSP' IN BOOLEAN MODE), 1, 0)
    ) AS match_count
FROM templates 
WHERE MATCH(name) AGAINST('TJ CNC 1 ADSP' IN BOOLEAN MODE)
ORDER BY match_count DESC, score DESC;

3. 自定义加权评分 #

如果需要更灵活的排序逻辑，可以给完全匹配、单个关键词匹配设置不同权重，自定义评分规则：

SELECT 
    name,
    MATCH(name) AGAINST('TJ CNC 1 ADSP' IN BOOLEAN MODE) as score,
    (
        -- 完全匹配直接加高分
        CASE WHEN name = 'TJ CNC 1 ADSP' THEN 100 ELSE 0 END +
        -- 每个匹配的关键词加分
        (
            IF(MATCH(name) AGAINST('+TJ' IN BOOLEAN MODE), 25, 0) +
            IF(MATCH(name) AGAINST('+CNC' IN BOOLEAN MODE), 25, 0) +
            IF(MATCH(name) AGAINST('+1' IN BOOLEAN MODE), 25, 0) +
            IF(MATCH(name) AGAINST('+ADSP' IN BOOLEAN MODE), 25, 0)
        )
    ) AS custom_score
FROM templates 
WHERE MATCH(name) AGAINST('TJ CNC 1 ADSP' IN BOOLEAN MODE)
ORDER BY custom_score DESC;

额外说明 #

布尔模式下的全文搜索评分仍会受全表词频影响，上面的方案都是通过在排序阶段加入额外逻辑，优先考虑单条记录的匹配情况（完全匹配、匹配关键词数量等），从而绕过默认的全表词频主导排序。

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