-- »ç¿ë¹ý
-- mysql> call sp_killid(300); 300ÃÊ ÃʰúÇÑ thread Àϰý °Á¦ Á¾·á
-- Çʿ信 µû¶ó À̺¥Æ® ½ºÄÉÁÙ·¯ ¹× CronÀ» ÀÀ¿ëÇÏ¸é µÇ°Ú½À´Ï´Ù.
DELIMITER $$
USE `test`$$
DROP PROCEDURE IF EXISTS `sp_killid`$$
CREATE PROCEDURE `sp_killid`(
set_time INT
)
BEGIN
DECLARE is_loop INT DEFAULT 0;
DECLARE thisid INT;
DECLARE thisuser CHAR(20);
DECLARE thistime INT;
DECLARE cur1 CURSOR FOR
SELECT `ID`,`USER`,`TIME` FROM `information_schema`.`PROCESSLIST`;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET is_loop = 1;
OPEN cur1;
REPEAT
FETCH cur1 INTO thisid,thisuser,thistime;
IF NOT is_loop THEN
IF thisuser<>'system user' AND thistime>set_time THEN
KILL thisid;
END IF;
END IF;
UNTIL is_loop END REPEAT;
CLOSE cur1;
END$$
DELIMITER ;