前言
今天突然看到了mysql event事件的定时执行,就想着试一下.
内容
1.首先我们查看一下是否启用事件调度器
mysql> show variables like 'event_scheduler';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| event_scheduler | OFF |
+-----------------+-------+
1 row in set
2.进行启用
mysql> set global event_scheduler = 1;
Query OK, 0 rows affected
mysql> show variables like 'event_scheduler';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| event_scheduler | ON |
+-----------------+-------+
1 row in set
3.设置一个每秒执行
create event
if not exists //如果不存在,再创建
eventsName //事件名称
on schedule
every 1 second //每秒执行
do insert into user values(1); //do 后面加要做的事情
4.查看user表
mysql> select * from user; //使用创建events命令之前
Empty set
mysql> select * from user; //使用创建events命令之后 每隔一秒进行添加数据
+--------+
| gender |
+--------+
| 1 |
| 1 |
| 1 |
| 1 |
| 1 |
| 1 |
| 1 |
| 1 |
| 1 |
| 1 |
| 1 |
| 1 |
| 1 |
| 1 |
| 1 |
| 1 |
| 1 |
| 1 |
| 1 |
| 1 |
| 1 |
| 1 |
| 1 |
| 1 |
+--------+
24 rows in set
5.展示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 |
+-------+------------+----------------+-----------+-----------+------------+----------------+----------------+---------------------+------+---------+------------+----------------------+----------------------+--------------------+
| study | eventsName | root@localhost | SYSTEM | RECURRING | NULL | 1 | SECOND | 2021-03-16 11:05:43 | NULL | ENABLED | 1 | utf8 | utf8_general_ci | utf8_general_ci |
+-------+------------+----------------+-----------+-----------+------------+----------------+----------------+---------------------+------+---------+------------+----------------------+----------------------+--------------------+
1 row in set
5.删除event事件
mysql> drop event eventsName;
Query OK, 0 rows affected
mysql> show events;
Empty set
期间 155 rows in set user 数据已经有155条
名词解释:
event_name :创建的event名字(唯一确定的)。
ON SCHEDULE:计划任务。
schedule: 决定event的执行时间和频率(注意时间一定要是将来的时间,过去的时间会出错),有两种形式 AT和EVERY。
[ON COMPLETION [NOT] PRESERVE]: 可选项,默认是ON COMPLETION NOT PRESERVE 即计划任务执行完毕后自动drop该事件;ON COMPLETION PRESERVE则不会drop掉。
[COMMENT ‘comment’] :可选项,comment 用来描述event;相当注释,最大长度64个字节。
[ENABLE | DISABLE] :设定event的状态,默认ENABLE:表示系统尝试执行这个事件, DISABLE:关闭该事情,可以用alter修改
DO event_body: 需要执行的sql语句(可以是复合语句)。CREATE EVENT在存储过程中使用时合法的。
结束 over