Mysql存储过程：如何处理空结果集

Question

我编写了一个过程，其中一个语句无法正常执行：

SELECT thumb_image into v_thumb_image FROM RESTAURANT_IMAGE WHERE 
   RESTAURANT_ID = v_restaurant_id

我调查的原因是，如果结果集在任何时候为空，则过程不会继续运行语句。

请注意，我将其在一个循环内调用。

我的关注点是，如果对于任何v_restaurant_id，结果集为空，不要停止执行。

完整的过程：

-- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `populate_restaurant_details`()
BEGIN
 DECLARE v_finished_cuisines, 
         v_finished, 
         v_restaurant_id, 
         v_count_discount
 INT DEFAULT 0;

 DECLARE v_cuisines, 
         v_thumb_image 
 varchar(200) DEFAULT "";

 DECLARE cuisine_title varchar(50) DEFAULT "";
 -- Fetch all restaurant id
 DECLARE restaurant_cursor CURSOR FOR
   SELECT id FROM delhifoodonline.restaurant order by id desc;

 DECLARE CONTINUE HANDLER 
  FOR NOT FOUND SET v_finished = 1;

 OPEN restaurant_cursor;

 get_restaurant: LOOP   

   FETCH restaurant_cursor INTO v_restaurant_id;
   IF v_finished = 1 THEN 
    LEAVE get_restaurant;
   END IF;

  SET v_finished_cuisines =""; 
  SET v_thumb_image = "";
begin
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_thumb_image = NULL;

  SELECT thumb_image into v_thumb_image 
  FROM restaurant_image 
  WHERE restaurant_id = v_restaurant_id
  ORDER BY id
  LIMIT 1;
end;

  SELECT count(*) into v_count_discount FROM restaurant_discount WHERE 
    restaurant_id = v_restaurant_id;

BLOCK2: BEGIN

  DECLARE cuisines_cursor CURSOR FOR 
   SELECT cuisine.title FROM restaurant_cuisine INNER JOIN cuisine 
    ON restaurant_cuisine.cuisine_id = cuisine.id
    WHERE 
    restaurant_cuisine.restaurant_id = v_restaurant_id
    LIMIT 0,5;

  DECLARE CONTINUE HANDLER 
    FOR NOT FOUND SET v_finished_cuisines = 1;
  SET v_cuisines = "";
  OPEN cuisines_cursor;

  get_cuisine: LOOP
   FETCH cuisines_cursor INTO cuisine_title;

   IF v_finished_cuisines = 1 THEN 
    LEAVE get_cuisine;
   END IF;

   SET v_cuisines = CONCAT(cuisine_title,", ",v_cuisines);

   END LOOP get_cuisine;
  CLOSE cuisines_cursor;

END BLOCK2;

  SET v_cuisines = TRIM(BOTH ", " FROM v_cuisines);

  IF v_count_discount > 0 THEN
   SET v_count_discount = 1;
  ELSE
   SET v_count_discount = 0;
  END IF;

  UPDATE restaurant SET 
                        thumb_image = v_thumb_image,
                        cuisines_list = v_cuisines,
                        discount_available = v_count_discount
                   WHERE id= v_restaurant_id;
 END LOOP get_restaurant;

CLOSE restaurant_cursor;

END

- Apul Gupta

---

空结果集不会阻止执行继续。当v_thumb_image为空时，您的进一步代码中应该存在某些错误。请更新您的问题并提供选择后的代码。 - Amir Rahimi Farahani

@AmirRahimiFarahani，如果我注释掉这个语句，程序就可以正常运行。我调查了一下，发现有一些v_restaurant_id在restaurant_image表中没有记录。你能提供一些建议吗？ - Apul Gupta

@AmirRahimiFarahani 完整代码已添加。 - Apul Gupta

@wchiquito，你是对的，但这不应该停止程序的执行。 - Apul Gupta

你如何调用存储过程？使用MySQL命令行？ - wchiquito

Answer 1

根据文档：

NOT FOUND 是以 '02' 开头的 SQLSTATE 值类别的简称。在光标的上下文中，这是相关的，用于控制当光标到达数据集的末尾时发生什么。如果没有更多的行可用，则会出现 No Data 条件，SQLSTATE 值为“02000”。您可以设置处理程序来检测此条件（或 NOT FOUND 条件）。有关示例，请参见第 13.6.6 节“游标”。对于不返回任何行的 SELECT ... INTO var_list 语句也会出现此条件。 因此，当从 restaurant_image 表中选择不返回任何行时，也将满足 NOT FOUND 状态，并调用定义的处理程序导致离开循环。

一种解决办法是通过将其放入 BEGIN...END 块中声明另一个处理程序来处理该 select 语句。

begin
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_thumb_image = NULL;

  SELECT thumb_image into v_thumb_image 
  FROM restaurant_image 
  WHERE restaurant_id = v_restaurant_id
  ORDER BY id
  LIMIT 1;
end;

毕竟，使用存储过程和游标会很慢，为什么不使用执行单个语句来实现相同的功能呢？

UPDATE restaurant 
SET thumb_image = (
    SELECT thumb_image 
    FROM restaurant_image 
    WHERE restaurant_id = restaurant.id
    ORDER BY id
    LIMIT 1),
discount_available = IF(EXISTS(
    SELECT 1
    FROM restaurant_discount 
    WHERE restaurant_id = restaurant.id), 1, 0), 
cuisines_list = (
    SELECT group_concat(cuisine.title separator ', ')
    FROM restaurant_cuisine
    INNER JOIN cuisine ON restaurant_cuisine.cuisine_id = cuisine.id
    WHERE restaurant_cuisine.restaurant_id = restaurant.id
    LIMIT 0,5)

或者通过消除每行的子查询来使其更快：

UPDATE restaurant r
LEFT JOIN 
    (SELECT restaurant_id, count(*) AS discount_available
    FROM restaurant_discount 
    GROUP BY restaurant_id) d ON r.id = d.restaurant_id
LEFT JOIN 
    (SELECT restaurant_id, thumb_image 
    FROM restaurant_image r1
    WHERE NOT EXISTS (
        SELECT 1 FROM restaurant_image r2 WHERE r2.restaurant_id = r1.restaurant_id AND r2.id < r1.id
    )) t ON r.id = t.restaurant_id
LEFT JOIN
    (SELECT rc.restaurant_id, SUBSTRING_INDEX(GROUP_CONCAT(c.title SEPARATOR ', '), ',', 5) AS cuisines_list
    FROM restaurant_cuisine rc
    INNER JOIN cuisine c ON rc.cuisine_id = c.id
    GROUP BY rc.restaurant_id
    ) rc ON r.id = rc.restaurant_id
SET r.discount_available = IF(d.discount_available = 0, 0, 1),
r.thumb_image = t.thumb_image,
r.cuisines_list = rc.cuisines_list

尝试分别执行这些子查询，以便更好地理解。

- Amir Rahimi Farahani

---

非常感谢，每个解决方案都完美地运作。我想采用单个SQL语句。你能建议我如何在关系表中更新多行的列吗？我想将所有值保留在逗号分隔的字符串中。 - Apul Gupta

GROUP_CONCAT 就是你要找的。 - Amir Rahimi Farahani

Amir，请你更新查询语句。我有点困惑。请检查过程中的BLOCK2。 - Apul Gupta