Posted by & filed under General, MySQL.

When marking comments as “spam” in WordPress, it doesn’t actually remove them from the database.

From my own experience, most WordPress installations have thousands of spam comments in their databases, taking up valuable space.

In the wp_comments table the comment_approved column will show a value of “spam” on any comment marked as spam – and a value of “1″ for any approved comment.

To quickly delete ALL spam comments, execute the following query on your MySQL database:

DELETE FROM wp_comments WHERE comment_approved = “spam”;

You can expand the SQL command to delete from multiple databases and multiple tables, quickly and effectively.

(3.00 out of 5)

5 Responses to “Delete Spam Comments in WordPress via MySQL”

  1. Mohsin Rasool

    Hi Chris,

    Thanks for sharing this sql query… BTW we came across a similar situation with little twist recently where comments were not tagged as spam…as there was no plugin in place to tag any comment as spam…so everything went to PENDING… in that case..you need to modify the query a little bit to take care of all of those thousands of spam comments in pending section… I wrote about this recently at my blog here:
    http://www.pressgold.com/wordpress/how-to-delete-all-those-spam-comments/

    Best Regards,
    Mohsin Rasool

    Reply
  2. Ryan

    Hi Chris,

    Thanks for this post. I’ve been trying to nut this out for a while.

    I’ve managed to follow your instructions for ‘spam’ spam, but do you know if there is a way of deleting spam comments that have been marked ‘approved’ without deleting my legitimate comments, which are also marked ‘approved’?

    Ryan

    Reply
    • Chris Stinson

      Ryan, unfortunately not!

      You could always query the table for any comments that have links in them, and then go through one-by-one. I find very few legitimate comments have links in them, so that’s a good start.

      Reply
      • Chris Stinson

        Here is a query to select/display all the comments that have HTML links (common with spam bots – since not all blogs will auto-format a URL to be a link). Most people just type in ‘http://’ and WordPress will display it as a link. Bots will enter the HTML ‘<a href=…’ required for a link to display.

        SELECT * FROM wp_comments WHERE comment_content LIKE “%<a href=%”;

        Or if you just want to delete everything that has HTML links…

        DELETE FROM wp_comments WHERE comment_content LIKE “%<a href=%”;

        Use with caution though…many legitimate comments may also get deleted if the poster used HTML formatting for the links within his/her post. You can change the content between the two % to search for comments with that content. For example, %weight loss% will bring up all comments with the “weight loss” phrase in it.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>