备注:测试数据库版本为MySQL 8.0
这个blog我们来聊聊MySQL事件。
MySQL的事件功能同Oracle里面的JOB，作为定时任务使用。
一.语法相关
创建事件语法:
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}
event_name :事件的名称
[ON COMPLETION [NOT] PRESERVE]： 可选项，默认是ON，运行后不删除，如果是ON COMPLETION NOT PRESERVE 则会删除。
[COMMENT 'comment'] ：可选项，事件的备注信息。
[ENABLE | DISABLE] ：设定event的状态，默认ENABLE：表示系统尝试执行这个事件， DISABLE：关闭该事情，可以用alter修改
DO event_body: 需要执行的sql，代码块，存储过程
调度时间相关:
YEAR -- 年
QUARTER -- 季度
MONTH -- 月
DAY -- 天
HOUR -- 小时
MINUTE -- 分钟
WEEK -- 周
SECOND -- 秒
YEAR_MONTH -- 年月
DAY_HOUR -- 天小时
DAY_MINUTE -- 天分钟
DAY_SECOND -- 天秒
HOUR_MINUTE -- 小时分钟
HOUR_SECOND -- 小时秒
MINUTE_SECOND -- 分钟秒
二.开启和关闭事件调度
MySQL 8.0 事件调度默认是开启的
查看事件调度是否开启:
mysql> show variables like '%event_scheduler%';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| event_scheduler | ON |
+-----------------+-------+
1 row in set, 1 warning (0.01 sec)
如果没有开启，可以通过如下命令开启:
SET GLOBAL event_scheduler = ON;
SET @@global.event_scheduler = ON;
SET GLOBAL event_scheduler = 1;
SET @@global.event_scheduler = 1;
如果需要关闭，可以通过如下命令进行关闭:
SET GLOBAL event_scheduler = OFF;
SET @@global.event_scheduler = OFF;
SET GLOBAL event_scheduler = 0;
SET @@global.event_scheduler = 0;
开启事件
alter event event_name(事件名称) ON COMPLETION PRESERVE ENABLE;
关闭事件
alter event event_name(事件名称) ON COMPLETION PRESERVE DISABLE;
删除事件
DROP EVENT [IF EXISTS] event_name(事件名称)
三.案例
1.创建一个指定时间执行的事件
也可以把at now()修改为执行时间
例如: at '2020-05-25 23:00:00'
事件代码:
create event evt_now_userinfo_bak
on schedule
at now()
do create table user_info_bak as select * from user_info;
执行记录:
mysql> create event evt_now_userinfo_bak
-> on schedule
-> at now()
-> do create table user_info_bak as select * from user_info;
Query OK, 0 rows affected (0.01 sec)
mysql> select * from user_info;
+----+--------+--------------------+
| id | name | id_number |
+----+--------+--------------------+
| 2 | 李四 | 420123199001011235 |
| 3 | 王五 | 420123199001011236 |
+----+--------+--------------------+
2 rows in set (0.00 sec)
mysql> select * from user_info_bak;
+----+--------+--------------------+
| id | name | id_number |
+----+--------+--------------------+
| 2 | 李四 | 420123199001011235 |
| 3 | 王五 | 420123199001011236 |
+----+--------+--------------------+
2 rows in set (0.00 sec)
mysql>
2.创建基于调度的事件
基于scheduler的事件。
创建每分钟执行一次的事件
create table t(id int,credate datetime);
create event evt_test2
on schedule every 1 minute
starts now()
do insert into t(id,credate) select ifnull(max(id),0),now() from t;
执行记录:
mysql> create event evt_test2
-> on schedule every 1 minute
-> starts now()
-> do insert into t(id,credate) select ifnull(max(id),0),now() from t;
Query OK, 0 rows affected (0.03 sec)
mysql>
mysql> select * from t;
+------+---------------------+
| id | credate |
+------+---------------------+
| 0 | 2020-05-25 23:08:07 |
+------+---------------------+
1 row in set (0.00 sec)
mysql> select * from t;
+------+---------------------+
| id | credate |
+------+---------------------+
| 0 | 2020-05-25 23:08:07 |
+------+---------------------+
1 row in set (0.00 sec)
mysql>
mysql>
mysql> select * from t;
+------+---------------------+
| id | credate |
+------+---------------------+
| 0 | 2020-05-25 23:08:07 |
| 0 | 2020-05-25 23:09:07 |
+------+---------------------+
2 rows in set (0.00 sec)
查看事件:
mysql> show events\G;
*************************** 1. row ***************************
Db: test
Name: evt_test2
Definer: root@localhost
Time zone: SYSTEM
Type: RECURRING
Execute at: NULL
Interval value: 1
Interval field: MINUTE
Starts: 2020-05-25 23:08:07
Ends: NULL
Status: ENABLED
Originator: 1
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
Database Collation: utf8mb4_0900_ai_ci
1 row in set (0.00 sec)