数据库学习案例20240125-MySQL定时任务EVENT事件的使用方法

一、查看定时策略是否开启

双主集群环境，slave端要关闭event。

SHOW VARIABLES LIKE 'event_scheduler';

mysql> SHOW VARIABLES LIKE 'event_scheduler';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| event_scheduler | OFF   |
+-----------------+-------+
1 row in set (0.00 sec)

mysql> 

如图，Value值 ON：打开，OFF：关闭。

2、设置event事件打开

SET GLOBAL event_scheduler = ON;

如果MySQL重启了，event会默认关闭，需要重新设置。

可在my.cnf配置文件里的【mysqld】增加 event_scheduler = ON（永久开启，设置后需要重启MySQL才会生效）

3、设置event事件关闭
SET GLOBAL event_scheduler = OFF;

二、定时任务说明

-- 创建定时任务

CREATE EVENT my_event ON SCHEDULE

EVERY '1' DAY STARTS '2023-01-01 01:00:00'

DO CALL my_event()

解释：
1、create event my_event：定义事件名称，名为my_event
2、EVERY '1' DAY：周期定时的规则，每天执行一次
3、STARTS '2023-01-01 01:00:00'：表示在具体执行开始时间，2023-01-01凌晨1点整开始执行
4、可选 on completion preserve disable：表示创建后并不开始生效。
5、do call my_event()：该event(事件)的操作内容,这里是调用名为my_event()的存储过程

创建event2

create table timeevent(id int auto_increment primary key,currenttime timestamp);

create event if not exists insert_timestamp_event

on schedule every 3 second

on completion preserve

enable

do

insert into timeevent(currenttime) values(current_timestamp());

05:35:  [mgr]> select *from timeevent;
+----+---------------------+
| id | currenttime         |
+----+---------------------+
|  1 | 2024-03-04 05:35:33 |
|  2 | 2024-03-04 05:35:36 |
|  3 | 2024-03-04 05:35:39 |
|  4 | 2024-03-04 05:35:42 |
|  5 | 2024-03-04 05:35:45 |
|  6 | 2024-03-04 05:35:48 |
|  7 | 2024-03-04 05:35:51 |
|  8 | 2024-03-04 05:35:54 |
|  9 | 2024-03-04 05:35:57 |
| 10 | 2024-03-04 05:36:00 |
| 11 | 2024-03-04 05:36:03 |
| 12 | 2024-03-04 05:36:06 |
| 13 | 2024-03-04 05:36:09 |
+----+---------------------+
13 rows in set (0.00 sec)

三、定时任务说明

1、查看所有event

SELECT * FROM information_schema.EVENTS where EVENT_SCHEMA = 'mgr';

05:36:  [mgr]> SELECT * FROM information_schema.EVENTS where EVENT_SCHEMA ='Mgr' \G
*************************** 1. row ***************************
       EVENT_CATALOG: def
        EVENT_SCHEMA: mgr
          EVENT_NAME: insert_timestamp_event
             DEFINER: root@localhost
           TIME_ZONE: SYSTEM
          EVENT_BODY: SQL
    EVENT_DEFINITION: insert into timeevent(currenttime) values(current_timestamp())
          EVENT_TYPE: RECURRING
          EXECUTE_AT: NULL
      INTERVAL_VALUE: 3
      INTERVAL_FIELD: SECOND
            SQL_MODE: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
              STARTS: 2024-03-04 05:35:33
                ENDS: NULL
              STATUS: ENABLED
       ON_COMPLETION: PRESERVE
             CREATED: 2024-03-04 05:35:33
        LAST_ALTERED: 2024-03-04 05:35:33
       LAST_EXECUTED: 2024-03-04 05:36:51
       EVENT_COMMENT: 
          ORIGINATOR: 100
CHARACTER_SET_CLIENT: utf8mb4
COLLATION_CONNECTION: utf8mb4_0900_ai_ci
  DATABASE_COLLATION: utf8mb4_general_ci
1 row in set (0.00 sec)

05:36:  [mgr]> 

SELECT EVENT_NAME, EVENT_DEFINITION, INTERVAL_VALUE, INTERVAL_FIELD, STATUS FROM INFORMATION_SCHEMA.EVENTS;

2、查看指定event

SHOW CREATE EVENT insert_timestamp_event;

3、修改指定event

-- 修改定时任务 2点执行 调用my_event_bak()存储过程
ALTER EVENT insert_timestamp_event
ON SCHEDULE
EVERY '1' DAY STARTS '2023-01-01 02:00:00'
DO CALL my_event_bak()

4、开启指定event

//开启定时任务

alter event insert_timestamp_event on completion preserve enable;

5、关闭指定event

//关闭定时任务

alter event insert_timestamp_event on completion preserve disable;

6、删除指定event

//删除定时任务

alter event insert_timestamp_event on completion preserve disable;
drop event insert_timestamp_event；

7、定时规则

7.1、执行周期（EVERY）
参数单位有：second、minute、hour、day、week(周)、quarter(季度)、month、year

//每天执行1次
on schedule every 1 day
//每周执行1次
on schedule every 1 week
//每月执行1次
on schedule every 1 month
//每年执行1次
on schedule every 1 year

7.2、执行开始时间（AT）
// 当前时间5天后执行
on schedule at current_timestamp() + interval 5 day
// 在2023年1月1日，凌晨1点整执行
on schedule at '2023-01-01 01:00:00'

7.3、执行时间段（STARTS ENDS）
//3天后开始每天都执行一次到下个月底结束
on schedule every 1 day starts current_timestamp() + interval 3 day ends current_timestamp() + interval 1 month 

//从现在起每天执行，执行3天
on schedule every 1 day ends current_timestamp() + interval 3 day