在sql窗口中创建存储过程:
-- 创建存储过程
DROP PROCEDURE IF EXISTS P_procedure_name ;
DELIMITER //
CREATE PROCEDURE P_procedure_name ()
BEGIN
delete from table_name where dt < DATE_FORMAT(DATE_SUB(NOW(),INTERVAL 2 DAY),'%Y%m%d');
COMMIT;
END //
DELIMITER ;
在存储过程中为:
CREATE DEFINER=`super`@`%` PROCEDURE `P_procedure_name `()
BEGIN
DELETE from table_name where DATE_FORMAT(dt,'%Y%m%d') < DATE_FORMAT(DATE_SUB(CURDATE(),INTERVAL 1 day),'%Y%m%d');
COMMIT;
END
创建事件:
-- 创建事件,定时调度存储过程任务
DROP EVENT if EXISTS E_event_name;
CREATE EVENT E_event_name
ON SCHEDULE EVERY 1 DAY STARTS DATE_ADD(DATE_ADD(CURDATE(), INTERVAL 1 DAY), INTERVAL 1 HOUR)
COMMENT '每天凌晨1点,调用存储过程,删除历史数据'
DO
call P_procedure_name ();