创建存储过程，保留100条数据，其他删除
DELIMITER |
DROP PROCEDURE IF EXISTS clear_bzp_dynamic_supervision_data |
CREATE PROCEDURE clear_bzp_dynamic_supervision_data()
BEGIN
SET @count_=(SELECT COUNT(id) FROM bzp_dynamic_supervision);
IF(@count_>100) THEN
SET @max_id=(SELECT MAX(id) FROM bzp_dynamic_supervision);
SET @max_id = @max_id - 100;
DELETE FROM `bzp_dynamic_supervision` WHERE id<@max_id;
END IF ;
END
|
创建定时任务：每晚四点触发
SET GLOBAL event_scheduler = 1;
CREATE EVENT IF NOT EXISTS clear_bzp_dynamic_supervision_data
on schedule EVERY 1 DAY STARTS '2018-12-06 04:00:00'
ON COMPLETION PRESERVE
DO CALL clear_bzp_dynamic_supervision_data();
启动定时任务：
ALTER EVENT clear_bzp_dynamic_supervision_data ON
COMPLETION PRESERVE ENABLE;
