如何通过逻辑判断和索引优化在MySQL中高效查询重叠时间段数据？

本文共计932个文字，预计阅读时间需要4分钟。

使用以下查询语句可以重叠时间段，但可能会丢失数据：

根本原因是 BETWEEN 只检查单端点是否落在目标区间内，而重叠本质是两个区间有交集，必须同时约束两端。

• 正确逻辑是：new_start old_start
• 字段类型必须是 TIME、DATETIME 或 TIMESTAMP；存成字符串会导致字典序比较，结果不可靠
• 如果字段含日期（如 DATETIME），注意时区一致性，避免因服务器/会话时区不同导致比较错乱

查某员工当天是否存在考勤重叠的 SQL 写法

场景：插入新考勤前，先确认该员工当天是否已有与其他记录重叠的时间段。

假设表结构为 attendance(emp_id, date, start_time, end_time)，新记录为员工 123、日期 '2024-06-15'、时间段 '09:20'–'10:45'：

SELECT COUNT(*) > 0 AS has_overlap FROM attendance WHERE emp_id = 123 AND date = '2024-06-15' AND '09:20' < end_time AND '10:45' > start_time;

• 注意：条件顺序不影响结果，但把等值过滤项（emp_id、date）放前面更利于索引下推
• 别写成 start_time '09:20' —— 虽然逻辑等价，但 MySQL 优化器对范围条件的索引选择可能更保守
• 返回 1 表示存在重叠，可直接拒绝插入

为什么 (emp_id, date, start_time, end_time) 复合索引必不可少

没有合适索引时，上面的查询会触发全表扫描，尤其在考勤表数据量大、并发高时，响应延迟明显，且容易拖垮数据库。

• 该索引能覆盖全部 WHERE 条件字段，避免回表
• emp_id 和 date 是等值查询，必须放在最左；start_time 和 end_time 是范围查询，顺序不影响，但按定义习惯放后面
• 如果只建 (emp_id, date) 索引，start_time 和 end_time 的范围条件仍需逐行计算，性能提升有限
• 执行 EXPLAIN 时，应看到 type=range 且 key 显示该复合索引名，rows 显著低于总行数

INSERT 时防并发重叠的原子写法

应用层先 SELECT 判断无重叠，再 INSERT，是典型竞态漏洞：两个请求几乎同时通过判断，然后都插入成功，破坏业务规则。

必须用一条语句完成“检查 + 插入”，推荐 INSERT ... SELECT ... WHERE NOT EXISTS：

INSERT INTO attendance (emp_id, date, start_time, end_time) SELECT 123, '2024-06-15', '09:20', '10:45' WHERE NOT EXISTS ( SELECT 1 FROM attendance WHERE emp_id = 123 AND date = '2024-06-15' AND '09:20' < end_time AND '10:45' > start_time );

• 这条语句要么插入成功，要么什么也不做（影响行为为 0），不会报错，应用层需检查 affected_rows
• 依赖前述复合索引，否则子查询效率低，反而放大锁等待
• 不要用 INSERT IGNORE 或 ON DUPLICATE KEY UPDATE 替代——它们无法捕获“逻辑重叠”，只防主键/唯一键冲突

边界情况容易被忽略：当 start_time = end_time（如签到签退同一秒），或字段允许 NULL，需额外约定并校验，否则比较行为可能不符合预期。