有时候测试程序,需要备份表,但只是要备份数据库中的某些表,每个表都要点击一下复制是比较麻烦的,所以写了这样一个复制表的存储过程。
CREATE DEFINER=`root`@`%` PROCEDURE `copyTable`()
BEGIN
-- 先创建临时表,用于存储要复制的表,因为mysql没有数组的定义
DROP TABLE IF EXISTS temp_table ;
create TEMPORARY table temp_table(
tName varchar(127) comment '需备份的表名'
)
;
-- 临时表插入数据,要备份的表名
insert into temp_table(tName) values
("sys_position"),
("sys_department"),
("sys_org_position")
;
-- 因为变量必须是在开头,所以嵌套多一层begin...end
BEGIN
-- 定义标志变量s, 用于循环结束
DECLARE s int DEFAULT 0;
-- 定义变量,用于查询结果集的赋值
DECLARE tname varchar(64);
-- 定义游标,并将sql结果集赋值到游标中
DECLARE t_index CURSOR FOR SELECT temp_table.tName FROM temp_table;
-- 声明当游标遍历完后将标志变量置成某个值
DECLARE CONTINUE HANDLER FOR NOT FOUND SET s=1;
-- 打开游标
OPEN t_index;
-- 将游标中的值赋值给变量,注意:变量名不要和返回的列名同名,变量顺序要和sql结果列的顺序一致
fetch t_index into tname;
-- 当s不等于1,也就是未遍历完时,会一直循环
while s<>1 do
-- 执行业务逻辑
-- 定义变量,复制表的后缀
set @sufixed = 20210426;
-- 定义变量,要复制的表名
set @tn = tname;
-- 定义变量,组装创建表的sql语句
set @ct := concat('create table ', tname, '_',@sufixed,' like ', tname);
-- 定义变量,插入数据的sql语句
set @it := concat('insert into ', tname, '_', @sufixed, ' select * from ', tname);
-- 预处理sql,并执行创建表的sql
PREPARE csql FROM @ct;
EXECUTE csql;
-- 预处理sql,并执行插入数据的sql
PREPARE isql FROM @it;
EXECUTE isql;
-- 将游标中的值再赋值给变量,供下次循环使用
fetch t_index into tname;
-- 当s等于1时表明遍历以完成,退出循环
end while;
-- 关闭游标
CLOSE t_index;
END;
-- 删除临时表
DROP TABLE IF EXISTS temp_table ;
END