MySQL8.0 定时任务EVENT |
您所在的位置:网站首页 › 计划任务功能 › MySQL8.0 定时任务EVENT |
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; |
CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |