mysql 判断工作日

DELIMITER //

CREATE FUNCTION IsWorkday(date DATE) RETURNS BOOLEAN
BEGIN
    DECLARE dayOfWeek INT;
    SET dayOfWeek = DAYOFWEEK(date);
    IF dayOfWeek BETWEEN 2 AND 6 THEN -- 2: Monday, 6: Friday
        RETURN TRUE;
    ELSE
        RETURN FALSE;
    END IF;
END //

DELIMITER ;

-- 使用示例
SELECT IsWorkday('2023-10-05') AS is_workday; -- 返回1 (TRUE)

CREATE TABLE Calendar (
    date DATE PRIMARY KEY,
    is_holiday BOOLEAN DEFAULT FALSE
);

-- 插入一些示例数据
INSERT INTO Calendar (date, is_holiday) VALUES
('2023-10-01', TRUE), -- 国庆节
('2023-10-02', TRUE),
('2023-10-03', TRUE),
('2023-10-04', TRUE),
('2023-10-05', FALSE); -- 工作日

-- 创建一个视图来判断工作日
CREATE VIEW Workdays AS
SELECT date
FROM Calendar
WHERE is_holiday = FALSE AND DAYOFWEEK(date) BETWEEN 2 AND 6;

-- 使用示例
SELECT * FROM Workdays WHERE date = '2023-10-05'; -- 返回2023-10-05

CREATE INDEX idx_date ON Calendar(date);