-- 学员端、教师端、管理员端首页增量数据库脚本(MySQL 5.7+/8.0)。 -- -- 执行顺序: -- 1. 先执行既有 account_management.sql、class_management.sql、exam_management.sql、 -- teaching_plan.sql、theoretical_practice_statistics.sql,并确认请假、实训室基础表已部署; -- 2. 再执行本脚本; -- 3. 最后发布 practical-business。 -- -- 设计说明: -- 1. 只增加首页真实聚合所需的最小字段、设备维护表和查询索引,不删除或覆盖业务数据。 -- 2. 项目现有业务表不统一建立外键,本脚本沿用该约定,由业务服务保证ID归属。 -- 3. 所有ALTER操作均先检查information_schema,脚本可以重复执行。 create table if not exists training_room_equipment ( id varchar(64) not null comment '设备ID(雪花算法字符串)', room_id varchar(64) not null comment '所属实训室ID', equipment_code varchar(64) not null comment '设备编号,同一实训室内唯一', name varchar(100) not null comment '设备名称', status varchar(20) not null default 'RUNNING' comment 'RUNNING运行中/FAULT故障/MAINTENANCE维护中/DISABLED停用', last_maintenance_date date null comment '上次维护日期', next_maintenance_date date null comment '下次计划维护日期', del_flag tinyint not null default 0 comment '逻辑删除:0正常/1删除', create_time datetime null comment '创建时间', update_time datetime null comment '最后更新时间', primary key (id), unique key uk_training_room_equipment_code (room_id, equipment_code), key idx_training_room_equipment_due (del_flag, status, next_maintenance_date), key idx_training_room_equipment_room (room_id, del_flag) ) engine=InnoDB default charset=utf8mb4 comment='实训室设备及维护日期'; delimiter $$ drop procedure if exists migrate_home_dashboard$$ create procedure migrate_home_dashboard() begin -- 班级归档与结课是两个不同状态;首页只统计未归档班级。 if not exists ( select 1 from information_schema.columns where table_schema = database() and table_name = 'class' and column_name = 'archived_flag' ) then alter table `class` add column archived_flag tinyint not null default 0 comment '是否归档:0未归档/1已归档' after remark; end if; if not exists ( select 1 from information_schema.statistics where table_schema = database() and table_name = 'class' and index_name = 'idx_class_dashboard_status' ) then alter table `class` add index idx_class_dashboard_status (archived_flag, start_date, end_date, id); end if; -- 学员首页从student_id反查个人考试,原exam_id前导索引无法覆盖该查询。 if not exists ( select 1 from information_schema.statistics where table_schema = database() and table_name = 'exam_student' and index_name = 'idx_exam_student_user_status' ) then alter table exam_student add index idx_exam_student_user_status (student_id, submit, mark_flag, exam_id); end if; -- 教师首页高频按教师和课程日期读取排课。 if not exists ( select 1 from information_schema.statistics where table_schema = database() and table_name = 'class_subject' and index_name = 'idx_class_subject_teacher_date' ) then alter table class_subject add index idx_class_subject_teacher_date (teacher_id, date, class_id); end if; -- 管理员活跃图按时间范围扫描成功登录,时间必须作为前导列。 if not exists ( select 1 from information_schema.statistics where table_schema = database() and table_name = 'account_login_log' and index_name = 'idx_account_login_time_result_user' ) then alter table account_login_log add index idx_account_login_time_result_user (login_time, result, user_id); end if; -- 原作答索引以practice_id开头,不能支持全平台最近七天活跃统计。 if not exists ( select 1 from information_schema.statistics where table_schema = database() and table_name = 'theoretical_practice_answer_record' and index_name = 'idx_tp_answer_time_student' ) then alter table theoretical_practice_answer_record add index idx_tp_answer_time_student (answered_at, student_id); end if; end$$ call migrate_home_dashboard()$$ drop procedure if exists migrate_home_dashboard$$ delimiter ; -- 历史班级没有归档信息,统一视为未归档;不根据结课日期擅自归档。 update `class` set archived_flag = 0 where archived_flag is null; -- 发布前只读检查:以下设备已到维护日期,会进入管理员首页“设备到期维护”。 select id, room_id, equipment_code, name, status, next_maintenance_date from training_room_equipment where coalesce(del_flag, 0) = 0 and status <> 'DISABLED' and next_maintenance_date is not null and next_maintenance_date <= current_date order by next_maintenance_date asc, id asc;