mysql> show variables like '%event%';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| event_scheduler | OFF |
+-----------------+-------+
1 row in set (0.00 sec)
mysql> set global event_scheduler = on;
Query OK, 0 rows affected (0.00 sec)
mysql> use event;
Database changed
例子:
我们来创建一个简单的文章表:
mysql> create table article (id serial,title varchar(64) not null, author_name varchar(64),content mediumtext not null, create_time datetime not null,update_time datetime not null);
Query OK, 0 rows affected (0.01 sec)
以及统计表:
mysql> create table report (id int not null auto_increment primary key, r_date date not null,aid int not null,total int not null);
Query OK, 0 rows affected (0.01 sec)
mysql> 插入测试数据。。。
我们来建立一个存储过程。
mysql> delimiter ||
mysql> create procedure sp_report()
-> begin
-> insert into report(r_date,aid,total) select date(update_time) as r_date, id,count(1) from article group by date(create_time) order by r_date asc;
-> end||
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
创建EVENT;
在一分钟后执行这个存储过程。
mysql> create event report_dawn on schedule at date_add(now(),interval 1 minute) on completion preserve do call sp_report();
Query OK, 0 rows affected (0.00 sec)
mysql> show processlist;
| 7 | event_scheduler | localhost | NULL | Daemon | 5 | Waiting for next activation | NULL |
mysql> select * from report;
Empty set (0.00 sec)
察看现在的EVENT
mysql> show create event report_dawn\G
*************************** 1. row ***************************
Event: report_dawn
sql_mode:
time_zone: SYSTEM
Create Event: CREATE EVENT `report_dawn` ON SCHEDULE AT '2008-03-21 15:46:57' ON COMPLETION PRESERVE DISABLE DO call sp_report()
character_set_client: latin1
collation_connection: latin1_swedish_ci
Database Collation: utf8_general_ci
1 row in set (0.00 sec)