mysql - 如何在 mysql 中正确执行 LOOP

Question

好吧，这可能很荒谬，但我已经挠头好几分钟了。这个语法有什么问题？

SET @maxIterations = 10;
SET @i = 0;

myloop: LOOP
    SET @i = @i + 1;

    UPDATE `mytable`
    SET `field` = 'value';

    if @i < @maxIterations THEN ITERATE myloop; END IF;

    LEAVE myloop;
END LOOP myloop;

我在第 1 行出现错误 near 'myloop: LOOP SET i = i + 1'

Answer 1

您的语法通常是正确的，除了 MySQL 不允许在存储过程或函数(“存储程序”)以外的任何内容中使用流控制结构，如 IF/ELSE、WHILE、LOOP。此限制is documented here :

MySQL supports the IF, CASE, ITERATE, LEAVE LOOP, WHILE, and REPEAT constructs for flow control within stored programs. It also supports RETURN within stored functions.

您将能够正确地将其创建为存储过程，如下所示:

DELIMITER $$
CREATE PROCEDURE myproc()
BEGIN
  SET @maxIterations = 10;
  SET @i = 0;

  myloop: LOOP
      SET @i = @i + 1;

      UPDATE `mytable`
      SET `field` = 'value';

      if @i < @maxIterations THEN ITERATE myloop; END IF;

      LEAVE myloop;
  END LOOP myloop;
END$$
DELIMITER ;