操作环境:
数据库:MySQL 5.7.20
连接工具:Navicat Premium 12.0.24
单表修改 (当前表)
表名:t_user , 字段名:create_time 和update_time ,替换后直接执行下方语句
alter table t_user modify column create_time timestamp not null default current_timestamp;
alter table t_user modify column update_time timestamp not null default current_timestamp on update current_timestamp;
多表修改(当前库中的所有表)
1、创建存储过程(循环体中:test为数据库名称,create_time和update_time是需替换的字段名称)
delimiter $$ -- mysql的名令结束符默认为(;)号,重定义结束符$$
create procedure proc_while() -- 创建存储过程,名字为proc_while
begin
declare s_tablename varchar(100); -- 定义变量名=s_tablename
declare cur_table_structure cursor -- 定义游标名 = cur_table_structure
for
select table_name from information_schema.tables where table_schema = 'test'; -- 查询指定数据库的表名
-- 在fetch语句中引用的游标位置处于结果表最后一行之后会发生02000异常,捕获后将变量值设置为null
declare continue handler for sqlstate '02000' SET s_tablename = null;
open cur_table_structure; -- 开启游标
fetch cur_table_structure into s_tablename; -- 右边指向的值赋值给变量
while ( s_tablename is not null) do -- 判断变量不为空,执行循环
set @createTimeSql = concat("alter table `",s_tablename,
"` modify column create_time timestamp not null default current_timestamp");
set @updateTimeSql = concat("alter table `",s_tablename,
"` modify column update_time timestamp not null default current_timestamp on update current_timestamp");
prepare sq1 from @createTimeSql; -- prepare预处理
prepare sq2 from @updateTimeSql;
execute sq1; -- 执行sql
execute sq2;
fetch cur_table_structure into s_tablename;
end while; -- 结束循环
close cur_table_structure; -- 关闭游标
end$$
delimiter; -- 将命令结束符重新设定为(;)号
2、执行存储过程
call proc_while()
3、删除存储过程
drop procedure proc_while
4、注意:
1、执行多表修改的时候,需保证每一张表中都有相同的创建时间(create_time )和修改时间(update_time )字段
2、执行存储过程前,请先修改数据库名称和对应字段名称,否则执行可能报错。
参考文章:mysql批量操作所有表用游标方式循环