selectDATE_FORMAT(date_add('2020-01-2000:00:00',intervalrowMONTH),'%Y-%m')datefrom

(

SELECT@row:=@row+1asrowFROM

(select0unionallselect1unionallselect2unionallselect3unionallselect4unionallselect5unionallselect6unionallselect7unionallselect8unionallselect9)t,

(select0unionallselect1unionallselect2unionallselect3unionallselect4unionallselect5unionallselect6unionallselect7unionallselect8unionallselect9)t2,

(SELECT@row:=-1)r

)se

whereDATE_FORMAT(date_add('2020-01-2000:00:00',intervalrowMONTH),'%Y-%m')<=DATE_FORMAT('2020-04-0200:00:00','%Y-%m')

2：mysql获取时间段所有日期

selectdate_add('2020-01-2000:00:00',intervalrowDAY)datefrom

(

SELECT@row:=@row+1asrowFROM

(select0unionallselect1unionallselect2unionallselect3unionallselect4unionallselect5unionallselect6unionallselect7unionallselect8unionallselect9)t,

(select0unionallselect1unionallselect2unionallselect3unionallselect4unionallselect5unionallselect6unionallselect7unionallselect8unionallselect9)t2,

(SELECT@row:=-1)r

)se

wheredate_add('2020-01-2000:00:00',intervalrowDAY)<='2020-03-0200:00:00'

备注：

这段代码表示数据条数限制，写两次查询的日期最多显示100条，写三次查询日期最多显示1000次，以此类推，根据你自己的需求决定

下面是设置最多显示条数10000写法

1、不使用存储过程，不使用临时表，不使用循环在Mysql中获取一个时间段的全部日期

selecta.Date

from(

selectcurdate()-INTERVAL(a.a+(10*b.a)+(100*c.a))DAYasDate

from(select0asaunionallselect1unionallselect2unionallselect3unionallselect4unionallselect5unionallselect6unionallselect7unionallselect8unionallselect9)asa

crossjoin(select0asaunionallselect1unionallselect2unionallselect3unionallselect4unionallselect5unionallselect6unionallselect7unionallselect8unionallselect9)asb

crossjoin(select0asaunionallselect1unionallselect2unionallselect3unionallselect4unionallselect5unionallselect6unionallselect7unionallselect8unionallselect9)asc

)a

wherea.Datebetween'2017-11-10'and'2017-11-15'

输出如下

Date

----------

2017-11-15

2017-11-14

2017-11-13

2017-11-12

2017-11-11

2017-11-10

2、mysql获取两个日期内的所有日期列表

select@num:=@num+1,date_format(adddate('2015-09-01',INTERVAL@numDAY),'%Y-%m-%d')asdate

frombtc_user,(select@num:=0)twhereadddate('2015-09-01',INTERVAL@numDAY)<=date_format(curdate(),'%Y-%m-%d')

orderbydate;

此方法优点就是不需要创建存储过程或者是日历表，缺点就是你必须要有一个表，它的数据条数大到足够支撑你要查询的天数

3、mysql获取给定时间段内的所有日期列表(存储过程)

DELIMITER$$

DROPPROCEDUREIFEXISTScreate_calendar$$

CREATEPROCEDUREcreate_calendar(s_dateDATE,e_dateDATE)

BEGIN

--生成一个日历表

SET@createSql=‘CREATETABLEIFNOTEXISTScalendar_custom(

`date`dateNOTNULL,

UNIQUEKEY`unique_date`(`date`)USINGBTREE

)ENGINE=InnoDBDEFAULTCHARSET=utf8‘;

preparestmtfrom@createSql;

executestmt;

WHILEs_date<=e_dateDO

INSERTIGNOREINTOcalendar_customVALUES(DATE(s_date));

SETs_date=s_date+INTERVAL1DAY;

ENDWHILE;

END$$

DELIMITER;

--生成数据到calendar_custom表2009-01-01~2029-01-01之间的所有日期数据

CALLcreate_calendar(‘2009-01-01‘,‘2029-01-01‘);

DELIMITER$$

DROPPROCEDUREIFEXISTScreate_calendar$$

CREATEPROCEDUREcreate_calendar(s_dateDATE,e_dateDATE)

BEGIN

--生成一个日历表

SET@createSql=‘truncateTABLEcalendar_custom‘;

preparestmtfrom@createSql;

executestmt;

WHILEs_date<=e_dateDO

INSERTIGNOREINTOcalendar_customVALUES(DATE(s_date));

SETs_date=s_date+INTERVAL1DAY;

ENDWHILE;

END$$

DELIMITER;

--生成数据到calendar_custom表2009-01-01~2029-01-01之间的所有日期数据

CALLcreate_calendar(‘2009-01-02‘,‘2009-01-07‘);

下一篇：MySQL创建数据库的两种方法