mysql建立日历表

CREATE TABLE calendar (
    date DATE PRIMARY KEY,
    year INT,
    month INT,
    day INT,
    day_of_week INT,
    is_weekend BOOLEAN,
    is_holiday BOOLEAN
);

DELIMITER $$
CREATE PROCEDURE generate_calendar (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 (date, year, month, day, day_of_week, is_weekend, is_holiday)
        VALUES (
            current_date,
            YEAR(current_date),
            MONTH(current_date),
            DAY(current_date),
            DAYOFWEEK(current_date),
            CASE WHEN DAYOFWEEK(current_date) IN (1, 7) THEN TRUE ELSE FALSE END,
            FALSE -- 这里可以根据实际情况设置节假日
        );
        SET current_date = DATE_ADD(current_date, INTERVAL 1 DAY);
    END WHILE;
END $$
DELIMITER ;

CALL generate_calendar('2020-01-01', '2030-12-31');