Comments are kind of passé. Well, OK, they’re still everywhere, but they’re almost universally garbage. Meaningful discussion happens on social media these days, even if it’s prompted by a blog post. And if you’re using WordPress as a general-purpose CMS rather than just as a blogging tool, then you probably have no use for comments whatsoever.
Yet, they’re built in, and they’re a spam magnet. Even if your templates aren’t actually showing comments anywhere, the default WordPress settings allow comments to come in, cluttering up your database and nagging you with a disconcertingly large number in a bright red circle in the WordPress admin bar.
Yuck.
Fortunately, if you have direct database access and the fortitude to run a few simple lines of SQL, you can quickly accomplish the following:
- Purge all queued spam and pending comments (while safely retaining any old, approved comments for archival purposes
- Prevent any new comments from appearing on any of your existing posts/pages
- Prevent comments from ever being accepted on future posts/pages
The last of those is a simple setting. In WP admin, you can go to Settings > Discussion and uncheck the second and third boxes under Default article settings at the top of the page. Actually, uncheck all three of those. If you’re going to turn off incoming pings, you should turn off pingbacks. But my SQL code below doesn’t.
If you’re just starting a brand new WordPress site and you don’t ever intend to allow comments, just go and uncheck those boxes and you’re done. But if you’re trying to rescue a long-suffering WordPress site from drowning in spam, read on.
Here then in all of its glory is the magic SQL you’ve been looking for:
DELETE FROM `wp_comments` WHERE `comment_approved` != 1;
DELETE FROM `wp_commentmeta` WHERE `comment_id` NOT IN (SELECT `comment_ID` FROM `wp_comments`);
UPDATE `wp_posts` SET `comment_status` = 'closed', `ping_status` = 'closed';
UPDATE `wp_options` SET `option_value` = 'closed' WHERE option_name = 'default_comment_status';
UPDATE `wp_options` SET `option_value` = 'closed' WHERE option_name = 'default_ping_status';
Want to dissect what each of these lines is doing? Sure…
Line 1
DELETE FROM `wp_comments` WHERE `comment_approved` != 1;
This is going to delete all “pending” and “spam” comments. It leaves approved comments untouched. Note: you may have spam comments that are approved; one site I was just working on had thousands that were “approved” because the settings were a little too generous. I can’t give a catch-all SQL statement to address that problem, unfortunately. It requires analyzing the content of the comments to some extent.
You’d think maybe `comment_approved` = 0 would be better, but I found as I poked around that the possible values aren’t just 0 or 1. It may also be spam. It may be something else. (I haven’t researched all of the possibilities.)
Line 2
DELETE FROM `wp_commentmeta` WHERE `comment_id` NOT IN (SELECT `comment_ID` FROM `wp_comments`);
There’s a separate table that stores miscellaneous meta data about comments. There’s a good chance there’s nothing in here, but you may as well delete any meta data corresponding to the comments you just deleted, so here you go.
Line 3
UPDATE `wp_posts` SET `comment_status` = 'closed', `ping_status` = 'closed';
This is going through all of the existing posts — which don’t include just “posts”… “pages” are posts, “attachments” are posts… anything in WordPress is a post, really — and setting them to no longer accept comments or pingbacks. This doesn’t delete any comments on the posts that were already approved; it just prevents any new ones.
It’s the equivalent of going into every single post and unchecking the two boxes in the screenshot above. But it only takes a couple of seconds. FEEL THE AWESOME POWER OF SQL!!!
Line 4
UPDATE `wp_options` SET `option_value` = 'closed' WHERE option_name = 'default_comment_status';
Remember that screenshot near the beginning of this post, showing the three checkboxes under Settings > Discussion? Well this is the equivalent of unchecking the third one.
Line 5
UPDATE `wp_options` SET `option_value` = 'closed' WHERE option_name = 'default_ping_status';
And this is the equivalent of unchecking the second one.
So there you have it. No more comments, no more spam, no need for an Akismet account.