Mariadb大删除与连接和限制

owfi6suc 于 2023-05-29 发布在其他

关注(0)|答案(1)|浏览(172)

我在试着去掉很多行
我的查询很简单，但有一个JOIN，LIMIT是不允许的。我真的不明白为什么，因为它不是LEFT JOIN。
我想将其 Package 在批处理中的事件调度程序中。我目前被LIMIT部分卡住了。

# working
DELETE invitations from invitations JOIN events on events.id = invitations.event_id WHERE (...)

# not working
DELETE invitations from invitations JOIN events on events.id = invitations.event_id WHERE (...) LIMIT 10

我也有过类似的想法

SET GLOBAL event_scheduler = ON;

CREATE EVENT invitation_cleaner_event
ON SCHEDULE EVERY 1 DAY
STARTS '2023-05-26 04:00:00'
DO
BEGIN
    REPEAT
        DO SLEEP(1);  
        # my query to delete with join and limit
    UNTIL ROW_COUNT() = 0 END REPEAT;
END

mariadb

来源：https://stackoverflow.com/questions/76339957/mariadb-large-deletion-with-join-and-limit

1条答案

按热度按时间

zengzsys1#

在MySQL中，LIMIT子句不允许出现在包含JOIN的DELETE语句中。存在此限制是因为LIMIT子句应用于整个结果集，而不是JOIN中涉及的单个表。因此，它可能会导致不明确或意外的行为。
与直接在DELETE语句中使用LIMIT不同，您可以使用带有LIMIT子句的子查询来检索要删除的特定行，然后在JOIN条件中使用该结果：

DELETE a
FROM a
JOIN (
  SELECT b.id
  FROM a
  JOIN b ON b.id = a.b_id
  LIMIT 10
) AS subquery ON subquery.id = a.b_id;

所以你的事件调度器看起来像这样：

SET GLOBAL event_scheduler = ON;

CREATE EVENT invitation_cleaner_event
ON SCHEDULE EVERY 1 DAY
STARTS '2023-05-26 04:00:00'
DO
BEGIN
  DECLARE rows_deleted INT;
  SET rows_deleted = 1;
  WHILE rows_deleted > 0 DO
    DELETE a
    FROM a
    JOIN (
      SELECT b.id
      FROM a
      JOIN b ON b.id = a.b_id
      LIMIT 10
    ) AS subquery ON subquery.id = a.b_id;

    SET rows_deleted = ROW_COUNT();
    DO SLEEP(1);
  END WHILE;
END

赞(0）回复(0）举报 2023-05-29

我来回答

Mariadb大删除与连接和限制

1条答案

相关问题

热门标签

最新问答