Some things to note
- Don’t forget to do a backup of your database before testing any of the queries below.
- Don’t forget to change the default table prefix
wp_by the one used by your database.
Delete all spam comments
When you have over 100,000 spam comments in your spam queue, deleting them using the built-in “delete all spam” button might result in a PHP memory error. To avoid this, just use this simple SQL request to delete all spam at once.
DELETE from wp_comments WHERE comment_approved = 'spam'
Delete all comments between two dates
Had a “spam attack” for a limited time? Here is an easy way to delete all comments between two dates.
DELETE FROM wp_comments WHERE comment_date > '2013-11-15 01:10:04' AND comment_date <= '2013-11-20 00:10:04'
Delete all pending comments
If your “pending comment” queue is filled with 99% spam comments and you don’t want to manually review each of them, here is a SQL command to instantly erase all pending comments.
DELETE FROM wp_comments WHERE comment_approved = '0'
Disable comments on all posts at once
Want to disable comments on all of your posts? Instead of closing comments on all your posts the one after the other, why not using this super simple SQL query?
UPDATE wp_posts SET comment_status = 'closed', ping_status = 'closed' WHERE comment_status = 'open'
Disable comments on older posts
To limit spam, why not closing comments on older posts? Here is the SQL to automatically close comments on all posts older than January 1, 2014:
UPDATE wp_posts SET comment_status = 'closed' WHERE post_date < '2014-01-01' AND post_status = 'publish'
Source: http://perishablepress.com/wordpress-discussion-management…
delete comments with a specific url
Even if you’re careful when approving new comments, sometimes you might just forget to visit the commenter url and detect a long time after that the linked site is spammy. Here is a very easy way to bulk delete all comments with a specific url, using a simple SQL query.
DELETE from wp_comments WHERE comment_author_url LIKE "%nastyspamurl%" ;
Source: http://www.wprecipes.com/wordpress-tip-bulk-delete-comments…
Search and replace comment text
If there’s a specific word or sentence that you want to replace in all comments, here is a very handy SQL query which use the mysql REPLACE function.
UPDATE wp_comments SET `comment_content` = REPLACE (`comment_content`, 'OriginalText', 'ReplacedText')
Globally enable comments for registered users only
A very effective way to dramatically decrease the amount of received spam comment is to enable comments for registered users only. Instead of doing it on each post, here’s a SQL query to run in oder to do it on all posts at once.
UPDATE wp_posts SET comment_status = 'registered_only'
Source: http://digwp.com/2010/08/wordpress-sql-comments/