如何在MySQL语句中使用嵌套循环和变量
我正在尝试编写一个 sql 语句,该语句将在给定一些设置变量的情况下插入数据。我不想创建存储过程,因为除了作为管理员之外,我不会在任何地方处理它,而且我不希望存储过程闲置。我只想要一个有效的声明。这是我到目前为止所得到的:
SET @app = 7;
SET @time = UNIX_TIMESTAMP(NOW());
SET @maxValue = 100;
SET @decrement = 10;
SET @category = 1;
SET @minSubcategory = 0;
SET @maxSubcategory = 19;
SET @subcategory = @minSubcategory;
subcat_loop: LOOP
SET @idx = 0;
insert_loop: LOOP
SET @id = CONCAT('TempId', @idx+1);
SELECT @name:=username FROM user WHERE id = @id;
SET @value = @maxValue - @decrement * @idx;
INSERT INTO data (userId, username, app, category, subcategory, value, date) VALUES
(@id, @name, @app, @category, @subcategory, @value, @time);
SET @idx = @idx+ 1;
IF @idx > 10 THEN
LEAVE insert_loop;
END IF;
END LOOP insert_loop;
SET @subcategory = @subcategory + 1;
IF @subcategory > @maxSubcategory THEN
LEAVE subcat_loop;
END IF;
END LOOP subcat_loop;
但由于某种原因,它不喜欢循环内的 SET @idx = 0
。我做错了什么?
请注意,这可能是我第一次尝试使用 MySQL 做任何如此复杂的事情,我的一知半解可能比完全忘记更危险,所以让我知道我是否完全错误地处理了这个问题(尽管我真的,真的不想为此使用存储过程)。
I'm trying to write a sql statement that will insert data given a few setup variables. I don't want to create a stored procedure, as it's not something I'll be dealing with anywhere except as an administrator and I don't want the stored procedure hanging around. I just want a statement that works. Here's what I have so far:
SET @app = 7;
SET @time = UNIX_TIMESTAMP(NOW());
SET @maxValue = 100;
SET @decrement = 10;
SET @category = 1;
SET @minSubcategory = 0;
SET @maxSubcategory = 19;
SET @subcategory = @minSubcategory;
subcat_loop: LOOP
SET @idx = 0;
insert_loop: LOOP
SET @id = CONCAT('TempId', @idx+1);
SELECT @name:=username FROM user WHERE id = @id;
SET @value = @maxValue - @decrement * @idx;
INSERT INTO data (userId, username, app, category, subcategory, value, date) VALUES
(@id, @name, @app, @category, @subcategory, @value, @time);
SET @idx = @idx+ 1;
IF @idx > 10 THEN
LEAVE insert_loop;
END IF;
END LOOP insert_loop;
SET @subcategory = @subcategory + 1;
IF @subcategory > @maxSubcategory THEN
LEAVE subcat_loop;
END IF;
END LOOP subcat_loop;
But it doesn't like the SET @idx = 0
inside the loop for some reason. What am I doing wrong?
Note that this is probably the first time I've tried doing anything this complicated with MySQL, and my little knowledge is probably more dangerous than being completely oblivious, so let me know if I'm going about this the wrong way completely (although I really, really don't want a stored procedure for this).
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(1)
不幸的是,您不能在存储程序之外使用 LOOP:存储过程、存储函数和触发器。
您确实有一些选择:
否则,如果您仍然不想创建存储过程,最好的选择是在 shell、python 等中编写一个小脚本来执行循环。
祝你好运!
Unfortunately you can't use LOOP outside of a stored program: stored procedures, stored functions, and triggers.
You do have some options:
Otherwise, if you still don't want to create a stored procedure, your best bet is to write a small script in shell, python, etc to do your looping.
Good luck!