Clear Old Session Records

gistfile1.sql

DROP procedure IF EXISTS `ClearSessions`;

DELIMITER $$

CREATE PROCEDURE ClearSessions(IN days_ago INT)
BEGIN
  DECLARE bDone INT;
  DECLARE count INT;
  DECLARE sessionId INT;

  DECLARE curs CURSOR FOR SELECT id FROM sessions WHERE updated_at < DATE_SUB(NOW(), INTERVAL days_ago DAY);
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET bDone = 1;

  OPEN curs;

  SET bDone = 0;
  SET count = 0;
  REPEAT
    FETCH curs INTO sessionId;
    DELETE FROM sessions WHERE id = sessionId;
    SET count = count + 1;
  UNTIL bDone END REPEAT;

  CLOSE curs;

  SELECT count AS 'Deleted';
END$$

DELIMITER ;