初始化清空表便于生产环境

素颜马尾好姑娘i 2022-05-08 01:24 51阅读 0赞

CREATE DEFINER=\`root\`@\`%\` PROCEDURE \`reset\_autoIncrement\`()  
begin

declare v\_table\_name varchar(100); --  自定义变量  
declare done int default false; --  自定义控制游标循环变量,默认false    
declare sql\_for\_select varchar(500);

declare my\_cursor cursor for  
select  table\_name  
    from information\_schema.tables  
    where table\_schema=database()  
        and table\_name not like 'ACT%'  
        and table\_name not like 'QRTZ%'  
        and table\_name not like 'sys%'  
        and table\_name != 'erp\_goods' ;

declare continue handler for not found set done = true; --  绑定控制变量到游标,游标循环结束自动转true  
open my\_cursor; --   打开游标    
   
    myloop: loop --  开始循环体,

fetch my\_cursor into  v\_table\_name; --  将游标当前读取行的数据顺序赋予自定义变量

set sql\_for\_select = concat("truncate table ", v\_table\_name);

\#select sql\_for\_select;     
        set @sql =  sql\_for\_select;  
        prepare stmt from @sql;         --  预处理动态sql语句  
        execute stmt ;                        --  执行sql语句  
          
        \#修改自增id  
        set sql\_for\_select = concat( "alter table ", v\_table\_name," auto\_increment = 1");    
        set @sql =  sql\_for\_select;  
        prepare stmt from @sql;         --  预处理动态sql语句  
        execute stmt ;                        --   执行sql语句

if done then --  判断是否继续循环    
             leave myloop; --  结束循环    
        end if;

end loop myloop; --  结束自定义循环体    
    deallocate prepare stmt;      --  释放prepare

close my\_cursor; --   关闭游标

end