我正试图从我的aws-rds-mysql示例中分块删除大量数据。我正在尝试修改这个链接中的代码,但是我遇到了一个无法修复的语法错误。
错误:
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;
1条答案
按热度按时间ztigrdn81#
这个
BEGIN ... END
以及LOOP
语句仅在mysql存储程序的上下文中有效(例如PROCEDURE
,FUNCTION
,TRIGGER
, )作为用法示例:
鉴于我们没有看到
CREATE PROCEDURE
语句,我们将猜测错误消息是作为裸语句执行的结果。预期结果是语法错误。https://dev.mysql.com/doc/refman/5.7/en/begin-end.html
https://dev.mysql.com/doc/refman/5.7/en/loop.html