MySQL全文搜索MATCH()与聚合函数混用失效问题排查

你遇到的问题是：在MySQL全文搜索场景中，仅使用MATCH()的查询结果正常，但加入COUNT()、GROUP_CONCAT()等聚合函数后，MATCH()返回的相关性分数完全异常——原本匹配的行分数为0，不匹配的行反而出现高分数，且在MySQL 8.0.37和8.3.0版本中均存在此现象。

问题原因 #

这是MySQL优化器的执行计划选择导致的：当查询中存在聚合函数时，优化器会调整执行顺序，将MATCH()的相关性计算延迟到聚合操作之后。此时MATCH()无法基于原始行的title字段计算分数，而是在聚合后的上下文执行，最终导致分数计算完全错误。

解决方案 #

核心思路是先计算全文搜索的相关性分数，再执行聚合操作，通过子查询将MATCH()的计算与聚合逻辑分离：

针对示例查询的修正

将MATCH()的计算放在内层子查询中，外层再进行聚合：

SELECT s.id, s.title, s.exactTitleRelevance, s.titleRelevance, COUNT(*) AS aggregateColumn
FROM (
    SELECT id, title,
        MATCH(title) AGAINST ('"the spirit of god"' IN BOOLEAN MODE) AS exactTitleRelevance,
        MATCH(title) AGAINST ('+spirit* +god*' IN BOOLEAN MODE) AS titleRelevance
    FROM Song
) s
GROUP BY s.id, s.title, s.exactTitleRelevance, s.titleRelevance
ORDER BY s.exactTitleRelevance DESC, s.titleRelevance DESC;

实际场景（关联表+GROUP_CONCAT）的修正

如果是关联其他表并使用GROUP_CONCAT，同样用子查询先处理全文搜索：

SELECT s.id, s.title, s.exactTitleRelevance, s.titleRelevance, GROUP_CONCAT(l.lyric) AS lyrics
FROM (
    SELECT id, title,
        MATCH(title) AGAINST ('"the spirit of god"' IN BOOLEAN MODE) AS exactTitleRelevance,
        MATCH(title) AGAINST ('+spirit* +god*' IN BOOLEAN MODE) AS titleRelevance
    FROM Song
) s
LEFT JOIN Lyrics l ON s.id = l.song_id
GROUP BY s.id, s.title, s.exactTitleRelevance, s.titleRelevance
ORDER BY s.exactTitleRelevance DESC, s.titleRelevance DESC;

原理说明 #

子查询强制MySQL先遍历Song表的每一行，基于原始的title字段计算出正确的相关性分数，再将这些结果作为临时表，在外层执行聚合操作。这样MATCH()的计算始终基于原始行数据，不会受到聚合逻辑的干扰。

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