wp_postmeta垃圾数据清理及删除重复的meta key和value

wp_postmeta 是wordpress查询最慢的一张表，字段以一张多形式，对应存放了文章、页面、自定义内容的分类目录、文章查看数、封面图片，自定义的字段等数据。

SQL清理wp_postmeta ；

1、 清理WordPress 运行过程中产生的垃圾数据

//规矩删除（删除文章中不存在文章的元信息）
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
)

2、删除wordpress后台上传的图片或者附件信息

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

注：WordPress 在开启了文章的版本控制情况下，存在了重复 post 和 meta key，数据表ID不是唯一约束。

3、其它删除

// 可选项目
DELETE FROM wp_postmeta WHERE meta_key = '_wp_old_slug';
DELETE FROM wp_postmeta WHERE meta_key = 'jd_tweet_this';
DELETE FROM wp_postmeta WHERE meta_key = 'wp_jd_clig';
DELETE FROM wp_postmeta WHERE meta_key = 'wp_jd_target';
DELETE FROM wp_postmeta WHERE meta_key = 'nofollow4post';
DELETE FROM wp_postmeta WHERE meta_key = 'ratings_score';
DELETE FROM wp_postmeta WHERE meta_key = 'ratings_users';
DELETE FROM wp_postmeta WHERE meta_key = 'ratings_average';
DELETE FROM wp_postmeta WHERE meta_key = 'wp_noextrenallinks_mask_links';
DELETE FROM wp_postmeta WHERE meta_key = '_wp_page_template';
DELETE FROM wp_postmeta WHERE meta_key = '_sexybookmarks_permaHash';
DELETE FROM wp_postmeta WHERE meta_key = '_sexybookmarks_shortUrl';

注：此删除有风险，须备份SQL.。不熟悉wordpress数据表字段的话，最好采用sweep插件安全删除，虽然慢点。