mysql遍历一段时间内的所有日期、月份

• 遍历一段时间内的所有月份

SELECT DISTINCT
	date_format(
		DATE_ADD(
			'2019-06-30' ,
			INTERVAL id MONTH
		),
		'%Y-%m'
	) AS `TIME`
FROM
	bbkb_busticket.time
WHERE
	DATE_ADD(
		'2019-06-30' ,
		INTERVAL id MONTH
	) <= '2020-06-30'
ORDER BY time

• 遍历一段时间内的所有日期

SELECT DISTINCT
	date_format(
		DATE_ADD(
			'2019-06-30' ,
			INTERVAL id day
		),
		'%Y-%m-%d'
	) AS `TIME`
FROM
	bbkb_busticket.time
WHERE
	DATE_ADD(
		'2019-06-30' ,
		INTERVAL id day
	) <= '2020-06-30'
ORDER BY time

注：time为数据量足够大的表，行数必须大于时间跨度(天)，如示例time表需大于等于366行。

time表

CREATE TABLE `time` (
  `id` int(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

插入366个id

DELIMITER ;; 
CREATE  PROCEDURE time_creater()
BEGIN
	#Routine body goes here...
	DECLARE a INT DEFAULT 1 ;
	while a < 367
	DO
		INSERT into time(`id`) values (a);
		set a = a+1;
	END WHILE;
END;;
call time_creater();