mysql存储过程在几天内循环

Question

我不确定，是否是个虫子。在此过程中，经过一段时间后，我的输出将为空：

CREATE DEFINER=`root`@`%` PROCEDURE `rebuild_cache_days`()
    MODIFIES SQL DATA
BEGIN
    DECLARE SID INT;
    DECLARE M INT;
    DECLARE Y INT;
    DECLARE D INT;

    DECLARE sDone INT;
    DECLARE tDone INT;

    DECLARE curs CURSOR FOR SELECT `SensorID` FROM `temp_sensors`;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET sDone = 1;

    OPEN curs;
    SET sDone = 0;

    loopSID: REPEAT
        FETCH curs INTO SID;
        SELECT DAY(`Timestamp`), MONTH(`Timestamp`), YEAR(`Timestamp`) FROM `temp_log` WHERE `SensorID` = SID ORDER BY `Timestamp` ASC LIMIT 1 INTO D, M, Y;
        SET tDone = 0;
        loopTime: REPEAT
            SELECT CONCAT('process SensorID ', SID, ' date ', Y, '-', M, '-', D) AS Output;
            DELETE FROM `temp_cache_days` WHERE `SensorID` = SID AND `Timestamp` = CONCAT(Y, '-', M,'-', D,' 00:00:00');
            INSERT INTO `temp_cache_days` (`SensorID`, `Timestamp`, `Avg`, `Min`, `Max`)
            SELECT `SensorID`, CONCAT(Y, '-', M,'-', D,' 00:00:00') AS TS,
                    ROUND(AVG(`Temperatur`), 1) AS Avg, ROUND(MIN(`Temperatur`), 1) AS Min, ROUND(MAX(`Temperatur`), 1) AS Max
            FROM `temp_log` WHERE `SensorID` = SID
            AND `Timestamp` BETWEEN CONCAT(Y, '-', M,'-', D,' 00:00:00') AND CONCAT(Y, '-', M,'-', D,' 23:59:59')
            GROUP BY TS LIMIT 1;
            SELECT  DAY(DATE_ADD(CONCAT(Y, '-', M,'-', D,' 00:00:00'), INTERVAL 1 DAY)),
                    MONTH(DATE_ADD(CONCAT(Y, '-', M,'-', D,' 00:00:00'), INTERVAL 1 DAY)),
                    YEAR(DATE_ADD(CONCAT(Y, '-', M,'-', D,' 00:00:00'), INTERVAL 1 DAY)) INTO D, M, Y;
            IF CONCAT(Y,'-',M,'-01 00:00:00') >= CONCAT(CURDATE(), " ",CURTIME()) THEN
                SET tDone = 1;
            END IF;
        UNTIL tDone END REPEAT loopTime;
    UNTIL sDone END REPEAT loopSID;
    SELECT CONCAT('process done') AS Output;
    CLOSE curs;
END

这就是输出：

+-----------------------------------+
| Output                            |
+-----------------------------------+
| process SensorID 1 date 2013-6-29 |
+-----------------------------------+
1 row in set (0,30 sec)

+-----------------------------------+
| Output                            |
+-----------------------------------+
| process SensorID 1 date 2013-7-30 |
+-----------------------------------+
1 row in set (0,45 sec)

+-----------------------------------+
| Output                            |
+-----------------------------------+
| process SensorID 1 date 2013-8-31 |
+-----------------------------------+
1 row in set (0,78 sec)

+----------------------------------+
| Output                           |
+----------------------------------+
| process SensorID 1 date 2013-8-1 |
+----------------------------------+
1 row in set (0,93 sec)

+----------------------------------+
| Output                           |
+----------------------------------+
| process SensorID 1 date 2013-8-2 |
+----------------------------------+

[...cut...]

+-----------------------------------+
| Output                            |
+-----------------------------------+
| process SensorID 1 date 2013-8-27 |
+-----------------------------------+
1 row in set (5,36 sec)

+-----------------------------------+
| Output                            |
+-----------------------------------+
| process SensorID 1 date 2013-8-28 |
+-----------------------------------+
1 row in set (5,55 sec)

+-----------------------------------+
| Output                            |
+-----------------------------------+
| process SensorID 1 date 2013-8-29 |
+-----------------------------------+
1 row in set (5,71 sec)

+-----------------------------------+
| Output                            |
+-----------------------------------+
| process SensorID 1 date 2013-8-30 |
+-----------------------------------+
1 row in set (5,87 sec)

+--------+
| Output |
+--------+
| NULL   |
+--------+
1 row in set (6,03 sec)

+--------+
| Output |
+--------+
| NULL   |
+--------+
1 row in set (6,03 sec)

因此，至少它每次只输出NULL，并且在日期跳转之前输出。我重新检查了代码多次，但没有发现任何失败。

它接缝，DATE_ADD有一个问题，因为也是当我删除删除和插入到.选择I只获得NULL作为输出。

# dpkg -l | grep mysql
ii  libdbd-mysql-perl                          4.028-2                                    i386         Perl5 database interface to the MySQL database
ii  libmysqlclient18:i386                      5.6.28-0ubuntu0.15.04.1                    i386         MySQL database client library
ii  mysql-client-5.6                           5.6.28-0ubuntu0.15.04.1                    i386         MySQL database client binaries
ii  mysql-client-core-5.6                      5.6.28-0ubuntu0.15.04.1                    i386         MySQL database core client binaries
ii  mysql-common                               5.6.28-0ubuntu0.15.04.1                    all          MySQL database common files, e.g. /etc/mysql/my.cnf
ii  mysql-server                               5.6.28-0ubuntu0.15.04.1                    all          MySQL database server (metapackage depending on the latest version)
ii  mysql-server-5.6                           5.6.28-0ubuntu0.15.04.1                    i386         MySQL database server binaries and system database setup
ii  mysql-server-core-5.6                      5.6.28-0ubuntu0.15.04.1                    i386         MySQL database server binaries
rc  php5-mysql                                 5.6.4+dfsg-4ubuntu6                        i386         MySQL module for php5
ii  php5-mysqlnd                               5.6.4+dfsg-4ubuntu6.4                      i386         MySQL module for php5 (Native Driver)

编辑：

我还尝试使用手动方式，这会导致同样的问题：

mysql> Set @D = 27, @M = 6, @Y = 2013;
Query OK, 0 rows affected (0,00 sec)

mysql> SELECT DAY(DATE_ADD(CONCAT(@Y, '-', @M, '-', @D,' 00:00:00'), INTERVAL 1 DAY)) AS D,
    -> MONTH(DATE_ADD(CONCAT(@Y, '-', @M, '-', @D,' 00:00:00'), INTERVAL 1 DAY)) AS M,
    -> YEAR(DATE_ADD(CONCAT(@Y, '-', @M, '-', @D,' 00:00:00'), INTERVAL 1 DAY)) AS Y INTO @D, @M, @Y;
Query OK, 1 row affected (0,00 sec)

mysql> SELECT @D, @M, @Y;
+------+------+------+
| @D   | @M   | @Y   |
+------+------+------+
|   28 |    6 | 2013 |
+------+------+------+
1 row in set (0,00 sec)

mysql> SELECT DAY(DATE_ADD(CONCAT(@Y, '-', @M, '-', @D,' 00:00:00'), INTERVAL 1 DAY)) AS D, MONTH(DATE_ADD(CONCAT(@Y, '-', @M, '-', @D,' 00:00:00'), INTERVAL 1 DAY)) AS M, YEAR(DATE_ADD(CONCAT(@Y, '-', @M, '-', @D,' 00:00:00'), INTERVAL 1 DAY)) AS Y INTO @D, @M, @Y;
Query OK, 1 row affected (0,00 sec)

mysql> SELECT @D, @M, @Y;                                                                                                                                                                            +------+------+------+
| @D   | @M   | @Y   |
+------+------+------+
|   29 |    6 | 2013 |
+------+------+------+
1 row in set (0,00 sec)

mysql> SELECT DAY(DATE_ADD(CONCAT(@Y, '-', @M, '-', @D,' 00:00:00'), INTERVAL 1 DAY)) AS D, MONTH(DATE_ADD(CONCAT(@Y, '-', @M, '-', @D,' 00:00:00'), INTERVAL 1 DAY)) AS M, YEAR(DATE_ADD(CONCAT(@Y, '-', @M, '-', @D,' 00:00:00'), INTERVAL 1 DAY)) AS Y INTO @D, @M, @Y;
Query OK, 1 row affected (0,00 sec)

mysql> SELECT @D, @M, @Y;                                                                                                                                                                            +------+------+------+
| @D   | @M   | @Y   |
+------+------+------+
|   30 |    7 | 2013 |
+------+------+------+
1 row in set (0,00 sec)

Answer 1

好的，我不知道把日、月、年分开存放有什么问题，但是我的解决方案是(是的，这是一种更优雅的方法)，只使用一个包含整个白天时间的变量。

BEGIN
    DECLARE SID INT;
    DECLARE Dt DATETIME ;

    DECLARE sDone INT;
    DECLARE tDone INT;

    DECLARE curs CURSOR FOR SELECT `SensorID` FROM `temp_sensors`;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET sDone = 1;

    OPEN curs;
    SET sDone = 0;

    loopSID: REPEAT
        FETCH curs INTO SID;
        SELECT DATE_FORMAT(`Timestamp`, '%Y-%m-%d 00:00:00') FROM `temp_log` WHERE `SensorID` = SID ORDER BY `Timestamp` ASC LIMIT 1 INTO Dt;
        SET tDone = 0;
        loopTime: REPEAT
            SELECT CONCAT('process SensorID ', SID, ' date ', Dt) AS Output;
            DELETE FROM `temp_cache_days` WHERE `SensorID` = SID AND `Timestamp` = Dt;
            INSERT INTO `temp_cache_days` (`SensorID`, `Timestamp`, `Avg`, `Min`, `Max`)
            SELECT `SensorID`, Dt AS TS,
                    ROUND(AVG(`Temperatur`), 1) AS Avg, ROUND(MIN(`Temperatur`), 1) AS Min, ROUND(MAX(`Temperatur`), 1) AS Max
            FROM `temp_log` WHERE `SensorID` = SID
            AND `Timestamp` BETWEEN Dt AND DATE_FORMAT(Dt, '%Y-%m-%d 23:59:59')
            GROUP BY TS LIMIT 1;
            SELECT DATE_FORMAT(Dt + INTERVAL 1 DAY, '%Y-%m-%d 00:00:00') INTO Dt;
            IF Dt >= CONCAT(CURDATE(), " ",CURTIME()) THEN
                SET tDone = 1;
            END IF;
        UNTIL tDone END REPEAT loopTime;
    UNTIL sDone END REPEAT loopSID;
    SELECT CONCAT('process done') AS Output;
    CLOSE curs;
END