如何从数据库中删除属于特定类别的wooccommerce产品

Question

我需要一个MySql查询语句来删除所有属于一个类别或不属于另一个类别的wooccommerce产品，类似于以下内容：

DELETE FROM wp_postmeta WHERE post_id IN (SELECT ID
FROM wp_posts
LEFT JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id)
LEFT JOIN wp_term_taxonomy ON (wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id)
WHERE wp_term_taxonomy.term_id NOT IN (29,31,32,33,34) AND post_type IN ('product','product_variation')
GROUP BY wp_posts.ID);

我正在处理大量的数据，超过100000种产品

Answer 1

如果有人有同样的需求。这就是我如何解决删除：

1-最重要的是确保类别不会相互重叠，就像你试图删除的产品没有附加其他类别一样。这可以手动完成(如果您的产品很少(，也可以通过查询取消附加不需要的关系。

2-接下来，我将建议删除每次只属于一个类别的产品，查询应该是特定的，以防止任何不希望的结果和冲突。

3-在删除之前选择产品，看看是否与此查询有任何冲突：

SELECT * FROM wp_posts WHERE ID IN(SELECT posts.object_id from (SELECT object_id FROM wp_term_relationships
LEFT JOIN wp_posts ON (wp_posts.ID = wp_term_relationships.object_id)
WHERE term_taxonomy_id = 38 AND post_type IN ('product','product_variation')
GROUP BY object_id) as posts);

4-每次只删除一个想要的类别(BY ID(产品：

DELETE FROM wp_posts WHERE ID IN(SELECT posts.object_id from (SELECT object_id FROM wp_term_relationships
LEFT JOIN wp_posts ON (wp_posts.ID = wp_term_relationships.object_id)
WHERE term_taxonomy_id = 38 AND post_type IN ('product','product_variation')
GROUP BY object_id) as posts);

对于每个类别的30000多种产品，上述查询执行了1秒

Answer 2

接受的答案删除产品，但保留元数据。

DELETE FROM wp_postmeta WHERE post_id IN( SELECT id FROM wp_posts WHERE ID IN(SELECT posts.object_id from (SELECT object_id FROM wp_term_relationships
LEFT JOIN wp_posts ON (wp_posts.ID = wp_term_relationships.object_id)
WHERE term_taxonomy_id = 38 AND post_type IN ('product','product_variation')
GROUP BY object_id) as posts));

在删除查询之前执行此SQL查询将删除该类别产品的元数据。在此之后，可以执行删除产品的查询(所选答案的第四步(。