MySQL 游标的 FETCH 不适用于时间数据类型

Question

MySQL 游标的 FETCH 不适用于时间数据类型

给定光标相关代码

declare Normaltimeopen time;
declare cursorName cursor for select statement;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET flag = 1;
open cursorName;
Fetch cursorName into Normaltimeopen ;
IF flag = 1 THEN
    CLOSE cursorName ;        
END IF;
close cursorName;

因此，Normaltimeopen 变量设置为“00:00:00”，但表包含值“16:00:00”。我用“mysql调试器”进行了调试，但没有得到任何线索。

Answer 1

我无法重现该问题，测试代码按预期工作:

/*Table structure for table `table1` */

DROP TABLE IF EXISTS `table1`;

CREATE TABLE `table1` (
  `tablenormaltimeopen` TIME DEFAULT NULL
) ENGINE=INNODB;

/*Data for the table `table1` */

INSERT  INTO `table1`(`tablenormaltimeopen`) VALUES ('16:00:00');

/* Procedure structure for procedure `sp_test` */

/*!50003 DROP PROCEDURE IF EXISTS  `sp_test` */;

DELIMITER $$

CREATE PROCEDURE `sp_test`()
BEGIN
    DECLARE `flag` TINYINT(1) DEFAULT 0;
    DECLARE `Normaltimeopen` TIME;
    DECLARE `cursorName` CURSOR FOR
    SELECT `tablenormaltimeopen` FROM `table1`;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET `flag` := 1;
    OPEN `cursorName`;
    FETCH `cursorName` INTO `Normaltimeopen`;
    IF NOT `flag` THEN
        SELECT `Normaltimeopen`;
    END IF;
    CLOSE `cursorName`;
END$$

DELIMITER ;

SQL Fiddle demo