mysql 定时任务-第一次使用

之前一直用着NAvicat的计划任务来定时备份数据库.昨天一个项目需要定时的执行SQL来清理数据。
发现现有的不支持.于是查询了mysql的定时任务.

查看event是否开启: show variables like ‘%sche%’;
将事件计划开启: set global event_scheduler=1;
关闭事件任务: alter event e_test ON COMPLETION PRESERVE DISABLE;
开户事件任务: alter event e_test ON COMPLETION PRESERVE ENABLE;

简单实例.
创建表 CREATE TABLE test(endtime DATETIME);

创建存储过程test
CREATE PROCEDURE test ()
BEGIN
update examinfo SET endtime = now() WHERE id = 14;
END;

创建event e_test
CREATE EVENT if not exists e_test
on schedule every 30 second
on completion preserve
do call test();
CREATE EVENT if not exists e_test
on schedule every 1 second
on completion preserve
do insert into aa values (now());
每隔30秒将执行存储过程test,将当前时间更新到examinfo表中id=14的记录的endtime字段中去.

 

触发器
delimiter //
CREATE TRIGGER trigger_htmlcache BEFORE INSERT ON t_model
FOR EACH ROW BEGIN
if CURDATE()<NEW.time then
INSERT INTO t_htmlcache(id,url) value(NEW.id,NEW.url);
end if;
END;

最终的demo SQL如下:
show variables like ‘%sche%’;
set global event_scheduler=1;
alter event e_test ON COMPLETION PRESERVE ENABLE;

CREATE PROCEDURE test()
BEGIN
update ACT_RU_TASK t2 inner join
(select * from BPM_PROC_PROCESSRUN where subject=’没有找到项目-没有找到任务名称’) t1 on t1.actInstId = t2.PROC_INST_ID_
set t2.assignee_ = ‘AutoClean’
where t2.execution_id_ = t2.PROC_INST_ID_ and (t2.proc_def_id_ like ‘%cqrxdbMainProcess%’ or t2.proc_def_id_ like ‘%importHisData%’);
END;
CREATE EVENT if not exists `auto_clear_att`
ON SCHEDULE EVERY 1 DAY STARTS ‘2014-03-05 17:44:00’
ON COMPLETION PRESERVE
ENABLE
DO call test();