SQL中如何在两列使用BETWEEN？及日期预约功能实现咨询

1. 在SQL的两个列上使用BETWEEN操作符 #

首先得明确：BETWEEN本身是用来判断单个值是否落在某个连续区间里的操作符。如果要在两个列上使用，通常分两种常见场景：

场景1：两个列分别满足各自的BETWEEN条件 #

比如你想筛选出age列在18-30之间，且score列在80-100之间的记录，直接用AND连接两个BETWEEN判断即可：

SELECT * FROM your_table
WHERE age BETWEEN 18 AND 30
  AND score BETWEEN 80 AND 100;

场景2：判断两个列组成的时间区间与目标区间重叠（预约场景常用） #

如果你的两个列是开始时间和结束时间（比如你的预约表中的reserverStartH和reserverEndH），想判断这个区间是否和某个目标时间段有重叠，这时候不能直接对两个列用BETWEEN，而是要用重叠判断逻辑：
假设目标区间是@target_start到@target_end，重叠的判断公式是：

SELECT * FROM timestamps
WHERE STR_TO_DATE(CONCAT(reserverDate, ' ', reserverStartH), '%Y-%m-%d %H:%i:%s') < @target_end
  AND STR_TO_DATE(CONCAT(reserverDate, ' ', reserverEndH), '%Y-%m-%d %H:%i:%s') > @target_start;

这个逻辑的核心是：只要现有预约的开始时间早于目标结束时间，且现有预约的结束时间晚于目标开始时间，就说明两个区间有重叠。

2. 基于你的timestamps表实现日期预约功能 #

从你的表结构来看，核心字段是预约日期、开始/结束时间，预约功能最关键的需求是避免时间冲突，同时完成预约创建、查询、确认的完整流程。我给你分步骤梳理：

第一步：优化表结构（可选但推荐） #

你的表中日期和时间是分开存储的，建议合并成完整的DATETIME字段，同时增加必要的约束保证数据合法性：

-- 修改表结构，添加完整的开始/结束时间字段
ALTER TABLE timestamps
ADD COLUMN reserver_start DATETIME,
ADD COLUMN reserver_end DATETIME,
-- 约束：结束时间必须晚于开始时间
ADD CONSTRAINT chk_time_order CHECK (reserver_end > reserver_start),
-- 约束：预约时间不能早于当前时间
ADD CONSTRAINT chk_future_time CHECK (reserver_start > NOW());

-- 用触发器自动同步原有日期时间字段到新的DATETIME字段
DELIMITER //
CREATE TRIGGER sync_datetime BEFORE INSERT ON timestamps
FOR EACH ROW
BEGIN
  SET NEW.reserver_start = STR_TO_DATE(CONCAT(NEW.reserverDate, ' ', NEW.reserverStartH), '%Y-%m-%d %H:%i:%s');
  SET NEW.reserver_end = STR_TO_DATE(CONCAT(NEW.reserverDate, ' ', NEW.reserverEndH), '%Y-%m-%d %H:%i:%s');
END //
DELIMITER ;

第二步：创建预约（带冲突检查） #

插入新预约前，必须先检查是否有重叠的已确认预约（如果是多资源场景，只需在WHERE中加入resource_id匹配条件即可）：

-- 用原子性INSERT实现冲突检查，避免并发问题
INSERT INTO timestamps (demandeur, reserverAvecQui, reserverWhy, reserver_start, reserver_end)
SELECT 'test.fr', 'anonyme', 'Je ne...', '2024-05-20 14:00:00', '2024-05-20 15:00:00'
FROM DUAL
WHERE NOT EXISTS (
  SELECT 1 FROM timestamps
  WHERE reserver_start < '2024-05-20 15:00:00'
    AND reserver_end > '2024-05-20 14:00:00'
    AND reserverOk = 1 -- 只检查已确认的有效预约
);

第三步：查询可用时间段 #

比如查询2024-05-20当天9:00-18:00之间的所有1小时可用时段：

-- 生成当天的所有小时段
WITH hours AS (
  SELECT DATE_ADD('2024-05-20 09:00:00', INTERVAL hour_num HOUR) AS slot_start
  FROM (SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8) AS h
)
SELECT slot_start, DATE_ADD(slot_start, INTERVAL 1 HOUR) AS slot_end
FROM hours
WHERE NOT EXISTS (
  SELECT 1 FROM timestamps
  WHERE reserver_start < DATE_ADD(slot_start, INTERVAL 1 HOUR)
    AND reserver_end > slot_start
    AND reserverOk = 1
);

第四步：确认预约 #

当用户完成确认后，更新reserverOk字段标记预约生效：

UPDATE timestamps
SET reserverOk = 1
WHERE idReserver = 1;

内容的提问来源于stack exchange，提问作者geek pédagogique lcms