MySQL MySQL用While循环插入
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26981901/
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
MySQL Insert with While Loop
提问by jessier3
I'm trying to create a bunch of records in my MySQL database. This is a one time creation so I am not trying to create a stored procedure. Here is my code:
我正在尝试在我的 MySQL 数据库中创建一堆记录。这是一次性创建，因此我不打算创建存储过程。这是我的代码：
BEGIN
SET i = 2376921001;
WHILE (i <= 237692200) DO
INSERT INTO `mytable` (code, active, total) values (i, 1, 1);
SET i = i+1;
END WHILE;
END
Here is the error:
这是错误：
[ERROR in query 1] 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 'SET i = 2376921001
WHILE (i <= 237692200) DO
INSERT INTO coupon(couponCod' at line 2
Execution stopped!
[查询 1 中的错误] 您的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册，以获取在 'SET i = 2376921001 WHILE (i <= 237692200) DO INSERT INTO coupon(couponCod' at line 2 执行停止！
I have tried a Declare with the same results. Code below:
我已经尝试了一个具有相同结果的声明。代码如下：
BEGIN
DECLARE i INT unsigned DEFAULT 2376921001;
WHILE (i <= 237692200) DO
INSERT INTO `mytable` (code, active, total) values (i, 1, 1);
SET i = i+1;
END WHILE;
END
The one other thing I have tried is with @i instead of just i. Same error. Can anyone see what I am doing wrong?
我尝试过的另一件事是使用@i 而不仅仅是 i。同样的错误。谁能看到我做错了什么？
采纳答案by Ruud Helderman
You cannot use WHILElike that; see: mysql DECLARE WHILE outside stored procedure how?
你不能那样使用WHILE；参见：mysql DECLARE WHILE 外部存储过程如何？
You have to put your code in a stored procedure. Example:
您必须将代码放在存储过程中。例子：
CREATE PROCEDURE myproc()
BEGIN
DECLARE i int DEFAULT 237692001;
WHILE i <= 237692004 DO
INSERT INTO mytable (code, active, total) VALUES (i, 1, 1);
SET i = i + 1;
END WHILE;
END
Fiddle: http://sqlfiddle.com/#!2/a4f92/1
小提琴：http://sqlfiddle.com/#!2/a4f92/1
Alternatively, generate a list of INSERTstatements using any programming language you like; for a one-time creation, it should be fine. As an example, here's a Bash one-liner:
或者，INSERT使用您喜欢的任何编程语言生成语句列表；对于一次性创建，应该没问题。例如，这是一个 Bash one-liner：
for i in {2376921001..2376921099}; do echo "INSERT INTO mytable (code, active, total) VALUES ($i, 1, 1);"; done
By the way, you made a typo in your numbers; 2376921001 has 10 digits, 237692200 only 9.
顺便说一下，你的数字打错了；2376921001有10位数字，237692200只有9位。
回答by Lily.He
drop procedure if exists doWhile;
DELIMITER //
CREATE PROCEDURE doWhile()
BEGIN
DECLARE i INT DEFAULT 2376921001;
WHILE (i <= 237692200) DO
INSERT INTO `mytable` (code, active, total) values (i, 1, 1);
SET i = i+1;
END WHILE;
END;
//
CALL doWhile();
