2016-05-13 回答
转:
mysql的定时任务一般用event(事件)来完成,触发器无法完成。
一、通过mysql的命令行客户端来完成
1、set global event_scheduler =1; //开启event_scheduler
执行这个语句如果出现,可在mysql的配置文档中设置[mysqld]段中添加 event_scheduler=on
如果重启mysql;这种情况下依然出错,error code: 1290. the mysql server is running with the --event-scheduler=disabled
or --skip-grant-tables option so it cannot execute this statement,这个错误是说启动服务器时如果指定了--skip-grant-tables选项,则event_scheduler则自动被设置为disabled。命令行或配置文件的设置都会被覆盖。建议重现安装mysql或是修改启动参数(在系统服务中指定)。
查看event_scheduler状态:show status like '%event%'; 或select @@event_scheduler;
2、create procedure mypro() //创建存储过程
begin
update userinfo set endtime = now() where id = '155';
end;
3、创建event my_enevt,每隔三十秒执行一次
create event if not exists e_test
on schedule every 30 second
on completion preserve
do call mypro();
4、关闭事件
alter event e_test on completion preserve disable;
5、开启事件
alter event e_test on completion preserve enable;
语法:
create event [if not exists] event_name on schedule schedule [on completion
[not] preserve] [enable | disable] [comment 'comment'] do sql_statement;
schedule: at timestamp [+ interval interval] | every interval [starts timestamp]
[ends timestamp] interval: quantity {year | quarter | month | day | hour |
minute | week | second | year_month | day_hour | day_minute | day_second |
hour_minute | hour_second | minute_second}
示例:
1) 首先来看一个简单的例子来演示每秒插入一条记录到数据表
create event e_test_insert on schedule every 1 second do insert into test.aaa values (current_timestamp);
2) 5天后清空test表:
create event e_test on schedule at current_timestamp + interval 5 day do truncate table test.aaa;
3) 2013年7月20日12点整清空test表:
create event e_test on schedule at timestamp '2013-07-20 12:00:00' dotruncate table test.aaa;
4) 每天定时清空test表:
create event e_test on schedule every 1 day do truncate table test.aaa;
5) 5天后开启每天定时清空test表:
create event e_test on schedule every 1 day starts current_timestamp + interval 5 day do truncate table test.aaa;
6) 每天定时清空test表,5天后停止执行:
create event e_test on schedule every 1 day ends current_timestamp + interval 5 day do truncate table test.aaa;
7) 5天后开启每天定时清空test表,一个月后停止执行:
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;
[on completion [not] preserve]可以设置这个事件是执行一次还是持久执行,默认为not preserve
8)
每天定时清空test表(只执行一次,任务完成后就终止该事件):
create event e_test on schedule every 1 day on completion not preserve do truncate table test.aaa;
[comment
comment]可以给该事件加上注释
2>修改事件(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) 将每天清空test表改为5天清空一次:
alter event e_test on schedule every 5 day;
3>删除事件(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;
二、使用第三方工具来创建,推荐使用heidisql网站:http://www.heidisql.com/,免费的开源客户端,由德国程序员ansgar becker开发;这个软件的界面有中文界面。
打开heidisql,右键单击表名,选创建新的的菜单,再选事件即可,很方便,我们可以编辑事件的时间设置和代码。这里定义人填写root@localhost。