鸿 网 互 联 www.68idc.cn

当前位置 : 服务器租用 > cms安装教程 > WordPress > >

wordpress 常用 数据库脚本

来源:互联网 作者:佚名 时间:2015-10-18 05:59
按栏目统计所有文章的访问量 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 = ' pu

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

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

 

网友评论
<