利用存储过程truncate清空数据库下的表

最新推荐文章于 2024-07-03 15:15:23 发布

我是一个好孩子[:o)

最新推荐文章于 2024-07-03 15:15:23 发布

阅读量1.6k

点赞数 1

CC 4.0 BY-SA版权

文章标签：数据库 sql mysql

本文链接：https://blog.youkuaiyun.com/qq_44789968/article/details/126539138

本文介绍如何在MySQL中通过存储过程来清空多个表的数据，同时避免系统特定库表。详细拆解了从获取库名到组装预编译SQL，以及最终执行清理的过程，提供了一个安全的操作范例。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

利用存储过程清空数据库下的表

一、需求说明：

在每个数据库中，都存在一个yimq_messages等多个表，现在需要利用存储过程清空表这些表的所有数据。需排除系统特定的库表。

二、拆解说明

首先使用系统维护的表信息，获取所有库的名称，再拼接成sql语句，执行truncate清理过程。根据实际情况筛选出需要清理的库。

select DISTINCT(table_schema)  from information_schema.tables 
where table_schema not  in  ('information_schema','performance_schema','sys','mysql','migrate','new-times','redpacket','statistic')

三、组装预编译

为避免报错以及字段冲突，使用预编译的方式。最后需要释放预编译。

set @messages = CONCAT("truncate table ","`",report_name,"`",".yimq_messages");
prepare stmt1 FROM @messages;
EXECUTE stmt1;

四、完成sql语句。

CREATE PROCEDURE `clear_allyimq`()
BEGIN
	-- 定义变量
	DECLARE s int DEFAULT 0;
	DECLARE report_name varchar(50);
	-- 定义游标，并将sql结果集赋值到游标中
	DECLARE report CURSOR FOR select DISTINCT(table_schema)  from information_schema.tables 
where table_schema not  in  ('information_schema','performance_schema','sys','mysql','migrate','new-times','redpacket','statistic')
and table_type = 'BASE TABLE';
	-- 声明当游标遍历完后将标志变量置成某个值
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET s=1;
	-- 打开游标
	open report;
		fetch report into report_name;
		-- 当s不等于1，也就是未遍历完时，会一直循环
		while s<>1 do
		
			-- 执行业务逻辑
			-- 清空yimq_messages

			set @messages = CONCAT("truncate table ","`",report_name,"`",".yimq_messages");
			prepare stmt1 FROM @messages;
			EXECUTE stmt1;
			-- 清空yimq_processes
			set @processes = CONCAT("truncate table ","`",report_name,"`",".yimq_processes");
			prepare stmt2 FROM @processes;
			EXECUTE stmt2;
			-- 清空yimq_subtasks
			set @subtasks = CONCAT("truncate table ","`",report_name,"`",".yimq_subtasks");
			prepare stmt3 FROM @subtasks;
			EXECUTE stmt3;
			
			-- 将游标中的值再赋值给变量，供下次循环使用
			fetch report into report_name;
		-- 当s等于1时表明遍历以完成，退出循环
		end while;
	-- 关闭游标
	close report;
	-- 关闭预编译
	DROP PREPARE stmt1;
	DROP PREPARE stmt2;
	DROP PREPARE stmt3;
END

对应注释已经在sql语句中，根据自己业务逻辑变更即可。