-- 出勤管理数据库变更(MySQL) -- 执行前请备份数据库;本脚本按“尚未添加这些列/索引”的数据库执行一次。 alter table class_subject_attendance modify column check_in_time datetime null comment '实际签到时间,缺勤时为空', add column status varchar(16) not null default 'NORMAL' comment 'NORMAL/LATE/ABSENT/LEAVE' after check_in_time, add column check_in_method varchar(16) null comment 'IP_CODE/IP_ONLY/TEACHER/SYSTEM/LEAVE/LEGACY' after status, add column check_in_ip varchar(45) null comment '服务端识别的签到IP' after check_in_method, add column teacher_confirmed tinyint(1) not null default 0 after check_in_ip, add column confirmed_by varchar(64) null after teacher_confirmed, add column confirmed_at datetime null after confirmed_by, add column created_time datetime null after confirmed_at, add column update_time datetime null after created_time; update class_subject_attendance set status = 'NORMAL', check_in_method = 'LEGACY', teacher_confirmed = 0, created_time = coalesce(created_time, check_in_time), update_time = coalesce(update_time, check_in_time) where check_in_method is null; -- 添加唯一索引前可先执行以下语句检查历史重复数据: -- select subject_id, user_id, count(*) from class_subject_attendance group by subject_id, user_id having count(*) > 1; alter table class_subject_attendance add unique key uk_attendance_subject_user (subject_id, user_id), add key idx_attendance_user_status (user_id, status), add key idx_attendance_subject_status (subject_id, status); create table class_subject_attendance_session ( id varchar(64) not null, subject_id int not null, teacher_id varchar(64) not null, verification_code char(6) null, scheduled_start_time datetime not null, scheduled_end_time datetime not null, started_at datetime null, code_expires_at datetime null, status varchar(16) not null comment 'ACTIVE/ENDED', created_time datetime not null, update_time datetime not null, primary key (id), unique key uk_attendance_session_subject (subject_id), key idx_attendance_session_end_status (scheduled_end_time, status) ) engine=InnoDB default charset=utf8mb4 comment='课程签到会话';