Mark just asked me how can he delete more than 6000 spam comments he received on his blog during his holidays.
First of it all, backup your WordPress database. Then, login to your phpmyadmin, select your WordPress blog database and click on SQL:
Then, insert the following sql command:
DELETE from wp_comments WHERE comment_approved = '0';
All non-approved comments we be deleted. But instead of having such trouble next time, you should definitely install Akismet!
Leave a Comment
WP Theme of the week
WPRecipes.com is hosted by VidaHost. Use our exclusive coupon CATSWHOCODE to get a 10% discount on hosting! 
On Twitter : WordPress tip: How to automatically insert a list of related articles below the post http://t.co/zu07UU2AIj
Copyright © 2008 - 2013 WordPress Development by Jean-Baptiste Jung. Graphic art by GreenBubbles.org.ua, all right reserved.
20 Responses
How can I delete all the comments? I have 400,000 spam comments which are approved.
Help
Hi there,
This is the clearest post I’ve read about this problem, so thanks. Do you know if there is a way of deleting spam comments that have been marked ‘approved’ without deleting my legitimate comments, which are also marked ‘approved’?
Ryan
How about the table called “wp_commentmeta”? That one has also data concerning the comments. Does it mess up the database (or at least leave a lot of unneeded data) if that table is untouched? I’d say that touching only the wp_comments table is not a proper way to handle the spam, unless the identical id-values are cleaner from the wp_commentmeta table.
I’d love to see a simple solution for that…
Also, I got 3 installation of multisite-wordpress’ and each blog in each wordpress has it’s own spam-comments. I’d love even more to see a plugin/tool to clean ALL those sub-blogs in a single stroke
@Kaira: check out similar post here, which shows howto delete all comments at once: http://techfreakz.com/itwebsites/content-management/wordpress/cleaning-up-wordpress-comments-mass-deleting-wordpress-spam-comments-mass-deleting-wordpress-comments/
You can use the code provided in this blog post to delete ALL approved comments. Simply change the 0 to 1. (comment_approved = ’1′)
Please use with care as this deletes ALL approved comments. If you are certain there are no comments you want to keep, this works.
sweet thanks… how do you do it for trash?
Wow, thanks for the tip. I have comments open in my new site. In case I get spam , I’ll know what to do.
Even akismet can’t handle most of them. Guess this is one of the major issue that needs to be handled in wordpress. May be some captcha plugin would help..
I tried the same syntax with variations over time but how about the few genuine comments
DELETE from wp_comments WHERE comment_approved = ’0′;
DELETE from wp_comments WHERE comment_approved = ‘spam’;
DELETE from wp_comments WHERE comment_approved = ‘trash’;
It worked great. It was so simple I was amazed. Thank You – you are a genius!
Awesome! This totally worked like a charm. My client had accumulated 18,000+ spam comments over the last 4 years. Luckily, none of them had been “approved.”
Thanks a ton for the database/phpMyAdmin help:
DELETE from wp_comments WHERE comment_approved = ’0′;
Brilliant, works like a charm, thanks!
Thank you sooo much. The monthly bandwidth of this site due to spam was more thab 20 GIGs. I just deleted with your instructions “60560 rows (spams)deleted. ( Query took 3.9377 sec )” EXACTLY!!!!
Hi there. I used your suggestion and it deleted all the comments (thanks), but the posts still show the previous comment count despite there being zero comments on the entire blog. I deleted the commentsmeta as well but it doesn’t seem to have worked. Do you have any suggestions?
Hello,
Instead of using 0 as the amount i choose the amount of 1 and got rid of all the comments.
/ Marcus
where does one enter this code in wordpress for it to get activated?
DELETE from wp_comments WHERE comment_approved = ’0′;
DELETE from wp_comments WHERE comment_approved = ‘spam’;
DELETE from wp_comments WHERE comment_approved = ‘trash’;
Great tip. I just deleted 18,945 comments, boy I’m grateful.
In the WP admin interface if you go Dashboard > Comments > Spam there is a button “Empty Spam” which does the same job. The script runs “forever” and results in an empty page! You may have to run it several times but this approach may be safer than messing with the database.
I just got rid of 100+ megabytes of spam and back under my ISP database quoter!
May be a good idea to press this button from time to time to prevent a build up.
Hi My host, Siteground, has shut down my site until I clear all of the Spam comments out.
I have stopped new registrations. I have installed a disable comments plugin. How do I get rid of thousands of spam comments from 300+ multisites from network admin? None of them will have been approved. If necessary I am happy to delete all comments.
Is there a way to do it without having to login to each individual site or find each individual comments table in mysqladmin?
Many thanks, Mike.
Do any of you know of a script/plugin that could do this as a novice? The Akismet when you install it afterwards has a timeout problem deleting 100k plus comments. To those who read this and can program — next to Akismet such a plugin will get alot of support.
This is awesome! I have 2000 spam comments and I always get nervous using PHPMYADMIN.
Cheers Dude!
Check out this plugin
http://www.jessepupparo.com/2012/08/31/simple-plugins-to-remove-all-wordpress-comments-pending-and-approved/
It worked a treat for me and no need for any sql coding.
Trackbacks: