Duke Yin's Technology database

PHPMyadmin 常用SQL命令 不断更新

常用操作

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

UPDATE  SET field2 = field1;

时间区域的增减操作:

将GMT时间减少8小时:

UPDATE wp_posts SET 'post_date_gmt' = DATE_SUB('post_date',INTERVAL 8 HOUR);

增加时间 DATE_ADD
减少时间 DATE_SUB
上述语句的HOUR可以替换成 MICROSECOND, SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, YEAR
前面要加 INTERVAL

批量替换(常用于网站换域名)
修改option_value里的站点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';

#

发布评论引用.

评论