Mysql Query to Delete Duplicate Wordpress Comments

问题:

I had an issue with Disqus whereby it created duplicate comments on many posts, sometimes 4 duplicates of the same comment. I've been going through to try and manually remove these but we have more than 10K comments total and unfortunately, this happened haphazardly whereby it only happened to some of the posts. So...

does anyone know of a mysql query whereby I could detect and delete duplicate comments by searching for entries that match in terms of the comment itself or the author? The comment IDs are not duplicate (it created new comment IDs for each) so Im not sure how to do this in mysql (plus Im not very good at it :-)... Any help would be greatly appreciated. Thank you.

回答1:

Improving on Blackbarn's suggestion, try this (after backing up the db):

global $wpdb;

$comments = $wpdb->get_results("SELECT * FROM ".$wpdb->prefix."_comments"
   ." ORDER BY comment_post_ID, comment_content");

$prev = NULL;

foreach($comments as $comment) {

  if ($prev && $prev->comment_content == $comment->comment_content
    && $prev->comment_post_ID == $comment->comment_post_ID ) { // add maybe other rules here

    $wpdb->query("DELETE FROM ".$wpdb->prefix."_comments WHERE comment_ID = ".$comment->comment_ID);

  }
  else
    $prev = $comment;
}

回答2:

Write a simple PHP script to clean your comments table (but make a backup before you do it).

Pseudo Code:

// get all the comments

// compare each comment with each comment

// if the content of the comment is the same (or compare all values except of the id, if you want to be sure that nothing gets broken), delete the comment with the higher id

It will be something like this:

global $wpdb;

$comments = $wpdb->get_results("SELECT * FROM ".$wpdb->prefix."_comments");

foreach($comments as $comment) {

foreach($comments as $compare) {

if(($comment->comment_content == $compare->comment_content) && ($comment->comment_ID != $compare->comment_ID )) { // add maybe other rules here

$wpdb->query("DELETE FROM ".$wpdb->prefix."_comments WHERE comment_ID == $compare->comment_ID");

}

}

Not tested, so use and improve with care...

Database with comments table: http://codex.wordpress.org/images/9/9e/WP3.0-ERD.png