-- 教师端“教学管理-教案管理”表结构(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(50) NOT NULL COMMENT '教案名称', course_type VARCHAR(16) NOT NULL COMMENT 'theory理论课/practice实训课', applicable_course VARCHAR(200) NOT NULL COMMENT '适用课程', vocational_level VARCHAR(200) NOT NULL COMMENT '职业(等级)', training_institution VARCHAR(200) NOT NULL DEFAULT '实训平台' COMMENT '培训机构', preparation_date DATE NOT NULL COMMENT '编写日期', teaching_date DATE NOT NULL COMMENT '授课日期', chapter_topic VARCHAR(50) NOT NULL COMMENT '章节(课题)', duration_minutes INT NOT NULL COMMENT '课时分钟数,15至480', class_name VARCHAR(200) NOT NULL COMMENT '授课班级', teaching_purpose MEDIUMTEXT NOT NULL COMMENT '教学目的', practice_content MEDIUMTEXT NULL COMMENT '实训内容,仅实训课使用', practice_equipment MEDIUMTEXT NULL COMMENT '实训设备,仅实训课使用', key_points MEDIUMTEXT NULL COMMENT '教学重点', difficult_points MEDIUMTEXT NULL COMMENT '教学难点', teaching_aids MEDIUMTEXT NULL COMMENT '教学辅助手段', teaching_note MEDIUMTEXT NULL COMMENT '教学后记', signature VARCHAR(200) NULL COMMENT '教师签名', status VARCHAR(16) NOT NULL DEFAULT 'DRAFT' COMMENT 'DRAFT/PENDING/APPROVED/REJECTED', submitted_time DATETIME NULL COMMENT '最近一次教师提交时间', 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_preparation (teacher_id, del_flag, preparation_date), KEY idx_teaching_plan_admin_filter_v2 (del_flag, status, course_type, class_name, teacher_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='教师教案'; -- 教学环节使用独立子表保存,sequence_no 对应编辑器数组顺序。 CREATE TABLE IF NOT EXISTS teaching_plan_step ( id VARCHAR(32) NOT NULL COMMENT '教学环节ID', plan_id VARCHAR(32) NOT NULL COMMENT '所属教案ID', stage_name VARCHAR(200) NOT NULL COMMENT '教学环节名称', teaching_content MEDIUMTEXT NULL COMMENT '该环节教学内容', teaching_method VARCHAR(200) NULL COMMENT '该环节教学方法', duration_minutes INT NULL COMMENT '该环节时长(分钟)', sequence_no INT NOT NULL COMMENT '显示顺序,从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_step_sequence (plan_id, sequence_no), KEY idx_teaching_plan_step_plan (plan_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='教案附件关联'; -- -- HTML 原型字段及管理端审核字段的增量迁移。 -- teaching_plan 可能已由旧版本脚本创建,因此不能只依赖上面的 CREATE TABLE IF NOT EXISTS; -- 新字段先按可空方式加入并回填旧数据,最后再收紧原型必填字段,整个过程可重复执行。 -- DELIMITER $$ DROP PROCEDURE IF EXISTS migrate_teaching_plan_v2$$ CREATE PROCEDURE migrate_teaching_plan_v2() BEGIN IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'teaching_plan' AND column_name = 'course_type') THEN ALTER TABLE teaching_plan ADD COLUMN course_type VARCHAR(16) NULL COMMENT 'theory理论课/practice实训课' AFTER title; END IF; IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'teaching_plan' AND column_name = 'applicable_course') THEN ALTER TABLE teaching_plan ADD COLUMN applicable_course VARCHAR(200) NULL COMMENT '适用课程' AFTER course_type; END IF; IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'teaching_plan' AND column_name = 'vocational_level') THEN ALTER TABLE teaching_plan ADD COLUMN vocational_level VARCHAR(200) NULL COMMENT '职业(等级)' AFTER applicable_course; END IF; IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'teaching_plan' AND column_name = 'training_institution') THEN ALTER TABLE teaching_plan ADD COLUMN training_institution VARCHAR(200) NULL COMMENT '培训机构' AFTER vocational_level; END IF; IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'teaching_plan' AND column_name = 'preparation_date') THEN ALTER TABLE teaching_plan ADD COLUMN preparation_date DATE NULL COMMENT '编写日期' AFTER training_institution; END IF; IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'teaching_plan' AND column_name = 'chapter_topic') THEN ALTER TABLE teaching_plan ADD COLUMN chapter_topic VARCHAR(50) NULL COMMENT '章节(课题)' AFTER teaching_date; END IF; IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'teaching_plan' AND column_name = 'duration_minutes') THEN ALTER TABLE teaching_plan ADD COLUMN duration_minutes INT NULL COMMENT '课时分钟数,15至480' AFTER chapter_topic; END IF; IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'teaching_plan' AND column_name = 'class_name') THEN ALTER TABLE teaching_plan ADD COLUMN class_name VARCHAR(200) NULL COMMENT '授课班级' AFTER duration_minutes; END IF; IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'teaching_plan' AND column_name = 'teaching_purpose') THEN ALTER TABLE teaching_plan ADD COLUMN teaching_purpose MEDIUMTEXT NULL COMMENT '教学目的' AFTER class_name; END IF; IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'teaching_plan' AND column_name = 'practice_content') THEN ALTER TABLE teaching_plan ADD COLUMN practice_content MEDIUMTEXT NULL COMMENT '实训内容,仅实训课使用' AFTER teaching_purpose; END IF; IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'teaching_plan' AND column_name = 'practice_equipment') THEN ALTER TABLE teaching_plan ADD COLUMN practice_equipment MEDIUMTEXT NULL COMMENT '实训设备,仅实训课使用' AFTER practice_content; END IF; IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'teaching_plan' AND column_name = 'teaching_aids') THEN ALTER TABLE teaching_plan ADD COLUMN teaching_aids MEDIUMTEXT NULL COMMENT '教学辅助手段' AFTER difficult_points; END IF; IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'teaching_plan' AND column_name = 'teaching_note') THEN ALTER TABLE teaching_plan ADD COLUMN teaching_note MEDIUMTEXT NULL COMMENT '教学后记' AFTER teaching_aids; END IF; IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'teaching_plan' AND column_name = 'signature') THEN ALTER TABLE teaching_plan ADD COLUMN signature VARCHAR(200) NULL COMMENT '教师签名' AFTER teaching_note; END IF; IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'teaching_plan' AND column_name = 'submitted_time') THEN ALTER TABLE teaching_plan ADD COLUMN submitted_time DATETIME NULL COMMENT '最近一次教师提交时间' 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_id' ) THEN ALTER TABLE teaching_plan ADD COLUMN reviewer_id VARCHAR(64) NULL COMMENT '最近一次管理端保存的管理员用户ID' AFTER submitted_time; 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 EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'teaching_plan' AND column_name = 'course_name') THEN SET @teaching_plan_backfill = 'UPDATE teaching_plan SET applicable_course = COALESCE(NULLIF(applicable_course, ''''), course_name), duration_minutes = COALESCE(duration_minutes, LEAST(480, GREATEST(15, class_hours * 45))), teaching_purpose = COALESCE(NULLIF(teaching_purpose, ''''), teaching_objectives), teaching_aids = COALESCE(NULLIF(teaching_aids, ''''), teaching_methods), teaching_note = COALESCE(NULLIF(teaching_note, ''''), after_class_summary)'; PREPARE teaching_plan_stmt FROM @teaching_plan_backfill; EXECUTE teaching_plan_stmt; DEALLOCATE PREPARE teaching_plan_stmt; -- 旧字段保留为兼容列,但解除非空约束,避免新版写入必须继续维护废弃字段。 ALTER TABLE teaching_plan MODIFY COLUMN course_name VARCHAR(200) NULL, MODIFY COLUMN class_hours INT NULL, MODIFY COLUMN teaching_objectives MEDIUMTEXT NULL, MODIFY COLUMN teaching_content MEDIUMTEXT NULL, MODIFY COLUMN teaching_methods MEDIUMTEXT NULL, MODIFY COLUMN teaching_process MEDIUMTEXT NULL; END IF; UPDATE teaching_plan SET course_type = COALESCE(NULLIF(course_type, ''), 'theory'), applicable_course = COALESCE(NULLIF(applicable_course, ''), '未设置'), vocational_level = COALESCE(NULLIF(vocational_level, ''), '未设置'), training_institution = COALESCE(NULLIF(training_institution, ''), '实训平台'), preparation_date = COALESCE(preparation_date, DATE(create_time), teaching_date), chapter_topic = COALESCE(NULLIF(chapter_topic, ''), LEFT(title, 50)), duration_minutes = COALESCE(duration_minutes, 90), class_name = COALESCE(NULLIF(class_name, ''), '未设置'), teaching_purpose = COALESCE(NULLIF(teaching_purpose, ''), '待补充'); ALTER TABLE teaching_plan MODIFY COLUMN course_type VARCHAR(16) NOT NULL, MODIFY COLUMN applicable_course VARCHAR(200) NOT NULL, MODIFY COLUMN vocational_level VARCHAR(200) NOT NULL, MODIFY COLUMN training_institution VARCHAR(200) NOT NULL DEFAULT '实训平台', MODIFY COLUMN preparation_date DATE NOT NULL, MODIFY COLUMN chapter_topic VARCHAR(50) NOT NULL, MODIFY COLUMN duration_minutes INT NOT NULL, MODIFY COLUMN class_name VARCHAR(200) NOT NULL, MODIFY COLUMN teaching_purpose MEDIUMTEXT NOT NULL; IF NOT EXISTS (SELECT 1 FROM information_schema.statistics WHERE table_schema = DATABASE() AND table_name = 'teaching_plan' AND index_name = 'idx_teaching_plan_teacher_preparation') THEN ALTER TABLE teaching_plan ADD INDEX idx_teaching_plan_teacher_preparation (teacher_id, del_flag, preparation_date); 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_v2') THEN ALTER TABLE teaching_plan ADD INDEX idx_teaching_plan_admin_filter_v2 (del_flag, status, course_type, class_name, teacher_id); END IF; END$$ CALL migrate_teaching_plan_v2()$$ DROP PROCEDURE IF EXISTS migrate_teaching_plan_v2$$ DELIMITER ;