MySQL 排班表設計：日期+時段+狀態，按周期生成記錄

需求

7 天排班，每天 6:00-23:00 按小時分時段，分上午/下午/夜晚，時段狀態：滿→忙碌，否→空閒。

表結構

CREATE TABLE schedule (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    work_date DATE NOT NULL,           -- 排班日期
    time_slot TIME NOT NULL,           -- 時段起始時間（如 06:00:00）
    period ENUM('morning','afternoon','evening') NOT NULL, -- 時段歸屬
    status ENUM('free','busy') NOT NULL DEFAULT 'free',    -- 狀態
    UNIQUE KEY uk_date_slot (work_date, time_slot)         -- 同天同時段唯一
);

初始化數據（存儲過程/腳本生成）

-- 生成 7 天 × 18 時段 = 126 條記錄
INSERT INTO schedule (work_date, time_slot, period, status)
SELECT d.dt, t.tm,
       CASE
         WHEN HOUR(t.tm) BETWEEN 6 AND 11 THEN 'morning'
         WHEN HOUR(t.tm) BETWEEN 12 AND 17 THEN 'afternoon'
         ELSE 'evening'
       END,
       'free'
FROM (
    SELECT DATE_ADD(CURDATE(), INTERVAL n DAY) AS dt
    FROM (SELECT 0 n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6) d
) d
CROSS JOIN (
    SELECT '06:00:00' tm UNION SELECT '07:00:00' UNION SELECT '08:00:00' UNION SELECT '09:00:00'
    UNION SELECT '10:00:00' UNION SELECT '11:00:00' UNION SELECT '12:00:00' UNION SELECT '13:00:00'
    UNION SELECT '14:00:00' UNION SELECT '15:00:00' UNION SELECT '16:00:00' UNION SELECT '17:00:00'
    UNION SELECT '18:00:00' UNION SELECT '19:00:00' UNION SELECT '20:00:00' UNION SELECT '21:00:00'
    UNION SELECT '22:00:00'
) t;

查詢某天排班

SELECT time_slot, period, status
FROM schedule
WHERE work_date = '2024-01-15'
ORDER BY time_slot;

狀態更新（預訂/取消）

UPDATE schedule SET status = 'busy' WHERE work_date = ? AND time_slot = ?;