mysql生成一段时间内连续每小时，每天，每月的sql

准备

DROP TABLE IF EXISTS `num`;
CREATE TABLE `num` (
  `i` int(2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `num` VALUES ('0');
INSERT INTO `num` VALUES ('1');
INSERT INTO `num` VALUES ('2');
INSERT INTO `num` VALUES ('3');
INSERT INTO `num` VALUES ('4');
INSERT INTO `num` VALUES ('5');
INSERT INTO `num` VALUES ('6');
INSERT INTO `num` VALUES ('7');
INSERT INTO `num` VALUES ('8');
INSERT INTO `num` VALUES ('9');

一天中每小时

select xc as date from (SELECT @xi:=@xi+1 as xc from
          (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) xc1,
          (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) xc2,
          (SELECT @xi:=-1) xc0) xx where xc < 24;

一段时间内每天

SELECT
    adddate('2018-12-01', numlist.id) AS date
FROM
    (
        SELECT
            n1.i + n10.i * 10 + n100.i * 100 + n1000.i * 1000 + n10000.i * 10000 AS id
        FROM
            num n1
        CROSS JOIN num AS n10
        CROSS JOIN num AS n100
        CROSS JOIN num AS n1000
        CROSS JOIN num AS n10000
    ) AS numlist
WHERE
    adddate(
        '2018-12-01', numlist.id) <= '2018-12-22';

一段时间内每月

    DATE_FORMAT(
        date_add(
            DATE_ADD(now(), INTERVAL - 11 MONTH),
            INTERVAL numlist.id MONTH
        ),
        '%Y-%m'
    ) AS date
FROM
    (
        SELECT
            n1.i + n10.i * 10 AS id
        FROM
            num n1
        CROSS JOIN num AS n10
    ) AS numlist
WHERE
    date_add(
        DATE_ADD(now(), INTERVAL - 11 MONTH),
        INTERVAL numlist.id MONTH
    ) <= DATE_ADD(now(), INTERVAL 0 MONTH);