如何重写DELETE查询，以便限制受影响的行？

Question

我知道mysql不允许对多个表删除查询使用限制。我已经在网上寻找了一个解决方案，但我不确定如何使它们适应我的需求。下面的查询删除了所有不在内容中的内容，以保留表和其他条件。

DELETE f
    FROM   field_data_body f
           INNER JOIN node n
                   ON f.entity_id = n.nid
           LEFT JOIN content_to_keep k
                  ON n.nid = k.nid
    WHERE  n.type = 'article'
           AND k.nid IS NULL

那么，我有哪些选项可以添加一个限制参数。

编辑:谢谢你的帮助，但大多数答案都使用了where in子句。当我使用它时，我得到一个错误，说“这个版本的MySQL还不支持'LIMIT &IN/ALL/ANY/SOME‘子查询’”

Answer 1

在选择要删除的行的代码部分添加ORDER BY和LIMIT，然后将其放入派生表中并连接回要删除的表：

DELETE f_del
FROM field_data_body AS f_del
   JOIN
      ( SELECT f.PK                            --- the Primary Key of the table
        FROM   field_data_body f
               INNER JOIN node n
                       ON f.entity_id = n.nid
               LEFT JOIN content_to_keep k
                      ON n.nid = k.nid
        WHERE  n.type = 'article'
               AND k.nid IS NULL
        ORDER BY some_column
        LIMIT 100
      ) AS tmp
      ON tmp.PK = f_del.PK ; 

Answer 2

DELETE
FROM   field_data_body
WHERE id in (
    SELECT f.id
    FROM   field_data_body f
           INNER JOIN node n
                   ON f.entity_id = n.nid
           LEFT JOIN content_to_keep k
                  ON n.nid = k.nid
    WHERE  n.type = 'article'
           AND k.nid IS NULL)
LIMIT 10; --or whatever you want

Answer 3

也许是为了让你走上正确的方向：

……WHERE IN (SELECT ...从nid为空的field_data_body )