mysql定时删除表中数据过程

表中就两个数据,门店名称以及时间.

今早突发奇想需要做一个定时删除数据库的想法,网上找了一些资料.

基本上都是先弄一个存储过成,然后创建事件,再吊起事件执行存储过程.

大体过程如下:

1.创建存储过程:

DELIMITER |
DROP PROCEDURE IF EXISTS pro_clear_data |
CREATE PROCEDURE pro_clear_data()
    BEGIN  
      
       SET @news_count=(SELECT COUNT(1) FROM store_name);
       IF(@news_count>6) THEN 
	set @minDate = (select min(searchDate) from (SELECT * FROM store_name order by searchDate desc limit 10) t);
            DELETE FROM store_name WHERE searchDate < @minDate;
       END IF ;
       
    END |

//注意的是select min(searchDate) from (SELECT * FROM store_name order by searchDate desc limit 10) t  必须先查找出来的时间再弄成一个表,要不然查出来是全表的最小时间

2.创建事件(事件启动1分钟执行存储过程)

CREATE EVENT IF NOT EXISTS edith.event_time_clear_data
 
ON SCHEDULE EVERY 60 SECOND
 
ON COMPLETION PRESERVE  
 
DO CALL pro_clear_data();

3.手动启动事件:

ALTER EVENT edith.event_time_clear_data ON 
 
COMPLETION PRESERVE ENABLE;

4.关闭定时事件

ALTER EVENT event_time_clear_data ON 
 
COMPLETION PRESERVE DISABLE;

5.删除存储过程:

DROP PROCEDURE pro_clear_data;

注:开启事件的另外方法:

SET GLOBAL event_scheduler = ON 或 SET GLOBAL event_scheduler = 1;