虽然存在于mysql中 [英] While exists in mysql

问题描述

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

How should i use while loop in mysql?

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

错误代码:1064 您的SQL语法有误；请查看与您的MySQL服务器版本相对应的手册，以获取在...附近使用的正确语法.

Error Code : 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near while....

这就是我想做的:

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

1. Inserting 20K records into a table2 from table1.
2. Looping table1 and inserting to table 3 the first 50 records. Deleting table1's 50 records with joining table3.
3. 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 WHILE Or 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 < 400 This 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中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！