答案：设计MySQL员工考勤系统需构建员工表、打卡记录表、考勤规则表和每日汇总表，通过定时任务处理打卡数据并生成考勤结果。首先创建employees表存储员工信息；attendance_records表记录每次打卡时间；attendance_rules表配置上下班时间和容差规则；daily_attendance表存储每日考勤状态以提升查询效率。打卡数据通过SQL聚合提取每日首尾打卡时间，结合考勤规则判断迟到、早退等状态，并利用存储过程GenerateDailyAttendance按日更新汇总表。常用查询包括员工月度出勤统计和某日全体人员考勤情况展示，实现高效管理和分析。
设计一个MySQL员工考勤系统，关键在于合理的数据表结构、清晰的业务逻辑和高效的数据查询。下面从需求分析到数据库设计再到常用SQL操作，带你一步步完成一个实用的员工考勤系统。
1. 明确考勤系统的功能需求
一个基本的员工考勤系统通常需要支持以下功能：
- 员工信息管理：记录员工编号、姓名、部门、职位等基本信息。
- 打卡记录：记录每天上下班的打卡时间（支持多次打卡）。
- 考勤规则配置：如上班时间、下班时间、迟到早退判断标准。
- 考勤统计：按天/月统计出勤、迟到、早退、缺卡等情况。
- 请假与异常处理：支持请假、补卡申请等人工干预。
基于这些需求，我们可以设计对应的数据库表结构。
2. 数据库表结构设计
以下是核心表的设计建议：
（1）员工表（employees）
CREATE TABLE employees (
emp_id INT PRIMARY KEY AUTO_INCREMENT,
emp_name VARCHAR(50) NOT NULL,
department VARCHAR(50),
position VARCHAR(50),
hire_date DATE,
status TINYINT DEFAULT 1 COMMENT '1-在职, 0-离职'
);
（2）打卡记录表（attendance_records）
CREATE TABLE attendance_records (
record_id BIGINT PRIMARY KEY AUTO_INCREMENT,
emp_id INT NOT NULL,
punch_time DATETIME NOT NULL,
device_type VARCHAR(20) COMMENT '打卡方式: 手机, 门禁机等',
FOREIGN KEY (emp_id) REFERENCES employees(emp_id)
);
（3）考勤规则表（attendance_rules）
CREATE TABLE attendance_rules (
rule_id INT PRIMARY KEY AUTO_INCREMENT,
work_date_type ENUM('weekday', 'weekend', 'holiday') DEFAULT 'weekday',
start_time TIME DEFAULT '09:00:00',
end_time TIME DEFAULT '18:00:00',
late_tolerance INT DEFAULT 10 COMMENT '迟到容忍分钟数',
early_leave_tolerance INT DEFAULT 10
);
（4）每日考勤汇总表（daily_attendance）
用于存储每日计算结果，提高查询效率。
CREATE TABLE daily_attendance (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
emp_id INT NOT NULL,
work_date DATE NOT NULL,
clock_in TIME,
clock_out TIME,
status ENUM('正常', '迟到', '早退', '缺卡', '旷工') DEFAULT '正常',
remarks VARCHAR(200),
UNIQUE KEY unique_emp_date (emp_id, work_date)
);
3. 实现打卡数据处理逻辑
每次员工打卡，先插入到 attendance_records 表。然后通过定时任务或触发器，按日整理打卡记录，生成每日最早和最晚打卡时间。
示例：提取某员工某天的上下班时间
SELECT
emp_id,
DATE(punch_time) AS work_date,
MIN(TIME(punch_time)) AS first_punch,
MAX(TIME(punch_time)) AS last_punch
FROM attendance_records
WHERE emp_id = 1001 AND DATE(punch_time) = '2024-04-01'
GROUP BY emp_id, DATE(punch_time);
判断是否迟到（假设上班时间是09:00，容忍10分钟）
SELECT
emp_id,
work_date,
first_punch,
CASE
WHEN first_punch > '09:10:00' THEN '迟到'
ELSE '正常'
END AS late_status
FROM (
SELECT
emp_id,
DATE(punch_time) AS work_date,
MIN(TIME(punch_time)) AS first_punch
FROM attendance_records
WHERE DATE(punch_time) = '2024-04-01'
GROUP BY emp_id, DATE(punch_time)
) t;
4. 自动生成每日考勤汇总
可以写一个存储过程，每天凌晨运行，处理前一天的打卡数据并更新 daily_attendance 表。
简化版存储过程逻辑示意：
DELIMITER //
CREATE PROCEDURE GenerateDailyAttendance(IN target_date DATE)
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE v_emp_id INT;
DECLARE v_first_punch, v_last_punch TIME;
DECLARE cur CURSOR FOR
SELECT emp_id, MIN(TIME(punch_time)), MAX(TIME(punch_time))
FROM attendance_records
WHERE DATE(punch_time) = target_date
GROUP BY emp_id;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN cur;
read_loop: LOOP
FETCH cur INTO v_emp_id, v_first_punch, v_last_punch;
IF done THEN LEAVE read_loop; END IF;
-- 判断状态（简化）
INSERT INTO daily_attendance (emp_id, work_date, clock_in, clock_out, status)
VALUES (
v_emp_id,
target_date,
v_first_punch,
v_last_punch,
CASE
WHEN v_first_punch IS NULL THEN '旷工'
WHEN v_first_punch > '09:10:00' THEN '迟到'
WHEN v_last_punch < '17:50:00' THEN '早退'
ELSE '正常'
END
) ON DUPLICATE KEY UPDATE
clock_in = v_first_punch,
clock_out = v_last_punch,
status = CASE
WHEN v_first_punch IS NULL THEN '旷工'
WHEN v_first_punch > '09:10:00' THEN '迟到'
WHEN v_last_punch < '17:50:00' THEN '早退'
ELSE '正常'
END;
END LOOP;
CLOSE cur;END//
DELIMITER ;
5. 常用查询示例
查询某员工月度考勤统计
SELECT
status,
COUNT(*) AS days
FROM daily_attendance
WHERE emp_id = 1001 AND work_date BETWEEN '2024-04-01' AND '2024-04-30'
GROUP BY status;
查询所有员工某天的考勤情况
SELECT
e.emp_name,
d.clock_in,
d.clock_out,
d.status
FROM daily_attendance d
JOIN employees e ON d.emp_id = e.emp_id
WHERE d.work_date = '2024-04-01';
