MySQL的事件就像Linux系统上的定时任务，按照设置的时间或者间隔时间执行设置好的任务。
如果用SQLyog一类的写存储过程、触发器或者事件会省事一些，例如SQLyog就会生成一个大致的模板：
DELIMITER $$
CREATE EVENT `report`.`monitor_user4cx` ON SCHEDULE EVERY 15 MINUTE DO
BEGIN
DECLARE 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:LOOP
FETCH cur INTO cx_id;
IF done THEN
LEAVE 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 ;
游标的写法：
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
游标示例：
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
BEGIN
DECLARE 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:LOOP
FETCH cur INTO cx_id;
IF done THEN
LEAVE 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