SQL statements to run
delete from wp_comments where comment_author_email = ''Delete all comments where the author has not completed their email address.
delete from wp_commentmeta where comment_id not in ( select comment_id from wp_comments)Now tidy up the comment meta table. Delete all entries which no longer have a matching entry in the comments table. Results I cleared up nearly 3,000 comments in one fell swoop. There were still 90 or so to look at, plus a couple of hundred that had been approved before I enabled Akismet. Two additional SQL statements did a good job:
delete from wp_comments where comment_content like '%rel="nofollow%'
delete from wp_comments where comment_content like '%[url=%'and do the delete from the comment meta table as well. That just left a few comments where the spammers had intentional typos. Here are a few examples of absolute rubbish.
- That’s a mold-bearker.
- It’s spooky how cvleer some ppl are
- Heck yeah this is eaxclty what I needed.
- What an awesome way to exapiln thisnow I know everything
- This forum needed shaknig up and youve just done that.
- Youre the one with the brians here. Im watching for your posts.
delete from wp_comments where comment_ID in ( select comment_id from wp_commentmeta where meta_key = '_wp_trash_meta_status' )Well, I thought I’d deleted everything with a blank email. but I’ve got 76 left. And they’ve got blank emails. What’s going on?