database - Delete all posts of a custom post type—efficiently

admin管理员组

文章数量:1122832

I'm looking for a safe and fast way to delete all of one custom post type's posts. Using get_posts() and wp_delete_post() for each returned post does not work; it's not fast enough due to the sheer amount of database queries involved (timeout error).

Preferably, I'm looking for a single database query to run that deletes all posts that are of a custom post type. Any thoughts?

I'm looking for a safe and fast way to delete all of one custom post type's posts. Using get_posts() and wp_delete_post() for each returned post does not work; it's not fast enough due to the sheer amount of database queries involved (timeout error).

Preferably, I'm looking for a single database query to run that deletes all posts that are of a custom post type. Any thoughts?

• custom-post-types
• database
• performance

Share Improve this question asked Nov 13, 2015 at 21:29 Marcus McLeanMarcus McLean 38111 gold badge33 silver badges44 bronze badges 9

• Is this a one-time event? If so, quick sql query via phpMyAdmin seems easiest. If this is a house-keeping step needed to be done programmatically/repeatedly, that won't help you. – jdm2112 Commented Nov 13, 2015 at 21:53
• It needs to be done regularly, without manual database access, unfortunately. – Marcus McLean Commented Nov 13, 2015 at 22:03
• Roger that. I would recommend looking at wpdb class then. Preferred method and "the WP way" for working directly with the DB. Let us know if you need help with that query. I can post a full answer later if needed codex.wordpress.org/Class_Reference/wpdb – jdm2112 Commented Nov 13, 2015 at 22:14
• 3 Use 'fields' => 'ids', in get_posts to get only post ID. This is all you need and it will significantely speed up your query – Pieter Goosen Commented Nov 14, 2015 at 3:09
• 1 @MarcusMcLean : obviously, you missed my point ;) A single post has data in the posts table, the postmeta table, the taxonomy table(s), and possibly the options table. Any pure SQL query you write to delete those posts will be quite complicated. You stand a good chance of leaving things behind in those various tables. I would try deleting, say, 50 at a time at 5 minute intervals using the Core functions and wp_cron() – s_ha_dum Commented Nov 14, 2015 at 4:08

 |  Show 4 more comments

4 Answers 4

Sorted by: Reset to default 42

You can delete all post via $wpdb

DELETE FROM wp_posts WHERE post_type='post_type';
DELETE FROM wp_postmeta WHERE post_id NOT IN (SELECT id FROM wp_posts);
DELETE FROM wp_term_relationships WHERE object_id NOT IN (SELECT id FROM wp_posts)

or use this query replace it with {{your CPT}} with your Custom Post Type

DELETE a,b,c
    FROM wp_posts a
    LEFT JOIN wp_term_relationships b
        ON (a.ID = b.object_id)
    LEFT JOIN wp_postmeta c
        ON (a.ID = c.post_id)
    WHERE a.post_type = '{{your CPT}}';

This can now be done with the WordPress CLI using wp post delete. Once the CLI is installed, the following terminal command (while in your site's root directory) will delete all posts of type mycustomtype:

wp post delete $(wp post list --post_type='mycustomtype' --format=ids)

No raw SQL (*shudder*), no worrying about timeouts, and it's fast. For example, I just deleted ~2500 posts in less than two minutes.

To skip Trash and delete it completely use --force

You can delete all posts of a custom post type in various methods but here I am gonna show you how to do this without using SQL query. Here, for example, our post type is product

$allposts= get_posts( array('post_type'=>'product','numberposts'=>-1) );
foreach ($allposts as $eachpost) {
wp_delete_post( $eachpost->ID, true );
}

See full tutorial Reference Here

If you have prefixed your CPT post type and your CPT taxonomies with eg. 'abc_my_custom_post_type' and 'abc_my_taxonomy' then it's trivial to remove everything from db with two queries:

DELETE a,b
FROM      $wpdb->posts    a
LEFT JOIN $wpdb->postmeta b ON a.ID = b.post_id
WHERE a.post_type LIKE 'abc_%';
            
DELETE a,b,c
FROM      $wpdb->term_taxonomy      a
LEFT JOIN $wpdb->term_relationships b ON a.term_taxonomy_id = b.term_taxonomy_id
LEFT JOIN $wpdb->terms              c ON a.term_id = c.term_id
WHERE a.taxonomy LIKE 'abc_%'

本文标签： databaseDelete all posts of a custom post typeefficiently