MySQL存储过程游标未获取数据时报错的解决方法咨询

当表为空时，原存储过程的REPEAT循环会先执行一次FETCH操作（触发NOT FOUND事件，将done设为1），但仍会执行循环体代码。此时login_info_id未被赋值（保持初始NULL），导致@id_list被拼接为无效值（NULL或空字符串），后续执行UPDATE时会生成语法错误的SQL语句（如WHERE id in ()），从而触发报错。

方案一：修改循环结构，避免无效执行 #

将REPEAT循环替换为LOOP，确保只有FETCH成功获取到数据时，才执行循环体的拼接和更新逻辑：

DROP PROCEDURE IF EXISTS `extend_login_timestamp_using_concat` ;;
CREATE PROCEDURE `extend_login_timestamp_using_concat`()

BEGIN
  DECLARE done INT DEFAULT 0;
  DECLARE login_info_id BIGINT;

  DECLARE cur CURSOR FOR SELECT `id` FROM `login_info` WHERE `device_id` = 'WEB';
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

  SET @extended_timestamp = (SELECT UNIX_TIMESTAMP(DATE_FORMAT(NOW() + INTERVAL 365 DAY,'%Y-%m-01 23:59:59')) * 1000);
  SET @id_list = NULL;
  SET @id_count = 0;

  OPEN cur;

  -- 改用LOOP，先FETCH再判断是否处理
  read_loop: LOOP
    FETCH cur INTO login_info_id;
    IF done THEN
      LEAVE read_loop;
    END IF;

    SET @id_list = CONCAT_WS (',', @id_list, login_info_id);
    SET @id_count = @id_count + 1;

    IF (@id_count = 2) THEN
      SET @update_query = CONCAT('UPDATE `login_info` SET `timestamp` = ', @extended_timestamp, ' WHERE `id` in (', @id_list, ')');
      PREPARE extend_expire_statement FROM @update_query;
      EXECUTE extend_expire_statement;
      DEALLOCATE PREPARE extend_expire_statement;

      SET @id_list = NULL;
      SET @id_count = 0;
    END IF;
  END LOOP read_loop;

  CLOSE cur;

  IF (@id_list IS NOT NULL) THEN
    SET @update_query = CONCAT('UPDATE `login_info` SET `timestamp` = ', @extended_timestamp, ' WHERE `id` in (', @id_list, ')');
    PREPARE extend_expire_statement FROM @update_query;
    EXECUTE extend_expire_statement;
    DEALLOCATE PREPARE extend_expire_statement;
  END IF;

END ;;

DELIMITER ;

方案二：简化逻辑，直接单条更新（推荐） #

原存储过程的核心需求是更新所有device_id为WEB的记录，完全无需通过游标获取ID再批量拼接SQL。直接使用单条UPDATE语句即可，代码更简洁、性能更高，且从根源避免空数据报错：

DROP PROCEDURE IF EXISTS `extend_login_timestamp_using_concat` ;;
CREATE PROCEDURE `extend_login_timestamp_using_concat`()

BEGIN
  SET @extended_timestamp = (SELECT UNIX_TIMESTAMP(DATE_FORMAT(NOW() + INTERVAL 365 DAY,'%Y-%m-01 23:59:59')) * 1000);
  -- 直接过滤条件更新，无需游标
  UPDATE `login_info` SET `timestamp` = @extended_timestamp WHERE `device_id` = 'WEB';
END ;;

DELIMITER ;

内容的提问来源于stack exchange，提问作者Another coder