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?

Tutorial details
Difficulty Intermediate (rss)
Root privileges No
Requirements mysql+shell access
Time 2m
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.

ADVERTISEMENTS

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;

🐧 Get the latest tutorials on SysAdmin, Linux/Unix, Open Source/DevOps topics:
CategoryList of Unix and Linux commands
File Managementcat
FirewallAlpine Awall CentOS 8 OpenSUSE RHEL 8 Ubuntu 16.04 Ubuntu 18.04 Ubuntu 20.04
Network Utilitiesdig host ip nmap
OpenVPNCentOS 7 CentOS 8 Debian 10 Debian 8/9 Ubuntu 18.04 Ubuntu 20.04
Package Managerapk apt
Processes Managementbg chroot cron disown fg jobs killall kill pidof pstree pwdx time
Searchinggrep whereis which
User Informationgroups id lastcomm last lid/libuser-lid logname members users whoami who w
WireGuard VPNAlpine CentOS 8 Debian 10 Firewall Ubuntu 20.04

ADVERTISEMENTS
4 comments… add one
  • Marios Zindilis Nov 19, 2013 @ 16:21

    This will leave a lot of orphaned records in the commentmeta table (about twice as much as the deleted comments). There will be broken references in the commentmeta.comment_id table, pointing to commets that no longer exist, whereas when you delete the pending comments from the web interface, it cleans up the meta as well.

    I don’t see the point of this post, given that WordPress includes a “Delete all” functionality in the admin-panel, that would do everything for you, and leave the database much more tidy…

    • Bob Nov 20, 2013 @ 13:04

      How? And where is a “Delete all” function you speak of in wp-admin? Can you provide a better version of query posted by OP or Adrian7?

      TIA.

      • Marios Zindilis Nov 20, 2013 @ 13:13

        In Dashboard, go to “Comments”, select “Spam”, then “Empty Spam”. I uploaded a screenshot of this at zindilis.com/pub/images/wordpress-delete-spam.png

  • Adrian7 Nov 20, 2013 @ 8:52

    Nice post.
    I have implemented a similar solution in a cron script. It deletes all pending comments older then 30 days. PHP code generating the query below:

    $thirty_days_ago = date('Y-m-d H:i:s', strtotime('-30 days'));
    $SQL = "DELETE FROM `wp_comment`s WHERE `comment_approved` = '0' AND `comment_date`<'{$thirty_days_ago}'";

Leave a Reply

Your email address will not be published.

Use HTML <pre>...</pre>, <code>...</code> and <kbd>...</kbd> for code samples.