mysql定时复制表数据_在mysql中基于存储过程用作业计划定时复制表数据到另外一张表的实现...

项目背景：

每次向TY推送挂号了的时候都会检测Push表是否有数据，如果有数据就不会再向TY推送挂号了，但现实情况是供应商的挂号有可能会出现50天后重复利用的情况，当重复利用挂号时就无法像TY推送数据，所以需要每天定时业务维护50天前的数据。按以下步骤编写功能

1、写MySQL的脚本,在表PushToTrackingSupplierInfo  CreateOn字段创建索引

2、写MySQL的脚本,创建PushToTrackingSupplierInfo_Bak_2019 表，结构跟PushToTrackingSupplierInfo一致(每年的数据业维到单独的表)

3、写MySQL的脚本,创建事件,每天22:00:00 定时将PushToTrackingSupplierInfo CreateOn 50天前的数据转到 创建PushToTrackingSupplierInfo_Bak_2019 表，需要分批次，每批建议是1-2w行数据

功能具体实现步骤

1、主表创建CreateOn索引，ck1_tracking 数据库中的PushToTrackingSupplierInfo表创建CreateOn索引

2、创建备份表，sql创建语句：

CREATE TABLE IF NOT EXISTS `PushToTrackingSupplierInfo_Bak_2019` (

`Id` INT(11) NOT NULL,

`TrackingSupplierCode` VARCHAR(50) NOT NULL,

`TrackingSupplierCarrier` VARCHAR(50) NOT NULL,

`TrackingNo` VARCHAR(50) NOT NULL,

`IsPushSuccess` BIT(1) NOT NULL,

`CrawlerTaskInfoJson` VARCHAR(2000) DEFAULT NULL,

`Remark` VARCHAR(200) DEFAULT NULL,

`CreateOn` DATETIME NOT NULL,

`CreateBy` VARCHAR(50) NOT NULL,

`UpdateOn` DATETIME NOT NULL,

`UpdateBy` VARCHAR(50) NOT NULL,

`IsReceivedChecking` BIT(1) DEFAULT NULL,

`LastReceivedCheckingTime` DATETIME DEFAULT NULL,

PRIMARY KEY (`Id`),

KEY `CreateOn` (`CreateOn`)

) ENGINE=INNODB DEFAULT CHARSET=utf8;

3、创建编写存储过程pro_backupPushToTrackingSupplierInfo：

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `pro_backupPushToTrackingSupplierInfo`(IN d INT,IN b INT)

BEGIN

DECLARE amount INTEGER DEFAULT 1;/*记录PushToTrackingSupplierInfo表中距离现在50天的单号的数量*/

DECLARE times INTEGER DEFAULT 1;/*分多少批次进行*/

DECLARE t_error INTEGER  DEFAULT 0;/*设置错误标识，默认为0*/

DECLARE tempday DATETIME;/*设置距离当前d天的时间*/

DECLARE is_break BIT DEFAULT 0;

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET t_error=1;/*执行数据库语句报错时，将t_error值设置为1*/

SET tempday=  DATE_ADD(NOW(),INTERVAL -d DAY);

SELECT COUNT(Id) INTO amount FROM PushToTrackingSupplierInfo WHERE  CreateOn < tempday AND NOT EXISTS (SELECT 1 FROM PushToTrackingSupplierInfo_Bak_2019 WHERE Id=PushToTrackingSupplierInfo.Id ) ;

SET times=amount/b+1;

IF  times < 1 THEN

SET times=times+1;

END IF;

WHILE times >=1 AND is_break=0 DO

START TRANSACTION;

INSERT INTO PushToTrackingSupplierInfo_Bak_2019 SELECT  * FROM PushToTrackingSupplierInfo WHERE  CreateOn < tempday   AND NOT EXISTS (SELECT 1 FROM PushToTrackingSupplierInfo_Bak_2019 WHERE Id=PushToTrackingSupplierInfo.Id ) ORDER BY Id LIMIT 0,b;

/*INSERT INTO PushToTrackingSupplierInfo_Bak_2019(Id) VALUES(365);*/

DELETE  FROM  PushToTrackingSupplierInfo WHERE  EXISTS (SELECT 1 FROM PushToTrackingSupplierInfo_Bak_2019 WHERE Id=PushToTrackingSupplierInfo.Id ) AND CreateOn < tempday ;

IF t_error=1 THEN

SET is_break=1;

ROLLBACK;

ELSE

COMMIT;

END IF;

SET times =times-1;

END WHILE;

END$$

DELIMITER ;

4、编写mysql事件定时任务：

CREATE    /*[DEFINER = { user | CURRENT_USER }]*/EVENT IF NOT EXISTS  `ck1_tracking`.`event_backupPushToTrackingSupplierInfo`

ON SCHEDULE EVERY 1 DAY STARTS DATE_ADD(DATE_ADD(CURDATE(),INTERVAL 1 DAY),INTERVAL 1 HOUR)

ON COMPLETION PRESERVE ENABLE

/*

每天晚上凌晨1点定时从PushToTrackingSupplierInfo表中备份50天前的数据到PushToTrackingSupplierInfo_Bak_2019表中，并且删除已备份好的数据，分批次进行

关闭事件方法：

alter event event_backupPushToTrackingSupplierInfo ON COMPLETION PRESERVE DISABLE;

开启事件方法：

alter event event_backupPushToTrackingSupplierInfo ON COMPLETION PRESERVE ENABLE;

*/

DO

BEGIN

DECLARE d INT DEFAULT 50;/*表示距离现在多少天前的数据*/

DECLARE b INT DEFAULT 100000;/*表示每批次要备份的数据量*/

CALL pro_backupPushToTrackingSupplierInfo(d,b);

END$$

DELIMITER ;

6、本地测试

需要备份导入300w条数据，每批次10w条数据，执行速度大约为25分钟

需要备份导入300w条数据，每批次5w条数据，执行速度大约为35分钟

需要备份导入2w条数据，每批次10w数据，执行速度大约为1秒钟(在备份表中已经有3百万数据的情况下)

需要注意的地方：

(1)开启mysql事件调度需要权限 select @@event_scheduler ; SET GLOBAL event_scheduler = ON (直接在query上面执行)

(2)需要设置innodb_buffer_pool_size缓冲区的值，默认一般是128M，这对于我们导入几百万数据时，会报错The total number of locks exceeds the lock table size ，数据库客户端内存不足，需要设置innodb_buffer_pool_size缓冲区内存为1G：SET GLOBAL  innodb_buffer_pool_size =1024*1024*1024