-- 管理员端“教务管理-班级管理”增量数据库脚本(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;