Clearing WordPress comments via a SQL Query

Ever have a drive-by comment spammer hit you and all a sudden you have thousands of word press comments, none of them worthwhile? If you decide that you want to declare WordPress bankruptcy and just delete all of them, you either will have a long time with the batch delete button or one simple SQL query (if you have access to the back end).

First, connect to your SQL database either via command prompt or a tool like MySql Workbench. In my case, I just ssh-ed on to the server and ran the command mysql -u <<UserName>>

After logging on I ran the command show databases; to see what databases were on my server (it had been a while since I had set up my blog) and then the command use <<database name>>; to select the proper database.

I would strongly recommend making a backup of your database before doing anything for the first time…

show tables; then listed all of the tables in the database, in my case I wanted <<wordpress prefix>>_comments. Next you run the query delete from <<wordpress prefix>>_comments where comment_approved=0;

It took a while to run but afterward, all the comments were cleaned up.

Leave a Reply

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