mysql event 备份数据_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;