MySql使用存储过程清除数据库所有表数据,保存数据结构
时间:2022-03-16 11:27
BEGIN
DECLARE strClear VARCHAR(256);
DECLARE done INT DEFAULT 0;
#定义游标
DECLARE curOne CURSOR FOR select concat(‘truncate table ‘,table_name,‘;‘) from information_schema.TABLES where table_schema=‘test‘;
#绑定控制变量到游标,游标循环结束自动转为True
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
#打开游标
OPEN curOne;
REPEAT
FETCH curOne INTO strClear;
#SELECT strClear;
#动态执行SQL语句
SET @mysql = strClear;
prePARE stmt from @mysql;
EXECUTE stmt;
DEALLOCATE prePARE stmt;
UNTIL done = 1 END REPEAT;
#关闭游标
CLOSE curOne;
END