mysql获取⼀个时间段中所有⽇期或者⽉份1：mysql获取时间段所有⽉份
select DATE_FORMAT(date_add('2020-01-20 00:00:00', interval row MONTH),'%Y-%m') 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 @row:=-1) r ) se
where DATE_FORMAT(date_add('2020-01-20 00:00:00', interval row MONTH),'%Y-%m') <= DATE_FORMAT('2020-04-02 00:00:00','%Y-%m')
2：mysql获取时间段所有⽇期
select date_add('2020-01-20 00:00:00', 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 @row:=-1) r ) se
where date_add('2020-01-20 00:00:00', interval row DAY) <= '2020-03-02 00:00:00'
备注：
这段代码表⽰数据条数限制，写两次查询的⽇期最多显⽰100条，写三次查询⽇期最多显⽰1000次，以此类推，根据你⾃⼰的需求决定下⾯是设置最多显⽰条数10000写法
希望能帮助到你，萌新在线求带下⾯是其他⽹友的补充⼤家可以参考⼀下
1、不使⽤存储过程，不使⽤临时表，不使⽤循环在Mysql中获取⼀个时间段的全部⽇期
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'
输出如下
Date----------2017-11-152017-11-142017-11-132017-11-122017-11-112017-11-10
2、mysql获取两个⽇期内的所有⽇期列表
select @num:=@num+1,date_format(adddate('2015-09-01', INTERVAL @num DAY),'%Y-%m-%d') as date
from btc_user,(select @num:=0) t where adddate('2015-09-01', INTERVAL @num DAY) <= date_format(curdate(),'%Y-%m-%d')order by date;
此⽅法优点就是不需要创建存储过程或者是⽇历表，缺点就是你必须要有⼀个表，它的数据条数⼤到⾜够⽀撑你要查询的天数3、mysql获取给定时间段内的所有⽇期列表(存储过程)
DELIMITER $$
DROP PROCEDURE IF EXISTS create_calendar $$
CREATE PROCEDURE create_calendar (s_date DATE, e_date DATE)BEGIN
-- ⽣成⼀个⽇历表
SET @createSql = ‘CREATE TABLE IF NOT EXISTS calendar_custom (`date` date NOT NULL,
UNIQUE KEY `unique_date` (`date`) USING BTREE)ENGINE=InnoDB DEFAULT CHARSET=utf8‘;prepare stmt from @createSql;execute stmt;
WHILE s_date <= e_date DO
INSERT IGNORE INTO calendar_custom VALUES (DATE(s_date)) ;SET s_date = s_date + INTERVAL 1 DAY ;END WHILE ;END$$
DELIMITER ;
-- ⽣成数据到calendar_custom表2009-01-01~2029-01-01之间的所有⽇期数据CALL create_calendar (‘2009-01-01‘, ‘2029-01-01‘);DELIMITER $$
DROP PROCEDURE IF EXISTS create_calendar $$
CREATE PROCEDURE create_calendar (s_date DATE, e_date DATE)BEGIN
-- ⽣成⼀个⽇历表
SET @createSql = ‘truncate TABLE calendar_custom‘;prepare stmt from @createSql;execute stmt;
WHILE s_date <= e_date DO
INSERT IGNORE INTO calendar_custom VALUES (DATE(s_date)) ;
SET s_date = s_date + INTERVAL 1 DAY ;END WHILE ;END$$
DELIMITER ;
-- ⽣成数据到calendar_custom表2009-01-01~2029-01-01之间的所有⽇期数据CALL create_calendar (‘2009-01-02‘, ‘2009-01-07‘);
到此这篇关于mysql获取指定时间段中所有⽇期或⽉份的语句(不设存储过程，不加表)的⽂章就介绍到这了,更多相关mysql获取指定时间段中的⽇期与⽉份内容请搜索以前的⽂章或继续浏览下⾯的相关⽂章希望⼤家以后多多⽀持！
因篇幅问题不能全部显示，请点此查看更多更全内容