wordpress 常用 数据库脚本

按栏目统计所有文章的访问量

SELECT sum(meta_value) FROM `wp_postmeta` where post_id in (SELECT id FROM `wp_posts` WHERE id in (SELECT object_id FROM `wp_term_relationships` WHERE `term_taxonomy_id` = '栏目的ID') and post_status = 'publish') and meta_key = 'views'

批量删除文章修订

DELETE FROM wp_posts WHERE post_type = "revision";

批量删除垃圾评论

DELETE from wp_comments WHERE comment_approved = '0';

手动重设密码

UPDATE wp_users SET user_pass = MD5('PASSWORD') WHERE wp_users.user_login ='admin';

更改WordPress的域名

UPDATE wp_options SET
option_value = replace(option_value, 'http://www.oldsite.com', 'http://www.newsite.com')
WHERE option_name = 'home' OR option_name = 'siteurl';
UPDATE wp_options SET
option_value = replace(option_value, 'http://www.oldsite.com', 'http://www.newsite.com')
WHERE option_name = 'home' OR option_name = 'siteurl';
UPDATE wp_posts SET
post_content = replace(post_content, 'http://www.oldsite.com', 'http://www.newsite.com');

UPDATE wp_options SET option_value = replace(option_value, 'http://www.旧网址.com','http://www.新网址.com') WHERE option_name = 'home' OR option_name = 'siteurl'

UPDATE wp_posts SET guid = REPLACE (guid, 'http://www.旧网址.com', 'http://www.新网址.com')

修改文章作者

UPDATE wp_posts SET post_author = 'b' WHERE post_author = 'a'

重设admin的用户名

UPDATE wp_users SET user_login = '新的用户名' WHERE user_login = 'admin'

手动重设密码

UPDATE wp_users SET user_pass = MD5('PASSWORD') WHERE wp_users.user_login ='用户名' LIMIT 1;

批量删除文章修订

WordPress2.6以后的版本增加了一个Post Revisions功能，就是为文章增加版本修订。虽然这个功能有点用，但文章修订增加了数据库的大小，我们可以选择批量删除。

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 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

删除所有的Pingback

DELETE FROM wp_comments
WHERE comment_type = 'pingback'

删除所有的垃圾评论

DELETE FROM wp_comments
WHERE comment_approved = 'spam'

关闭旧文章的留言

我们可以在WordPress控制面板中设置XX天之后的文章关闭留言功能，但是对于一些旧文章，该功能就起不到任何作用了。我们可以使用SQL语句设定XXXX之前的文章关闭留言功能。

UPDATE wp_posts
SET comment_status = 'closed'
WHERE post_date < '2009-01-01' AND post_status = 'publish'

更新留言者的网址

大量且有意义的读者留言是网站的财富，是我们的客户，如果某天一个读者网站的域名地址变更，给你发邮件要求更改网站中所有留言设计的读者站点网站，这时候是不是挺为难啊？因为WordPress未提供修改留言者网站地址的功能。不要着急，以下语句可以轻松解决你的烦恼。

UPDATE wp_comments
SET comment_author_url = REPLACE( comment_author_url, 'http://旧网址.com', 'http://新网址.com' )

开启所有评论

UPDATE wp_posts
SET comment_status = ‘open’
WHERE post_status = ‘publish’;

清理wp_postmeta表

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}}’;