Manual WooCommerce Database Cleanup

WordPress: the blogging engine for Ecommerce

Since WordPress was initially built for blogging, everything built on top of it has a little of that flavor infused into it. When working with WooCommerce you’ll find that products, orders, and subscriptions are all just types of posts (seems like everything in WordPress is a post… or at least related to one).

So when it comes to identifying them and cleaning them out of the database it’s helpful to know how they work together.

Everything’s a Post

I need to do some bulk cleanup on a WooCommerce site so I went straight into PHPMyAdmin rather than deleting things through the WordPress admin. Below you’ll see a SELECT and corresponding DELETE statement. I always want to see what I’m about to delete so I’ve included both here.

Users and their metadata

WooCommerce customers are all found in the wp_users table. Their role is stored in the wp_usermeta table so we have to join those to get all of the data identified and removed.

Review

SELECT
    *
FROM
    wp_users
INNER JOIN wp_usermeta ON wp_users.ID = wp_usermeta.user_id
WHERE
    meta_key = "wp_capabilities" AND INSTR(meta_value, "customer") AND NOT INSTR(meta_value, "administrator");

Remove

DELETE
    wp_users, wp_usermeta
FROM
    wp_users
INNER JOIN wp_usermeta ON wp_users.ID = wp_usermeta.user_id
WHERE
    meta_key = "wp_capabilities" AND INSTR(meta_value, "customer") AND NOT INSTR(meta_value, "administrator");

WooCommerce Reviews and Orders notes

WooCommerce reviews piggyback off of the existing function of WordPress comments. If you’re looking for reviews, you will find them in the wp_comments table. You’ll also find order notes in there. Again, since orders are just a post type, tying multiple notes in the form of comments to them is functionality built-in to WordPress. It’s great to reuse the existing framework but can make it difficult to find things since they’re not named intuitively.

Review

SELECT * FROM wp_comments WHERE comment_type="review";

Remove

DELETE wp_comments FROM wp_comments WHERE comment_type="review";

Review

SELECT * FROM wp_comments WHERE comment_type="order_notes";

Remove

DELETE wp_comments FROM wp_comments WHERE comment_type="order_notes";

Clean up ALL comment meta

For my purposes, I wanted to remove all of the comment meta. This may not work in your case if there are other blog post comments that you want to keep. If so you’ll have to make a JOIN statement with the corresponding records in wp_comments in order to be more specific about what to delete.

Remove

DELETE wp_commentmeta FROM wp_commentmeta;

Clean up orphaned postmeta

Since we deleted the orders and subscriptions, we also need to clean up the metadata from wp_postmeta that got left behind.

DELETE pm
FROM wp_postmeta pm
LEFT JOIN wp_posts wp ON wp.ID = pm.post_id
WHERE wp.ID IS NULL;

At this point we have a cleaned up database that is ready to start taking new orders, or in my case, start migrating from another site.