mysql獲取時(shí)間段所有日期

• 方式1

select date_add('2020-01-01', interval row DAY) date from
 ( 
    SELECT @row := @row + 1 as row FROM 
    (select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t,
    (select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t2, 
    (select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t3, 
    (SELECT @row:=-1) r
 ) se
 where date_add('2020-01-01', interval row DAY) <= '2020-12-31'

這段代碼表示數(shù)據(jù)條數(shù)限制，寫兩次查詢的日期最多顯示100條，寫三次查詢?nèi)掌谧疃囡@示1000次，以此類推，根據(jù)你自己的需求決定

# 需要增加行數(shù)，就增加一條此查詢
(select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9)

• 方式2

當(dāng)前時(shí)間 CURDATE() - (a.a + (10 * b.a) + (100 * c.a))計(jì)算出來的天數(shù)

select a.Date
from (
    select curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY as Date
    from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c
) a
where a.Date between '2017-11-10' and '2017-11-15'

a、b、c分別代表個(gè)、十、百數(shù)，最高能往前推 999 天，如果要獲得更多天數(shù)，可增加一個(gè)千位

# 增加一個(gè) cross join d，在select 中增加一個(gè)千位數(shù) (1000 * d.a)
select a.Date
from (
    select curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a) + (1000 * d.a)) DAY as Date
    from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as d
) a
where a.Date between '2000-01-01' and '2000-12-31'