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插件安全删除，虽然慢点。

#现在前往