MySQL: 查询两个日期之间的所有月份

第一步: 先查询出1970年1月1日之后的XXX天的每个月的第一天的日期

主要用到了 adddate(date,INTERVAL expr type) 这一函数, 详情请参见: MySQL DATE_ADD() 函数

select adddate('1970-01-01', interval
    t2.i * 100 + t1.i * 10 + t0.i month) `date`
from (select 0 i union select 1 union
      select 2 union select 3 union
      select 4 union select 5 union
      select 6 union select 7 union
      select 8 union select 9) t0,
     (select 0 i union select 1 union
      select 2 union select 3 union
      select 4 union select 5 union
      select 6 union select 7 union
      select 8 union select 9) t1,
     (select 0 i union select 1 union
      select 2 union select 3 union
      select 4 union select 5 union
      select 6 union select 7 union
      select 8 union select 9) t2

查询结果如下:

第二步: 使用where限制日期

select *
from (select adddate('1970-01-01', interval
    t2.i * 100 + t1.i * 10 + t0.i month) `date`
from (select 0 i union select 1 union
      select 2 union select 3 union
      select 4 union select 5 union
      select 6 union select 7 union
      select 8 union select 9) t0,
     (select 0 i union select 1 union
      select 2 union select 3 union
      select 4 union select 5 union
      select 6 union select 7 union
      select 8 union select 9) t1,
     (select 0 i union select 1 union
      select 2 union select 3 union
      select 4 union select 5 union
      select 6 union select 7 union
      select 8 union select 9) t2) a
where `date` between date('2020-1-1') and now();

即可查询出 2020年1月1日 到现在每个月的第一天的日期, 结果如下图所示

最后, 格式化日期即可

select date_format(`date`, '%Y-%m') yearAndMonth
from (select adddate('1970-01-01', interval
            t2.i * 100 + t1.i * 10 + t0.i month) `date`
      from (select 0 i union select 1 union
      select 2 union select 3 union
      select 4 union select 5 union
      select 6 union select 7 union
      select 8 union select 9) t0,
     (select 0 i union select 1 union
      select 2 union select 3 union
      select 4 union select 5 union
      select 6 union select 7 union
      select 8 union select 9) t1,
     (select 0 i union select 1 union
      select 2 union select 3 union
      select 4 union select 5 union
      select 6 union select 7 union
      select 8 union select 9) t2) a
where `date` between date('2020-1-1') and now();

结果如下图:

参考资源:
[1] generate days from date range