米扑博客在使用WordPress过程中,经常要对数据库进行修改操作

比如,更换域名、修改附件目录、批量修改文章内容等等。

这个时候,使用SQL查询语句可以大大简化我们的工作量。

 

本文分享WordPress常用的SQL查询语句

准备工作

1. 在使用SQL查询修改语句前,请务必导出备份数据库!

2. WordPress数据库都是使用默认的 wp_ 表头,请根据自己的实际修改。

3. 联系SQL语句之前,请先熟悉 WordPress 数据库及各表结构

 

SQL常用语句

1. 删除所有未使用的标签

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

 

语句示例

1)查询标签tag排名的tag名称

select a.term_id, a.name, a.slug, c.taxonomy, c.count 
from wp_terms AS a 
left JOIN wp_term_taxonomy AS c ON a.term_id = c.term_id  
where c.taxonomy = 'post_tag' 
order by c.count desc limit 10;

查询结果:

+---------+--------------+--------------------------------------+----------+-------+
| term_id | name         | slug                                 | taxonomy | count |
+---------+--------------+--------------------------------------+----------+-------+
|    2084 | 代理         | %e4%bb%a3%e7%90%86                   | post_tag |   839 |
|    3638 | 米扑代理     | %e7%b1%b3%e6%89%91%e4%bb%a3%e7%90%86 | post_tag |   819 |
|    2088 | Proxy        | proxy                                | post_tag |   816 |
|    4769 | 代理IP       | %e4%bb%a3%e7%90%86ip                 | post_tag |   813 |
|    4768 | 免费代理     | %e5%85%8d%e8%b4%b9%e4%bb%a3%e7%90%86 | post_tag |   806 |
|     636 | linux        | linux                                | post_tag |   133 |
|     619 | python       | python-2                             | post_tag |   127 |
|     530 | mysql        | mysql                                | post_tag |    86 |
|       7 | android      | android                              | post_tag |    85 |
|    1215 | C#           | c                                    | post_tag |    84 |
+---------+--------------+--------------------------------------+----------+-------+

 

查询标签tag和分类category排名

select a.term_id, a.name, a.slug, c.taxonomy, c.count 
from wp_terms AS a  
left JOIN wp_term_taxonomy AS c ON a.term_id = c.term_id 
where c.count > 50 
order by c.count desc limit 10;

查询结果:

+---------+--------------+--------------------------------------+----------+-------+
| term_id | name         | slug                                 | taxonomy | count |
+---------+--------------+--------------------------------------+----------+-------+
|    4924 | CSDN         | csdn-blog                            | category |   877 |
|    2084 | 代理         | %e4%bb%a3%e7%90%86                   | post_tag |   839 |
|    3638 | 米扑代理     | %e7%b1%b3%e6%89%91%e4%bb%a3%e7%90%86 | post_tag |   819 |
|    2088 | Proxy        | proxy                                | post_tag |   816 |
|    4769 | 代理IP       | %e4%bb%a3%e7%90%86ip                 | post_tag |   813 |
|    4766 | 米扑代理     | mimvp-proxy                          | category |   808 |
|    4768 | 免费代理     | %e5%85%8d%e8%b4%b9%e4%bb%a3%e7%90%86 | post_tag |   806 |
|       3 | 创业邦       | start-up                             | category |   277 |
|      10 | 科技资讯     | it-news                              | category |   246 |
|     635 | Linux/Unix   | linux-unix                           | category |   229 |
+---------+--------------+--------------------------------------+----------+-------+

 

修改标签tag排名大于500

a)首先,查询标签tag排名大于500

select term_taxonomy_id, term_id, taxonomy, parent, count 
from wp_term_taxonomy 
where count > 500 and taxonomy = 'post_tag' 
order by count desc;

查询结果:

+------------------+---------+----------+--------+-------+
| term_taxonomy_id | term_id | taxonomy | parent | count |
+------------------+---------+----------+--------+-------+
|             2092 |    2084 | post_tag |      0 |   839 |
|             3647 |    3638 | post_tag |      0 |   819 |
|             2096 |    2088 | post_tag |      0 |   816 |
|             4778 |    4769 | post_tag |      0 |   813 |
|             4777 |    4768 | post_tag |      0 |   806 |
+------------------+---------+----------+--------+-------+

 

b)然后,修改标签tag排名大于500为50

再次提示:修改删除操作数据库前,请务必先备份数据库!

update wp_term_taxonomy 
set count = 50 
where count > 500 and taxonomy = 'post_tag';

查询修改后的结果验证

select term_taxonomy_id, term_id, taxonomy, parent, count 
from wp_term_taxonomy 
where term_id in (2084,3638,2088,4769,4768);

验证的结果:

+------------------+---------+----------+--------+-------+
| term_taxonomy_id | term_id | taxonomy | parent | count |
+------------------+---------+----------+--------+-------+
|             2092 |    2084 | post_tag |      0 |    50 |
|             2096 |    2088 | post_tag |      0 |    50 |
|             3647 |    3638 | post_tag |      0 |    50 |
|             4777 |    4768 | post_tag |      0 |    50 |
|             4778 |    4769 | post_tag |      0 |    50 |
+------------------+---------+----------+--------+-------+

 

2)查询标签tag排名的文章名称

select a.term_id, a.name, a.slug, c.taxonomy, c.count, b.object_id, d.post_title
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
LEFT JOIN wp_posts AS d ON d.id = b.object_id
WHERE c.taxonomy = 'post_tag' order by c.count desc limit 10;

查询结果:

+---------+--------+--------------------+----------+-------+-----------+--------------------------------------------------------+
| term_id | name   | slug               | taxonomy | count | object_id | post_title                                             |
+---------+--------+--------------------+----------+-------+-----------+--------------------------------------------------------+
|    2084 | 代理   | %e4%bb%a3%e7%90%86 | post_tag |   839 |     12953 | 【米扑代理】最新免费代理IP(2016-04-19)               |
|    2084 | 代理   | %e4%bb%a3%e7%90%86 | post_tag |   839 |     12952 | 【米扑代理】最新免费代理IP(2016-04-18)               |
|    2084 | 代理   | %e4%bb%a3%e7%90%86 | post_tag |   839 |     12951 | 【米扑代理】最新免费代理IP(2016-04-17)               |
|    2084 | 代理   | %e4%bb%a3%e7%90%86 | post_tag |   839 |     12950 | 【米扑代理】最新免费代理IP(2016-04-16)               |
|    2084 | 代理   | %e4%bb%a3%e7%90%86 | post_tag |   839 |     12948 | 【米扑代理】最新免费代理IP(2016-04-14)               |
|    2084 | 代理   | %e4%bb%a3%e7%90%86 | post_tag |   839 |     12949 | 【米扑代理】最新免费代理IP(2016-04-15)               |
|    2084 | 代理   | %e4%bb%a3%e7%90%86 | post_tag |   839 |     12947 | 【米扑代理】最新免费代理IP(2016-04-13)               |
|    2084 | 代理   | %e4%bb%a3%e7%90%86 | post_tag |   839 |     12943 | 【米扑代理】最新免费代理IP(2016-04-12)               |
|    2084 | 代理   | %e4%bb%a3%e7%90%86 | post_tag |   839 |     12940 | 【米扑代理】最新免费代理IP(2016-04-11)               |
|    2084 | 代理   | %e4%bb%a3%e7%90%86 | post_tag |   839 |     12939 | 【米扑代理】最新免费代理IP(2016-04-10)               |
+---------+--------+--------------------+----------+-------+-----------+--------------------------------------------------------+

 

3)查询特定分类(term_id = 4766)的文章

select a.term_id, a.name, a.slug, c.taxonomy, c.count, b.object_id, d.post_title
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
LEFT JOIN wp_posts AS d ON d.id = b.object_id
WHERE a.term_id = 4766 order by c.count desc, post_date_gmt desc limit 10;

查询结果:

+---------+--------------+-------------+----------+-------+-----------+--------------------------------------------------------+
| term_id | name         | slug        | taxonomy | count | object_id | post_title                                             |
+---------+--------------+-------------+----------+-------+-----------+--------------------------------------------------------+
|    4766 | 米扑代理     | mimvp-proxy | category |   808 |     20361 | 【米扑代理】最新免费代理IP(2017-09-23)               |
|    4766 | 米扑代理     | mimvp-proxy | category |   808 |     20350 | 【米扑代理】最新免费代理IP(2017-09-22)               |
|    4766 | 米扑代理     | mimvp-proxy | category |   808 |     20331 | 【米扑代理】最新免费代理IP(2017-09-21)               |
|    4766 | 米扑代理     | mimvp-proxy | category |   808 |     20314 | 【米扑代理】最新免费代理IP(2017-09-20)               |
|    4766 | 米扑代理     | mimvp-proxy | category |   808 |     20313 | 【米扑代理】最新免费代理IP(2017-09-19)               |
|    4766 | 米扑代理     | mimvp-proxy | category |   808 |     20304 | 【米扑代理】最新免费代理IP(2017-09-18)               |
|    4766 | 米扑代理     | mimvp-proxy | category |   808 |     20303 | 【米扑代理】最新免费代理IP(2017-09-17)               |
|    4766 | 米扑代理     | mimvp-proxy | category |   808 |     20302 | 【米扑代理】最新免费代理IP(2017-09-16)               |
|    4766 | 米扑代理     | mimvp-proxy | category |   808 |     20301 | 【米扑代理】最新免费代理IP(2017-09-15)               |
|    4766 | 米扑代理     | mimvp-proxy | category |   808 |     20286 | 【米扑代理】最新免费代理IP(2017-09-14)               |
+---------+--------------+-------------+----------+-------+-----------+--------------------------------------------------------+

 

以上操作数据库实战,效果详见米扑博客https://blog.mimvp.com

 

2. 删除所有文章修订版本(Revisions)以及它们的Meta数据

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'

 

3. 更改WordPress地址和首页地址

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

米扑博客查询示例:

select * from wp_options where option_name = 'home' or option_name = 'siteurl';
+-----------------+-------------+------------------------+----------+
| option_id       | option_name | option_value           | autoload |
+-----------------+-------------+------------------------+----------+
|              37 | home        | https://blog.mimvp.com | yes      |
| 246673192255488 | siteurl     | https://blog.mimvp.com | yes      |
+-----------------+-------------+------------------------+----------+

 

4. 更改文章的GUID

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

 

5. 更改正文中的链接地址

UPDATE wp_posts
SET post_content = REPLACE (post_content, 'http://www.ithomer.com', 'https://www.mimvp.com')

说明:

旧网址:http://www.ithomer.com 

新网址:https://www.mimvp.com

 

6. 更新文章的Meta值

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

 

7. 重设Admin密码

UPDATE wp_users
SET user_pass = MD5( 'new_password' )
WHERE user_login = 'admin'

wp_users 数据表字段:

desc wp_users;
+---------------------+---------------------+------+-----+---------------------+----------------+
| Field               | Type                | Null | Key | Default             | Extra          |
+---------------------+---------------------+------+-----+---------------------+----------------+
| ID                  | bigint(20) unsigned | NO   | PRI | NULL                | auto_increment |
| user_login          | varchar(60)         | NO   | MUL |                     |                |
| user_pass           | varchar(255)        | NO   |     |                     |                |
| user_nicename       | varchar(50)         | NO   | MUL |                     |                |
| user_email          | varchar(100)        | NO   | MUL |                     |                |
| user_url            | varchar(100)        | NO   |     |                     |                |
| user_registered     | datetime            | NO   |     | 0000-00-00 00:00:00 |                |
| user_activation_key | varchar(255)        | NO   |     |                     |                |
| user_status         | int(11)             | NO   |     | 0                   |                |
| display_name        | varchar(250)        | NO   |     |                     |                |
+---------------------+---------------------+------+-----+---------------------+----------------+

 

8. 重设admin的用户名

UPDATE wp_users
SET user_login = 'newname'
WHERE user_login = 'admin'

 

9. 将作者a的文章全部转移到作者b

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

 

10. 删除文章的meta标签

DELETE FROM wp_postmeta
WHERE meta_key = 'your-meta-key'

 

11. 导出所有评论中的邮件地址 有用

SELECT DISTINCT comment_author_email
FROM wp_comments

 

12. 删除所有的Pingback

DELETE FROM wp_comments
WHERE comment_type = 'pingback'

停用/激活trackback与pingback

指定comment_status的值为open、closed或registered_only。

向所有用户激活pingbacks/trackbacks:
UPDATE wp_posts  SET ping_status = 'open';

向所有用户禁用pingbacks/trackbacks:
UPDATE wp_posts  SET ping_status = 'closed';

 

13. 删除所有的垃圾评论有用

DELETE FROM wp_comments
WHERE comment_approved = 'spam'

删除特定URL的评论
当你发现很多垃圾评论都带有相同的URL链接,可以利用下面的查询一次性删除这些评论。%表示含有“%”符号内字符串的所有URL都将被删除。
DELETE from  wp_comments WHERE comment_author_url LIKE "%nastyspamurl%";

 

14. 禁用所有激活的插件

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

 

15. 列出所有未使用的Meta标签

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

 

16. 关闭旧文章的留言

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

 

17. 更新留言者的网址有用

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

 

18. 更新正文内所有的 target="_blank" 为 rel="nofollow"

UPDATE wp_posts
SET post_content = REPLACE (post_content, 'target="_blank',  'rel="nofollow')

 

19. 删除未使用的Meta标签

DELETE pm
FROM wp_postmeta pm
LEFT JOIN wp_posts wp ON wp.ID = pm.post_id
WHERE wp.ID IS NULL

 

20. 删除重复的自定义字段Custom Fields

自定义字段(Custom Fields)为WordPress提供了非常有用的扩展功能。

测试最近浏览量最多的文章的时候,发现居然存在重复的文章,查看后发现该文章存在两个同样的字段 views

出现这种情况,可能是由于网站搬家的导出导入文章造成的,下面分享两种方法删除重复的自定义字段(只保留一个)。

方法1:通过SQL语句删除

delete from wp_postmeta
where meta_id in (
	select *
	from (
       select meta_id
       from wp_postmeta a
       where a.meta_key = 'views'
       and meta_id not in (
           select min(meta_id)
           from wp_postmeta b
           where b.post_id = a.post_id
           and b.meta_key = 'views'
       )
	) as x
);

请根据自己的实际修改第 1、6、10 行的 wp_postmeta 的前缀 wp_

如果你的数据库前缀不是wp_ 的话,本例的第 7 、12 行 的 views 就是要删除的自定义字段,请自行修改。

 

方法2:通过PHP代码删除

1)在网站的根目录新建一个名为 remove-duplicate-custom-fields.php 文件

vim  remove-duplicate-custom-fields.php

复制下面的代码到该文件,保存:

<?php
define('WP_USE_THEMES', false);
require('wp-blog-header.php');
 
    define( 'WP_DEBUG_DISPLAY', true ); 
    ini_set( 'display_errors', true );
    $allposts = get_posts('numberposts=-1&post_type=post&post_status=any');
    $keys = array('views','test_meta');//要检索的自定义字段
    foreach ( $keys as $key ) {
        foreach( $allposts as $postinfo) {
            // 获取(上面所填写的)自定义字段的值
            $postmeta = get_post_meta($postinfo->ID, $key);
 
            if (!empty($postmeta) ) {
                // 删除这篇文章的(上面所填写的)自定义字段
                delete_post_meta($postinfo->ID, $key);
 
                // 插入一个且只有一个(上面所填写的)自定义字段
                update_post_meta($postinfo->ID, $key, $postmeta[0]);
            }
        }
    }
?>

注意修改第 8 行的字段,本例删除的是 ‘views’和’test_meta’ 两个字段,请自行修改(多个字段使用半角英文逗号隔开)。

 

2) 通过浏览器访问

http://你的域名/remove-duplicate-custom-fields.php

稍等片刻,即可删除多余的重复字段啦!

 

21. 更改文章自定义字段的名称

UPDATE `wp_postmeta` SET `meta_key` = '新名称' WHERE `meta_key` = '旧名称';

 

22. 为所有文章和页面添加自定义字段

有时需要给WordPress数据库内所有文章和页面添加一个自定义字段。

这时,你需要做的就是把代码中的‘UniversalCutomField‘替换成你需要的文字,然后把‘MyValue‘改成需要的值。

INSERT INTO wp_postmeta  (post_id, meta_key, meta_value)
SELECT ID AS post_id,  'UniversalCustomField'
AS meta_key 'MyValue AS  meta_value FROM wp_posts
WHERE ID NOT IN (SELECT  post_id FROM wp_postmeta WHERE meta_key = 'UniversalCustomField');

如果只需要为文章添加自定义字段,可以使用下面这段代码:

INSERT INTO wp_postmeta  (post_id, meta_key, meta_value)
SELECT ID AS post_id,  'UniversalCustomField'
AS meta_key 'MyValue AS  meta_value
FROM  wp_posts WHERE ID NOT IN
(SELECT  post_id FROM wp_postmeta WHERE meta_key = 'UniversalCustomField')
`` AND post_type = 'post';

如果只需要为页面添加自定义字段,可以使用下面这段代码:

INSERT INTO wp_postmeta  (post_id, meta_key, meta_value)
SELECT ID AS post_id,  'UniversalCustomField'
AS meta_key 'MyValue AS  meta_value
FROM  wp_posts WHERE ID NOT IN
(SELECT  post_id FROM wp_postmeta WHERE meta_key = 'UniversalCustomField')
AND `post_type` = 'page';

 

23. 将文章转为页面

依然只要通过PHPMyAdmin运行一个SQL查询就可以搞定:

UPDATE wp_posts  SET post_type = 'page' WHERE post_type = 'post'

将页面转换成文章:

UPDATE wp_posts  SET post_type = 'post' WHERE post_type = 'page'

 

24. 搜索并替换文章内容

OriginalText换成被替换内容,ReplacedText换成目标内容:

UPDATE wp_posts SET `post_content`
 = REPLACE (`post_content`,
 'OriginalText', 'ReplacedText');


25. 更改图片URL

下面的SQL命令可以帮你修改图片路径:

UPDATE wp_posts
SET post_content  = REPLACE (post_content, 'src=”http:///blog.mimvp.com',
'src=”https://blog.mimvp.com');

 

 

参考推荐

WordPress 数据库及各表结构

WordPress 常用数据库SQL查询语句大全