mysql的group_concat()函数会忽略null值

项目场景：

对一天内某个任务的多次执行结果只取最新的。而且任务的开始时间，结束时间有可能为null。但在业务上null值也要被取到。

---

问题描述

使用以下sql的过程中 出现 start_time串值的情况，本应该为null，结果却是这个任务对应的l历史记录中最大时间值。

 select
            B.id as id,
            substring_index(group_concat(result order by data_type desc , job_time desc ), ',', 1) as result,
            substring_index(group_concat(start_time order by job_time desc ), ',', 1) as start_time,
            substring_index(group_concat(end_time order by job_time desc ), ',', 1) as end_time
            
        from
            (
                select
                       id         as id,
                       result     as result,
                       start_time as start_time,
                       end_time   as end_time,
                       job_time   as job_time,
                       'log'      as data_type
                 from t_table_log A
                union 
               select
                       id         as id,
                       result     as result,
                       start_time as start_time,
                       end_time   as end_time,
                       job_time   as job_time,
                       'bak'      as data_type
                 from t_table_log_bak B
            ) B 
            group by id

---

原因分析：

经排查发现，是group_concat函数在拼接值的过程中会忽略掉null值

select id, 
substring_index(group_concat(start_time order by job_time desc),',',1) as `substring_index,1` 
,group_concat(start_time order by job_time desc) as` group_concat`

---

解决方案：

对null值做处理。将null值转成其他值。group_concat （ifnull(start_time,'0000-00-00')）