MySQL8.0 定时任务EVENT
1.配置环境
检查是否已开启该功能
SHOW VARIABLES LIKE ‘event_scheduler’;
mysql> SHOW VARIABLES LIKE 'event_scheduler';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| event_scheduler | ON |
+-----------------+-------+
1 row in set (0.01 sec)
开启计划任务功能:
SET GLOBAL event_scheduler = 1;
或
SET GLOBAL event_scheduler = ON;
2.命令
(1)创建定时任务event
CREATE
[DEFINER = user]
EVENT
[IF NOT EXISTS]
event_name
ON SCHEDULE schedule
[ON COMPLETION [NOT] PRESERVE]
[ENABLE | DISABLE | DISABLE ON SLAVE]
[COMMENT 'string']
DO event_body;
schedule:
AT timestamp [+ INTERVAL interval] ...
| EVERY interval
[STARTS timestamp [+ INTERVAL interval] ...]
[ENDS timestamp [+ INTERVAL interval] ...]
interval:
quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |
WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE |
DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}
分为6个部分:
(1)单次计划任务: AT 时戳 ,一次性任务
(2)重复的计划任务 EVERY 时间(单位)的数量,时间单位 [STARTS 时戳][ENDS 时戳] 。
在两种计划任务中,时戳可以是任意的TIMESTAMP 和DATETIME 数据类型,要求提供的是将来的时间(大于CURRENT_TIMESTAMP),而且小于Unix时间的最后时间(等于或小于’2037-12-31 23:59:59’)
时间单位是关键词:
YEAR,MONTH,DAY,HOUR,MINUTE 或者SECOND
(3)[ON COMPLETION [NOT] PRESERVE] COMPLETION 当单次计划任务执行完毕后或当重复性的计划任务执行到了ENDS阶段。而声明PRESERVE的作用是使事件在执行完毕后不会被Drop掉
(4)[ENABLE|DESABLE] ENABLE 开启事件 DESABLE 关闭事件
(5)COMMENT 注释
(6)DO sql_statement 执行的sql语句
(2)维护定时任务event
删除定时任务
drop event if exists event_name;
设置定时任务启动、关闭
alter event event_name disable;
alter event event_name enable;
3.测试
创建测试表
create table evt_insert_test (timeline timestamp,randstr varchar(16));
创建定时任务,每隔一秒插入数据
create event e_test_insert
on schedule every 1 second
starts '2023-10-12 00:00:00'
do
begin
insert into evt_insert_test values (current_timestamp,left(uuid(), 16));
commit;
end;
检查任务:
mysql> show events\G;
*************************** 1. row ***************************
Db: bigdatashow
Name: e_test_insert
Definer: root@%
Time zone: +08:00
Type: RECURRING
Execute at: NULL
Interval value: 1
Interval field: SECOND
Starts: 2023-10-12 00:00:00
Ends: NULL
Status: ENABLED
Originator: 1
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
Database Collation: utf8mb4_0900_ai_ci
每隔一小时执行一次存储过程。
create event e_test_procedure
on schedule every 1 hour
starts '2023-10-12 00:00:00'
do
begin
call pro_test_var;
end;