1.wp_post优化

删除不需要分类下文章

DELETE p
FROM wp_posts p
INNER JOIN wp_term_relationships tr ON p.ID = tr.object_id
INNER JOIN wp_term_taxonomy tt ON tr.term_taxonomy_id = tt.term_taxonomy_id
WHERE tt.term_id = 236

2.wp_term_relationships优化

删除没有文章引用的term

DELETE tr
FROM wp_term_relationships tr
LEFT JOIN wp_posts p ON tr.object_id = p.ID
WHERE p.ID IS NULL;

3.文件优化

查找未被引用的附近

SELECT * FROM wp_posts
WHERE post_type = 'attachment'
 AND ID NOT IN (
 SELECT post_id FROM wp_postmeta
 WHERE meta_key IN ('_thumbnail_id', '_wp_attached_file')
 )

python删除文件脚本(需要替换自己的数据库连接信息以及文件上传位置)

import os
import pymysql
import datetime

def delete_unused_attachments(db_conn, upload_dir):
 """删除未使用的 WordPress 附件

 Args:
 db_conn: 数据库连接对象
 upload_dir: 上传目录

 Returns:
 None
 """

 cursor = db_conn.cursor()

 # 执行 SQL 查询，获取未使用的附件信息
 sql = """
 SELECT ID, post_title, post_date
 FROM wp_posts
 WHERE post_type = 'attachment'
 AND ID NOT IN (
 SELECT post_id FROM wp_postmeta WHERE meta_key IN ('_thumbnail_id', '_wp_attached_file')
 )
 """
 cursor.execute(sql)
 results = cursor.fetchall()

 for result in results:
 attachment_id = result[0]
 post_title = result[1] # 使用 post_title 作为文件名
 post_date = result[2] # 直接获取 post_date，假设是 datetime.datetime 对象

 # 检查 post_date 的类型并进行处理
 if isinstance(post_date, datetime.datetime):
 # 如果是 datetime 对象，直接使用
 year = post_date.year
 month = post_date.month
 elif isinstance(post_date, str):
 # 如果是字符串，尝试解析
 try:
 post_date = datetime.datetime.strptime(post_date, '%Y-%m-%d %H:%M:%S') # 调整格式字符串
 year = post_date.year
 month = post_date.month
 except ValueError:
 print(f"日期格式错误: {post_date}")
 continue
 else:
 print(f"不支持的数据类型: {type(post_date)}")
 continue

 # 将年份和月份格式化为双数格式
 year_str = str(year)
 month_str = f"{month:02d}" # 将月份格式化为双数格式

 # 构建完整文件名，直接使用 post_title
 file_name = post_title # 不再添加扩展名

 # 构建完整路径
 full_path = os.path.join(upload_dir, year_str, month_str, file_name)
 print(f"full_path: {full_path}")

 # 删除文件
 if os.path.exists(full_path):
 try:
 os.remove(full_path)
 # 删除数据库记录 (根据实际情况调整)
 # ...
 print(f"已删除附件 ID: {attachment_id}, 文件: {full_path}")
 except OSError as e:
 print(f"删除文件失败: {e}")
 else:
 print(f"文件 {full_path} 不存在")

# 连接到数据库
db = pymysql.connect( 
 host='xxxxx', 
 user='xxx', 
 password='xxxx', 
 database='xxxx' 
) 
# 设置上传目录
upload_dir = "/alidata2/html/wordpress/wp-content/uploads"

# 调用函数删除附件
delete_unused_attachments(db, upload_dir)

# 关闭数据库连接
db.close()

安装python3

yum install Python3

执行Python3脚本

python3 delete_unuse_file.py

删除成功后，删除数据库记录

delete FROM wp_posts
WHERE post_type = 'attachment'
 AND ID NOT IN (
 SELECT post_id FROM wp_postmeta
 WHERE meta_key IN ('_thumbnail_id', '_wp_attached_file')
 )

删除关系关系

DELETE tr
FROM wp_term_relationships tr
LEFT JOIN wp_posts p ON tr.object_id = p.ID
WHERE p.ID IS NULL;