mysql event type_mysql的Event

mysql的Event

Event简介

Event是mysql中的一个事件，和触发器类似，触发器是在某条sql语句执行后可能会触发，而Event是每隔一段时间或某个特定的时间点执行，可以精确到秒。

准备

在创建Event前，需要将mysql中的event_scheduler属性设置为ON。

使用命令mysql> show variables like "%event_scheduler%";

+-----------------+-------+

| Variable_name | Value |

+-----------------+-------+

| event_scheduler | OFF |

+-----------------+-------+

1 row in set (0.19 sec)

mysql> set global event_scheduler = on;

Query OK, 0 rows affected (0.04 sec)

mysql> show variables like "%event_scheduler%";

+-----------------+-------+

| Variable_name | Value |

+-----------------+-------+

| event_scheduler | ON |

+-----------------+-------+

1 row in set (0.06 sec)

创建Event

语法格式create event event_name on schedule

[at time][every interval]

do event_body;

interval:

quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |

WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE |

DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}

示例：每1分钟在ADMIN表中插入一条数据。

首先创建一个存储过程delimiter //

create procedure aminute(in in_id int)

begin

insert into ADMIN(id,user_id,traveldate,fee,days) values(in_id,@@hostname,now(),100,10);

end//

delimiter;

然后创建一个Eventcreate event insert_minute

on schedule

every 1 minute

do call aminute(floor(rand()*10000000+1));

运行结果

查看Event

查看当前database的Eventmysql> show events;

+-----+---------------+---------+-----------+-----------+------------+----------------+----------------+---------------------+------+---------+------------+----------------------+----------------------+--------------------+

| Db | Name | Definer | Time zone | Type | Execute at | Interval value | Interval field | Starts | Ends | Status | Originator | character_set_client | collation_connection | Database Collation |

+-----+---------------+---------+-----------+-----------+------------+----------------+----------------+---------------------+------+---------+------------+----------------------+----------------------+--------------------+

| DB1 | insert_minute | root@% | SYSTEM | RECURRING | NULL | 1 | MINUTE | 2019-11-26 10:32:58 | NULL | ENABLED | 1 | utf8mb4 | utf8mb4_general_ci | utf8_general_ci |

+-----+---------------+---------+-----------+-----------+------------+----------------+----------------+---------------------+------+---------+------------+----------------------+----------------------+--------------------+

1 row in set (0.73 sec)

mysql>

修改Event

使用Alter命令alter event event_name on schedule

[at time][every interval][rename to newname]

do event_body;

比如，将上面的示例修改为每秒插入一条，并修改Event的名称alter event insert_minute on schedule every 1 second rename to insert_second do call aminute(floor(rand()*10000000+1));

运行结果

删除Event

使用drop命令drop event event_name;

示例：mysql> show events;

+-----+---------------+---------+-----------+-----------+------------+----------------+----------------+---------------------+------+---------+------------+----------------------+----------------------+--------------------+

| Db | Name | Definer | Time zone | Type | Execute at | Interval value | Interval field | Starts | Ends | Status | Originator | character_set_client | collation_connection | Database Collation |

+-----+---------------+---------+-----------+-----------+------------+----------------+----------------+---------------------+------+---------+------------+----------------------+----------------------+--------------------+

| DB1 | insert_second | root@% | SYSTEM | RECURRING | NULL | 1 | SECOND | 2019-11-26 10:49:20 | NULL | ENABLED | 1 | utf8mb4 | utf8mb4_general_ci | utf8_general_ci |

+-----+---------------+---------+-----------+-----------+------------+----------------+----------------+---------------------+------+---------+------------+----------------------+----------------------+--------------------+

1 row in set (0.15 sec)

mysql> drop event insert_second;

Query OK, 0 rows affected (0.09 sec)

mysql> show events;

Empty set