首页 / MYSQL / mysql的Event
mysql的Event
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了mysql的Event,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含4176字,纯文字阅读大概需要6分钟。
内容图文
![mysql的Event](/upload/InfoBanner/zyjiaocheng/881/35dc10dbccf24c43b8fb907351b11360.jpg)
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;
然后创建一个Event
create event insert_minute
on schedule
every 1 minute
do call aminute(floor(rand()*10000000+1));
运行结果
查看Event
查看当前database的Event
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_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
内容总结
以上是互联网集市为您收集整理的mysql的Event全部内容,希望文章能够帮你解决mysql的Event所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。