mysql查询日期区间内出所有的日期_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');