-- 教师端“教学管理-教案管理”表结构(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', 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) ) 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='教案附件关联';