PHPMyadmin 常用SQL命令 不断更新

常用操作

将 field1的内容拷贝到field2覆盖：

UPDATE 表名 SET 字段= REPLACE(字段,'查找内容','替换内容');

例如：

UPDATE wp_postmeta SET meta_value = REPLACE(meta_value,'old','new');

在wp_postmeta表搜索meta_value中包含 old 的项，把old替换为 new，使用WHERE可以指定特定的项：

修改站点url：

UPDATE wp_options SET option_value = replace(option_value, 'http://old.com','http://new.com') WHERE option_name = 'home' OR option_name = 'siteurl';

修改文章中内部链接及附件的地址：

UPDATE wp_posts SET post_content = replace(post_content,'http://old.com','http://new.com');

修改wordpress文章默认的永久链接：

UPDATE wp_posts SET guid = replace(guid, 'http://old.com','http://new.com');

清理及优化

批量删除meta_key

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}}';
DELETE FROM wp_postmeta WHERE meta_key = ‘_su_description’;
DELETE FROM wp_postmeta WHERE meta_key = ‘_wpas_done_all’;
DELETE FROM wp_postmeta WHERE meta_key = ‘_wpt_status_message’;
DELETE FROM wp_postmeta WHERE meta_key = ‘_wpt_failed’;
DELETE FROM wp_postmeta WHERE meta_key = ‘_wpt_status_message’;
DELETE FROM wp_postmeta WHERE meta_key = ‘_wp_jd_url’;
DELETE FROM wp_postmeta WHERE meta_key = ‘_wp_jd_wp’;
DELETE FROM wp_postmeta WHERE meta_key = ‘_wp_jd_yourls’;
DELETE FROM wp_postmeta WHERE meta_key = ‘_jd_tweet_this’;
DELETE FROM wp_postmeta WHERE meta_key = ‘_jd_twitter’;
DELETE FROM wp_postmeta WHERE meta_key = ‘_jd_wp_twitter’;

删除不存在文章的元信息：

DELETE FROM wp_postmeta WHERE post_id NOT IN (SELECT post_id FROM wp_posts);

删除附件信息（如果你使用特色图像，后两行不能使用，否则特色图像会失效）

DELETE FROM wp_postmeta WHERE meta_key = 'enclosure';
DELETE FROM wp_postmeta WHERE meta_key = '_wp_attached_file';
DELETE FROM wp_postmeta WHERE meta_key = '_wp_attachment_metadata';

评论元信息 根据是否需要保留akismit信息来决定是否执行最后一条

DELETE FROM wp_commentmeta WHERE comment_id NOT IN (SELECT comment_id FROM wp_comments);
DELETE FROM wp_commentmeta WHERE meta_key LIKE '%trash%';
DELETE FROM wp_commentmeta WHERE meta_key REGEXP 'akismet';