MYSQL定时执行事件

开启事件调度器

1、确认是否开启

SHOW VARIABLES LIKE 'event_scheduler';

2、开启命令

SET GLOBAL event_scheduler = ON;

创建事件<创建后默认开启>
CREATE EVENT [IF NOT EXISTS] event_name ON SCHEDULE schedule
[ON COMPLETION [NOT] PRESERVE] [ENABLE | DISABLE] [COMMENT 'comment']

DO sql_statement;

例如：
1.每分钟(60S)清空一次记录表

use database_name;
create event table_truncate on schedule every 60 second do truncate table operator_record_log;

2.每30天(2592000S)清空30天前的所有记录，仅保留近30天数据

use database_name;
create event table_truncate on schedule every 2592000 second do delete from operator_record_log where create_date < DATE_SUB(CURDATE(),INTERVAL 30 DAY);

3.指定时间将表清空

use database_name;
create event table_truncate on schedule at timestamp '2022-01-01 00:00:00' do truncate table operator_record_log;

4.每天定时清空

use database_name;
create event table_truncate on schedule every 1 day starts '2022-01-01 00:00:00' do truncate table operator_r

关闭事件

alter event event_name disable;

开启事件

alter event event_name enable;

删除事件

drop event if exists event_name;

查看事件

show events;