如何在MySQL语句中使用嵌套循环和变量 [英] How to use nested loops and variables in MySQL statement

问题描述

我正在尝试编写一条sql语句，该语句将在给定一些设置变量的情况下插入数据.我不想创建存储过程，因为除了管理员以外，我不会在任何地方处理该存储过程，并且我不希望该存储过程随处可见.我只想发表一个行之有效的声明.这是我到目前为止的内容:

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;

但是由于某种原因，它不喜欢循环内的 SET @idx = 0 .我在做什么错了?

But it doesn't like the SET @idx = 0 inside the loop for some reason. What am I doing wrong?

请注意，这可能是我第一次尝试使用MySQL做这种复杂的事情，而我的一点知识可能比完全遗忘更危险，所以请让我知道我是否打算完全以错误的方式进行操作(虽然我真的，真的不想要一个存储过程).

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

推荐答案

不幸的是，您不能在存储程序之外使用LOOP:存储过程，存储函数和触发器.

Unfortunately you can't use LOOP outside of a stored program: stored procedures, stored functions, and triggers.

您确实有一些选择:

1. 您可以创建存储过程并限制特权，以便其他用户无法执行它.
2. 另一种选择是在脚本中临时创建一个存储过程，运行它，然后将其放在脚本末尾.

否则，如果您仍然不想创建存储过程，那么最好的选择是用shell，python等编写一个小的脚本来进行循环.

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.

祝你好运！

这篇关于如何在MySQL语句中使用嵌套循环和变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！