虽然存在于mysql中 | 虽然存在于mysql中

本文介绍了虽然存在于mysql中的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我应该如何在mysql中使用while循环?

How should i use while loop in mysql?

while exists (select * from table1)
Do
 //sql statements
end while

这就是我想做的:

将20K条记录从table1插入table2中.
环绕table1并插入到table3前50条记录.使用联接表3删除表1的50条记录.
从table1中删除20K条记录时结束循环.

Inserting 20K records into a table2 from table1.
Looping table1 and inserting to table3 the first 50 records. Deleting table1's 50 records with joining table3.
End loop when 20K records are deleted from table1.

有什么帮助/想法吗?

推荐答案

您可以尝试确保在END WHILE之后使用分号或者，也可以尝试在语句周围放置BEGIN和END;.

You might try making sure there is a semicolon after your END WHILEOr alternatively try putting a BEGIN and END; around your statements.

但是，我们确实应该研究基于集合的方法.

However, we really should examine a set-based method.

如果像您的评论一样，您决定以50行为单位进行此操作....无论出于何种原因，...您可以尝试选择行数，然后将其除以您的批处理大小，以获得一个交互计数变量.

If, as in your comments, you are determined to do this in 50 row batches.... for whatever reason...You may try selecting the rowcount, then dividing it by your batch size to get an interation count variable.

例如，如果您有20,000行，并且要按50行批量插入，则您将需要进行400次迭代.然后，您可以使用int并执行WHILE X < 400这将比进行EXISTS查询更快.

For example, if you have 20,000 rows and you are inserting them in batched of 50 then you know that you will need 400 iterations. Then you can use an int and do WHILE X < 400This will be faster than doing EXISTS queries.

未经测试的代码(可能也不是有效的语法，但应该可以使您理解)

Untested code (probably not valid syntax either, but should give you the idea)

INSERT INTO table2 (columns)
SELECT columns from table1

DECLARE @TopCount int DEFAULT 0;
DECLARE @CurrCount int DEFAULT 0;
SELECT @TopCount = COUNT(*) from table2

DO WHILE (@CurrCount < @TopCount)
 BEGIN
  INSERT INTO table3 (columns)
  SELECT columns from table2 LIMIT 50
  DELETE table2 LIMIT 50
  SET @CurrCount = @CurrCount + 50
 END;
END WHILE;

这真是愚蠢，恕我直言，但我仍然不明白你的目标.

This is pretty silly, imho, but I still do not understand your goal.

这篇关于虽然存在于mysql中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！