mysql 定时调用存储过程，将一个月前数据备份到历史表

-- 创建存储过程，备份一个月以前的数据到历史表中
CREATE DEFINER=`root`@`%` PROCEDURE `device_record_history`()
BEGIN
    #Routine body goes here...
    INSERT ignore into temp_device_record_history 
    SELECT * from temp_device_record record where  date_format(record.create_time,'%Y-%m-%d') <= date_format(DATE_SUB(curdate(), INTERVAL 1 MONTH),'%Y-%m-%d');
    DELETE record
    FROM temp_device_record record WHERE date_format(record.create_time,'%Y-%m-%d') <= date_format(DATE_SUB(curdate(), INTERVAL 1 MONTH),'%Y-%m-%d');
END

-- 创建定时任务，每天执行一次存储过程
DROP EVENT IF EXISTS user_event ;   
CREATE EVENT `user_event`   -- 创建名字为user_event的事件
ON SCHEDULE EVERY 1 DAY STARTS DATE_ADD(DATE_ADD(CURDATE(), INTERVAL 1 DAY), INTERVAL 1 HOUR) -- 每隔一天执行一次，开始执行时间为明天凌晨1点整
ON COMPLETION NOT PRESERVE
ENABLE
DO call device_record_history() -- 指定要执行的存储过程

-- 查询任务
select * from information_schema.`EVENTS`;