MySQL的事件就像Linux系统上的定时任务,按照设置的时间或者间隔时间执行设置好的任务。
如果用SQLyog一类的写存储过程、触发器或者事件会省事一些,例如SQLyog就会生成一个大致的模板:
1 DELIMITER $$ 2 CREATE EVENT `report`.`monitor_user4cx` ON SCHEDULE EVERY 15 MINUTE DO 3 BEGIN 4 DECLARE cx_id INT(10); 5 DECLARE t_query VARCHAR(500); 6 DECLARE done INT DEFAULT FALSE; 7 DECLARE cur CURSOR FOR SELECT id FROM information_schema.PROCESSLIST WHERE `USER`='cx' AND `TIME` > 600 AND `Command`='Query'; 8 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; 9 OPEN cur;10 read_loop:LOOP11 FETCH cur INTO cx_id;12 IF done THEN13 LEAVE read_loop;14 END IF;15 SELECT t.trx_query,t.trx_started,p.`USER` FROM information_schema.PROCESSLIST p INNER JOIN information_schema.innodb_trx t ON p.id=t.trx_mysql_thread_id WHERE p.id=cx_id INTO @t_query,@t_time,@p_user;16 INSERT INTO test.monitor_user4cx(`p_id`,`start_time`,`user`,`time`,`query`) VALUES (cx_id,@t_time,@p_user,NOW(),@t_query);17 KILL cx_id;18 END LOOP;19 CLOSE cur;20 END$$21 DELIMITER ;
游标的写法:
CREATE PROCEDURE `test`.`new_procedure` ()BEGIN -- 需要定义接收游标数据的变量 DECLARE a CHAR(16); -- 声明游标的结束标志 DECLARE done INT DEFAULT FALSE; -- 将所需数据赋予游标,游标必须定义在变量/条件后,handler前;否则会报错。 -- ERROR 1337 (42000): Variable or condition declaration after cursor or handler declaration DECLARE cur CURSOR FOR SELECT i FROM test.t; -- 将结束标志绑定到游标,如果捕获到not found异常时就会将变量done设置为TRUE,done=TRUE可以当作循环跳出条件 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; -- 打开游标 OPEN cur; -- 开始循环 read_loop: LOOP -- 提取游标里的数据,这里只有一个,也可以有多个,例如fetch <游标名> into <变量1> , <变量2> FETCH cur INTO a; -- 声明结束的时候 IF done THEN LEAVE read_loop; END IF; -- 这里做你想做的循环的事件 sql; END LOOP; -- 关闭游标 CLOSE cur;END 变量2> 变量1> 游标名>
游标示例:
1、repeat循环(该循环用do while,先执行后判断)
drop procedure if exists test_proce2; create procedure test_proce2() begin declare temp_id int(11); declare temp_time datetime; declare isFinished boolean default false; declare test_cursor cursor for select id,time from test; declare continue handler for not found set isFinished=true; open test_cursor; repeat fetch test_cursor into temp_id,temp_time; if not isFinished then select concat(concat(temp_id,":"),temp_time); end if; until isFinished end repeat; close test_cursor; end
2、loop循环
drop procedure if exists test_proce3; create procedure test_proce3() begin declare temp_id int(11); declare temp_time datetime; declare isFinished boolean default false; declare test_cursor cursor for select id,time from test; declare continue handler for not found set isFinished=true; open test_cursor; test_loop:loop fetch test_cursor into temp_id,temp_time; if isFinished then leave test_loop; end if; //若该if语句放在fetch后面,该循环为while型;若该if语句紧接在end loop前该循环为do while型。 select concat(concat(temp_id,":"),temp_time); end loop test_loop; close test_cursor; end
示例:
定期检查长时间执行的查询,记录并杀掉
DELIMITER $$CREATE EVENT `report`.`monitor_user4cx` ON SCHEDULE EVERY 15 MINUTE DO BEGINDECLARE cx_id INT(10);DECLARE t_query VARCHAR(500);DECLARE done INT DEFAULT FALSE;DECLARE cur CURSOR FOR SELECT id FROM information_schema.PROCESSLIST WHERE `USER`='cx' AND `TIME` > 600 AND `Command`='Query';DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;OPEN cur;read_loop:LOOPFETCH cur INTO cx_id;IF done THENLEAVE read_loop;END IF;SELECT t.trx_query,t.trx_started,p.`USER` FROM information_schema.PROCESSLIST p INNER JOIN information_schema.innodb_trx t ON p.id=t.trx_mysql_thread_id WHERE p.id=cx_id INTO @t_query,@t_time,@p_user;INSERT INTO test.monitor_user4cx(`p_id`,`start_time`,`user`,`time`,`query`) VALUES (cx_id,@t_time,@p_user,NOW(),@t_query);KILL cx_id;END LOOP;CLOSE cur;END$$DELIMITER ;
语法中变量的使用方法:
### 可以直接用set赋值set @a=1;set @b=(select count(*) from information_schema.processlist);insert into test_db.table1 select @a,@b,now();### 可以用into将结果集赋值给变量select id,name,create_time from test_db.table2 into @u_id,@u_name,@u_addtime;同select id,name,create_time into @u_id,@u_name,@u_addtime from test_db.table2;select @u_id,@u_name,@u_addtime;
删除事件:
drop event event_name;
部分内容转自:
http://www.cnblogs.com/trying/p/3296793.html
http://blog.csdn.net/willchyis/article/details/7943467
posted on 2014-11-27 18:08 阅读( ...) 评论( ...)