FpAEvLdZ

1CREATE TABLE entry (
2  blog_id INT,
3  created datetime
4);
5
6DROP PROCEDURE IF EXISTS set_uninterrupted_count;
7DELIMITER //
8CREATE PROCEDURE set_uninterrupted_count(IN id INT, OUT uninterrupted_count INT)
9  BEGIN
10    DECLARE done INT DEFAULT 0;
11    DECLARE date, next_date, tmp_date date;
12    DECLARE count INT DEFAULT 0;
13    DECLARE cur CURSOR FOR
14      SELECT
15        DATE(created), DATE(created + INTERVAL 1 DAY)
16      FROM
17        entry
18      WHERE
19        blog_id = id ORDER BY created DESC;
20    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
21    OPEN cur;
22    date_loop: REPEAT
23      FETCH cur INTO date, next_date;
24      IF NOT done THEN
25	IF tmp_date IS NULL THEN
26          SET count = count + 1;
27	ELSEIF next_date = tmp_date THEN
28          SET count = count + 1;
29        ELSE
30          LEAVE date_loop;
31        END IF;
32        SET tmp_date = date;
33      END IF;
34    UNTIL done END REPEAT;
35    SET uninterrupted_count = count;
36  END;
37//
38DELIMITER ;
39
40CALL set_uninterrupted_count(1, @count);
41
42SELECT @count;