基于MySQL查询医生首个及下一个可用预约时段的实现问题

问题1：ADDTIME计算结束时间返回空 #

故障原因 #

• 语法错误：ADDTIME() 第二个参数要求为TIME类型，直接传入duration*100生成的数字会被解析为HHMMSS格式，当duration≥60时，生成的数字如6000会被解析为00小时60分，超过TIME类型分钟最大取值59，导致返回空
• 笔误：你写的表名apptointment多了一个t，正确表名应为appointment

修复代码 #

-- 用DATE_ADD按分钟累加，更稳定
SELECT appt_id, st_time, DATE_ADD(st_time, INTERVAL duration MINUTE) AS end_time
FROM appointment;

问题2：可用时段查询无返回 #

故障原因 #

• 底层依赖的end_time计算逻辑错误，返回空值导致整条逻辑失效
• 现有逻辑只统计了已有预约之间的间隙，遗漏了「工作开始到第一个预约」「最后一个预约到工作结束」两类间隙，无预约的场景下直接无结果

修复思路 #

建议改用MySQL 8.0+支持的递归CTE生成所有候选时间槽，逻辑更清晰，示例如下（以15分钟时长为例）：

WITH RECURSIVE time_slots AS (
    -- 起始时间为当天工作开始时间
    SELECT '2021-09-16 09:00:00' AS slot_st, DATE_ADD('2021-09-16 09:00:00', INTERVAL 15 MINUTE) AS slot_end
    UNION ALL
    SELECT DATE_ADD(slot_st, INTERVAL 15 MINUTE), DATE_ADD(slot_end, INTERVAL 15 MINUTE)
    FROM time_slots
    -- 结束时间不超过当天工作结束时间
    WHERE slot_end < '2021-09-16 17:00:00'
)
-- 排除和已有预约重叠的槽位
SELECT s.slot_st, s.slot_end
FROM time_slots s
LEFT JOIN appointment a 
  ON a.doctor_id = 1
  AND s.slot_st < a.end_time
  AND s.slot_end > a.st_time
WHERE a.appt_id IS NULL;

问题3：关联工作时长和例外表逻辑 #

关联规则 #

你的working_day取值和MySQL内置函数DAYOFWEEK()返回值完全匹配（1=周日，2=周一...），可以直接适配：

1. 先按指定日期取DAYOFWEEK(目标日期)得到当天对应的星期编码
2. 取该医生对应星期的常规工作时段
3. 排除和例外时段重叠的部分，得到实际可用工作时段

示例代码（查询指定医生指定日期的有效工作时段） #

SET @target_date = '2021-09-16';
SET @doctor_id = 1;

SELECT 
  STR_TO_DATE(CONCAT(@target_date, ' ', TIME_FORMAT(w.st_time, '%H:%i:%s')), '%Y-%m-%d %H:%i:%s') AS valid_work_st,
  STR_TO_DATE(CONCAT(@target_date, ' ', TIME_FORMAT(w.end_time, '%H:%i:%s')), '%Y-%m-%d %H:%i:%s') AS valid_work_end
FROM doctor_working_hours w
LEFT JOIN doctor_working_exception e
  ON w.doctor_id = e.doctor_id
  AND w.working_day = e.working_day
  AND e.st_time < w.end_time
  AND e.end_time > w.st_time
WHERE w.doctor_id = @doctor_id
  AND w.working_day = DAYOFWEEK(@target_date)
  AND e.doctor_id IS NULL;

三个业务需求的实现思路 #

• 查询每位医生的首个可用预约：将上述时间槽生成逻辑关联所有医生的有效工作时段，按时间排序后取每个医生的第一条即可
• 指定医生的下一个可用预约：过滤医生ID+时间≥给定日期，按时间排序取第一条
• 指定时间范围所有空闲时段：将时间槽的起始结束范围设置为查询的时间范围，输出所有未被预约占用的槽位即可

内容的提问来源于stack exchange，提问作者user1578872