MySQL如何按status字段(running/upcoming/closed)及日期时间排序记录

实现方法 #

MySQL 内置的 FIELD() 函数可以更简洁地实现枚举值自定义排序，相比 CASE 语句代码可读性更高，性能一致。
完整排序语句如下：

SELECT * 
FROM 你的活动表名
ORDER BY
  -- 第一排序规则：按status自定义优先级升序
  FIELD(status, 'running', 'upcoming', 'closed') ASC,
  -- 第二排序规则：按日期升序（如果date字段本身为DATE类型，可去掉STR_TO_DATE直接用`date` ASC即可）
  STR_TO_DATE(`date`, '%d-%m-%Y') ASC,
  -- 第三排序规则：按时间升序，同日期下更早的记录排在前面
  `time` ASC;

逻辑说明 #

• FIELD(status, 'running', 'upcoming', 'closed') 会自动给不同status赋值权重：running返回1、upcoming返回2、closed返回3，按权重升序排列即可实现要求的优先级顺序。
• 示例中date字段存储格式为dd-mm-yyyy字符串，需要用STR_TO_DATE转换为日期类型后排序，避免字符串排序出现逻辑错误。
• 最后按time字段升序排列，符合同status下时间更早的记录靠前的要求。

效果验证 #

代入提供的7条测试数据，排序结果为：R3、R2、R1、R4、R6、R7、R5，完全符合预期。

性能优化建议 #

如果数据量较大，可以做两个优化：

• 将status字段设置为ENUM类型，存储时直接按权重存储，排序效率更高
• 建立(status, date, time)联合索引，排序时可直接走索引，避免文件排序

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