How to delete all comments that are not approved (are spam, trashed or unapproved)

**Fli** · 02-19-2015, 03:18 PM

What are good mysql commands to manage/delete Wordpress comments?

so far i found this one:

DELETE FROM wp_comments WHERE comment_approved = 0;DELETE FROM wp_commentmeta WHERE comment_id NOT IN ( SELECT comment_id FROM wp_comments );optimize table wp_comments;optimize table wp_commentmeta;

This deleted all Wordpress comments in folder SPAM and in folder Awaiting approval, it also deleted comments meta data of the comments that was removed and optimized comments tables to free disk space. Great command.

Another ways to delete comments:
google: delete wordpress comments mysql

----

When i cleaned up czech language site i used this command. It is very strict and recommended only for czech wordpresss site. It removes all comments that contain hyperlink for example or all where comment author e-mail is yahoo, live, removes most of the english comments, etc.:

DELETE FROM `wp_comments` WHERE `comment_approved` = "0" AND `comment_content` LIKE "%href%" OR `comment_content` LIKE "%http%" OR `comment_content` LIKE "%have %" OR `comment_content` LIKE "%the %" OR `comment_content` LIKE "%best %" OR `comment_content` LIKE "%for %" OR `comment_content` LIKE "%good %" OR `comment_content` LIKE "% is %" OR `comment_author_url` LIKE "%.com%" OR `comment_author_url` LIKE "%.ru%" OR `comment_author_url` LIKE "%.in%" OR `comment_author_url` LIKE "%.org%" OR `comment_author_IP` LIKE "%5.188.211%" AND `comment_author_email` NOT LIKE "%.cz%" OR `comment_author_email` LIKE "%yahoo.com%" OR `comment_author_email` LIKE "%mail.ru%" OR `comment_author_email` LIKE "%live.com%" OR `comment_author_email` LIKE "%sify.com%" OR `comment_author` LIKE "%http%" OR `comment_approved` = "post-trashed";DELETE FROM wp_commentmeta WHERE comment_id NOT IN ( SELECT comment_id FROM wp_comments );optimize table wp_comments;optimize table wp_commentmeta;