带While循环的MySQL插入 [英] MySQL Insert with While Loop
问题描述
我正在尝试在MySQL数据库中创建一堆记录.这是一次创建,因此我不尝试创建存储过程.这是我的代码:
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:
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
这是错误:
[查询1中的错误]您的SQL语法有错误;检查与您的MySQL服务器版本相对应的手册,以在'SET i = 2376921001附近使用正确的语法
当(i< = 237692200)做
在第2行中插入coupon
(couponCod'
执行停止!
[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!
我尝试了一个具有相同结果的声明.下面的代码:
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
我尝试过的另一件事是使用@i而不是仅使用i.同样的错误. 有人可以看到我在做什么错吗?
The one other thing I have tried is with @i instead of just i. Same error. Can anyone see what I am doing wrong?
推荐答案
您不能像这样使用WHILE
;参见: mysql声明在存储过程之外的方式如何?
You cannot use WHILE
like that; see: mysql DECLARE WHILE outside stored procedure how?
您必须将代码放入存储过程中.示例:
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
提琴: http://sqlfiddle.com/#!2/a4f92/1
或者,使用您喜欢的任何编程语言生成INSERT
语句的列表;对于一次性创建,应该没问题.例如,这是一个Bash单线:
Alternatively, generate a list of INSERT
statements using any programming language you like; for a one-time creation, it should be fine. As an example, here's a Bash one-liner:
for i in {2376921001..2376921099}; do echo "INSERT INTO mytable (code, active, total) VALUES ($i, 1, 1);"; done
顺便说一句,你在数字上打错了字; 2376921001有10位数字,只有237692200 9.
By the way, you made a typo in your numbers; 2376921001 has 10 digits, 237692200 only 9.
这篇关于带While循环的MySQL插入的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!