清除数据库内所有表的数据【MySQL】
-- 如果存在,则删除存储过程
drop procedure IF EXISTS delAllTableData;
delimiter $$
-- 删除数据库所有数据
create procedure delAllTableData(tableName varchar(255))
begin
declare i int default 0;
declare table_size int default 0;
declare table_name_param varchar(255);
SELECT count(*) into table_size FROM information_schema.tables WHERE table_schema = tableName;
while (i < table_size) do
SELECT TABLE_NAME into table_name_param FROM information_schema.tables WHERE table_schema = tableName limit 1 offset i;
set i = i+1;
set @tem_table := concat('truncate table ',table_name_param);
prepare tem_table from @tem_table;
execute tem_table;
end while;
end $$;
delimiter ;
-- 引号内是需要清除数据的数据库名称
call delAllTableData('database_name');