MySQL用While循环插入

2021-12-26 00:00:00 while-loop mysql

我正在尝试在我的 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) 做INSERT INTO coupon (couponCod' 在第 2 行执行停止！

[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.

相关文章