MySQL:EVENT

MySQL:EVENT
Event类似于Oracle的Job/Schedule,原来没有关注过这个东东.最近开发人员用到这个,顺便回顾一下Event.有了Event让MySQL可以自动的执行数据汇总、定时表数据迁移、清除表数据等功能，类似Linux  Crontab,但是有没有Crontab好用待以后研究.
要用Event首先查看是否开启
mysql> Select @@event_scheduler;
+-------------------+
| @@event_scheduler |
+-------------------+
| OFF               |
+-------------------+
1 row in set (0.00 sec)
如果返回OFF,则需要执行下面的命令启动
mysql> SET GLOBAL event_scheduler = ON;
Query OK, 0 rows affected (0.00 sec)

mysql> Select @@event_scheduler;
+-------------------+
| @@event_scheduler |
+-------------------+
| ON                |
+-------------------+
1 row in set (0.00 sec)
但是在每次重启MySQL的时候并不会自动开启Event,那么如何自动启动该功能呢?
方法一：编辑my.cnf 文件，在 [mysqld] 的下面加入如下行
event_scheduler=1
方法二：启动 mysql的时候增加--event_scheduler=1
mysql start --event_scheduler=1

创建Event语法如下:
CREATE
[DEFINER = { user | CURRENT_USER }]
EVENT
[IF NOT EXISTS]
event_name
ON SCHEDULE schedule
[ON COMPLETION [NOT] PRESERVE]    --设置这个表示Event是执行一次还是持久执行,默认为 NOT PRESERVE
[ENABLE | DISABLE | DISABLE ON SLAVE]  --设置该Event创建后状态是否开启或关闭，默认为 ENABLE
[COMMENT ''comment'']  --给Event加注释
DO sql_statement;

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}

以下举几个演示一下:
mysql> use test;
Database changed
mysql> show tables;
Empty set (0.00 sec)
mysql> create table test (id varchar(200) not null);
Query OK, 0 rows affected (0.04 sec) 
mysql> show create table test\G
*************************** 1. row ***************************
       Table: test
Create Table: CREATE TABLE `test` (
  `id` varchar(200) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

创建存储过程向test表插入数值"1"
mysql> 
mysql> DELIMITER $$
mysql> create procedure t_test()
    -> begin
    -> insert into test values ('1');
    -> end $$
Query OK, 0 rows affected (0.00 sec)

创建Event
每隔1秒调用t_test存储过程
mysql> CREATE EVENT IF NOT EXISTS event_test
    -> ON SCHEDULE EVERY 1 SECOND
    -> ON COMPLETION PRESERVE   
    -> DO CALL t_test();
Query OK, 0 rows affected (0.00 sec)

关闭Event事件
mysql> ALTER EVENT event_test ON COMPLETION PRESERVE DISABLE;
Query OK, 0 rows affected (0.00 sec) 

开启Event事件
mysql> ALTER EVENT event_test ON COMPLETION PRESERVE ENABLE; 
Query OK, 0 rows affected (0.00 sec)

查看结果
mysql> select count(*) from test;
+----------+
| count(*) |
+----------+
|      248 |
+----------+
1 row in set (0.00 sec)

过几秒继续查询
mysql> select count(*) from test;
+----------+
| count(*) |
+----------+
|      252 |
+----------+
1 row in set (0.00 sec)

OK.目的达成

那么在看看其它例子
例如创建一个每10秒清空test表数据的任务
CREATE EVENT T_DELETE_test ON SCHEDULE EVERY 10 SECOND DO DELETE FROM test ;
10秒之后的结果
mysql> select count(*) from test;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

7天后每天定时清空test表，一个月后停止执行
CREATE EVENT event_test ON SCHEDULE EVERY 1 DAY STARTS CURRENT_TIMESTAMP + INTERVAL 7 DAY ENDS CURRENT_TIMESTAMP + INTERVAL 1 MONTH DO TRUNCATE TABLE test;

每天定时清空test表
CREATE EVENT event_test ON SCHEDULE EVERY 1 DAY ON COMPLETION NOT PRESERVE DO TRUNCATE TABLE test;

修改Event
ALTER EVENT event_name
[ON SCHEDULE schedule]
[RENAME TO new_event_name]
[ON COMPLETION [NOT] PRESERVE]
[COMMENT ''comment'']
[ENABLE | DISABLE]
[DO sql_statement]

临时关闭Event
ALTER EVENT event_test DISABLE;
开启Event
ALTER EVENT event_test ENABLE;
将每天清空test表改为3天清空一次:
ALTER EVENT event_test ON SCHEDULE EVERY 3 DAY;

删除Event

语法很简单,如下所示:

DROP EVENT [IF EXISTS] event_name

例如删除前面创建的 event_test 事件

DROP EVENT event_test;

当然前提是这个事件存在，否则会产生 ERROR 1513 (HY000): Unknown event 错误，因此最好加上 IF EXISTS

DROP EVENT IF EXISTS event_test;