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