mysql的定时任务

定时任务
查看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'*/
    BEGIN
	declare 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; 
			begin

			update 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 = 1
		END 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;
//