mysql 存储过程复制表（备份表）

我会带着你远行 2023-01-16 03:20 247阅读 0赞

有时候测试程序，需要备份表，但只是要备份数据库中的某些表，每个表都要点击一下复制是比较麻烦的，所以写了这样一个复制表的存储过程。

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