How to Mass Delete Pending Comments in WordPress?

Question: I have around 5,000 pending comments in my WordPress and most of those are spams. I would like to know if there’s a MySQL query that lets me to mass delete all of those?

Answer:

Yes, you can delete all the pending comments using MySQL query. All you need to do is, just connect to your WordPress database and lookout for ‘wp_comments’ table.

Note: Make sure you take a backup of WordPress database before making any changes to it.

Backup WordPress database:

$mysqldump -u mysql_user -p WordPress_database > backup.sql

Delete all pending comments in WordPress:

$mysql -u mysql_user -p

mysql> use WordPress_database

mysql> desc wp_comments;

Sample output:

+----------------------+---------------------+------+-----+---------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------------+---------------------+------+-----+---------------------+----------------+
| comment_ID | bigint(20) unsigned | NO | PRI | NULL | auto_increment |
| comment_post_ID | bigint(20) unsigned | NO | MUL | 0 | |
| comment_author | tinytext | NO | | NULL | |
| comment_author_email | varchar(100) | NO | MUL | | |
| comment_author_url | varchar(200) | NO | | | |
| comment_author_IP | varchar(100) | NO | | | |
| comment_date | datetime | NO | | 0000-00-00 00:00:00 | |
| comment_date_gmt | datetime | NO | MUL | 0000-00-00 00:00:00 | |
| comment_content | text | NO | | NULL | |
| comment_karma | int(11) | NO | | 0 | |
| comment_approved | varchar(20) | NO | MUL | 1 | |
| comment_agent | varchar(255) | NO | | | |
| comment_type | varchar(20) | NO | | | |
| comment_parent | bigint(20) unsigned | NO | MUL | 0 | |
| user_id | bigint(20) unsigned | NO | | 0 | |
| comment_subscribe | enum('Y','N') | NO | | N | |
+----------------------+---------------------+------+-----+---------------------+----------------+
16 rows in set (0.00 sec)

In the above output, we are interested in the column ‘comment_approved’.

To view all the Pending comments:

mysql > select * from wp_comments where comment_approved = '0';

To delete all Pending comments:

mysql> delete from wp_comments where comment_approved = '0';

That’s it, you are done!

Also Read: 50 Things to do after Installing WordPress – Security, SEO and Customization.