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');