mysql 生成时间维度表

CREATE TABLE calendar_dim (
    date_id INT PRIMARY KEY AUTO_INCREMENT,
    date DATE NOT NULL,
    year INT NOT NULL,
    quarter INT NOT NULL,
    month INT NOT NULL,
    day INT NOT NULL,
    day_of_week INT NOT NULL,
    is_weekend BOOLEAN NOT NULL
);

DELIMITER $$
CREATE PROCEDURE generate_calendar_dim(IN start_date DATE, IN end_date DATE)
BEGIN
    DECLARE current_date DATE;
    SET current_date = start_date;
    WHILE current_date <= end_date DO
        INSERT INTO calendar_dim (date, year, quarter, month, day, day_of_week, is_weekend)
        VALUES (current_date,
                YEAR(current_date),
                QUARTER(current_date),
                MONTH(current_date),
                DAY(current_date),
                DAYOFWEEK(current_date),
                DAYOFWEEK(current_date) IN (1, 7));
        SET current_date = DATE_ADD(current_date, INTERVAL 1 DAY);
    END WHILE;
END$$
DELIMITER ;

CALL generate_calendar_dim('2020-01-01', '2023-12-31');