MySQL - 使用 WHILE DO 批量更新

Question

有人可以告诉我我在这里做错了什么吗？

我不断收到您的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册，了解在附近使用的正确语法...

我正在尝试小批量运行 UPDATE 命令，以防止表被阻塞(在运行 1000 万条记录时)。

DELIMITER //

CREATE FUNCTION batch_update()
BEGIN
  SET processed = 0;
  SET eachbatch = 1000;

  WHILE processed < 1000000 DO          
      UPDATE `users` SET `active` = 1 WHERE `active` = 0 LIMIT eachbatch;

       -- Finish when no rows remaining
      IF ROW_COUNT() = 0 THEN LEAVE;
      END IF;

      -- No infinite loop, thanks
      SET processed = processed + eachbatch;
  END WHILE;
END //

DELIMITER ;

CALL batch_update();

编辑:对于任何需要指针的人，我的工作方式如下:

DROP FUNCTION IF EXISTS process_batch_rows;   

DELIMITER //    
CREATE FUNCTION process_batch_rows() RETURNS VARCHAR(10) DETERMINISTIC
BEGIN
  DECLARE processed INT DEFAULT 0;
  DECLARE eachbatch INT DEFAULT 1000;

  my_loop: WHILE processed < 1000000
  DO
    UPDATE `users` SET `active` = 1 WHERE `active` = 0 LIMIT eachbatch;

    IF ROW_COUNT() < 1 THEN
        LEAVE my_loop;
    END IF;

    SET processed = processed + eachbatch;
  END WHILE my_loop;    

  RETURN "Done";
END //    
DELIMITER ;

SELECT process_batch_rows();

Answer 1

试试这个，我在需要的地方添加了评论 =>

DELIMITER //

-- You must specify return type

CREATE FUNCTION batch_update() RETURNS VARCHAR(10) DETERMINISTIC

BEGIN

    -- you must declare variables

    DECLARE processed int(10);

    DECLARE eachbatch int(10);

    SET processed = 0;

    SET eachbatch = 1000;

    -- needed my loop here so it can be used in leave

    myloop: WHILE processed < 1000000 DO          

        UPDATE `users` SET `active` = 1 WHERE `active` = 0 LIMIT eachbatch;

        -- Finish when no rows remaining

        IF ROW_COUNT() = 0 

        THEN 

            LEAVE myloop;

        END IF;

        -- No infinite loop, thanks

        SET processed = processed + eachbatch;

    END WHILE;

    return 'Done';

END //

DELIMITER ;