mysql存储过程加定时任务实现按月备份表数据

	        
DELIMITER

-- 如果存储过程已经存在，先删除
DROP PROCEDURE IF EXISTS 'back_history';
-- 创建存储过程
CREATE PROCEDURE back_history()
BEGIN
-- 现在是几年几月
SET @dateStr = DATE_FORMAT(NOW(),'%Y%m');
-- 30天前是哪一天(年月日)
SET @preDateStr = DATE_FORMAT(DATE_SUB(CURDATE(),INTERVAL 30 DAY),'%Y%m%d');
-- 30天前是哪一天(年月)
SET @preMonthStr = DATE_FORMAT(DATE_SUB(CURDATE(),INTERVAL 30 DAY),'%Y%m');
-- 30天之前的月份的上一个月
SET @ppreMonthStr = DATE_FORMAT(DATE_SUB(DATE_SUB(CURDATE(),INTERVAL 30 DAY),INTERVAL 1 MONTH),'%Y%m');

#上个月的年月
SET @lastDateStr = DATE_FORMAT(DATE_SUB(NOW(),INTERVAL 1 MONTH),'%Y%m');

-- 定义建表sql
SET @createStr = CONCAT("CREATE TABLE dialog_result_detail_history" ,@preMonthStr,
                    "(`owner_id` varchar(32) NOT NULL DEFAULT '业户编号必须唯一',
                    `owner_name` varchar(100) DEFAULT NULL COMMENT '业户名称',
                    `business_id` varchar(100) DEFAULT NULL COMMENT '经营许可证编号',
                    `owner_tel` varchar(30) DEFAULT NULL COMMENT '联系电话',
                    `address` varchar(200) DEFAULT NULL COMMENT '所在地址',
                    `create_time` datetime DEFAULT NULL COMMENT '记录日期',
                    PRIMARY KEY (`owner_id`))");

-- 查询30天前的那天的数据
SET @total = (SELECT count(*)from result where DATE_FORMAT(create_date,'%Y-%m-%d')=DATE_FORMAT(DATE_SUB(CURDATE(),INTERVAL 30 DAY),'%Y-%m-%d'));
-- 定义一次批量插入的数据量
SET @everyNum = 30000;

-- 开启事务
START TRANSACTION;
-- 判断CONCAT('dialog_result_detail_history',@preMonthStr)表是否存在
if (SELECT COUNT(table_name) FROM information_schema.TABLES WHERE table_name=CONCAT('dialog_result_detail_history',@preMonthStr) )>0 THEN
	-- 表已经存在，查询30天之前的那天的数据，往表中插入
	
	-- 记录剩余数据量
	SET @remainTotal = @total;
	-- 如果数据量大于@everyNum，循环分批插入
	IF @total>@@everyNum THEN
		SET @temp=0;
		SET @temp1 = @everyNum;

		loop_insertLoop: LOOP
			SET	@tableName = CONCAT("dialog_result_detail_history_",@preMonthStr);
			SET @insertStr = CONCAT("insert into ",@tableName,
															"(owner_id,owner_name,business_id,owner_tel,address,create_time)
															 (select owner_id,owner_name,business_id,owner_tel,address,create_time
																from result where DATE_FORMAT(create_date,'%Y%m%d')=DATE_FORMAT(DATE_SUB(CURDATE(),INTERVAL 30 DAY),'%Y%m%d')ORDER BY
																owner_id DESC LIMIT ", @temp,",",@temp1,"  )" );
			-- 预编译sql
			PREPARE stmt FROM @insertStr;
			-- 执行sql
			EXECUTE stmt;
			
			-- 计算还有多少数据量需要插入表中
			SET @remainTotal = @remainTotal-@temp1;
			IF @remainTotal>@everyNum THEN
				SET @temp = @temp+@temp1;
			ELSE
				SET @temp = @temp+@temp1;
				-- 剩余数据量小于@everyNum，一次执行，并推出循环
				SET @tableName = CONCAT("dialog_result_detail_history_",@preMonthStr);
				SET @insertStr = CONCAT("insert into ",@tableName,
															"(owner_id,owner_name,business_id,owner_tel,address,create_time)
															 (select owner_id,owner_name,business_id,owner_tel,address,create_time
																from result where DATE_FORMAT(create_date,'%Y%m%d')=DATE_FORMAT(DATE_SUB(CURDATE(),INTERVAL 30 DAY),'%Y%m%d')ORDER BY
																owner_id DESC LIMIT ", @temp,",",@remainTotal,"  )" );
				PREPARE stmt FROM @insertStr;
				EXECUTE stmt;
				
				LEAVE loop_insertLoop;
			END IF;
		END LOOP;
	
	ELSE
		-- 总数据量小于@everyNum
		-- 定义插入表语句
		SET @tableName = CONCAT("dialog_result_detail_history_",@preMonthStr);
		SET @insertStr = CONCAT("insert into ",@tableName,
													"(owner_id,owner_name,business_id,owner_tel,address,create_time)
													 (select owner_id,owner_name,business_id,owner_tel,address,create_time
														from result where DATE_FORMAT(create_date,'%Y%m%d')=DATE_FORMAT(DATE_SUB(CURDATE(),INTERVAL 30 DAY),'%Y%m%d'))");
		PREPARE stmt FROM @insertStr;
		EXECUTE stmt;
	END IF;

	-- 删除原表数据
	SET @deleteSql = "DELETE FROM result where owner_id IN (SELECT owner_id FROM (SELECT owner_id FROM result WHERE DATE_FORMAT(create_date,'%Y%m%d')=DATE_FORMAT(DATE_SUB(CURDATE(),INTERVAL 30 DAY),'%Y%m%d'))t1 )  ";
	PREPARE stmt FROM @deleteSql
	EXECUTE stmt;

else 
	-- 表不存在，先判断30天之前的那个月的上一个月的表是否存在，如果存在表示已经初始化了，30天之前刚好是那月的1号，30天前的那个月的表还未创建
	IF (SELECT COUNT(table_name) FROM information_schema.TABLES where table_name=CONCAT('dialog_result_detail_history_',@ppreMonthStr))>0 THEN
		-- 创建表
		SET @ownerInfo = CONCAT(@createStr,"ENGINE=InnoDB DEFAULT CHARSET=utf8;");
		PREPARE stmt1 FROM @ownerInfo;
		EXECUTE stmt1;
	
		-- 重复上面的if里面的分批循环插入数据
	ELSE
		-- 30天之前的那个月的上一个月的表也不存在
		-- 说明程序是第一次启动，需要初始化
		-- 上个月的日期
		SET @preMonthStr = DATE_SUB(CURDATE(),INTERVAL 30 DAY);

		-- 今年年份
		SET @dateStr = DATE_FORMAT(NOW(),'%Y');
		SET @i = 0;
		loop_label: LOOP
			-- 日期表名
			SET @loop_table = DATE_FORMAT(@preMonthStr,'%Y%m');
			
			-- 创建表并复制30天前的那个月的数据
			SET @stmt1 = CONCAT("create table ","dialog_result_detail_history_",@loop_table,"(SELECT * FROM result where DATE_FORMAT(create_date,'%Y%m')=",@loop_table," AND DATE_FORMAT(create_date,'%Y%m%d')<=DATE_FORMAT(DATE_SUB(CURDATE(),INTERVAL 30 DAY),'%Y%m%d') );");
			PREPARE stmt1 FROM @stmt1;
			EXECUTE stmt1;

			-- 这样创建并复制数据的表没有主键，需要设置主键
			-- 设置表的主键
			SET @stmt2 = CONCAT("ALTER TABLE dialog_result_detail_history_",@loop_table," ADD PRIMARY KEY(`owner_id`)");
			
			-- 删除原表数据
			SET @deleteSql = CONCAT("DELETE FROM result where owner_id IN (SELECT owner_id FROM (SELECT owner_id FROM result where DATE_FORMAT(create_date,'%Y%m')=",@loop_table," AND DATE_FORMAT(create_date,'%Y%m%d')<=DATE_FORMAT(DATE_SUB(CURDATE(),INTERVAL 30 DAY),'%Y%m%d') )t1);");
			PREPARE delsql FROM @delSql;
			EXECUTE delsql;
			
			-- 查询现在执行的月份的上个月
			SET @preMonthStr = DATE_SUB(@preMonthStr,INTERVAL 1 MONTH);
			-- 如果循环到今年1月，结束循环
			IF @loop_table = CONCAT(@dateStr,'01') THEN
				LEAVE loop_label;
			END IF;
			
			END LOOP loop_label;
	


	END IF;

END IF;
-- 提交事务
COMMIT;
END
DELIMITER;