-- 教师端“教学管理-我的课程”增量数据库脚本(MySQL 5.7+/8.0)。 -- -- 本功能不新增业务表或字段,只补齐教师日历和并发点名依赖的索引。 -- 唯一索引创建前会检查历史重复数据;发现重复时主动终止,不会自动删除任何业务记录。 delimiter $$ drop procedure if exists migrate_teacher_course$$ create procedure migrate_teacher_course() begin if not exists ( select 1 from information_schema.tables where table_schema = database() and table_name = 'class_subject_attendance' ) or not exists ( select 1 from information_schema.tables where table_schema = database() and table_name = 'class_subject_attendance_session' ) then signal sqlstate '45000' set message_text = '缺少出勤表,请先执行项目基础出勤模块数据库脚本'; end if; -- 教师日历固定按教师和日期范围查询,课程ID用于稳定排序和回表。 if not exists ( select 1 from information_schema.statistics where table_schema = database() and table_name = 'class_subject' and index_name = 'idx_subject_teacher_date' ) then alter table class_subject add index idx_subject_teacher_date (teacher_id, date, id); end if; -- 点名UPSERT依赖“一门课、一个学员只能有一条最终记录”。 if exists ( select 1 from class_subject_attendance group by subject_id, user_id having count(*) > 1 limit 1 ) then signal sqlstate '45000' set message_text = 'class_subject_attendance存在重复课程学员记录,请先人工核对处理'; end if; if not exists ( select 1 from ( select index_name, group_concat(column_name order by seq_in_index separator ',') as indexed_columns from information_schema.statistics where table_schema = database() and table_name = 'class_subject_attendance' and non_unique = 0 group by index_name ) unique_indexes where unique_indexes.indexed_columns = 'subject_id,user_id' ) then alter table class_subject_attendance add unique index uk_attendance_subject_user (subject_id, user_id); end if; -- 同一排课只能有一个签到会话和一份验证码,重复点击通过唯一索引实现幂等。 if exists ( select 1 from class_subject_attendance_session group by subject_id having count(*) > 1 limit 1 ) then signal sqlstate '45000' set message_text = 'class_subject_attendance_session存在重复课程会话,请先人工核对处理'; end if; if not exists ( select 1 from ( select index_name, group_concat(column_name order by seq_in_index separator ',') as indexed_columns from information_schema.statistics where table_schema = database() and table_name = 'class_subject_attendance_session' and non_unique = 0 group by index_name ) unique_indexes where unique_indexes.indexed_columns = 'subject_id' ) then alter table class_subject_attendance_session add unique index uk_attendance_session_subject (subject_id); end if; end$$ call migrate_teacher_course()$$ drop procedure if exists migrate_teacher_course$$ delimiter ; -- 如迁移因重复数据终止,可先用以下只读SQL定位记录,确认业务归属后再人工处理: select subject_id, user_id, count(*) as duplicate_count from class_subject_attendance group by subject_id, user_id having count(*) > 1; select subject_id, count(*) as duplicate_count from class_subject_attendance_session group by subject_id having count(*) > 1;