博客

一、需求说明：

在每个数据库中，都存在一个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语句中，根据自己业务逻辑变更即可。