④通常情况下,如果一个事件过期已过期,则会被立即删除。但是,create event定义中通过on completion preserve子句可以保留已过期的时间。
默认:ON COMPLETION NOT PRESERVE,也就是不保存
⑤默认情况下,enable on slave,事件一旦创建后就立即开始执行;可以通过disable关键字来禁用该事件。
⑥comment子句用于给事件添加注释。
⑦do子句用于指示事件需要执行的操作,可以是一条SQL语句,也可以是被begin...end包括的语句块,也可以在语句块中调用存储过程。
基本格式:
CREATE EVENT event_name
ON SCHEDULE <schedule>
DO <event_body>;
mysql> create event my_event -> on schedule every 10 second -> do update myschema.mytable set mycol = mycol + 1;
示例:建立一个计划任务,每分钟往表t2中添加数据(当前时间)
mysql> show events;
Empty set (0.02 sec)
mysql> create table t2(id int auto_increment primary key,t_time datetime);
mysql> delimiter $$
mysql> CREATE EVENT e_daily
-> ON SCHEDULE
-> EVERY 1 MINUTE
-> COMMENT 'Saves total number of sessions then clears the table each day'
-> DO
-> BEGIN
-> INSERT INTO t2 values (null,current_timestamp);
-> END $$
mysql> delimiter ;
……过一段时间……
mysql> select * from t2;
+----+---------------------+
| id | t_time |
+----+---------------------+
| 1 | 2017-04-04 18:02:38 |
| 2 | 2017-04-04 18:03:38 |
| 3 | 2017-04-04 18:04:38 |
…………
3、查看新建的计划任务
mysql> select EVENT_NAME,LAST_EXECUTED from information_schema.EVENTS;
+------------+---------------------+
| EVENT_NAME | LAST_EXECUTED |
+------------+---------------------+
| e_daily | 2017-04-04 18:02:38 |
+------------+---------------------+
mysql> show events\G;
*************************** 1. row ***************************
Db: db1
Name: e_daily
Definer: root@localhost
Time zone: SYSTEM
Type: RECURRING
Execute at: NULL
Interval value: 1
Interval field: MINUTE
Starts: 2017-04-04 18:02:38
Ends: NULL
Status: ENABLED
Originator: 0
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: latin1_swedish_ci
[root@linuxidc ~]# tail -1 /var/log/mysqld.log
2017-04-04T08:01:16.311514Z 12 [Note] Event Scheduler: scheduler thread started with id 12
通过查看MySQL日志,查看执行情况 。
4、修改alter event