如何遍历表的所有行？(MySQL)
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5817395/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I loop through all rows of a table? (MySQL)
提问by Raffael
I have a table A and there is one primary key ID.
我有一张表 A，有一个主键 ID。
Now I want to go through all rows in A.
现在我想遍历 A 中的所有行。
I found something like 'for each record in A', but this seems to be not how you do it in MySQL.
我发现了类似“对于 A 中的每条记录”这样的内容，但这似乎不是您在 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.
对于每一行，我想获取一个字段并对其进行转换，将其插入另一个表中，然后更新该行的一些字段。我可以将 select 部分和 insert 放入一个语句中，但我也不知道如何在其中获取更新。所以我想循环。为了练习，我不想使用 MySQL 以外的任何东西。
edit
编辑
I would appreciate an example.
我会很感激的一个例子。
And a solution which does not need to be put into a procedure.
以及不需要放入程序的解决方案。
edit 2
编辑 2
okay think of this scenario:
好吧，想想这个场景：
Table A and B, each with fields ID and VAL.
表 A 和 B，每个都有字段 ID 和 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]);
}
basically copying content of A into B using a loop.
基本上使用循环将 A 的内容复制到 B 中。
(this is just a simplified example, of course you wouldn't use a loop for this.) }
（这只是一个简化的例子，当然你不会为此使用循环。）}
回答by Mr Purple
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 ;;
Then here's the procedure as per your example (table_A and table_B used for clarity)
然后这是根据您的示例的程序（为清楚起见，使用 table_A 和 table_B）
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;
;;
Then dont forget to reset the delimiter
然后不要忘记重置分隔符
DELIMITER ;
And run the new procedure
并运行新程序
CALL ROWPERROW();
You can do whatever you like at the "INSERT INTO" line which I simply copied from your example request.
你可以在“INSERT INTO”行做任何你喜欢的事情，我只是从你的示例请求中复制了它。
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.
请注意，此处使用的“INSERT INTO”行反映了问题中的行。根据对此答案的评论，您需要确保您的查询对于您正在运行的 SQL 版本在语法上是正确的。
In the simple case where your ID field is incremented and starts at 1 the line in the example could become:
在 ID 字段递增并从 1 开始的简单情况下，示例中的行可能变为：
INSERT INTO table_B(ID, VAL) VALUES(ID, VAL) FROM table_A WHERE ID=i;
Replacing the "SELECT COUNT" line with
将“SELECT COUNT”行替换为
SET n=10;
Will let you test your query on the first 10 record in table_A only.
将让您仅在 table_A 中的前 10 条记录上测试您的查询。
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.
我的建议是尽可能使用基于集合的查询，并且仅在必要时使用简单的循环或游标。
回答by Raj More
You should really use a set based solution involving two queries (basic insert):
您应该真正使用涉及两个查询（基本插入）的基于集合的解决方案：
INSERT INTO TableB (Id2Column, Column33, Column44)
SELECT id, column1, column2 FROM TableA
UPDATE TableA SET column1 = column2 * column3
And for your transform:
对于您的转换：
INSERT INTO TableB (Id2Column, Column33, Column44)
SELECT
id,
column1 * column4 * 100,
(column2 / column12)
FROM TableA
UPDATE TableA SET column1 = column2 * column3
Now if your transform is more complicated than that and involved multiple tables, post another question with the details.
现在，如果您的转换比这更复杂并且涉及多个表，请发布另一个问题并提供详细信息。
回答by Ron Weston
CURSORS are an option here, but generally frowned upon as they often do not make best use of the query engine. Consider investigating 'SET Based Queries' to see if you can achieve what it is you want to do without using a CURSOR.
CURSORS 是这里的一个选项，但通常不赞成，因为它们通常没有充分利用查询引擎。考虑研究“基于 SET 的查询”，看看您是否可以在不使用 CURSOR 的情况下实现您想要做的事情。
回答by Erkan RUA
Mr Purple's example I used in mysql trigger like that,
我在 mysql 触发器中使用的 Purple 先生的例子就是这样，
begin
DECLARE n INT DEFAULT 0;
DECLARE i INT DEFAULT 0;
Select COUNT(*) from user where deleted_at is null INTO n;
SET i=0;
WHILE i<n DO
INSERT INTO user_notification(notification_id,status,userId)values(new.notification_id,1,(Select userId FROM user LIMIT i,1)) ;
SET i = i + 1;
END WHILE;
end
回答by utee
Use this:
$stmt = $user->runQuery("SELECT * FROM tbl WHERE ID=:id");
$stmt->bindparam(":id",$id);
$stmt->execute();
$stmt->bindColumn("a_b",$xx);
$stmt->bindColumn("c_d",$yy);
while($rows = $stmt->fetch(PDO::FETCH_BOUND))
{
//---insert into new tble
}
