mysql定时删除30天前的数据

切换数据库

use ship_status

#创建存储过程PROCEDURE

DELIMITER $$

DROP PROCEDURE IF EXISTS `prc_del_gps_gga`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `prc_del_gps_gga`(IN date_inter INT)

    COMMENT '自动删除历史数据'

BEGIN

DELETE FROM  gps_gga WHERE (TO_DAYS(NOW()) - TO_DAYS(timestamp))>=date_inter;  

END$$

DELIMITER ;

#创建事件EVENT

CREATE EVENT del_log

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

ON COMPLETION PRESERVE ENABLE

DO call prc_del_gps_gga(30);

设置事件计划为开启状态

set global event_scheduler = ON

查看事件计划是否为已打开状态

show variables like 'event%'

查看存储

show procedure status;

查看事件

show events;