存储过程定时对数据库表进行迁移

业务运行一段时间后，会出现有些表数据量很大，可能对系统性能产生不良的影响，常见的如订单表、登录log表等，这些数据很有时效性，比如我们一般很少去查上个月的订单，最多也就是报表统计会涉及到。

解决方案：创建一张与A表字段一样的B表，每天定时把A表昨天的数据插入到B表，再把A表昨天之前的数据删除，也可以根据需求改成一个月一次。如果不想只保存到一张表里，也可以在存储过程中，以年月份为后缀每次新建表。

1、首先创建一个存储过程

DELIMITER $

/*数据库名称*/

USE `dbcp`$$

/*存储过程名称*/

DROP PROCEDURE IF EXISTS `apaas_log_procedure`$$

CREATE DEFINER=`root`@`%` PROCEDURE `apaas_log_procedure`()

BEGIN

    SET @current_time = NOW();

    /*备份昨天之前的数据*/

    INSERT INTO `apaas_log_copy`

    SELECT * FROM `apaas_log`

    WHERE DATEDIFF(`create_Date`,@current_time) < 0;

    /*删除昨天之前的数据*/

    DELETE FROM `apaas_log`

    WHERE DATEDIFF(`create_Date`,@current_time) < 0;

    /*备份昨天之前的数据*/

    INSERT INTO `apaas_log_unqualified_copy`

    SELECT * FROM `apaas_log_unqualified`

    WHERE DATEDIFF(`create_Date`,@current_time) < 0;

    /*删除昨天之前的数据*/

    DELETE FROM `apaas_log_unqualified`

    WHERE DATEDIFF(`create_Date`,@current_time) < 0;

    END$$

DELIMITER ;

2、创建定时事件

DELIMITER $$

/*每天4点执行*/

ALTER DEFINER=`root`@`%` EVENT `apaas_log_event`

ON SCHEDULE EVERY 1 DAY STARTS '2019-02-19 04:00:00'

ON COMPLETION PRESERVE ENABLE DO CALL apaas_log_procedure()$$

DELIMITER ;

3.启动定时任务

/*启动定时任务*/

SET GLOBAL event_scheduler = 1

/*关闭定时任务*/

SET GLOBAL event_scheduler = 0

/*查看定时任务状态*/

SHOW VARIABLES LIKE '%event_sche%'

---------------------------------------------------------------------------

1.在使用之前必须确保 event_scheduler已开启

2.创建素具迁移的存储过程

DELIMITER $$

USE `yst_uic_service`$$

DROP PROCEDURE IF EXISTS `Sync_uchome_seen_history`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `Sync_uchome_seen_history`(IN tabSize INT)

BEGIN

 DECLARE i INT DEFAULT 0;

## 获取需同步数据的时间节点（上个月）

SET @upmonth= DATE_ADD(NOW(), INTERVAL -1 MONTH);

WHILE i < tabSize DO

SET @sqlstr=CONCAT('INSERT INTO `uchome_history_',i,'`(`icon`,`uid`,`epgId`,`detailsId`,`score`,`titleData`,`datePoint`,`dateLine`,`objtype`,`templateId`,`times`,`watchTime`)

   SELECT `icon`,`uid`,`epgId`,`detailsId`,`score`,`titleData`,`datePoint`,`dateLine`,`objtype`,`templateId`,`times`,`watchTime` FROM `uchome_seen_',i,'`  WHERE `dateLine`<=?');     

SET @delsqlstr=CONCAT('delete from `uchome_seen_',i,'` WHERE `dateLine`<=? ');

#执行数据迁移

PREPARE _stmt FROM @sqlstr;

EXECUTE _stmt USING @upmonth;

DEALLOCATE PREPARE _stmt;

#执行迁移后的数据删除

PREPARE _stdel FROM @delsqlstr;

EXECUTE _stdel USING @upmonth;

DEALLOCATE PREPARE _stdel;

SET i := i+1;

END WHILE;

END$$

DELIMITER ;

3.创建定时任务

DELIMITER $$

ALTER DEFINER=`root`@`localhost` EVENT `Sync_uchome_seen_history`

 ON SCHEDULE EVERY 1 DAY

   STARTS '2015-06-29 01:30:00'

 ON COMPLETION NOT PRESERVE ENABLE

   DO CALL`Sync_uchome_seen_history`(10)$$

DELIMITER ;

4.关闭和开启任务时间

临时关闭： ALTER EVENT  Sync_uchome_seen_history  ON  COMPLETION PRESERVE DISABLE;

关闭任务 ：  ALTER EVENT  Sync_uchome_seen_history DISABLE;

临时开启:  ALTER EVENT Sync_uchome_seen_histor