Delete Comment Spam Bulk From Wordpress Blacklist SQL

bulk Delete Comment Spam From Existing Blacklist

To bulk delete comments, we are going to use phpmyadmin. Most hosting panels have /phpmyadmin active on your account.

Log into phpmyadmin and click on your database on the LEFT, then wp_comments or whatever database prefix you are using.

Then head to Search

Bulk Delete Comment Spam From WordPress
Bulk Delete Comment Spam From WordPress

Scroll down to comment_content and set the Operator column to %LIKE%, which matched wildcard anything before or after your term.

For this example, we are going to use the word sex:

Bulk Search and Delete from WordPress Comment Database
Bulk Search and Delete from WordPress Comment Database

You will receive a valid SQL query that we are going to reuse.

Bulk Search Comments WordPress Then Delete
Bulk Search Comments WordPress Then Delete

All you need to do is replace SELECT * with DELETE FROM

WARNING: If you have any of the terms elsewhere in your comments database, they will be deleted.

Don’t do this if you don’t know what you are doing.

Now, get your Comment Blacklist ready.

Open Text Mechanic here so we can prefix and suffix our list: https://textmechanic.com/text-tools/basic-text-tools/add-prefixsuffix-into-line/

Paste your comment spam blacklist at the top.

Add the Prefix as DELETE FROM…..%

And the Suffix as %’;

Don’t forget the ; at the end as we are using multiple lines

Delete Comment Spam Bulk From WordPress Blacklist SQL
Delete Comment Spam Bulk From WordPress Blacklist SQL

Then in phpmyadmin paste your list of SQL deletions into the SQL section and press SIMULATE QUERY

Bulk Delete WordPress Comments Table WordPress
Bulk Delete WordPress Comments Table WordPress

Anti-warning: since this is our actual “comment blacklist” already from the WordPress comments section, these comments would not have passed the filter anyway.

Simulate Query will show you what will be deleted when you press Go.

MYSQL Bulk Delete WordPress Comment Query
MYSQL Bulk Delete WordPress Comment Query

If all looks good, close the simulated query window and hit GO to permanently delete comments on your WordPress database that match your blacklist.

Wordpress Comment Spam Before
WordPress Comment Spam Before

My comment database went from 6000 to 1500!

Wordpress Comment Spam After
WordPress Comment Spam After

My comment blacklist can be downloaded here.




Leave a Reply

Your email address will not be published. Required fields are marked *