mysql 循环插入,MySQL插入带有While循环

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

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!

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?

解决方案

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

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

By the way, you made a typo in your numbers; 2376921001 has 10 digits, 237692200 only 9.