16个wordpress有用的sql语句

收集一些wordpress的sql语句，以备后用：

1. 手动修改密码

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

2. 批量转换日式的作者

UPDATE wp_posts SET post_author=NEW_AUTHOR_ID WHERE post_author=OLD_AUTHOR_ID;

3. 删除日志修订版本和其相关的信息

DELETE a,b,c FROM wp_posts a WHERE a.post_type = 'revision' LEFT JOIN wp_term_relationships b ON (a.ID = b.object_id) LEFT JOIN wp_postmeta c ON (a.ID =

c.post_id);

4.批量删除所有未审核的评论

DELETE from wp_comments WHERE comment_approved = '0';

5. 查找未使用的标签

SELECT * From wp_terms wt INNER JOIN wp_term_taxonomy wtt ON wt.term_id=wtt.term_id WHERE wtt.taxonomy='post_tag' AND wtt.count=0;

6. 查找并替换数据

UPDATE table_name SET field_name = replace( field_name, 'string_to_find', 'string_to_replace' ) ;

7. 获得发表评论访问者的电子邮箱地址列表

SELECT DISTINCT comment_author_email FROM wp_comments;

8. 一次性禁用掉所有插件

UPDATE wp_options SET option_value = '' WHERE option_name = 'active_plugins';

9. 删除所有标签

DELETE a,b,cFROM database.prefix_terms AS a LEFT JOIN database.prefix_term_taxonomy AS c ON a.term_id = c.term_id LEFT JOIN
database.prefix_term_relationships AS b ON b.term_taxonomy_id = c.term_taxonomy_idWHERE ( c.taxonomy = 'post_tag' AND c.count = 0 );

10. 列举无用的日志信息

SELECT * FROM wp_postmeta pm LEFT JOIN wp_posts wp ON wp.ID = pm.post_id WHERE wp.ID IS NULL;

11. 禁用老日志的评论功能

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

12. 替换评论者的链接

UPDATE wp_comments SET comment_author_url = REPLACE( comment_author_url, 'http://oldurl.com', 'http://newurl.com' );

13. 替换评论者的电子邮箱地址

UPDATE wp_comments SET comment_author_email = REPLACE( comment_author_email, 'old-email@address.com', 'new-email@address.com' );

14. 删除包含指定链接的评论

DELETE from wp_comments WHERE comment_author_url LIKE "%wpbeginner%" ;

15.修改文章属性

首先你要找到你正确的用户名，使用下面的SQL语句可以找到你的用户ID号。

SELECT ID, display_name FROM wp_users;

假设这个ID为NEW_AUTHOR_ID，而管理员admin的ID为OLD_AUTHOR_ID，接着，运行下面的SQL语句。

UPDATE wp_posts SET post_author=NEW_AUTHOR_ID WHERE post_author=OLD_AUTHOR_ID;

16.更改wordpress的域名

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

接着，你还要使用下面的SQL将文章的GUID也进行修改。

UPDATE wp_posts SET guid = replace(guid, 'http://www.xptt.com','http://www.agting.com');

赏