mysql 生成所有月份数据

CREATE TABLE months (
    month_id INT PRIMARY KEY,
    year INT,
    month INT
);

DELIMITER $$
CREATE PROCEDURE generate_months()
BEGIN
    DECLARE v_year INT DEFAULT 2020;
    DECLARE v_month INT DEFAULT 1;
    DECLARE done INT DEFAULT FALSE;

    WHILE NOT done DO
        INSERT INTO months (month_id, year, month) VALUES (v_year * 100 + v_month, v_year, v_month);

        SET v_month = v_month + 1;
        IF v_month > 12 THEN
            SET v_month = 1;
            SET v_year = v_year + 1;
        END IF;

        IF v_year > YEAR(CURDATE()) THEN
            SET done = TRUE;
        END IF;
    END WHILE;
END$$
DELIMITER ;

CALL generate_months();

SELECT 
    year * 100 + month AS month_id,
    year,
    month
FROM (
    SELECT 
        @year := @year + 1 AS year,
        CASE WHEN @year > YEAR(CURDATE()) THEN 1 ELSE 12 END AS month
    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) AS digits1,
        (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) AS digits2,
        (SELECT @year := 2020) AS init
) AS year_month
WHERE 
    year >= 2020;