MySQL多列检索结果排序：如何按匹配列优先级排序查询结果

你遇到的问题是MySQL全文检索默认会按照相关性得分排序，而不是你需要的指定id顺序。另外，你的原SQL语句里有个列名错误：translation_forms应该改为translation_form，和你的表结构定义保持一致，否则会触发列不存在的错误。

要同时满足“优先匹配word列，再依次匹配其他列”的匹配优先级，并且最终按id=4,2,0,1,3的固定顺序排列，可以通过以下两种方案调整你的SQL：

方案1：结合匹配优先级与指定id排序 #

这个方案先给不同列的匹配结果赋予权重，确保word列匹配的记录排在最前面，再用FIELD()函数强制指定最终的id顺序（即使有同优先级的匹配结果，也能严格遵循你要的顺序）：

SELECT * 
FROM dictionary 
WHERE MATCH(`word`) AGAINST('a*' IN BOOLEAN MODE) 
   OR MATCH(`translation`) AGAINST('a*' IN BOOLEAN MODE) 
   OR MATCH(`forms`) AGAINST('a*' IN BOOLEAN MODE) 
   OR MATCH(`translation_form`) AGAINST('a*' IN BOOLEAN MODE) 
   OR MATCH(`tags`) AGAINST('a*' IN BOOLEAN MODE)
ORDER BY 
    -- 按匹配列的优先级排序：word匹配 > translation > forms > translation_form > tags
    CASE 
        WHEN MATCH(`word`) AGAINST('a*' IN BOOLEAN MODE) THEN 1
        WHEN MATCH(`translation`) AGAINST('a*' IN BOOLEAN MODE) THEN 2
        WHEN MATCH(`forms`) AGAINST('a*' IN BOOLEAN MODE) THEN 3
        WHEN MATCH(`translation_form`) AGAINST('a*' IN BOOLEAN MODE) THEN 4
        WHEN MATCH(`tags`) AGAINST('a*' IN BOOLEAN MODE) THEN 5
        ELSE 6
    END,
    -- 严格按指定的id顺序排列
    FIELD(id, 4, 2, 0, 1, 3);

方案2：直接强制指定id顺序 #

如果你确认符合检索条件的记录正好是这5条，并且必须严格按4,2,0,1,3的顺序排列，可以简化排序逻辑，直接使用FIELD()函数：

SELECT * 
FROM dictionary 
WHERE MATCH(`word`) AGAINST('a*' IN BOOLEAN MODE) 
   OR MATCH(`translation`) AGAINST('a*' IN BOOLEAN MODE) 
   OR MATCH(`forms`) AGAINST('a*' IN BOOLEAN MODE) 
   OR MATCH(`translation_form`) AGAINST('a*' IN BOOLEAN MODE) 
   OR MATCH(`tags`) AGAINST('a*' IN BOOLEAN MODE)
ORDER BY FIELD(id, 4, 2, 0, 1, 3);

关键说明 #

• FIELD(id, val1, val2, ...)函数会返回目标id在参数列表中的位置，比如id=4会返回1，id=2返回2，以此类推，MySQL会按照这个返回值从小到大排序，正好符合你要的顺序。
• 方案1更严谨，兼顾了匹配列的优先级和自定义排序；方案2则直接满足你要求的最终排序结果，适合结果集固定的场景。

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