-- 管理员端“教务管理-班级管理”增量数据库脚本(MySQL 5.7+/8.0)。 -- -- 设计说明: -- 1. 项目已经存在class表,因此使用information_schema逐项判断列和索引,脚本可重复执行。 -- 2. 新字段暂时允许NULL,以便旧班级在无法可靠推断工种或培训日期时继续正常查询; -- 新增和编辑接口会在应用层强制校验完整字段。 -- 3. 项目现有业务表普遍不配置外键,本脚本保持相同风格,由服务层校验班主任、等级和关系数据。 delimiter $$ drop procedure if exists migrate_class_management$$ create procedure migrate_class_management() begin if not exists ( select 1 from information_schema.columns where table_schema = database() and table_name = 'class' and column_name = 'occupation' ) then alter table `class` add column occupation varchar(32) null comment '工种:污水/污泥' after level; end if; if not exists ( select 1 from information_schema.columns where table_schema = database() and table_name = 'class' and column_name = 'training_year' ) then alter table `class` add column training_year smallint null comment '培训年度' after occupation; end if; if not exists ( select 1 from information_schema.columns where table_schema = database() and table_name = 'class' and column_name = 'start_date' ) then alter table `class` add column start_date date null comment '培训开始日期' after training_year; end if; if not exists ( select 1 from information_schema.columns where table_schema = database() and table_name = 'class' and column_name = 'end_date' ) then alter table `class` add column end_date date null comment '培训结束日期' after start_date; end if; if not exists ( select 1 from information_schema.columns where table_schema = database() and table_name = 'class' and column_name = 'head_teacher_id' ) then alter table `class` add column head_teacher_id varchar(64) null comment '班主任用户ID' after end_date; end if; if not exists ( select 1 from information_schema.columns where table_schema = database() and table_name = 'class' and column_name = 'location' ) then alter table `class` add column location varchar(255) null comment '培训地点' after head_teacher_id; end if; if not exists ( select 1 from information_schema.columns where table_schema = database() and table_name = 'class' and column_name = 'capacity' ) then alter table `class` add column capacity int null comment '计划人数,管理端限制1到200' after location; end if; if not exists ( select 1 from information_schema.columns where table_schema = database() and table_name = 'class' and column_name = 'remark' ) then alter table `class` add column remark varchar(1000) null comment '班级备注' after capacity; end if; if not exists ( select 1 from information_schema.columns where table_schema = database() and table_name = 'class' and column_name = 'update_time' ) then alter table `class` add column update_time datetime null comment '更新时间' after create_time; end if; if not exists ( select 1 from information_schema.statistics where table_schema = database() and table_name = 'class' and index_name = 'idx_class_group' ) then alter table `class` add index idx_class_group (occupation, training_year, level); end if; if not exists ( select 1 from information_schema.statistics where table_schema = database() and table_name = 'class' and index_name = 'idx_class_year_name' ) then alter table `class` add index idx_class_year_name (training_year, name); end if; if not exists ( select 1 from information_schema.statistics where table_schema = database() and table_name = 'class' and index_name = 'idx_class_head_teacher' ) then alter table `class` add index idx_class_head_teacher (head_teacher_id); end if; end$$ call migrate_class_management()$$ drop procedure if exists migrate_class_management$$ delimiter ; -- 从既有等级、排课和学员关系中尽力回填旧班级。无法可靠推断的值保持NULL,避免写入伪造数据。 update `class` c left join sys_level level_info on cast(level_info.id as char) = c.level left join ( select class_id, min(date) as first_date, max(date) as last_date from class_subject group by class_id ) schedule on schedule.class_id = c.id left join ( select class_id, count(*) as actual_count from class_student group by class_id ) members on members.class_id = c.id set c.occupation = coalesce( nullif(trim(c.occupation), ''), case when level_info.name like '污水%' then '污水' when level_info.name like '污泥%' then '污泥' else null end), c.start_date = coalesce(c.start_date, schedule.first_date), c.end_date = coalesce(c.end_date, schedule.last_date), c.training_year = coalesce( c.training_year, year(schedule.first_date), year(c.create_time)), c.capacity = coalesce( c.capacity, case when members.actual_count > 0 then members.actual_count else null end); -- 执行后可用以下查询定位仍需人工补齐的旧班级。查询只读,不会阻断迁移。 select id, name, occupation, training_year, start_date, end_date, capacity from `class` where occupation is null or training_year is null or start_date is null or end_date is null; -- 学员移除/转班后,class_student只保留当前有效关系;本表保存不可变的历史轨迹。 -- 不设置外键,保持与项目现有业务表一致,由班级学员服务在同一事务内保证引用有效。 create table if not exists class_student_change_log ( id bigint not null auto_increment comment '主键', user_id varchar(64) not null comment '学员用户ID', source_class_id int not null comment '原班级ID', target_class_id int null comment '目标班级ID,移除操作为空', operation_type varchar(16) not null comment '操作类型:REMOVE/TRANSFER', operator_id varchar(64) null comment '管理员用户ID', operation_time datetime not null comment '操作时间', primary key (id), index idx_class_student_change_user (user_id, operation_time), index idx_class_student_change_source (source_class_id, operation_time), index idx_class_student_change_target (target_class_id, operation_time) ) comment='班级学员关系变更记录'; -- 班级维度的结业记录用于保留批次、说明和电子证书,不复用职业资格证书字段。 -- 唯一键阻止同一学员在同一班级因重复点击产生两条结业记录。 create table if not exists student_graduation_record ( id bigint not null auto_increment comment '主键', class_id int not null comment '结业班级ID', user_id varchar(64) not null comment '学员用户ID', graduation_date date not null comment '结业日期', batch_no varchar(100) null comment '结业批次', remark varchar(1000) null comment '结业说明', certificate_issued tinyint(1) not null default 0 comment '是否发放电子结业证书', certificate_no varchar(64) null comment '电子结业证书编号', notification_requested tinyint(1) not null default 0 comment '是否请求发送站内通知', operator_id varchar(64) null comment '管理员用户ID', create_time datetime not null comment '创建时间', primary key (id), unique key uk_student_graduation_class_user (class_id, user_id), unique key uk_student_graduation_certificate (certificate_no), index idx_student_graduation_user (user_id, graduation_date) ) comment='学员结业办理记录';