事件游标

 

事件游标：

BEGIN
DECLARE cx_id INT(10);
DECLARE t_query VARCHAR(500);
DECLARE done INT DEFAULT FALSE;
DECLARE cur CURSOR FOR SELECT junda_pmcloud.t_ipguard_status.ipguard_id FROM junda_pmcloud.t_ipguard_status;
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 TIMESTAMPDIFF(SECOND, (SELECT t_ipguard_status.updatetime from t_ipguard_status where t_ipguard_status.ipguard_id=cx_id) ,now()) INTO @t_time;
UPDATE t_ipguard_status set t_ipguard_status.is_active=0 WHERE @t_time>60 and t_ipguard_status.ipguard_id=cx_id;

END LOOP;
CLOSE cur;
END

 

创建一个事件，对某个表的内容进行遍历循并当时间差>60时，进行数据库操作。

当在END LOOP; 前添加 kill cx_id; 则只执行一次。而不是遍历。

 

进一步改进：

其时间通过读取数据库进行更新设置

BEGIN
DECLARE cx_id INT(10);
DECLARE t_query VARCHAR(500);
DECLARE done INT DEFAULT FALSE;
DECLARE cur CURSOR FOR SELECT junda_pmcloud.t_ipguard_status.ipguard_id FROM junda_pmcloud.t_ipguard_status;
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 TIMESTAMPDIFF(SECOND, (SELECT t_ipguard_status.updatetime from t_ipguard_status where t_ipguard_status.ipguard_id=cx_id) ,now()) INTO @t_time;
SELECT submit_interval from t_ipguard_option where ipguard_id=cx_id limit 0,1 INTO @submit_interval;
UPDATE t_ipguard_status set t_ipguard_status.is_active=1 WHERE @t_time>3*@submit_interval and t_ipguard_status.ipguard_id=cx_id;

END LOOP;
CLOSE cur;
END