mysql的定时任务

来源：互联网发布：淘宝卖的最奇葩的东西编辑：程序博客网时间：2024/05/01 07:05

定时任务
查看event是否开启: show variables like '%sche%';
将事件计划开启: set global event_scheduler=1;

关闭事件任务:

 alter event jcj_cd_event ON COMPLETION PRESERVE DISABLE;

开户事件任务:

alter event jcj_cd_event ON COMPLETION PRESERVE ENABLE;

简单实例.

需求目标：将表tbl_name_log表中重复的名字只保留一个

1、创建表 CREATE TABLE tbl_name_log(id,name);--这个地方大家自己建立下

2、创建记录处理过程表

CREATE TABLE `tbl_clean` (  `last_id` bigint(20) unsigned zerofill NOT NULL,  `modify_time` datetime NOT NULL) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;/*Data for the table `tbl_clean` */insert  into `tbl_clean`(`last_id`,`modify_time`) values (00000000000000000001,'2013-05-12 08:52:56');

3、创建存储过程clean_data，这里假定数据库名称是jcj，采用游标一次取得25个数据进行清理操作

DELIMITER $$DROP PROCEDURE IF EXISTS `jcj`.`clean_data`$$CREATE PROCEDURE `jcj`.`clean_data`()    /*LANGUAGE SQL    | [NOT] DETERMINISTIC    | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }    | SQL SECURITY { DEFINER | INVOKER }    | COMMENT 'string'*/    BEGINdeclare p_num int;declare stopFlag int;DECLARE t_name varchar(40);        DECLARE data_cursor CURSOR FOR select id,name FROM tbl_name_log where id>(select last_id  from tbl_clean) limit 0,25;DECLARE CONTINUE HANDLER FOR NOT FOUND set stopFlag=1; OPEN data_cursor;REPEAT FETCH data_cursor INTO p_num,t_name; beginupdate tbl_clean set last_id=p_num,modify_time=now();delete from tbl_name_log where name=t_name and id<>p_num;end; UNTIL stopFlag = 1END REPEAT; CLOSE data_cursor;     END$$DELIMITER ;

4、创建event jcj_cd_event

 CREATE EVENT if not exists jcj_cd_event  on schedule every 1 MINUTE  on completion preserve  do call clean_data();

每隔1分钟将执行存储过程clean_data,将当前时间以及最新处理的id更新到tbl_clean表中，同时清理了tbl_name_log表中数据

btw：提供一个创建触发器的例子

5、触发器

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;//

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;
//