mysql支持批量备份表的存储过程

需求是把多个都有status 字段的表中的数据定期备份，清空原表数据，保留status=initial的数据

CREATE  PROCEDURE `callTableBak`()
BEGIN
DROP TABLE IF EXISTS temp_table_name;
CREATE TEMPORARY TABLE temp_table_name
select 1 as id,'temp_table' as tname union 
select 2 as id,'temp_table_copy1' as tname union 
select 3 as id,'temp_table_copy2' as tname;

SET @param_current_date= DATE_FORMAT(NOW(),'%Y%m%d%H%i%s');
set @maxIndex =(select max(id) from temp_table_name);
set @minIndex =(select min(id) from temp_table_name); 
    WHILE @minIndex <= @maxIndex DO 
         SET @param_current_tname=(select tname from temp_table_name where id=@minIndex);          

set @param_current_bak_tname=concat(@param_current_tname,@param_current_date,"bak");
         SET @sql_content=CONCAT("DROP table IF EXISTS ",@param_current_bak_tname,";
 create table ",@param_current_bak_tname,"
 select * from ",@param_current_tname,"; 
 truncate table ",@param_current_tname,"; 
 insert into ",@param_current_tname,"
 select * from  ",@param_current_bak_tname,"  where status='initial';");
         select @sql_content;
         call execute_batch_sql(@sql_content); 
         SET @minIndex = @minIndex+ 1; 
             
    END WHILE; 
     
end;

其中用到了我写的令一个脚本，批量执行sql语句

支持批量执行的mysql存储过程 - 圣叹&北京 - 博客园 (cnblogs.com)

执行再次的结果：

 

如果数据量比较大，并且涉及表没有外键的话，可以采用重命名法进行备份

CREATE  PROCEDURE `callTableBak`()
BEGIN
DROP TABLE IF EXISTS temp_table_name;
CREATE TEMPORARY TABLE temp_table_name
select 1 as id,'temp_table1' as tname union
select 2 as id,'temp_table2' as tname  ; 

SET @param_current_date= DATE_FORMAT(NOW(),'%Y%m%d');
set @maxIndex =(select max(id) from temp_table_name);
set @minIndex =(select min(id) from temp_table_name); 
    WHILE @minIndex <= @maxIndex DO 
         SET @param_current_tname=(select tname from temp_table_name where id=@minIndex);          

set @param_current_bak_tname=concat("bak_",@param_current_tname,@param_current_date);
         SET @sql_content=CONCAT("DROP table IF EXISTS ",@param_current_bak_tname,";
 alter table ",@param_current_tname," rename to ",@param_current_bak_tname,"; 
 CREATE TABLE ",@param_current_tname," LIKE ",@param_current_bak_tname,"; 
 insert into ",@param_current_tname,"
 select * from  ",@param_current_bak_tname,"  where status='initial';");
         select @sql_content;
         call execute_batch_sql(@sql_content); 
         SET @minIndex = @minIndex+1 ; 
             
    END WHILE; 
     
end;