mysql如何设计考勤系统_mysql时间段重叠查询处理-mysql教程-PHP中文网

正确判断时间重叠需用区间相交逻辑：new_start old_start；between易漏跨段重叠；须建(emp_id,date,start_time,end_time)复合索引并用insert...select+not exists防并发插入。

mysql如何设计考勤系统_mysql时间段重叠查询处理

查考勤重叠：用 `BETWEEN` 会漏数据，得用区间相交逻辑

直接用 start_time BETWEEN ? AND ? 或 end_time BETWEEN ? AND ? 判断重叠，大概率漏掉跨段情况。比如已有记录是 09:00–10:30，新提交的是 10:00–11:00，两个 BETWEEN 都不成立，但实际重叠了 10:00–10:30。

正确逻辑是：两段时间有交集 ⇔ new_start old_start。MySQL 没有原生区间类型，所以必须手写这个判断。

查某员工当天是否与其他排班重叠：

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

start_time 和 end_time 必须是 TIME 或 DATETIME 类型，别存成字符串，否则比较失效
如果字段含日期（如 DATETIME），注意时区和索引覆盖——(emp_id, date, start_time, end_time) 复合索引能显著提速

插入前防重叠：用 `INSERT ... SELECT` + `NOT EXISTS` 原子检查

应用层先 SELECT 再 INSERT 是经典竞态漏洞。两个请求同时查“无重叠”，然后都插入，就破防了。必须把检查和插入压进一条语句里。

MySQL

编写正确的MySQL查询，避免字符集、索引和锁方面的常见陷阱。

下载

安全写法（MySQL 5.7+）：

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

返回影响行数为 0，说明被拦截；为 1 才真正插入成功
别用 INSERT IGNORE 或 ON DUPLICATE KEY UPDATE，它们依赖唯一索引，而重叠不是键冲突，无法触发

查询某人某天所有考勤段并合并连续时段

员工一天打卡多次（如上午班+加班+夜间值班），原始记录是离散的，但统计工时需要合并相邻或相接的段。MySQL 8.0+ 可用窗口函数，5.7 只能靠变量模拟。

8.0+ 推荐写法（按 start_time 排序后识别“断点”）：

WITH ordered AS (
  SELECT *,
         LAG(end_time) OVER (PARTITION BY emp_id, date ORDER BY start_time) AS prev_end
  FROM attendance
  WHERE emp_id = 123 AND date = '2024-06-15'
),
grouped AS (
  SELECT *,
         SUM(CASE WHEN start_time > prev_end THEN 1 ELSE 0 END)
           OVER (PARTITION BY emp_id, date ORDER BY start_time) AS grp
  FROM ordered
)
SELECT MIN(start_time) AS merged_start,
       MAX(end_time) AS merged_end
FROM grouped
GROUP BY emp_id, date, grp;

若用 MySQL 5.7，必须用用户变量，但要注意执行顺序不可靠，生产环境慎用；优先升级或改用应用层合并
合并逻辑默认不处理“相接”（如 09:00–10:00 和 10:00–11:00），如需包含，把 start_time > prev_end 改成 start_time > DATE_ADD(prev_end, INTERVAL 1 SECOND)

长期运行后查询变慢？重点优化 `start_time`/`end_time` 的联合条件

随着考勤表增长，重叠查询容易全表扫描。问题不在数据量，而在 WHERE 条件里 new_start start_time 无法高效走索引——它不是标准范围查找，而是两个方向的不等式。

最有效方案：加复合索引 (emp_id, date, start_time, end_time)，让过滤 emp_id + date 后，剩余数据量可控，再对小结果集做内存扫描
别单独给 start_time 或 end_time 加索引，意义不大；也别试图用函数索引（如 start_time + end_time），MySQL 不支持表达式索引用于这种逻辑
如果单日数据超 10 万行，考虑分区：按 date RANGE 分区，避免扫无关月份

重叠判断看着简单，但边界条件（相接、跨日、NULL 值）、并发控制、索引有效性三者叠加，才是真实卡点。别在应用层补丁，从 SQL 逻辑和索引设计开始对齐。