mysql - 从两个表中删除与相同两个表的另一个查询不匹配的行

Question

以下不一定使用正确的语法，但旨在传达我的意图:

DELETE FROM wp_posts, wp_postmeta 
 WHERE wp_posts.ID <> min(f.post_ID), wp_postmeta.post_id <> min(f.post_ID) 

(其实就是两个delete合二为一，分别针对第一张表和第二张表的WHERE条件。)

其中 min(f.post_ID) 来自下面的虚拟表:

SELECT Min(f.post_id), 
       f.post_title, 
       f.meta_value 
FROM   (SELECT wp_posts.post_title, 
               Min(wp_postmeta.meta_value) AS minprice 
        FROM   wp_postmeta 
               JOIN wp_posts 
                 ON wp_postmeta.post_id = wp_posts.id 
        WHERE  wp_posts.post_type = 'Product' 
               AND wp_postmeta.meta_key = '_regular_price' 
        GROUP  BY wp_posts.post_title) AS x 
       INNER JOIN (SELECT wp_postmeta.post_id, 
                          wp_posts.post_title, 
                          wp_postmeta.meta_value 
                   FROM   wp_postmeta 
                          JOIN wp_posts 
                            ON wp_postmeta.post_id = wp_posts.id 
                   WHERE  wp_posts.post_type = 'Product' 
                          AND wp_postmeta.meta_key = '_regular_price' 
                   ORDER  BY wp_posts.post_title, 
                             wp_postmeta.meta_value) AS f 
               ON x.post_title = f.post_title 
                  AND f.meta_value = x.minprice 
GROUP  BY f.post_title 

查询看起来如何？

编辑:值得考虑的是，任何在一个表中先于另一个表删除的实现都会改变第二次删除的结果。 (第二次 DELETE 将受到第一次删除的影响，因为子查询中表中的项目已在第一次 DELETE 中删除。)

Answer 1

您不能同时从两个表中删除。如果表是相关的，则从子表中删除，然后从父表中删除。如果它们不相关，则删除可能以任何顺序发生。如果他们不相关但有其他表依赖于他们(即他们自己是 parent 并且有 child )那么必须首先从其他表中清除数据。如果关系约束设置为CASCADE DELETE模式，则删除父表数据时子表数据会自动删除。如果删除必须作为全有或全无事件发生(即，如果在第一次删除成功后第二次删除失败，您不希望第一次删除成功)，则应在事务中完成。

因此:

DELETE FROM wp_postmeta WHERE post_id NOT IN (

  SELECT Min(f.post_id)
  FROM   (SELECT wp_posts.post_title, 
           Min(wp_postmeta.meta_value) AS minprice 
    FROM   wp_postmeta 
           JOIN wp_posts 
             ON wp_postmeta.post_id = wp_posts.id 
    WHERE  wp_posts.post_type = 'Product' 
           AND wp_postmeta.meta_key = '_regular_price' 
    GROUP  BY wp_posts.post_title) AS x 
   INNER JOIN (SELECT wp_postmeta.post_id, 
                      wp_posts.post_title, 
                      wp_postmeta.meta_value 
               FROM   wp_postmeta 
                      JOIN wp_posts 
                        ON wp_postmeta.post_id = wp_posts.id 
               WHERE  wp_posts.post_type = 'Product' 
                      AND wp_postmeta.meta_key = '_regular_price' 
               ORDER  BY wp_posts.post_title, 
                         wp_postmeta.meta_value) AS f 
           ON x.post_title = f.post_title 
              AND f.meta_value = x.minprice 
  GROUP  BY f.post_title
)

DELETE FROM wp_posts WHERE ID NOT IN (

  SELECT Min(f.post_id)
  FROM   (SELECT wp_posts.post_title, 
           Min(wp_postmeta.meta_value) AS minprice 
    FROM   wp_postmeta 
           JOIN wp_posts 
             ON wp_postmeta.post_id = wp_posts.id 
    WHERE  wp_posts.post_type = 'Product' 
           AND wp_postmeta.meta_key = '_regular_price' 
    GROUP  BY wp_posts.post_title) AS x 
   INNER JOIN (SELECT wp_postmeta.post_id, 
                      wp_posts.post_title, 
                      wp_postmeta.meta_value 
               FROM   wp_postmeta 
                      JOIN wp_posts 
                        ON wp_postmeta.post_id = wp_posts.id 
               WHERE  wp_posts.post_type = 'Product' 
                      AND wp_postmeta.meta_key = '_regular_price' 
               ORDER  BY wp_posts.post_title, 
                         wp_postmeta.meta_value) AS f 
           ON x.post_title = f.post_title 
              AND f.meta_value = x.minprice 
  GROUP  BY f.post_title
)

警告

如果没有先备份您的数据，请不要运行互联网上某人给您的删除查询。至少，使用以下代码模式启动事务、运行删除、选择结果并查看它们以确保它们正确无误:

START TRANSACTION;
DELETE FROM ...
DELETE FROM ...
SELECT * FROM ... -/*to check the deletes worked and didn't remove too much*/
ROLLBACK;

当你高兴的时候，将 ROLLBACK 更改为 COMMIT

编辑:

选项 1

使帖子元依赖于帖子(它可能已经这样做了，确保删除是级联的):

ALTER TABLE posts_meta
ADD CONSTRAINT fk_pm FOREIGN KEY (posts_id) REFERENCES posts(id) ON DELETE CASCADE

现在在 posts 表上运行删除，posts_meta 条目也将消失

选项2

按照上面的建议在 posts 表上运行删除

使用以下查询从 posts_meta 中删除在帖子中没有匹配记录的任何记录:

DELETE FROM posts_meta WHERE post_id IN (select post_id from(
  SELECT pm.post_id 
  FROM
    posts_meta pm
    LEFT JOIN 
    posts p
    ON p.id = pm.post_id
  WHERE
    p.id IS NULL

) i )

查找 id 列表的最内层子查询被包裹在另一个子查询中是有原因的；在某些情况下，如果模式是 DELETE FROM x WHERE y IN (SELECT x FROM y)，MySQL 将拒绝删除，因为您无法修改您从中选择的表。将它包装在另一个选择中是一种黑客攻击，导致 MySQL 不将其视为从您选择的同一个表中删除