MySQL 准备语句 - 如何循环

我有一个充满 ID 号的表，我需要循环遍历该表，并将其用作准备好的语句中的变量。我不知道是否需要为此使用存储过程，或者常规查询是否可以做到这一点。这是一个简单的例子。

SELECT id from var_list;

loop through @ID = var_list.id ....


SET @s1 = "SELECT * FROM data WHERE id = @ID"; 
PREPARE stmt1 FROM @s1;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;

正如评论中提到的，我只需要它来导出一些数据，我的表中有大约 50-100 个 ID，并且编写了一个查询来一次将一个文件导出到服务器。

编辑 我计划使用类似......的东西将每次迭代的结果转储到文件中。

INTO OUTFILE '/tmp/orders.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'

正如其他人已经建议的那样，我们通常avoid循环遍历结果集RBAR（逐行排列）主要是出于性能原因。我们只是不想养成循环结果集的习惯。但这并不能回答你提出的问题。

为了回答您提出的问题，这里有一个 MySQL 存储程序的基本示例，该程序使用 CURSOR 单独处理查询返回的行。 MySQL 不支持匿名块，因此唯一的方法是在 MySQL 存储程序中，例如 PROCEDURE

DELIMITER $$

CREATE PROCEDURE loop_through_var_list
BEGIN
   DECLARE done INT DEFAULT 0;
   DECLARE v_id INT DEFAULT NULL;  
   DECLARE csr_var_list CURSOR FOR SELECT id FROM var_list ORDER BY id;
   DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
   OPEN csr_var_list;
   get_id: LOOP
      FETCH csr_var_list INTO v_id; 
      IF done = 1 THEN
         LEAVE get_id;
      END IF;

      -- at this point, we have an id value in v_id, so we can do whatever
      SET @s1 = CONCAT('SELECT ... WHERE id =''', v_id, ''' ...');


   END LOOP get_id;
   CLOSE csr_var_list;
END$$

DELIMITER ;

要执行该过程：

CALL loop_through_var_list();

注意：MySQL 中处理 CURSOR 的语法与其他数据库有很大不同。

为了获得“循环”，我们需要使用LOOP ... END LOOP构造。

但为了防止该循环永远运行，我们需要一个 LEAVE 语句来允许我们退出循环。

我们使用条件测试来确定何时离开。在此示例中，我们希望在处理完最后一行后退出。

The FETCH当没有更多的行可供获取时，将引发异常。

我们在 CONTINUE HANDLER 中“捕获”该异常（由于某些神秘的原因，“处理程序”必须声明最后的内容；如果我们尝试在 HANDLER 之后声明某些内容（而不是另一个 HANDLER），MySQL 会抛出错误。）

当 MySQL 抛出“no more rows”异常时，会触发处理程序代码。在此示例中，我们只是设置一个变量（名为done) 到一个值。

由于它是一个“继续”处理程序，因此处理会在引发异常的语句处重新开始，在本例中，这将是 FETCH 之后的语句。因此，我们要做的第一件事就是检查我们是否“完成”。如果我们“完成”，那么我们退出循环，并关闭光标。

否则，我们知道我们有一个id价值来自var_list存储在名为的过程变量中v_id。所以现在我们可以做任何我们想做的事。看起来您想将一些 SQL 文本放入用户定义的变量中（包括将 v_id 的值放入 SQL 文本中，然后 PREPARE、EXECUTE 和 DEALLOCATE PREPARE。

请务必声明v_id具有适当数据类型的变量，该变量与id列于var_list，我只是假设它是一个 INT。

当我们到达循环末尾时，MySQL“循环”回到循环的开头，然后再次开始。

在循环体中，您可能需要将 v_id CONCAT 到要执行的 SQL 文本中。看起来您已经掌握了 PREPARE、DEALLOCATE 准备。为了进行测试，您可能需要在游标声明中的 SELECT 上添加 LIMIT 子句，然后执行简单的 SELECT v_id;在正文中，只是为了在添加更多代码之前验证循环是否正常工作。

FOLLOWUP

我想提到该任务的另一种替代方法，即基于模板运行一系列语句，替换单个 SQL select 语句提供的值...

例如，如果我有这个模板：

SELECT * 
  INTO OUTFILE '/tmp/[email protected] /cdn-cgi/l/email-protection'
  FIELDS TERMINATED BY ',' ENCLOSED BY '"'
  LINES TERMINATED BY '\n'
FROM data 
WHERE id = @ID
ORDER BY 1 

我需要将出现的 @ID 替换为 SELECT 语句返回的列表中的特定 id 值，例如

SELECT id
  FROM var_list
 WHERE id IS NOT NULL
 GROUP BY id

我可能不会使用带有 CURSOR 循环的 MySQL 存储程序，我会使用不同的方法。

我将使用 SELECT 语句来生成一组可以执行的 SQL 语句。假设id是整数类型，我可能会这样做：

SELECT CONCAT(' SELECT * 
                   INTO OUTFILE ''/tmp/orders_',s.id,'.csv''
                   FIELDS TERMINATED BY '','' ENCLOSED BY ''"''
                   LINES TERMINATED BY ''\n''
                FROM data
               WHERE id = ',s.id,'
               ORDER BY 1;') AS `stmt`
 FROM ( SELECT v.id
          FROM var_list v
         WHERE v.id IS NOT NULL
         GROUP BY v.id
      ) s
ORDER BY s.id

对于每个值id从返回s，该语句返回可以（并且需要）执行的 SQL SELECT 语句的文本。将其捕获到文本文件中将为我提供一个可以运行的 SQL 脚本。