-- 管理员端“系统管理-账户管理”增量数据库脚本(MySQL 5.7+/8.0)。 -- -- 执行顺序:先备份数据库,再执行本文件,最后发布practical-auth和practical-business。 -- 设计说明: -- 1. 脚本通过information_schema逐项判断,可重复执行,不会重复添加字段或索引。 -- 2. del_flag仍由认证服务识别;account_status保存页面业务状态,两者由服务层同步维护。 -- 3. 角色表新增account_type后,角色下拉不再通过中文名称猜测适用范围。 -- 4. 项目现有关系表没有外键,本脚本沿用同一风格,删除/归档采用逻辑状态而非物理删除。 delimiter $$ drop procedure if exists migrate_account_management$$ create procedure migrate_account_management() begin if not exists ( select 1 from information_schema.columns where table_schema=database() and table_name='user' and column_name='create_time' ) then alter table user add column create_time datetime null comment '账户创建时间' after update_time; end if; if not exists ( select 1 from information_schema.columns where table_schema=database() and table_name='user' and column_name='employee_no' ) then alter table user add column employee_no varchar(50) null comment '教师工号' after username; end if; if not exists ( select 1 from information_schema.columns where table_schema=database() and table_name='user' and column_name='account_status' ) then alter table user add column account_status varchar(20) null comment '学员PENDING/STUDYING/GRADUATED/ARCHIVED;教职工ENABLED/DISABLED' after first_login_flag; end if; if not exists ( select 1 from information_schema.columns where table_schema=database() and table_name='user' and column_name='graduation_time' ) then alter table user add column graduation_time datetime null comment '学员结业时间' after account_status; end if; if not exists ( select 1 from information_schema.columns where table_schema=database() and table_name='user' and column_name='archive_time' ) then alter table user add column archive_time datetime null comment '账户归档时间' after graduation_time; end if; if not exists ( select 1 from information_schema.columns where table_schema=database() and table_name='role' and column_name='account_type' ) then alter table role add column account_type varchar(20) null comment '角色适用账户类型:STUDENT/TEACHER/ADMIN' after description; end if; if not exists ( select 1 from information_schema.statistics where table_schema=database() and table_name='user' and index_name='idx_user_account_status' ) then alter table user add index idx_user_account_status (account_status, del_flag); end if; if not exists ( select 1 from information_schema.statistics where table_schema=database() and table_name='user' and index_name='idx_user_phone' ) then alter table user add index idx_user_phone (phone_number); end if; if not exists ( select 1 from information_schema.statistics where table_schema=database() and table_name='user' and index_name='idx_user_username' ) then alter table user add index idx_user_username (username); end if; if not exists ( select 1 from information_schema.statistics where table_schema=database() and table_name='user' and index_name='idx_user_employee_no' ) then alter table user add index idx_user_employee_no (employee_no); end if; end$$ call migrate_account_management()$$ drop procedure if exists migrate_account_management$$ delimiter ; -- 登录成功和失败均保留日志。user_id允许NULL,以便记录不存在账号的尝试。 create table if not exists account_login_log ( id bigint not null auto_increment comment '日志ID', user_id varchar(64) null comment '用户ID;用户不存在时为空', login_name varchar(100) null comment '本次提交的登录账号', login_time datetime not null default current_timestamp comment '登录时间', ip_address varchar(64) null comment '服务端识别的客户端IP', device varchar(255) null comment 'User-Agent/终端描述', result varchar(20) not null comment 'SUCCESS/FAILED', failure_reason varchar(255) null comment '失败原因', primary key (id), key idx_account_login_user_time (user_id, login_time), key idx_account_login_name_time (login_name, login_time) ) engine=InnoDB default charset=utf8mb4 comment='账户登录日志'; -- 管理端所有写操作的审计记录,满足批量结业、归档和重置密码的追溯要求。 create table if not exists account_operation_log ( id bigint not null auto_increment comment '日志ID', operator_id varchar(64) null comment '操作管理员用户ID', operator_name varchar(100) null comment '操作管理员姓名快照', target_user_id varchar(64) null comment '被操作账户ID', action varchar(50) not null comment 'CREATE/UPDATE/CHANGE_STATUS/RESET_PASSWORD', detail varchar(500) null comment '操作摘要', operation_time datetime not null default current_timestamp comment '操作时间', primary key (id), key idx_account_operation_target_time (target_user_id, operation_time), key idx_account_operation_operator_time (operator_id, operation_time) ) engine=InnoDB default charset=utf8mb4 comment='账户管理操作审计日志'; -- 兼容旧数据:优先根据phone_role大类角色推导账户状态,不覆盖已经人工维护的状态。 update user u set u.account_status = case when exists(select 1 from phone_role p where p.phone=u.phone_number and p.role_name='学生') then case when coalesce(u.del_flag,0)=1 then 'ARCHIVED' when coalesce(u.first_login_flag,1)=1 then 'PENDING' else 'STUDYING' end when coalesce(u.del_flag,0)=1 then 'DISABLED' else 'ENABLED' end where u.account_status is null or trim(u.account_status)=''; -- 从现有中文角色名尽力标注适用端;无法可靠识别的角色保持NULL,需按下方检查结果人工确认。 update role set account_type = case when name in ('学生','学员') then 'STUDENT' when name like '%管理员%' or name like '%审计%' then 'ADMIN' when name like '%教师%' or name like '%讲师%' or name like '%教练%' or name like '%考评员%' or name like '%安全员%' then 'TEACHER' else account_type end where account_type is null or trim(account_type)=''; -- 使用已有时间尽力回填创建时间;完全没有历史时间的账户保留NULL,避免伪造创建日期。 update user set create_time=coalesce(create_time, update_time, login_date) where create_time is null; -- 发布前检查:以下结果应由管理员人工补齐role.account_type,否则对应角色不会出现在新增账户下拉框。 select id, name, description, account_type from role where account_type is null or account_type not in ('STUDENT','TEACHER','ADMIN'); -- 发布前检查:重复手机号/账号不会被脚本强行删除,需确认后人工合并;应用层已阻止新增重复值。 select phone_number, count(*) as duplicate_count from user where phone_number is not null and trim(phone_number)<>'' group by phone_number having count(*) > 1; select username, count(*) as duplicate_count from user where username is not null and trim(username)<>'' group by username having count(*) > 1;