Recently I had to transfer all the blog posts from an old 2.4 version of WordPress, to a new 3.0 install. After I exported the content (via Tools & Export), and tried to import it into the new blog, the importer froze up and never completed. I was surprised to notice that the XML file was 50MB. Opening up the file in Notepad++ revealed that this was all spam. Apparently the blog’s spam had never been deleted and there were over 10,000 spam comments!

Attempting to delete these manually from the admin panels proved fruitless because it was only possible to delete a handful each time. It was evident that this would take too long, and that they needed to be deleted through the database.

Updated Method (2023): Use WP-CLI to Delete Unapproved Comments

WordPress now has a command-line interface tool that provides tons of helpful commands to manage your install of WordPress. WP-CLI is not installed by default; see the official instruction instructions for details. Once you have WP-CLI installed on your server, you can run:

Delete All Spam Comments

wp comment delete $(wp comment list --status=spam --format=ids)

Delete All Unapproved Comments

wp comment delete $(wp comment list --status=hold --format=ids)

Original Method (2011): Delete Using a SQL Query

If you have access to your database, through PHPMyAdmin, command line, or an external application, then you can run a simple query to delete all the spam comments:

DELETE from wp_comments WHERE comment_approved = '0';

The human readable status of “hold” is basically a value of zero for comment_approved in the database, if you dig in to the source of WordPress’ various comment update functions.

Leave a Reply

You can use the <pre> tag to post a block of code, or <code> to highlight code within text.