mysql - 处理 mySQL 过程循环中未找到的数据

Question

我想我正在缩小我的问题范围。我有一个只触发一次的循环:

DELIMITER $$

DROP PROCEDURE IF EXISTS `thread_updates` $$
CREATE PROCEDURE `thread_updates`()
BEGIN

DECLARE done INT DEFAULT 0;
DECLARE my_curr_id INT DEFAULT NULL;
-- DECLARE other vars
DECLARE fixer_cursor CURSOR FOR
  SELECT DISTINCT(id)
  FROM log
  WHERE date >= '2018-01-01';
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

OPEN fixer_cursor;
REPEAT
  FETCH fixer_cursor INTO my_curr_id;
  IF NOT done THEN
    SELECT data FROM table WHERE id = my_curr_id; -- This may not exist 
    -- Do other stuff with 'data' or NULL from above
  END IF;
UNTIL done END REPEAT;
CLOSE fixer_cursor;
END $$
DELIMITER ;

我认为问题可能是在 IF NOT did THEN 循环内，我有一些 select 语句可能试图选择不存在(未找到)的结果。

这对我来说很好，因为逻辑继续在这些位置使用 NULL 值，但我怀疑我的 CONTINUE HANDLER FOR NOT FOUND 正在捕获 NOT FOUND > 警告这些选择会抛出循环内部，从而过早地停止整个循环。

如何仅监听光标上的 NOT FOUND 警告？

或者，如何抑制循环内 MAYBE FOUND select 语句中的 NOT FOUND 警告，以便循环继续？

Answer 1

我认为我已经通过在循环中实现计数器而不是依赖 NOT FOUND 处理程序解决了这个问题:

DELIMITER $$

DROP PROCEDURE IF EXISTS `thread_updates` $$
CREATE PROCEDURE `thread_updates`()
BEGIN

DECLARE my_total INT DEFAULT NULL; -- Declare total
DECLARE my_counter INT DEFAULT 0; -- Declare counter starting at 0
DECLARE my_curr_id INT DEFAULT NULL;
-- DECLARE other vars
DECLARE fixer_cursor CURSOR FOR
  SELECT DISTINCT(id)
  FROM log
  WHERE date >= '2018-01-01';

OPEN fixer_cursor;

SELECT FOUND_ROWS() INTO my_total; -- Get total number of rows

my_fixerloop: LOOP

  FETCH fixer_cursor INTO my_curr_id;
  IF my_counter >= my_total THEN -- Compare counter to total
    CLOSE fixer_cursor;
    LEAVE my_fixerloop;
  END IF;

  SET my_counter = my_counter + 1; -- Increment by one for each record

  SELECT data FROM table WHERE id = my_curr_id; -- This may not exist 
  -- Do other stuff with 'data' or NULL from above

END LOOP;
END $$
DELIMITER ;