mysql 对于null字段排序处理

最近遇到一个需求，需要对一个报表的多个字段进行多字段复杂条件排序

排序字段为NULL时

Mysql对于排序字段为NULL时，有自身默认的排序规则，默认是认为null 值是无穷小

ELECT id,
       script_id,
       last_modified,
       live_count,
       next_show
FROM virtual_live_script
where 1 = 1
  and creator is null
ORDER BY
    live_count desc,
    next_show asc,
    last_modified desc;

可以看到 next_show 为空的字段的确按照升序是最小的
在这里插入图片描述如果想让 next_show 为 null的记录排在最后应该怎么处理呢？可以在引入一个新的字段比如sort 字段，排序的时候对这个新的字段也排序，就可以实现上述逻辑，同样的思路还可以对null 值字段进行特殊处理

SELECT id,
       script_id,
       last_modified,
       live_count,
       next_show,
       next_show is null as sort
FROM virtual_live_script
where 1 = 1

  and creator is null

ORDER BY live_count desc,
         sort asc,
         next_show asc,
         last_modified desc;

在这里插入图片描述

还有一种不加辅助字段的方法，利用IFNULL 函数，这里给为空时间，指定一个值，只不过要在业务代码中要去除2030-01-01 00:00:00 这个，有点侵入性！

SELECT id,
       script_id,
       last_modified,
       live_count,
       ifnull(next_show,'2030-01-01 00:00:00') as tmp
FROM virtual_live_script
where 1 = 1

  and creator is null

ORDER BY live_count desc,
         tmp asc,
         last_modified desc;

在这里插入图片描述

多条件复杂排序

对于多条件的复杂排序，可以使用case … when语句，类似如下思路


SELECT id,
       script_id,
       script_name,
       cover_url,
       last_modified,
       live_count,
       pre_count,
       end_count,
       status,
       next_show
FROM virtual_live_script
where 1 = 1

  and creator = :creator
order by live_count desc,
         CASE
             WHEN live_count = 0 AND next_show IS NULL THEN -1
             WHEN live_count = 0 AND pre_count > 0 THEN 0
             ELSE 0
             END desc,
         next_show asc,
         last_modified desc;