环境

jdk 1.8 mysql 5.6 mybatis 3.4.4 springboot 2.2.6

需求

按照时间段来查询显示该时间段内每一天的数据量，如果某一天没有数据，显示数据量为0.

按照正常思路直接使用GROUP BY分组查询即可，运行如下sql

SELECT COUNT(1) as `count`,date_format(u.createtime,'%Y-%m-%d') as `date` FROM user u GROUP BY date_format(u.createtime,'%Y-%m-%d')

果然，是不行的，没有数据的天数没有显示出来

解决方案

查询日期表，外连接数据表来显示 网络上查询到生成日期表的方法有几种 需要另建一张日期表，直接从此表查询（比较麻烦）使用笛卡尔积生成时间（推荐） 基础sql如下 select date_add('2020-04-01',interval @i:=@i+1 day) as date from ( select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1) as tmp, (select @i:= -1) t

结果如下

这样我们就得到了一段连续的日期了，然后只需要关联查询即可显示了

select count(u.id) as count,s.date from (select date_add('2020-04-01',interval @i:=@i+1 day) as date from ( select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1) as tmp, (select @i:= -1) t ) s left join user u on s.date = date_format(u.createtime,'%Y-%m-%d') GROUP BY s.date

查询结果

现在还有一个问题，如何控制生成多少个日期，也就是union all select 1 的个数，

使用存储过程（耦合性过高，不易维护，暂不考虑）mybatis 循环拼接（√）

选择了java代码计算日期差，使用mybatis的  foreach 标签实现sql拼接，全部代码如下

service @Override public Object queryByDate(String[] date) { String date1 = date[0]; String date2 = date[1]; int num = calcBetweenDate(date1, date2); String[] countArr = new String[num]; return userMapper.queryByDate(date1,countArr); } public int calcBetweenDate(String start, String end) { SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd"); Date startDate = null; Date endDate = null; try { startDate = df.parse(start); endDate = df.parse(end); } catch (Exception e) { System.out.println("日期转换出错"); } int count = (int) ((endDate.getTime() - startDate.getTime()) / (24 * 60 * 60 * 1000)); return count; } mapper List<Map<String,Object>> queryByDate(@Param("date")String date, @Param("countArr")String[] countArr); xml <select id="queryByDate" resultType="java.util.HashMap"> select count(u.id) as count,s.date from (select date_add('2020-04-01',interval @i:=@i+1 day) as date from ( select 1 <foreach item="index" collection="countArr"> union all select 1 </foreach> ) as tmp, (select @i:= -1) t ) s left join user u on s.date = date_format(u.createtime,'%Y-%m-%d') GROUP BY s.date </select>

效率检测

我们使用postman查询，检测效率,

查询日期 11天，耗时 10-20ms 左右

查询日期 365天，耗时 30-40ms 左右

查询日期 3650天，耗时 222ms 左右

查询日期 5000天，爆炸。。

Cause: java.sql.SQLException: Thread stack overrun: 246144 bytes used of a 262144 byte stack, and 16000 bytes needed. Use 'mysqld --thread_stack=#' to specify a bigger stack. ; uncategorized SQLException; SQL state [HY000]; error code [1436]; Thread stack overrun: 246144 bytes used of a 262144 byte stack, and 16000 bytes needed. Use 'mysqld --thread_stack=#' to specify a bigger stack.; nested exception is java.sql.SQLException: Thread stack overrun: 246144 bytes used of a 262144 byte stack, and 16000 bytes needed. Use 'mysqld --thread_stack=#' to specify a bigger stack.] 3662

注意事项

本方式仅适用于小范围时间查询，查询时间在上方，建议自己测试，若天数间隔大于10年及以上，可能会导致mybatis报错，若长时间段查询不建议使用此方式！