CREATE DEFINER=`admin`@`%` 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();
declare continue handler for not found set done = true; -- 绑定控制变量到游标,游标循环结束自动转true
open my_cursor; -- 打开游标
myloop: loop -- 开始循环体,
fetch my_cursor into v_table_name; -- 将游标当前读取行的数据顺序赋予自定义变量
if done then -- 判断是否继续循环 注意：不要放在后面，直接就在fetch后面判断
leave myloop; -- 结束循环
end if;
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语句
end loop myloop; -- 结束自定义循环体
deallocate prepare stmt; -- 释放prepare
close my_cursor; -- 关闭游标
end
每次拿到不同数据库下执行就可以清除数据保留表结构了