mysql - 在存储过程中使用游标时无法获取日期时间列

Question

我使用游标获取两个值，int 和 datetime，但我发现返回值为 null 而不是 datetime 值。

mysql> select * from test1|
+-------+---------------------+ 
| id    | qdtime              |
+-------+---------------------+ 
| 10001 | 2015-03-05 12:15:36 | 
| 10004 | 2015-03-05 12:15:36 |
+-------+---------------------+ 
2 rows in set (0.00 sec)

mysql> delimiter | 
mysql> drop procedure if exists demo | 
Query OK, 0 rows affected (0.00 sec)

mysql> create procedure demo()
    -> BEGIN
    ->    DECLARE TARGETID INT;
    ->    DECLARE QDTIME DATETIME;
    ->    DECLARE done INT DEFAULT FALSE;
    ->    DECLARE CUR1 CURSOR FOR SELECT id,qdtime FROM test1;
    ->    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    ->    OPEN CUR1;
    ->  judge_loop: LOOP
    ->    FETCH CUR1 INTO TARGETID, QDTIME;
    ->    IF done THEN
    ->      LEAVE judge_loop;
    ->    END IF;
    ->
    ->    SELECT TARGETID, QDTIME;
    -> END LOOP;
    -> CLOSE CUR1;
    -> END;| Query OK, 0 rows affected (0.00 sec)

mysql> call demo()|
+----------+--------+ 
| TARGETID | QDTIME |
+----------+--------+ 
|    10001 | NULL   |
+----------+--------+ 
1 row in set (0.00 sec)

+----------+--------+ 
| TARGETID | QDTIME |
+----------+--------+ 
|    10004 | NULL   |
+----------+--------+ 
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Answer 1

切勿将变量命名为列或任何其他名称。为避免名称不明确，请使用前缀。例如，让每个变量都以 v_ 开头，每个参数都以 p_ 开头。这也大大提高了代码的可读性。

你的光标

DECLARE CUR1 CURSOR FOR SELECT id,qdtime FROM test1;

在这里选择变量，而不是列。由于此时您的变量的值为 NULL，因此您的结果也是如此。