-- 教师端“教学管理-教案管理”表结构(MySQL 5.7+/8.0)。 -- -- 脚本使用 IF NOT EXISTS,可在不同环境重复执行。项目采用逻辑删除,因此不配置级联外键; -- 业务服务会在同一事务内维护教案、附件关系和 bs_file 文件元数据的一致性。 CREATE TABLE IF NOT EXISTS teaching_plan ( id VARCHAR(32) NOT NULL COMMENT '教案ID', teacher_id VARCHAR(64) NOT NULL COMMENT '所属教师用户ID', title VARCHAR(200) NOT NULL COMMENT '教案标题', course_name VARCHAR(200) NOT NULL COMMENT '手工填写的课程名称', teaching_date DATE NOT NULL COMMENT '授课日期', class_hours INT NOT NULL COMMENT '正整数课时', teaching_objectives MEDIUMTEXT NOT NULL COMMENT '教学目标', teaching_content MEDIUMTEXT NOT NULL COMMENT '教学内容', key_points MEDIUMTEXT NOT NULL COMMENT '教学重点', difficult_points MEDIUMTEXT NOT NULL COMMENT '教学难点', teaching_methods MEDIUMTEXT NOT NULL COMMENT '教学方法', teaching_process MEDIUMTEXT NOT NULL COMMENT '教学过程', after_class_summary MEDIUMTEXT NULL COMMENT '课后总结', status VARCHAR(16) NOT NULL DEFAULT 'DRAFT' COMMENT 'DRAFT/PENDING/APPROVED/REJECTED', reviewer_id VARCHAR(64) NULL COMMENT '最近一次管理端保存的管理员用户ID', reviewer_name VARCHAR(100) NULL COMMENT '最近一次审核人姓名快照', review_opinion VARCHAR(2000) NULL COMMENT '最近一次审核意见', review_time DATETIME NULL COMMENT '最近一次管理端保存或审核时间', del_flag TINYINT NOT NULL DEFAULT 0 COMMENT '逻辑删除:0正常,1删除', create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间', PRIMARY KEY (id), KEY idx_teaching_plan_teacher_status (teacher_id, del_flag, status), KEY idx_teaching_plan_teacher_date (teacher_id, del_flag, teaching_date), KEY idx_teaching_plan_admin_filter (del_flag, status, teaching_date, teacher_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='教师教案'; CREATE TABLE IF NOT EXISTS teaching_plan_attachment ( id VARCHAR(32) NOT NULL COMMENT '附件关联ID', plan_id VARCHAR(32) NOT NULL COMMENT '教案ID', file_id INT NOT NULL COMMENT 'bs_file文件ID', file_size BIGINT NOT NULL COMMENT '文件大小(字节)', del_flag TINYINT NOT NULL DEFAULT 0 COMMENT '逻辑删除:0正常,1删除', create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间', PRIMARY KEY (id), UNIQUE KEY uk_teaching_plan_file (plan_id, file_id), KEY idx_teaching_plan_attachment_plan (plan_id, del_flag), KEY idx_teaching_plan_attachment_file (file_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='教案附件关联'; -- -- 管理端审核字段增量迁移。 -- teaching_plan 可能已由旧版本脚本创建,因此不能只依赖上面的 CREATE TABLE IF NOT EXISTS; -- 下面逐项检查字段和索引,允许本脚本在开发、测试和生产环境安全重复执行。 -- 旧的已通过/已驳回记录没有可信审核人来源,迁移时有意保持审核字段为 NULL。 -- DELIMITER $$ DROP PROCEDURE IF EXISTS migrate_teaching_plan_admin$$ CREATE PROCEDURE migrate_teaching_plan_admin() BEGIN IF NOT EXISTS ( SELECT 1 FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'teaching_plan' AND column_name = 'reviewer_id' ) THEN ALTER TABLE teaching_plan ADD COLUMN reviewer_id VARCHAR(64) NULL COMMENT '最近一次管理端保存的管理员用户ID' AFTER status; END IF; IF NOT EXISTS ( SELECT 1 FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'teaching_plan' AND column_name = 'reviewer_name' ) THEN ALTER TABLE teaching_plan ADD COLUMN reviewer_name VARCHAR(100) NULL COMMENT '最近一次审核人姓名快照' AFTER reviewer_id; END IF; IF NOT EXISTS ( SELECT 1 FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'teaching_plan' AND column_name = 'review_opinion' ) THEN ALTER TABLE teaching_plan ADD COLUMN review_opinion VARCHAR(2000) NULL COMMENT '最近一次审核意见' AFTER reviewer_name; END IF; IF NOT EXISTS ( SELECT 1 FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'teaching_plan' AND column_name = 'review_time' ) THEN ALTER TABLE teaching_plan ADD COLUMN review_time DATETIME NULL COMMENT '最近一次管理端保存或审核时间' AFTER review_opinion; END IF; IF NOT EXISTS ( SELECT 1 FROM information_schema.statistics WHERE table_schema = DATABASE() AND table_name = 'teaching_plan' AND index_name = 'idx_teaching_plan_admin_filter' ) THEN ALTER TABLE teaching_plan ADD INDEX idx_teaching_plan_admin_filter (del_flag, status, teaching_date, teacher_id); END IF; END$$ CALL migrate_teaching_plan_admin()$$ DROP PROCEDURE IF EXISTS migrate_teaching_plan_admin$$ DELIMITER ;