-- 学员评议“撤回后重新发起”增量脚本(MySQL 5.7+/8.0)。 -- -- 原唯一索引把已撤回历史也视为重复课次。本脚本改用生成列:未撤回任务映射为subject_id, -- 已撤回任务映射为NULL。MySQL唯一索引允许多个NULL,因此既能保留撤回历史,也能继续依靠 -- 数据库唯一约束拦截两个管理员并发发起同一课次。脚本可重复执行。 delimiter $$ drop procedure if exists migrate_evaluation_reissue$$ create procedure migrate_evaluation_reissue() begin if not exists ( select 1 from information_schema.tables where table_schema = database() and table_name = 'evaluation_task' ) then signal sqlstate '45000' set message_text = '缺少evaluation_task表,请先执行evaluation.sql'; end if; if not exists ( select 1 from information_schema.columns where table_schema = database() and table_name = 'evaluation_task' and column_name = 'active_subject_id' ) then alter table evaluation_task add column active_subject_id int generated always as ( case when status = 'REVOKED' then null else subject_id end ) stored comment '未撤回任务的课次ID,用于并发防重;撤回后为NULL' after status; end if; -- 先补普通索引再删除旧唯一索引,确保subject_id外键始终有可用的前导索引。 if not exists ( select 1 from information_schema.statistics where table_schema = database() and table_name = 'evaluation_task' and index_name = 'idx_evaluation_task_subject' ) then alter table evaluation_task add index idx_evaluation_task_subject (subject_id); end if; if exists ( select 1 from information_schema.statistics where table_schema = database() and table_name = 'evaluation_task' and index_name = 'uk_evaluation_task_subject' ) then alter table evaluation_task drop index uk_evaluation_task_subject; end if; if not exists ( select 1 from information_schema.statistics where table_schema = database() and table_name = 'evaluation_task' and index_name = 'uk_evaluation_task_active_subject' ) then alter table evaluation_task add unique index uk_evaluation_task_active_subject (active_subject_id); end if; end$$ call migrate_evaluation_reissue()$$ drop procedure if exists migrate_evaluation_reissue$$ delimiter ; -- 部署后核验:结果应为0;查询只读,不改变业务数据。 select count(*) as duplicate_non_revoked_subject_count from ( select subject_id from evaluation_task where status != 'REVOKED' group by subject_id having count(*) > 1 ) duplicate_subjects;