blocks|key|268458|text|游标在这里是一个选项，但通常不受欢迎，因为它们通常不能充分利用查询引擎。考虑研究“基于集合的查询”，看看您是否可以在不使用游标的情况下实现您想要做的事情。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|268459|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

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.

blocks|key|3183848|text|您实际上应该使用包含两个查询(基本插入)的基于集合的解决方案：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3183849|INSERT+INTO+TableB+(Id2Column,+Column33,+Column44)
SELECT+id,+column1,+column2+FROM+TableA

UPDATE+TableA+SET+column1+=+column2+*+column3|code-block|syntax|javascript|3183850|而对于您的转换：|3183851|INSERT+INTO+TableB+(Id2Column,+Column33,+Column44)
SELECT+
++++id,+
++++column1+*+column4+*+100,+
++++(column2+/+column12)+
FROM+TableA

UPDATE+TableA+SET+column1+=+column2+*+column3|3183852|现在，如果您的转换比这更复杂，并且涉及多个表，请发布另一个带有详细信息的问题。|3183853|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Q|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|R|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|S|8|@]|9|@]|A|$]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

You should really use a set based solution involving two queries (basic insert):

<pre><code>INSERT INTO TableB (Id2Column, Column33, Column44)
SELECT id, column1, column2 FROM TableA

UPDATE TableA SET column1 = column2 * column3
</code></pre>

And for your transform:

<pre><code>INSERT INTO TableB (Id2Column, Column33, Column44)
SELECT 
 id, 
 column1 * column4 * 100, 
 (column2 / column12) 
FROM TableA

UPDATE TableA SET column1 = column2 * column3
</code></pre>

Now if your transform is more complicated than that and involved multiple tables, post another question with the details.

blocks|key|268519|text|因为循环的建议暗示了对过程类型解决方案的请求。这是我的。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|268520|从表中获取的任何单个记录上的任何查询都可以包装在一个过程中，使其遍历表的每一行，如下所示：|268521|首先删除任何具有相同名称的现有过程，并更改分隔符，这样SQL就不会在编写过程时尝试运行每一行。|268522|DROP+PROCEDURE+IF+EXISTS+ROWPERROW;
DELIMITER+;;|code-block|syntax|javascript|268523|下面是示例中的过程(为了清楚起见，使用了table_A和table_B+)|268524|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+%2B+1;
END+WHILE;
End;
;;|268525|然后别忘了重置分隔符|268526|DELIMITER+;|268527|并运行新的过程|268528|CALL+ROWPERROW();|268529|您可以在"INSERT+INTO“行做任何您想做的事情，我只是从您的示例请求中复制了这一行。|268530|请注意，这里使用的"INSERT+INTO“行反映了问题中的行。根据对此答案的注释，您需要确保您的查询在语法上是正确的，无论您运行的是哪种版本的SQL。|268531|在简单的情况下，您的ID字段是递增的，并且从1开始，示例中的行可以变成：|268532|INSERT+INTO+table_B(ID,+VAL)+VALUES(ID,+VAL)+FROM+table_A+WHERE+ID=i;|268533|将"SELECT+COUNT“行替换为|268534|SET+n=10;|268535|将允许您仅在table_A中的前10条记录上测试查询。|268536|最后一件事。这个过程也很容易跨不同的表嵌套，这也是我可以在一个表上执行一个过程的唯一方法，该过程从父表的每一行动态地将不同数量的记录插入到新表中。|268537|如果你需要它运行得更快，那么一定要试着让它基于set，如果不是这样，那么这是很好的。你也可以用游标的形式重写上面的代码，但这可能不会提高性能。例如：|268538|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;
;;|268539|请记住，将使用的变量声明为与查询表中的变量相同的类型。|268540|我的建议是尽可能使用基于集合的查询，并且只在必要时使用简单的循环或游标。|268541|entityMap^0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|1M|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|1N|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|1O|8|@]|9|@]|A|$]]|$1|F|3|G|5|H|7|1P|8|@]|9|@]|A|$I|J]]|$1|K|3|L|5|6|7|1Q|8|@]|9|@]|A|$]]|$1|M|3|N|5|H|7|1R|8|@]|9|@]|A|$I|J]]|$1|O|3|P|5|6|7|1S|8|@]|9|@]|A|$]]|$1|Q|3|R|5|H|7|1T|8|@]|9|@]|A|$I|J]]|$1|S|3|T|5|6|7|1U|8|@]|9|@]|A|$]]|$1|U|3|V|5|H|7|1V|8|@]|9|@]|A|$I|J]]|$1|W|3|X|5|6|7|1W|8|@]|9|@]|A|$]]|$1|Y|3|Z|5|6|7|1X|8|@]|9|@]|A|$]]|$1|10|3|11|5|6|7|1Y|8|@]|9|@]|A|$]]|$1|12|3|13|5|H|7|1Z|8|@]|9|@]|A|$I|J]]|$1|14|3|15|5|6|7|20|8|@]|9|@]|A|$]]|$1|16|3|17|5|H|7|21|8|@]|9|@]|A|$I|J]]|$1|18|3|19|5|6|7|22|8|@]|9|@]|A|$]]|$1|1A|3|1B|5|6|7|23|8|@]|9|@]|A|$]]|$1|1C|3|1D|5|6|7|24|8|@]|9|@]|A|$]]|$1|1E|3|1F|5|H|7|25|8|@]|9|@]|A|$I|J]]|$1|1G|3|1H|5|6|7|26|8|@]|9|@]|A|$]]|$1|1I|3|1J|5|6|7|27|8|@]|9|@]|A|$]]|$1|1K|3|-4|5|6|7|28|8|@]|9|@]|A|$]]]|1L|$]]

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:
First delete any existing procedure with the same name, and change the delimiter so your SQL doesn't try to run each line as you're trying to write the procedure.
<pre><code>DROP PROCEDURE IF EXISTS ROWPERROW;
DELIMITER ;;
</code></pre>
Then here's the procedure as per your example (table_A and table_B used for clarity)
<pre><code>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&lt;n DO 
 INSERT INTO table_B(ID, VAL) SELECT (ID, VAL) FROM table_A LIMIT i,1;
 SET i = i + 1;
END WHILE;
End;
;;
</code></pre>
Then dont forget to reset the delimiter
<pre><code>DELIMITER ;
</code></pre>
And run the new procedure
<pre><code>CALL ROWPERROW();
</code></pre>
You can do whatever you like at the &quot;INSERT INTO&quot; line which I simply copied from your example request.
Note CAREFULLY that the &quot;INSERT INTO&quot; 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.
In the simple case where your ID field is incremented and starts at 1 the line in the example could become:
<pre><code>INSERT INTO table_B(ID, VAL) VALUES(ID, VAL) FROM table_A WHERE ID=i;
</code></pre>
Replacing the &quot;SELECT COUNT&quot; line with
<pre><code>SET n=10;
</code></pre>
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:
<pre><code>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;
;;
</code></pre>
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.

blocks|key|272124|text|++++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
++++}+++|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|272125|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code> Use this:

 $stmt = $user-&gt;runQuery("SELECT * FROM tbl WHERE ID=:id");
 $stmt-&gt;bindparam(":id",$id);
 $stmt-&gt;execute();

 $stmt-&gt;bindColumn("a_b",$xx);
 $stmt-&gt;bindColumn("c_d",$yy);


 while($rows = $stmt-&gt;fetch(PDO::FETCH_BOUND))
 {
 //---insert into new tble
 } 
</code></pre>

blocks|key|268552|text|我在mysql触发器中使用的Purple先生的例子，|type|unstyled|depth|inlineStyleRanges|entityRanges|data|268553|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+%2B+1;
END+WHILE;
end|code-block|syntax|javascript|268554|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Mr Purple's example I used in mysql trigger like that,

<pre><code>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&lt;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
</code></pre>

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

Now I want to go through all rows in A.

I found something like 'for each record in A', but this seems to be not how you do it in 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.

edit

I would appreciate an example.

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

edit 2

okay think of this scenario:

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

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

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

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.)
}

How can I loop through all rows of a table? (MySQL)

MySQL

我有一个表A，并且有一个主键ID。现在我要遍历A中的所有行。我找到了类似于'for each record in A‘的内容，但在MySQL中似乎不是这样做的。对于每一行，我想要获取一个字段并对其进行转换，将其插入到另一个表中，然后更新该行的一些字段。我可以将select部分和insert放入一条语句中，但我不知道如何...

问如何遍历一个表的所有行？(MySQL)
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何遍历一个表的所有行？(MySQL)EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何遍历一个表的所有行？(MySQL)
EN