WordPress批量处理文章SQL

发表于 2021年06月29日
WordPress

最近在筛查维护博客内的所有文章，频繁的用到，记录一下：

目录表

批量删除分类下所有文章

delete
from
wp_posts 
using 
wp_posts,
wp_term_relationships,
wp_term_taxonomy 
where 
wp_posts.id=wp_term_relationships.object_id 
and 
wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id 
and 
wp_term_relationships.term_taxonomy_id = 55555555

delete

from

wp_posts

using

wp_posts,

wp_term_relationships,

wp_term_taxonomy

where

wp_posts.id=wp_term_relationships.object_id

and

wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id

and

wp_term_relationships.term_taxonomy_id = 55555555

批量修改状态

UPDATE wp_posts, wp_term_relationships, wp_term_taxonomy
SET wp_posts.post_status = REPLACE( post_status, 'publish', 'private' )
WHERE
wp_posts.ID = wp_term_relationships.object_id
AND 
wp_term_taxonomy.taxonomy = 'category'
AND 
wp_term_taxonomy.term_taxonomy_id = wp_term_relationships.term_taxonomy_id
AND 
wp_posts.post_status = 'publish'
AND 
wp_posts.post_type = 'post'
AND 
wp_term_taxonomy.term_id = '55555555';

UPDATE wp_posts, wp_term_relationships, wp_term_taxonomy

SET wp_posts.post_status = REPLACE( post_status, 'publish', 'private' )

WHERE

wp_posts.ID = wp_term_relationships.object_id

AND

wp_term_taxonomy.taxonomy = 'category'

AND

wp_term_taxonomy.term_taxonomy_id = wp_term_relationships.term_taxonomy_id

AND

wp_posts.post_status = 'publish'

AND

wp_posts.post_type = 'post'

AND

wp_term_taxonomy.term_id = '55555555';

上面sql中的“55555555”是分类ID，其它语句根据自己情况调整，如加密：

SET wp_posts.post_password = '009988777651523712skjfdkdsjkf_86%^'

1	SET wp_posts.post_password = '009988777651523712skjfdkdsjkf_86%^'

或者修改状态：（文章状态和字段值的对应关系）

pending：待审，已完成并提交审核但尚未发布的文章
draft：草稿，已保存但尚未完成且尚未发布的文章
auto-draft：自动保存的草稿，WordPress具有自动保存功能，可自动将草稿保存为修订版
inherit：继承，子页面继承父级页面属性；
trash：回收站，被添加到回收站的文章
publish：已发布
future：定时，计划稍后定时发布的文章，也就是通过上图设置发布时间为未来某个时间点即可
private：私有，标记为私密的文章，只有自己登录后可见

指替换内容

和修改状态大同小异，不赘述

UPDATE wp_posts SET post_content = REPLACE(post_content, '原内容' , '替换为的内容');

1	UPDATE wp_posts SET post_content = REPLACE(post_content, '原内容' , '替换为的内容');

清空媒体库数据库

如果你需要清空WordPress媒体库的信息，可以在数据库中执行下面这些命令。

DELETE from wp_posts where post_type = 'attachment'

1	DELETE from wp_posts where post_type = 'attachment'

上面的命令可以删除所有媒体库信息。如果你想查询媒体库信息的话，就是下面这个命令。

Select * from wp_posts where post_type = 'attachment';

1	Select * from wp_posts where post_type = 'attachment';

清空媒体数据库后，怎么删除媒体文件和文章之间的联系呢？使用下面的命令。

DELETE FROM `wp_postmeta` WHERE meta_key IN ('_wp_attached_file', '_wp_attachment_backup_sizes', '_wp_attachment_metadata', '_thumbnail_id')

1	DELETE FROM `wp_postmeta` WHERE meta_key IN ('_wp_attached_file', '_wp_attachment_backup_sizes', '_wp_attachment_metadata', '_thumbnail_id')

如果只是查询媒体库和文章的关联，那么就使用下面的命令。

 SELECT * FROM  `wp_postmeta` WHERE meta_key IN ('_wp_attached_file', '_wp_attachment_backup_sizes',  '_wp_attachment_metadata',  '_thumbnail_id')

1	SELECT * FROM `wp_postmeta` WHERE meta_key IN ('_wp_attached_file', '_wp_attachment_backup_sizes', '_wp_attachment_metadata', '_thumbnail_id')

以上就是

标签：WordPress 原文连接：WordPress批量处理文章SQL 所有媒体，可在保留署名、原文连接的情况下转载，若非则不得使用我方内容。

内容维护通知…… No podspec found for `flutter_keyboard_visibility_web`解决