mysql event 时间备份数据

 

-- 创建存储过程
DELIMITER $$

DROP PROCEDURE IF EXISTS `dep_syn_data`$$
CREATE PROCEDURE `dep_syn_data` (date_inter INT,table_name VARCHAR(255),filed VARCHAR(225))
BEGIN

SET @soutable = CONCAT(table_name) ;

SET @dateformat = CONCAT(DATE_FORMAT(CURDATE(),'%m'));

-- 备份表名规则 一年 三个表
IF @dateformat BETWEEN 1 and 4 THEN
set @tnum = '1';
ELSEIF @dateformat BETWEEN 5 and 8 THEN
set @tnum = '2';
ELSE
set @tnum = '3';
END IF;

SET @tabletr = CONCAT(@soutable,'_',DATE_FORMAT(CURDATE(),'%Y'),'_',@tnum) ;

-- 创建备份表
SET @cretmpstr = CONCAT( 'create table if not exists ' ,@tabletr, ' like ' ,@soutable ) ;
PREPARE stmt2 FROM @cretmpstr ;
EXECUTE stmt2 ;

-- 查询数据条数
SET @countstr = CONCAT('select count(id) into @countnum from ' ,@soutable,
' where (TO_DAYS(NOW()) - TO_DAYS(',filed,')) >=', CAST(date_inter AS CHAR));
PREPARE stmt1 FROM @countstr ;
EXECUTE stmt1 ;

SET @num = @countnum;

-- 循环备份数据 只执行10次
SET @i = 1;
WHILE @num > 0 DO
IF @i > 3 THEN
SET @num = 0;
ELSE
SET @csqlstr = CONCAT( 'insert into ' ,@tabletr, ' select * from ' ,@soutable,
' where (TO_DAYS(NOW()) - TO_DAYS(',filed,')) >=',
CAST(date_inter AS CHAR),' limit 2000;') ;

SET @dsqlstr = CONCAT( 'delete from ' ,@soutable, ' where (TO_DAYS(NOW()) - TO_DAYS(',filed,')) >=',
CAST(date_inter AS CHAR),' limit 2000;') ;

PREPARE stmt3 FROM @csqlstr ;
PREPARE stmt4 FROM @dsqlstr ;
EXECUTE stmt3 ;
EXECUTE stmt4 ;
SET @i = @i+1;
END IF;
END WHILE;

END$$
DELIMITER ;

-- 创建event 触发器
DROP EVENT IF EXISTS `backup_wbs_tt_router_data`;
CREATE EVENT `backup_wbs_tt_router_data` ON SCHEDULE EVERY 1 DAY STARTS NOW() ON COMPLETION NOT PRESERVE ENABLE DO
CALL dep_syn_data (180,'wbs_tt_router','insert_time');

-- 开启触发器
ALTER EVENT backup_wbs_tt_router_data ON COMPLETION PRESERVE ENABLE;