如何遍历表的所有行? (MySQL) [英] How can I loop through all rows of a table? (MySQL)

问题描述

我有一个表A，并且有一个主键ID.

I have a table A and there is one primary key ID.

现在我要遍历A中的所有行.

Now I want to go through all rows in A.

我发现类似针对A中的每个记录"的内容，但这似乎不是您在MySQL中的处理方式.

I found something like 'for each record in A', but this seems to be not how you do it in MySQL.

对于每行，我想要一个字段并对其进行转换，将其插入到另一个表中，然后更新该行的某些字段.我可以将select部分和insert放入一个语句中，但是我也不知道如何在其中进行更新.所以我想循环.实际上，除了MySQL，我不想使用其他任何东西.

Thing is for each row I want to take a field and transform it, insert it into another table and then update some of the row's fields. I can put the select part and the insert into one statement, but I don't know how to get the update in there as well. So I want to loop. And for practice I don't want to use anything else than MySQL.

修改

我希望得到一个例子.

还有一个不需要放在过程中的解决方案.

And a solution which does not need to be put into a procedure.

修改2

好的，请考虑这种情况:

okay think of this scenario:

表A和B，每个都有ID和VAL字段.

Table A and B, each with fields ID and VAL.

现在这是我想做的伪代码:

Now this is the pseudo code for what I want to do:

for(each row in A as rowA)
{
  insert into B(ID, VAL) values(rowA[ID], rowA[VAL]);
}

使用循环将A的内容基本上复制到B.

basically copying content of A into B using a loop.

(这只是一个简化的示例，当然您不会为此使用循环.) }

(this is just a simplified example, of course you wouldn't use a loop for this.) }

推荐答案

由于建议使用循环，因此要求提供过程类型的解决方案.这是我的.

Since the suggestion of a loop implies the request for a procedure type solution. Here is mine.

任何处理从表中获取的任何单个记录的查询都可以包装在一个过程中，以使其遍历表的每一行，如下所示:

Any query which works on any single record taken from a table can be wrapped in a procedure to make it run through each row of a table like so:

DROP PROCEDURE IF EXISTS ROWPERROW;
DELIMITER ;;

然后按照您的示例进行操作(为清楚起见，使用table_A和table_B)

Then here's the procedure as per your example (table_A and table_B used for clarity)

CREATE PROCEDURE ROWPERROW()
BEGIN
DECLARE n INT DEFAULT 0;
DECLARE i INT DEFAULT 0;
SELECT COUNT(*) FROM table_A INTO n;
SET i=0;
WHILE i<n DO 
  INSERT INTO table_B(ID, VAL) SELECT (ID, VAL) FROM table_A LIMIT i,1;
  SET i = i + 1;
END WHILE;
End;
;;

然后别忘了重置定界符

DELIMITER ;

并运行新过程

CALL ROWPERROW();

您可以在我从示例请求中简单复制的"INSERT INTO"行中做任何您想做的事情.

You can do whatever you like at the "INSERT INTO" line which I simply copied from your example request.

请注意，此处使用的"INSERT INTO"行将反映问题中的行.根据此答案的注释，您需要确保您的查询在语法上对于所运行的SQL版本都是正确的.

Note CAREFULLY that the "INSERT INTO" line used here mirrors the line in the question. As per the comments to this answer you need to ensure that your query is syntactically correct for which ever version of SQL you are running.

在您的ID字段递增并从1开始的简单情况下，示例中的行可能变为:

In the simple case where your ID field is incremented and starts at 1 the line in the example could become:

INSERT INTO table_B(ID, VAL) VALUES(ID, VAL) FROM table_A WHERE ID=i;

将"SELECT COUNT"行替换为

Replacing the "SELECT COUNT" line with

SET n=10;

仅让您在table_A的前10条记录上测试查询.

Will let you test your query on the first 10 record in table_A only.

最后一件事.此过程也非常容易嵌套在不同的表中，这是我可以对一个表执行一个过程的唯一方法，该过程可将父表的每一行中的不同数量的记录动态插入到新表中.

One last thing. This process is also very easy to nest across different tables and was the only way I could carry out a process on one table which dynamically inserted different numbers of records into a new table from each row of a parent table.

如果您需要它运行得更快，请确保尝试将其设置为基础，如果不是，那么就可以了. 您也可以用游标形式重写上面的内容，但这可能不会提高性能.例如:

If you need it to run faster then sure try to make it set based, if not then this is fine. You could also rewrite the above in cursor form but it may not improve performance. eg:

DROP PROCEDURE IF EXISTS cursor_ROWPERROW;
DELIMITER ;;

CREATE PROCEDURE cursor_ROWPERROW()
BEGIN
  DECLARE cursor_ID INT;
  DECLARE cursor_VAL VARCHAR;
  DECLARE done INT DEFAULT FALSE;
  DECLARE cursor_i CURSOR FOR SELECT ID,VAL FROM table_A;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
  OPEN cursor_i;
  read_loop: LOOP
    FETCH cursor_i INTO cursor_ID, cursor_VAL;
    IF done THEN
      LEAVE read_loop;
    END IF;
    INSERT INTO table_B(ID, VAL) VALUES(cursor_ID, cursor_VAL);
  END LOOP;
  CLOSE cursor_i;
END;
;;

请记住声明要使用的变量与查询表中的变量相同.

Remember to declare the variables you will use as the same type as those from the queried tables.

我的建议是，尽可能使用基于集合的查询，并且在必要时仅使用简单的循环或游标.

My advise is to go with setbased queries when you can, and only use simple loops or cursors if you have to.

这篇关于如何遍历表的所有行? (MySQL)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！