Delete 14,700+ Unfiltered “Approved” Spam Comments in WordPress using SQL

For some reason this blog had all of its discussion settings reset, and plugins turned off, on June 2, 2015.

Within a couple of weeks, the blog had accumulated nearly 15 thousand spam comments. These comments were obviously spam, but with Akismet and WP Spam Shield down, and my discussion settings reset — they all went right through and were immediately published.

Deleting 14,700+ comments a screen at a time was not going to work well.

Thanks to this helpful post by the folks at Cats Who Code, I found a much more efficient fix using SQL in PHPMyAdmin.

The steps:

1. Backup your database. (To be safe.)

2. Login to PHPMyAdmin.

3. Navigate to the desired database. (The main database for the blog.)

4. Select SQL, which allows you to run SQL queries.

5. Run a first query to select all comments after the date that everything went bad:

SELECT * FROM wp_comments
WHERE comment_date > '2015-06-01 00:00:00'

6. If you’ve selected them right, you should see the results in a table. Sort the results by date, in both directions, and review them to ensure they’re what you want to delete.

7. If so, then delete them using this SQL command:

DELETE FROM wp_comments 
WHERE comment_date > '2015-06-01 00:00:00'

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s