清理WordPress中数据库冗余数据的方法

//不去调用RSS缓存。 0 则不缓存 magpie RSS(改为1则缓存)
define('MAGPIE_CACHE_ON', '0');

//wordpress数据库手动清除Transients（下面SQL语句，任选一条执行）
DELETE FROM wp_options WHERE option_name LIKE ('%\_transient\_%');
DELETE FROM wp_options WHERE option_name REGEXP '_transient_';

//删除所有非发布状态帖子信息（只保留已经发布的状态的文章、菜单、页面）
DELETE FROM wp_posts WHERE NOT(post_status = 'publish' AND post_type IN('post','nav_menu_item','page'));

//删除全部文章修订版本及所对应的关联数据
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_posts where (post_status='auto-draft' or post_status='inherit') and post_type='post';

//规矩删除（删除文章中不存在文章的元信息）
DELETE FROM wp_postmeta WHERE post_id NOT IN (SELECT post_id FROM wp_posts);

//安全删除（删除_edit_lock和_edit_last条目是安全的）
DELETE FROM wp_postmeta WHERE meta_key = '_edit_lock';
DELETE FROM wp_postmeta WHERE meta_key = '_edit_last';

//风险删除（除了这两条还执行了一些其他语句由于有些风险：自己酌情考虑）
DELETE FROM wp_postmeta WHERE meta_key = '_wp_old_slug';
DELETE FROM wp_postmeta WHERE meta_key = '_revision-control';
DELETE FROM wp_postmeta WHERE meta_value = '{{unknown}}';

//特殊插件删除（postnav插件会记录每个文章的访问数，如果不需要，可以删除）
DELETE FROM wp_postmeta WHERE meta_key = 'views';

//删除孤立的文章元信息（当文章的记录删除后，还有日志扩展表postmeta的数据还没有删除，也要人工清理下）
DELETE pm FROM wp_postmeta pm LEFT JOIN wp_posts wp ON wp.ID = pm.post_id WHERE wp.ID IS NULL

//删除重复的 meta key 和 value 记录，仅保留最新的一个
DELETE FROM wp_postmeta WHERE meta_id IN (
select * from (select meta_id FROM wp_postmeta pm WHERE
meta_id NOT IN (SELECT max(meta_id) FROM wp_postmeta pm2 where pm2.post_id=pm.post_id and pm2.meta_key=pm.meta_key)
) as g1
)

//特殊操作删除
DELETE FROM wp_postmeta WHERE meta_key = '_wp_attached_file';
DELETE FROM wp_postmeta WHERE meta_key = '_wp_attachment_metadata';

//洁癖删除
TRUNCATE TABLE wp_postmeta;

//删除孤立的评论元信息（评论删除后残留在wp_commentmeta表中的信息）
DELETE FROM wp_commentmeta WHERE comment_id NOT IN (SELECT comment_id FROM wp_comments);
DELETE FROM wp_commentmeta WHERE meta_key REGEXP 'akismet';
//删除记录评论被删除的时间，这些信息用处不是很大
DELETE FROM wp_commentmeta WHERE meta_key LIKE '%trash%';

//直接全部删除wp_commentmeta数据表内容，影响不会太大，这里面不会涉及重要的数据
TRUNCATE TABLE wp_commentmeta;

//删除所有垃圾留言（包括待审、垃圾评论、回收站评论）
DELETE FROM wp_comments WHERE comment_approved != '1';
//删除待审评论
DELETE FROM wp_comments WHERE comment_approved = '0';
//删除垃圾评论
DELETE FROM wp_comments WHERE comment_approved = 'spam';
//删除回收站评论
DELETE FROM wp_comments WHERE comment_approved = 'trash';

//孤立的关系信息（文章、评论等删除后残留在wp_term_relationships表中的信息）
DELETE FROM wp_term_relationships WHERE term_taxonomy_id=1 AND object_id NOT IN (SELECT id FROM wp_posts);

//删除没有的标签（有些文章删除了，但标签还在，WordPress不会自动删除的）
DELETE a,b,c FROM wp_terms AS a
LEFT JOIN wp_term_taxonomy AS c ON a.term_id = c.term_id
LEFT JOIN wp_term_relationships AS b ON b.term_taxonomy_id = c.term_taxonomy_id
WHERE (
c.taxonomy = 'post_tag' AND
c.count = 0
);

OPTIMIZE TABLE wp_commentmeta;
OPTIMIZE TABLE wp_comments;
OPTIMIZE TABLE wp_links;
OPTIMIZE TABLE wp_options;
OPTIMIZE TABLE wp_postmeta;
OPTIMIZE TABLE wp_posts;
OPTIMIZE TABLE wp_terms;
OPTIMIZE TABLE wp_term_relationships;
OPTIMIZE TABLE wp_term_taxonomy;
OPTIMIZE TABLE wp_usermeta;
OPTIMIZE TABLE wp_users;

<!--?php //wordpress数据库优化清理脚本 $hostname_blog = "localhost";//设定数据库主机，同wp-config.php $database_blog = "wordpress";//设定数据库名，同wp-config.php $username_blog = "root";//设定数据库用户名，同wp-config.php $password_blog = "";//设定数据库密码，同wp-config.php $blog = mysql_pconnect($hostname_blog, $username_blog, $password_blog) or trigger_error(mysql_error(),E_USER_ERROR); mysql_select_db($database_blog, $blog); mysql_query('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"'); mysql_query('DELETE FROM wp_posts WHERE NOT(post_status = "publish" AND post_type IN("post","nav_menu_item","page")'); mysql_query('DELETE FROM wp_postmeta WHERE meta_key = "_edit_lock"'); mysql_query('DELETE FROM wp_postmeta WHERE meta_key = "_edit_last"'); mysql_query('DELETE FROM wp_commentmeta WHERE meta_key LIKE "%trash%"'); mysql_query('DELETE FROM wp_comments WHERE comment_approved != "1"'); mysql_query('DELETE pm FROM wp_postmeta pm LEFT JOIN wp_posts wp ON wp.ID = pm.post_id WHERE wp.ID IS NULL'); mysql_query('DELETE FROM wp_commentmeta WHERE comment_id NOT IN (SELECT comment_id FROM wp_comments)'); mysql_query('DELETE FROM wp_term_relationships WHERE term_taxonomy_id=1 AND object_id NOT IN (SELECT id FROM wp_posts)'); mysql_query('DELETE FROM wp_options WHERE option_name REGEXP "_transient_"'); mysql_query('DELETE FROM wp_postmeta WHERE meta_key = ‘_wp_attached_file’'); mysql_query('DELETE FROM wp_postmeta WHERE meta_key = ‘_wp_attachment_metadata’'); mysql_query("delete from wp_posts where (post_status='auto-draft' or post_status='inherit') and post_type='post'"); $tablelist = mysql_query("SHOW TABLES"); while($checklist = mysql_fetch_array($tablelist)) { $optimization=mysql_query("OPTIMIZE TABLE `$checklist[0]`"); }echo 'Done'; //ps记得修改数据库前缀~ //使用时将脚本上传至网站任意目录后并且通过浏览器访问即可一键清理wordprsss数据库。 ?-->

//禁止垃圾评论提交到数据库
function wp_fuckspam($comment) {
if( is_user_logged_in()){ return $comment;} //登录用户无压力...
if( wp_blacklist_check($comment['comment_author'],$comment['comment_author_email'],$comment['comment_author_url'], $comment['comment_content'], $comment['comment_author_IP'], $comment['comment_agent'] )){
header("Content-type: text/html; charset=utf-8");
wp_die('
您评论包含辱骂，过激或者违反法律等言论，或者您的IP已被加入黑名单，如有疑问请联系管理员处理！返回文章页
');
} else {
return $comment;
}
}
add_filter('preprocess_comment', 'wp_fuckspam');