MySQL预处理语句-如何循环遍历 [英] MySQL Prepared Statement - How to Loop Through

问题描述

我有一张充满ID号的表，我需要遍历它，并在准备好的语句中用作变量.我不知道是否需要为此使用存储过程，或者是否可以通过常规查询来执行此操作.这是一个简单的例子.

I have a table filled with ID numbers, which I need to loop through, and use as variables in a prepared statement. I do not know if I need to use a stored procedure for this, or if a regular query can do it. Here is a simple example.

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，并编写了查询以一次将一个文件导出到服务器.

As mentioned in the comment, I just need this to export some data, I have about 50-100 ID's in the table and have a query written to export a file to server for a single one at a time.

编辑 我计划使用....

EDIT I plan to dump the results of each iteration into a file using something like ....

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

推荐答案

正如其他人已经建议的那样，我们通常避免遍历结果集 RBAR (行排成一行) )，主要是出于性能方面的考虑.我们只是不想养成遍历结果集的习惯.但这不能回答您提出的问题.

As others have already suggested, we typically avoid looping through a resultset RBAR (row by agonizing row) primarily for performance reasons. We just don't want to get into the habit of looping through a resultset. But that doesn't answer the question you asked.

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

To answer the question you asked, here's a rudimentary example of a MySQL stored program that uses a CURSOR to individually process rows returned by a query. MySQL doesn't support anonymous blocks, so the only way to do this is in a MySQL stored program, like a 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 ;

要执行该过程，请执行以下操作:

To execute the procedure:

CALL loop_through_var_list();

注释:在MySQL中处理CURSOR的语法与其他数据库有很大不同.

NOTES: The syntax for processing a CURSOR in MySQL is quite a bit different than other databases.

要获得循环"，我们需要使用LOOP ... END LOOP构造.

To get the "looping", we need to use a LOOP ... END LOOP construct.

但是要防止该循环永远运行，我们需要一个LEAVE语句，该语句将允许我们退出循环.

But to prevent that loop from running forever, we need a LEAVE statement that will allow us to exit the loop.

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

We use a conditional test to determine when to leave. In this example, we want to exit after we've finished processing the last row.

当没有更多行要提取时，FETCH将引发异常.

The FETCH is going to throw an exception when there are no more rows to be fetched.

我们在一个CONTINUE HANDLER中捕获"该异常(由于某些不可思议的原因，"handlers"必须要声明的最后一件事情；如果我们尝试在一个HANDLER之后(而不是另一个HANDLER之外)进行声明，则MySQL会引发错误.

We "catch" that exception in a CONTINUE HANDLER (for some arcane reason, the "handlers" have to the last things declared; MySQL throws an error if we try to declare something after a HANDLER (other than another HANDLER.)

当MySQL抛出没有更多行"异常时，将触发处理程序代码.在此示例中，我们只是将变量(名为done)设置为一个值.

When MySQL throws the "no more rows" exception, that fires the handler code. In this example, we're just setting a variable (named done) to a value.

由于它是继续"处理程序，因此处理将在引发异常的语句处重新开始，在这种情况下，该语句将是FETCH之后的语句.因此，我们要做的第一件事是检查我们是否完成"了.如果完成"，则退出循环，然后关闭光标.

Since it's a "continue" handler, processing starts back up at the statement where the exception was thrown, in this case, that will be the statement following the FETCH. So, the first thing we do is check if we're "done" or not. If we're "done", then we exit the loop, and close the cursor.

否则，我们知道已经从var_list中获得了一个id值，该值存储在名为v_id的过程变量中.所以现在我们可以做任何我们想做的事情.看起来您想将一些SQL文本放入用户定义的变量中(包括将v_id的值放入SQL文本中，然后进行PREPARE，EXECUTE和DEALLOCATE PREPARE.

Otherwise, we know that we've got an id value from var_list stored in a procedure variable named v_id. So now we can do whatever we want. Looks like you want to put some SQL text into a user-defined variable (including in the value of v_id into the SQL text, then PREPARE, EXECUTE, and DEALLOCATE PREPARE.

请确保使用适当的数据类型声明v_id变量，该变量与var_list中的id列的数据类型匹配，我只是假设它是一个INT.

Be sure to declare the v_id variable with the appropriate datatype, that matches the datatype of the id column in var_list, I've just assumed that it's an INT.

当我们到达循环的结尾时，MySQL会循环"回到循环的开始，然后我们又去了.

When we reach the end of the loop, MySQL "loops" back to the beginning of the loop, and off we go again.

在循环的主体中，您可能希望将v_id CONCAT转换为要执行的SQL文本.看起来您已经对PREPARE，DEALLOCATE准备好了.为了进行测试，您可能希望在游标声明中的SELECT上添加一个LIMIT子句，然后执行一个简单的SELECT v_id.在正文中，只是在添加更多代码之前验证循环是否正常工作.

In the body of the loop, likely you'll want to CONCAT v_id into the SQL text you want to execute. Looks like you already have a handle on the PREPARE, DEALLOCATE prepare. For testing, you may want to add a LIMIT clause on the SELECT in the cursor declaration, and then do a simple SELECT v_id; in the body, just to verify the loop is working, before you add more code.

关注

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

I wanted to mention another alternative approach to the task, i.e. running a series of statements based on a template, substituting in values provided from a single SQL select statement...

例如，如果我有此模板:

For example, if I had this template:

SELECT * 
  INTO OUTFILE '/tmp/[e-mail]'
  FIELDS TERMINATED BY ',' ENCLOSED BY '"'
  LINES TERMINATED BY '\n'
FROM data 
WHERE id = @ID
ORDER BY 1 

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

and I needed to replace the occurrences of @ID with a specific id value from a list returned from a SELECT statement, e.g.

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

我可能不会使用带有CURSOR循环的MySQL存储程序，而是使用另一种方法.

I probably would not use a MySQL stored program with a CURSOR loop, I'd use a different approach.

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

I'd make use of a SELECT statement to generate a set of SQL statements which could be executed. Assuming id is integer type, I would likely do something like this:

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

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

For every value of id returned from s, the statement returns the text of a SQL SELECT statement that could (and need to) execute. Capturing that into a text file would give me a SQL script I could run.

这篇关于MySQL预处理语句-如何循环遍历的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！