摘要: Mysql的event schedule可以让你设置你的mysql数据库在某个时间段执行你想要的动作 create event test1 on schedule every 1 day starts '2007-09-01 12:00:00' on completion not preserve do in ...
| 1)首先来看一个简单的例子来演示每秒插入一条记录到数据表: use test create table aaa(timeline timestamp); create event e_test_insert on schedule every 1 second do insert into test.aaa values(current_timestamp); 等待三秒,再执行查询看看: mysql>select * from test.aaa; 就可以看到有三条数据存在 2)5天后清空aaa表: create event e_test on schedule at current_timestamp+interval 5 day do truncate table test.aaa; 3)2007年7月20日12点整清空aaa表: create event e_test on schedule at timestamp '2007-07-20 12:00:00' do truncate table test.aaa; 4)每天定时清空aaa表:---执行之后,是指每天当前创建时间执行该event create event e_test on schedule every 1 day do truncate table test.aaa; 5)5天后开启每天定时清空aaa表: create event e_test on schedule every 1 day starts current_timestamp +interval 5 day do truncate table test.aaa; 6)每天定时清空aaa表,5天后停止执行: create event e_test on schedule every 1 day ends current_timestamp + interval 5 day do truncate table test.aaa; 7)5天后开启每天定时清空aaa表,一个月后停止执行 create event e_test on schedule every 1 day starts current_timestamp + interval 5 day ends current_timestamp + interval 1 month do truncate table test.aaa; 三,修改事件(alter event) alter event event_name [on schedule schedule] [rename to new_event_name] [on completion [not] preserve] [comment 'comment'] [enable|disable] [do sql_statement] 1)临时关闭事件 alter event e_test disable 2)开启事件 alter event e_test enable 3)将每天清空aaa表修改成每5天清空一次 alter event e_test on schedule every 5 day; 四,删除事件(drop event) 语法很简单,如下所示: drop event [if exists] event_name 例如删除前面创建的e_test事件 drop event e_test 当前前提是这个事件存在,否则会产生error 1513(HY000):unknown event错误,因此最好加上if exists drop event if exists e_test 另外当在my.ini或者my.cnf中添加了event_scheduler=1参数,那么在mysql启动之后,存在的event还是会继续运行. |
