-- 学员基本信息与认证管理增量脚本(MySQL 5.7+/8.0)。 -- 仅操作当前连接的database(),不包含USE语句或固定数据库名,可安全用于各套环境。 -- 所有列和索引均先查询information_schema,脚本可重复执行。 delimiter $$ drop procedure if exists migrate_student_certification$$ create procedure migrate_student_certification() begin if not exists (select 1 from information_schema.columns where table_schema=database() and table_name='user' and column_name='household_type') then alter table user add column household_type varchar(32) null comment '户口性质'; end if; if not exists (select 1 from information_schema.columns where table_schema=database() and table_name='user' and column_name='administrative_district') then alter table user add column administrative_district varchar(64) null comment '行政区'; end if; if not exists (select 1 from information_schema.columns where table_schema=database() and table_name='user' and column_name='person_types') then alter table user add column person_types text null comment '人员类别JSON数组'; end if; if not exists (select 1 from information_schema.columns where table_schema=database() and table_name='user' and column_name='person_type_other') then alter table user add column person_type_other varchar(100) null comment '其他人员类别说明'; end if; if not exists (select 1 from information_schema.columns where table_schema=database() and table_name='user' and column_name='unit_phone') then alter table user add column unit_phone varchar(32) null comment '单位联系电话'; end if; if not exists (select 1 from information_schema.columns where table_schema=database() and table_name='user' and column_name='labor_book_no') then alter table user add column labor_book_no varchar(64) null comment '劳动手册编号'; end if; if not exists (select 1 from information_schema.columns where table_schema=database() and table_name='user' and column_name='current_work_year') then alter table user add column current_work_year int null comment '本工种工作年限'; end if; if not exists (select 1 from information_schema.columns where table_schema=database() and table_name='user' and column_name='previous_level_cert_no') then alter table user add column previous_level_cert_no varchar(100) null comment '上一等级职业资格证书编号'; end if; if not exists (select 1 from information_schema.columns where table_schema=database() and table_name='user' and column_name='graduation_school') then alter table user add column graduation_school varchar(128) null comment '毕业院校'; end if; if not exists (select 1 from information_schema.columns where table_schema=database() and table_name='user' and column_name='graduation_date') then alter table user add column graduation_date varchar(10) null comment '毕业日期yyyy-MM-dd'; end if; if not exists (select 1 from information_schema.columns where table_schema=database() and table_name='user' and column_name='graduation_cert_no') then alter table user add column graduation_cert_no varchar(100) null comment '毕业证书编号'; end if; if not exists (select 1 from information_schema.columns where table_schema=database() and table_name='user' and column_name='training_occupation') then alter table user add column training_occupation varchar(64) null comment '培训职业工种'; end if; if not exists (select 1 from information_schema.columns where table_schema=database() and table_name='user' and column_name='training_level') then alter table user add column training_level varchar(32) null comment '培训等级'; end if; if not exists (select 1 from information_schema.columns where table_schema=database() and table_name='user' and column_name='training_category') then alter table user add column training_category varchar(32) null comment '培训类别单选值'; end if; if not exists (select 1 from information_schema.columns where table_schema=database() and table_name='user' and column_name='subsidy_flag') then alter table user add column subsidy_flag tinyint(1) null comment '是否申请培训补贴'; end if; if not exists (select 1 from information_schema.columns where table_schema=database() and table_name='user' and column_name='assessment_occupation') then alter table user add column assessment_occupation varchar(64) null comment '鉴定职业工种'; end if; if not exists (select 1 from information_schema.columns where table_schema=database() and table_name='user' and column_name='assessment_level') then alter table user add column assessment_level varchar(32) null comment '鉴定等级'; end if; if not exists (select 1 from information_schema.columns where table_schema=database() and table_name='user' and column_name='student_profile_completed') then -- 默认0且不回填历史数据,旧学员必须完成一次正式提交后才进入认证管理列表。 alter table user add column student_profile_completed tinyint(1) not null default 0 comment '认证报名资料是否已完善'; end if; if not exists (select 1 from information_schema.columns where table_schema=database() and table_name='user' and column_name='student_profile_submit_time') then alter table user add column student_profile_submit_time datetime null comment '认证报名资料最近提交时间'; end if; if not exists (select 1 from information_schema.statistics where table_schema=database() and table_name='user' and index_name='idx_user_profile_completed') then alter table user add index idx_user_profile_completed (student_profile_completed, del_flag, student_profile_submit_time); end if; end$$ call migrate_student_certification()$$ drop procedure if exists migrate_student_certification$$ delimiter ;