-- 管理员端“教务管理-考试管理”增量数据库脚本(MySQL 5.7+/8.0)。 -- -- 执行顺序:先执行class_management.sql,确保班级工种、年度和培训日期可用于考试校验。 -- 设计说明: -- 1. 项目已经存在exam和exam_student表,本脚本只补充分数快照、审计字段及常用索引。 -- 2. 所有变更都通过information_schema判断,可在不同环境重复执行。 -- 3. 沿用项目现有的服务层关系校验方式,不新增外键,避免历史孤儿数据阻断部署。 delimiter $$ drop procedure if exists migrate_exam_management$$ create procedure migrate_exam_management() begin if not exists ( select 1 from information_schema.columns where table_schema = database() and table_name = 'exam' and column_name = 'total_score' ) then alter table exam add column total_score decimal(8,2) null comment '创建考试时复制的试卷总分快照' after location; end if; if not exists ( select 1 from information_schema.columns where table_schema = database() and table_name = 'exam' and column_name = 'pass_score' ) then alter table exam add column pass_score decimal(8,2) null comment '考试合格分' after total_score; end if; if not exists ( select 1 from information_schema.columns where table_schema = database() and table_name = 'exam' and column_name = 'create_time' ) then alter table exam add column create_time datetime null comment '创建时间' after pass_score; end if; if not exists ( select 1 from information_schema.columns where table_schema = database() and table_name = 'exam' and column_name = 'update_time' ) then alter table exam add column update_time datetime null comment '更新时间' after create_time; end if; -- 班级计划页和班级时间冲突校验均按class_id、exam_time缩小范围。 if not exists ( select 1 from information_schema.statistics where table_schema = database() and table_name = 'exam' and index_name = 'idx_exam_class_time' ) then alter table exam add index idx_exam_class_time (class_id, exam_time, id); end if; -- 全局计划状态与教师时间冲突查询使用以下索引;invigilation_id仍兼容旧版逗号分隔值。 if not exists ( select 1 from information_schema.statistics where table_schema = database() and table_name = 'exam' and index_name = 'idx_exam_time' ) then alter table exam add index idx_exam_time (exam_time, id); end if; if not exists ( select 1 from information_schema.statistics where table_schema = database() and table_name = 'exam' and index_name = 'idx_exam_chief_time' ) then alter table exam add index idx_exam_chief_time (chief_id, exam_time, id); end if; if not exists ( select 1 from information_schema.statistics where table_schema = database() and table_name = 'exam_student' and index_name = 'idx_exam_student_exam_user' ) then alter table exam_student add index idx_exam_student_exam_user (exam_id, student_id); end if; end$$ call migrate_exam_management()$$ drop procedure if exists migrate_exam_management$$ delimiter ; -- 旧考试尽可能从关联试卷回填总分;没有有效试卷的数据保持NULL,避免伪造业务分值。 update exam e left join theoretical_paper paper on paper.id = e.paper_id set e.total_score = coalesce(e.total_score, paper.sum), e.pass_score = coalesce( e.pass_score, round(coalesce(e.total_score, paper.sum) * 0.60, 2) ), e.update_time = coalesce(e.update_time, now()) where e.total_score is null or e.pass_score is null or e.update_time is null; -- 执行后可用该只读查询定位无法从旧试卷关系补齐的考试。 select e.id, e.name, e.paper_id, e.total_score, e.pass_score from exam e where e.total_score is null or e.pass_score is null;