1.新建存储过程:
DELIMITER $$
USE `rntec`$$
DROP PROCEDURE IF EXISTS `copy_data_1_day`$$
CREATE DEFINER=`root`@`%` PROCEDURE `copy_data_1_day`()
BEGIN
DECLARE v_date_0 VARCHAR(30);
-- 获取6个月前日期
SELECT DATE_FORMAT(DATE_SUB(DATE(NOW()),INTERVAL 6 MONTH),'%Y-%m-%d %H:%i:%s') INTO v_date_0;
-- 复制数据
-- bean_info
INSERT INTO bean_info_history
SELECT *
FROM bean_info t
WHERE t.CREATE_TIME < v_date_0;
COMMIT;
END$$
DELIMITER ;
2.新建定时事件
//定时copy数据
CREATE EVENT IF NOT EXISTS event_copy_one
ON SCHEDULE EVERY 1 DAY STARTS DATE_ADD(DATE_ADD(CURDATE(), INTERVAL 1 DAY), INTERVAL 12 HOUR) ON COMPLETION PRESERVE ENABLE
DO CALL copy_data_1_day()
3.查看定时器是否启动
SHOW VARIABLES LIKE 'event_scheduler';-- 查看是否开启定时器
SET GLOBAL event_scheduler = 1; -- 开启定时器 0:off 1:on
-- 开启事件
ALTER EVENT event_test ON
COMPLETION PRESERVE ENABLE;
-- 关闭事件
ALTER EVENT event_test ON
COMPLETION PRESERVE DISABLE;