WordPress MySql Statement To Delete All Pending Comments

I

have over 1800+ pending comments and most of them are spams in WordPress based blog. How do I delete all (mass delete) the pending comments using sql statements?

 

You can delete all pending comments from your database. WordPress has wp_comments table. This table can be used to store, retrieve, modify and delete comments.

Procedure to delete all the pending comments from your database

First, login to your remote or local server over the ssh based sesson.

Next, type the following command to login into your mysql database:

Warning: Before you get started with the following commands, it’s a good idea to back up your database. This means if there are any issues you can easily restore your database. See how to backup your database for more information.

mysql -u Your-DB-User-Name-Here -p Your-DB-Name-Here

OR

mysql -u Your-DB-User-Name-Here -h Db-Server-IP-Or-HostName -p Your-DB-Name-Here

In this example, login to db called blog as foo user on localhost:

mysql -u foo -p db

OR

mysql -u foo -h localhost -p db

Once logged in type the following desc wp_comments; command to see wp_comments structure:

mysql> desc wp_comments;

Sample outputs:

+———————-+———————+——+—–+———————+—————-+

| 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   |     |                     |                |

| 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                   |                |

+———————-+———————+——+—–+———————+—————-+

15 rows in set (0.00 sec)

To see all the pending comments, type:

mysql> select * from wp_comments where comment_approved =  0 ;

Or better just show all the pending comment count, enter:

mysql> select count(*) from wp_comments where comment_approved =  0 ;

Sample outputs:

+———-+

| count(*) |

+———-+

|       18 |

+———-+

1 row in set (0.01 sec)

Sql statement to delete all pending comments

Type the following command at mysql> prompt>

mysql> delete from wp_comments where comment_approved =  0 ;

Sample outputs:

Query OK, 18 rows affected (0.09 sec)

To quite from mysql session, enter:

mysql> quit;