使用 MySQL 分块删除 [英] Deleting in chunks with MySQL

问题描述

我正在尝试从我的 AWS RDS MySQL 实例中分块删除大量数据.我正在尝试修改此 link 中的代码，但我我遇到了一个我无法修复的语法错误.

I'm trying to delete a large amount of data from my AWS RDS MySQL instance in chunks. I'm trying to adapt the code found in this link, but I'm stuck with a syntax error I cannot fix.

错误:

SQL Error (1064): 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 'main1: LOOP
    SELECT @z := operation_id FROM operations_test WHERE operation_id' at line 2

代码:

SET @apt = 'DTW'; 

SET @a = (SELECT MIN(operation_id) FROM operations_test);

BEGIN
    main1: LOOP
        SELECT @z := operation_id FROM operations_test WHERE operation_id >= @a ORDER BY operation_id LIMIT 1000,1;
        IF @z IS NULL THEN
          LEAVE main1;  -- last chunk
       END IF;
       DELETE operations_test, profiles_test FROM profiles_test LEFT JOIN operations_test ON operations_test.operation_id=profiles_test.operation_id WHERE operations_test.airport_id = @apt
            AND operations_test.operation_id >= @a
             AND operations_test.operation_id <  @z;
       DELETE operations_test FROM operations_test WHERE airport_id = @apt
            AND operation_id >= @a
             AND operation_id <  @z;
       SET @a = @z;
        SLEEP 1;  -- be a nice guy, especially in replication
    END LOOP main1;
END;
# Last chunk:
DELETE operations_test, profiles_test FROM profiles_test LEFT JOIN operations_test ON operations_test.operation_id=profiles_test.operation_id WHERE operations_test.airport_id = @apt
    AND id >= @a;
DELETE operations_test FROM operations_test WHERE airport_id = @apt
    AND id >= @a;

推荐答案

BEGIN ... END 和 LOOP 语句仅在 MySQL 存储的上下文中有效程序(例如 PROCEDURE、FUNCTION、TRIGGER、)

The BEGIN ... END and LOOP statements are only valid in the context of a MySQL stored program (e.g. a PROCEDURE, FUNCTION, TRIGGER, )

作为使用示例:

DELIMITER $$

CREATE PROCEDURE foo 
BEGIN 
   SET ... ;
   SET ... ; 
   myloop: LOOP 
     ...
   END LOOP myloop;
END$$

DELIMITER ;

鉴于我们没有看到 CREATE PROCEDURE 语句，我们将猜测错误消息是将其作为裸语句执行的结果.语法错误是预期的结果.

Given that we don't see a CREATE PROCEDURE statement, we're going to guess that the error message is the result of executing this as a bare statement. A syntax error is the expected result.

https://dev.mysql.com/doc/refman/5.7/en/begin-end.html

https://dev.mysql.com/doc/refman/5.7/en/loop.html

这篇关于使用 MySQL 分块删除的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！