整理WordPress数据库

WordPress运行久了，难免会产生一些垃圾，比如自动保存的草稿、比如插件残余数据、比如垃圾评论。

WordPress本身没集成这些功能，不过可以使用插件实现，不过，你还可以直接对数据库进行操作。我参考了删除Wordpress草稿评论标签垃圾数据SQL这篇文章，将其中的一些SQL语句摘录如下，详细可参照原文。

删除残余垃圾数据

DELETE FROM wp_postmeta WHERE meta_key = '_edit_lock';
DELETE FROM wp_postmeta WHERE meta_key = '_edit_last';

删除草稿修订版本

DELETE a,b,c FROM wp_posts a LEFT JOIN wp_term_relationships b ON (a.ID = b.object_id) LEFT JOIN wp_postmeta c ON (a.ID = c.post_id)
WHERE a.post_type = 'revision';

删除所有垃圾评论

DELETE FROM wp_comments WHERE comment_approved = 'spam';

评论的状态：

0 = Comment Awaiting Moderation
1 = Approved Comment
spam = Comment marked as Spam

删除未使用标签

SELECT * FROM wp_terms wt
INNER JOIN wp_term_taxonomy wtt ON wt.term_id=wtt.term_id
INNER JOIN wp_term_relationships wtr ON wtr.term_taxonomy_id=wtt.term_taxonomy_id
LEFT JOIN wp_posts wp ON wp.ID=wtr.object_id
WHERE taxonomy='post_tag'
AND ID IS null
AND NOT EXISTS(SELECT * From wp_terms wt2
INNER JOIN wp_term_taxonomy wtt2 ON wt2.term_id=wtt2.term_id WHERE wtt2.parent=wt.term_id) ORDER BY name;