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

妖狐艹你老母 2024-03-30 14:55 76阅读 0赞

业务运行一段时间后，会出现有些表数据量很大，可能对系统性能产生不良的影响，常见的如订单表、登录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$$