MySQL全文搜索结果得分相同时如何实现自定义排序？

我正在使用MySQL全文搜索查找所有包含单词lane的结果，SQL语句如下：

SELECT *,
    MATCH (title) AGAINST ('lane') AS score
FROM
    `books`
WHERE
    MATCH (title) AGAINST ('lane')
ORDER
    BY score DESC

（注：原语句WHERE子句中的AS score属于语法错误，已修正）

该语句能返回预期结果，但所有结果的score均相同，导致显示顺序随机。我希望能按特定规则排序（例如将含lane开头的结果优先展示）：

原结果顺序：

Red Lane
Lanes
Search For Hidden Lane
House On The Lane
Lane Of The Day

期望排序后：

Lanes
Lane Of The Day
Red Lane
House On The Lane
Search For Hidden Lane

请问当所有结果得分相同时，还有哪些排序方案可选？

以下是几种实用的排序思路，可根据业务需求组合使用：

• 按匹配词的位置优先级排序
  优先展示匹配词出现在标题开头的结果，再按匹配词出现的位置先后排序：

  SELECT *,
      MATCH (title) AGAINST ('lane') AS score
  FROM `books`
  WHERE MATCH (title) AGAINST ('lane')
  ORDER BY
      -- 标题以lane（大小写不敏感）开头的排最前
      CASE WHEN LOWER(title) LIKE 'lane%' THEN 0 ELSE 1 END,
      -- 匹配词在标题中出现的位置越靠前，排序越优先
      LOCATE('lane', LOWER(title)),
      score DESC;
  

  这个逻辑完美契合你给出的期望排序效果，先把Lanes、Lane Of The Day这类开头含lane的结果前置。

• 按匹配词的出现频率排序
  如果部分标题中lane出现多次，可以优先展示出现次数多的记录：

  SELECT *,
      MATCH (title) AGAINST ('lane') AS score,
      -- 计算lane在标题中的出现次数（大小写不敏感）
      (LENGTH(title) - LENGTH(REPLACE(LOWER(title), 'lane', ''))) / LENGTH('lane') AS occurrence_count
  FROM `books`
  WHERE MATCH (title) AGAINST ('lane')
  ORDER BY occurrence_count DESC, score DESC;
  

• 按标题长度排序
  可选择短标题优先或长标题优先，比如短标题优先适合快速展示核心信息：

  SELECT *,
      MATCH (title) AGAINST ('lane') AS score
  FROM `books`
  WHERE MATCH (title) AGAINST ('lane')
  ORDER BY LENGTH(title) ASC, score DESC;
  

• 按标题字典序排序
  规则简单且结果稳定，直接按标题的字母顺序排列：

  SELECT *,
      MATCH (title) AGAINST ('lane') AS score
  FROM `books`
  WHERE MATCH (title) AGAINST ('lane')
  ORDER BY title ASC, score DESC;
  

• 自定义权重排序
  针对不同匹配场景设置权重，比如同时考虑开头、结尾匹配：

  SELECT *,
      MATCH (title) AGAINST ('lane') AS score,
      CASE
          WHEN LOWER(title) LIKE 'lane%' THEN 0  -- 开头匹配权重最高
          WHEN LOWER(title) LIKE '%lane' THEN 1  -- 结尾匹配权重次之
          ELSE 2  -- 中间匹配权重最低
      END AS custom_rank
  FROM `books`
  WHERE MATCH (title) AGAINST ('lane')
  ORDER BY custom_rank ASC, score DESC;
  

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