mysql定时删除一个月前的数据

1,查看数据库的事件是否打开2种方式:

show variables like 'event_scheduler';

select @@event_scheduler;

2,如果显示的是disabled禁止,就要去mysql的配置文件my.ini

1、    SET GLOBAL event_scheduler = 1;

2、    SET @@global.event_scheduler = 1;

3、   SET GLOBAL event_scheduler = ON;

4、SET @@global.event_scheduler = ON;

将这个注释掉:
#skip-grant-tables
加上:
event_scheduler=ON

3,查看mysql的安装位置

show variables like "%char%";

4,创建存储过程:

DROP PROCEDURE IF EXISTS pro_clear_data;
CREATE PROCEDURE pro_clear_data()
    BEGIN  
      DELETE FROM income WHERE DATE(time)<=DATE(DATE_SUB(NOW(),INTERVAL 30 DAY));
    END

5,创建事件

CREATE EVENT IF NOT EXISTS event_time_clear

ON SCHEDULE EVERY 1 DAY STARTS '2018-10-26  15:40:00'

ON COMPLETION PRESERVE DO CALL income_clear();

6,显示事件:

select * from  mysql.event;

7,关闭事件

ALTER EVENT deleteLog ON COMPLETION PRESERVE DISABLE;

8,开启某件事件:

ALTER EVENT 事件名 ON COMPLETION PRESERVE ENABLE