湘乡彭于晏学习前端
MySQL之event(定时任务)
2023-04-20mysql录2058

在前面的文章中有讲到docker部署mysql并挂载，同时配置文件时默认启动了event定时功能

一、事件（EVENT）是干什么的
自MySQL5.1.6起，增加了一个非常有特色的功能 - 事件调度器（Event Scheduler），可以用做定时执行某些特定任务（例如：删除记录、数据统计报告、数据备份等等），来取代原先只能由操作系统的计划任务来执行的工作。
值得一提的是MySQL的事件调度器可以精确到每秒钟执行一个任务，而操作系统的计划任务（如：Linux的cron）只能精确到每分钟执行一次。对于一些对数据实时性要求比较高的应用（例如：股票、赔率、比分等）就非常适合。
事件有时也可以称为临时触发器（temporal triggers），因为事件调度器是基于特定时间周期触发来执行某些任务，而触发器（Triggers）是基于某个表所产生的事件触发的，区别也就在这里。

二、开启“事件”功能

使用“事件”功能之前必须确保event_scheduler已开启

1.查询功能是否开启

 
复制


-- 方法一
SELECT @@event_scheduler;
-- 方法二
SHOW VARIABLES LIKE 'event%';

显示 “ON”说明功能已开启；如下图：

复制


mysql> SELECT @@event_scheduler;
+-------------------+
| @@event_scheduler |
+-------------------+
| ON                |
+-------------------+
1 row in set (0.00 sec)

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

2、开启、关闭功能命令

 
复制


-- 开启功能命令：
SET GLOBAL event_scheduler = 1;
SET GLOBAL event_scheduler = ON;
-- 关闭功能命令：
SET GLOBAL event_scheduler = 0;
SET GLOBAL event_scheduler = OFF;

当然，通过命令开启当数据库重启后会自动关闭；

持久化开启方式：将event_scheduler=ON写到my.cnf配置文件中，如下图：

复制


[mysqld]  
event_scheduler=ON
default-time-zone='+08:00'

常用的事件操作命令：

  • 关闭指定事件： ALTER EVENT 事件名称 ON COMPLETION PRESERVE DISABLE;
  • 开启指定事件：ALTER EVENT 事件名称 ON COMPLETION PRESERVE ENABLE;
  • 查看当前事件：SHOW EVENTS ;

三、事件 - SQL语法创建

下面是EVENT事件创建语句，乍一看挺复杂的，让我们拆开解读一下

复制


CREATE EVENT [IFNOT EXISTS] event_name
    　　 ON SCHEDULE schedule(调度时间设置)
    　　 [ON COMPLETION [NOT] PRESERVE]
    　　 [ENABLE | DISABLE | DISABLE ON SLAVE]
    　　 [COMMENT 'comment']
    　　 DO sql_statement;
SQL语法 说明
DEFINER 可选项，给指定用户使用权限
IF NOT EXISTS 可选项，用于判断要创建的事件是否存在
EVENT event_name 必选项，指定事件名称，event_name的最大长度为64个字符，如果为指定event_name，则默认为当前的MySQL用户名（不区分大小写）
ON SCHEDULE schedule 必选项，这里的schedule用于定义执行的时间和时间间隔，在下面我们详细讲解
ON COMPLETION [NOT] PRESERVE 可选项，配置事件执行完一次后的处理方式,当为on completion preserve 的时候,当event到期了,event会被disable,但是该event还是会存在当为on completion not preserve的时候,当event到期的时候,该event会被自动删除掉.
ENABLE、DISABLE、DISABLE ON SLAVE 可选项，用于指定事件的一种属性。ENABLE表示该事件是开启的，也就是调度器检查事件是否必选调用；DISABLE表示该事件是关闭的，也就是事件的声明存储到目录中，但是调度器不会检查它是否应该调用；DISABLE ON SLAVE表示事件在从机中是关闭的。如果不指定这三个选择中的任意一个，则在一个事件创建之后，它立即变为活动的
COMMENT ‘comment’ 可选项，用于定义事件的注释
DO event_body 必选项，用于指定事件启动时所要执行的代码。可以是任何有效的SQL语句、存储过程或者一个计划执行的事件。如果包含多条语句，可以使用BEGIN…END复合结构

schedule 调度时间配置语法：调度时间配置包括AT 和 EVERY两种

复制


AT timestamp [+ INTERVAL interval] ...
  | EVERY interval
    [STARTS timestamp [+ INTERVAL interval] ...]
    [ENDS timestamp [+ INTERVAL interval] ...]


-- INTERVAL中包含的时间单位如下:
{YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |
 WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE |
 DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}

1、创建 - 单次定时执行事件

 
复制


AT TIMESTAMP 时间字符串 [+ INTERVAL INTERVAL]
  • AT TIMESTAMP表示该事件只执行一次
  • TIMESTAMP表示一个具体的时间点，后面可以加上一个时间间隔，表示在这个时间间隔后事件发生。
  • [+ INTERVAL INTERVAL]表示延迟触发时间；

  需要注意的是，TIMESTAMP和具体字符串连用，如果不是具体字符串（如CURRENT_TIMESTAMP取当前时间等），则不加TIMESTAMP;

示例 1：往demo_1119表中插入一行数据，执行时间：2020-11-20 00:00:00

 
复制


CREATE EVENT demo_event2 
ON SCHEDULE AT TIMESTAMP '2020-11-20 00:00:00' 
DO INSERT INTO `demo_1119` (`id`, `name`, `createTime`) VALUES (null, 'Anyc', NOW())

结果查询：

复制


mysql> select * from demo_1119;
+-----+-----------+---------------------+
| id  | name      | createTime          |
+-----+-----------+---------------------+
| 145 | Anyc      | 2020-11-20 00:00:00 |
+-----+-----------+---------------------+
9 rows in set (0.00 sec)

示例 2：往demo_1119表中插入一行数据，执行时间：当前时间往后5个小时；

 
复制


CREATE EVENT demo_event2 
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 5 HOUR
DO INSERT INTO `demo_1119` (`id`, `name`, `createTime`) VALUES (null, 'Anyc', NOW())

2、创建 - 循环定时执行事件

 
复制


EVERY INTERVAL [STARTS TIMESTAMP] [ENDS TIMESTAMP]
  • EVERY表示循环执行该事件
  • 其中STARTS子句用于指定开始时间
  • ENDS子句用于指定结束时间。

示例 1：从当前开始，每10秒往demo_1119表中插入一行数据

 
复制


CREATE EVENT demo_event3 
ON SCHEDULE EVERY 10 SECOND 
ON COMPLETION PRESERVE 
DO INSERT INTO `demo_1119` (`id`, `name`, `createTime`) VALUES (null, 'Anyc', NOW())

复制


mysql> select * from demo_1119;
+-----+-----------+---------------------+
| id  | name      | createTime          |
+-----+-----------+---------------------+
| 145 |   Anyc    | 2023-04-20 11:10:39 |
| 146 |   Anyc    | 2023-04-20 11:10:49 |
| 147 |   Anyc    | 2023-04-20 11:10:59 |
| 148 |   Anyc    | 2023-04-20 11:11:09 |
| 149 |   Anyc    | 2023-04-20 11:11:19 |
| 150 |   Anyc    | 2023-04-20 11:11:29 |
| 151 |   Anyc    | 2023-04-20 11:11:39 |
+-----+-----------+---------------------+
9 rows in set (0.00 sec)

示例 2：从2020-11-20 12:00:00开始，每10分钟往demo_1119表中插入一行数据

 
复制


CREATE EVENT demo_event4 
ON SCHEDULE EVERY 10 MINUTE STARTS '2020-11-20 12:00:00' 
ON COMPLETION PRESERVE 
DO INSERT INTO `demo_1119` (`id`, `name`, `createTime`) VALUES (null, '陈哈哈', NOW())

复制


mysql> select * from demo_1119;
+-----+-----------+---------------------+
| id  | name      | createTime          |
+-----+-----------+---------------------+
| 152 |   Anyc    | 2023-04-20 12:00:00 |
| 153 |   Anyc    | 2023-04-20 12:10:00 |
| 154 |   Anyc    | 2023-04-20 12:20:00 |
| 155 |   Anyc    | 2023-04-20 12:30:00 |
| 156 |   Anyc    | 2023-04-20 12:40:00 |
+-----+-----------+---------------------+
5 rows in set (0.00 sec)

示例 3：从当前时间一小时后开始，每10分钟往demo_1119表中插入一行数据；已经测试过可用，我就不再贴冗余查询的数据咯~

 
复制


CREATE EVENT demo_event5 
ON SCHEDULE EVERY 10 MINUTE STARTS CURRENT_TIMESTAMP+INTERVAL 1 HOUR 
ON COMPLETION PRESERVE 
DO INSERT INTO `demo_1119` (`id`, `name`, `createTime`) VALUES (null, 'Anyc', NOW())

示例4：从当前时间一天后开始，每1小时往demo_1119表中插入一行数据，三天后结束

 
复制


CREATE EVENT demo_event5 
ON SCHEDULE EVERY 1 HOUR 
STARTS CURRENT_TIMESTAMP+INTERVAL 1 DAY 
ENDS CURRENT_TIMESTAMP+INTERVAL 3 DAY 
ON COMPLETION PRESERVE 
DO INSERT INTO `demo_1119` (`id`, `name`, `createTime`) VALUES (null, 'Anyc', NOW())

示例5：每天零点定时清一下demo_1119表数据

 
复制


CREATE EVENT demo_event5 
ON SCHEDULE EVERY 1 DAY STARTS '2023-4-20 00:00:00' 
ON COMPLETION PRESERVE 
DO TRUNCATE table `demo_1119`

3、修改事件

修改事件语句跟创建语句如出一辙，语法如下：

复制


ALTER EVENT event_name
    　　 [ONSCHEDULE schedule]
    　　 [old_NAME TO new_NAME]
    　　 [ON COMPLETION [NOT] PRESERVE]
    　　 [COMMENT 'comment']
    　　 [ENABLE | DISABLE]
    　　 [DO sql_statement]
  • 关闭事件任务： ALTER EVENT 事件名称 ON COMPLETION PRESERVE DISABLE;
  • 开启事件任务：ALTER EVENT 事件名称 ON COMPLETION PRESERVE ENABLE;

4、删除事件

 
复制


DROP EVENT [IF EXISTS] event_name

5.自己在项目中遇到的一些例子

定时从中间数据库拿数据并推至业务数据库（全量更新）

  1. demo（每18秒钟将清空表1，查询表2并插入表1）
复制


CREATE EVENT full_update
ON SCHEDULE
EVERY 18 SECOND
ON COMPLETION PRESERVE
COMMENT '新增用户信息定时任务'
DO
BEGIN

    TRUNCATE TABLE ncu.xs_xsjbsj;   # 清空表

    INSERT INTO ncu.xs_xsjbsj SELECT * FROM reservation.xs_xsjbsj;   # 插入全量数据

END;
  1. 每天凌晨三点同步b数据
复制


create event full_update_b
    #每天凌晨三点同步b数据
    on schedule every 1 day starts '2016-12-30 03:00:00'
    on completion preserve enable
    comment '同步b数据'
    do
    BEGIN

    TRUNCATE TABLE db1.b;   # 清空表

    INSERT INTO db1.b SELECT * FROM db2.b;   # 插入全量数据

END;
  1. 每天凌晨三点十分同步y数据
复制


create event full_update_y
    #每天凌晨三点十分同步y数据
    on schedule every 1 day starts '2016-12-30 03:10:00'
    on completion preserve enable
    comment '同步y数据'
    do
    BEGIN

    TRUNCATE TABLE db1.y;   # 清空表

    INSERT INTO db1.y SELECT * FROM db2.y;   # 插入全量数据

END;
  1. 每天凌晨三点二十同步j数据
复制


create event full_update_j
    #每天凌晨三点二十同步j数据
    on schedule every 1 day starts '2016-12-30 03:20:00'
    on completion preserve enable
    comment '同步j数据'
    do
    BEGIN

    TRUNCATE TABLE db1.j;   # 清空表

    INSERT INTO db1.j SELECT * FROM db2.j;   # 插入全量数据

END;
  1. 每天凌晨三点三十同步x数据
复制


create event full_update_x
    #每天凌晨三点三十同步x数据
    on schedule every 1 day starts '2016-12-30 03:30:00'
    on completion preserve enable
    comment '同步x数据'
    do
    BEGIN

    TRUNCATE TABLE db1.x;   # 清空表

    INSERT INTO db1.x SELECT * FROM db2.x;   # 插入全量数据

END;
湘乡彭于晏，专注学习web开发
img
img
img
img
文章目录
一、事件（EVENT）是干什么的
自MySQL5.1.6起，增加了一个非常有特色的功能 - 事件调度器（Event Scheduler），可以用做定时执行某些特定任务（例如：删除记录、数据统计报告、数据备份等等），来取代原先只能由操作系统的计划任务来执行的工作。
值得一提的是MySQL的事件调度器可以精确到每秒钟执行一个任务，而操作系统的计划任务（如：Linux的cron）只能精确到每分钟执行一次。对于一些对数据实时性要求比较高的应用（例如：股票、赔率、比分等）就非常适合。
事件有时也可以称为临时触发器（temporal triggers），因为事件调度器是基于特定时间周期触发来执行某些任务，而触发器（Triggers）是基于某个表所产生的事件触发的，区别也就在这里。
二、开启“事件”功能
使用“事件”功能之前必须确保event_scheduler已开启
1.查询功能是否开启
2、开启、关闭功能命令
三、事件 - SQL语法创建
1、创建 - 单次定时执行事件
2、创建 - 循环定时执行事件
3、修改事件
4、删除事件
5.自己在项目中遇到的一些例子
© 2023 By 湘乡彭于晏.本站未经授权禁止转载.
湘ICP备2021021086号.湘公网安备43038102001117号
xxpyy.top