wordpress 数据库和文件优化

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_postmetaWHERE meta_key IN ('_thumbnail_id', '_wp_attached_file'))

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

import os
import pymysql
import datetimedef 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_dateFROM wp_postsWHERE 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.yearmonth = post_date.monthelif isinstance(post_date, str):# 如果是字符串，尝试解析try:post_date = datetime.datetime.strptime(post_date, '%Y-%m-%d %H:%M:%S') # 调整格式字符串year = post_date.yearmonth = post_date.monthexcept ValueError:print(f"日期格式错误: {post_date}")continueelse:print(f"不支持的数据类型: {type(post_date)}")continue# 将年份和月份格式化为双数格式year_str = str(year)month_str = f"{month:02d}" # 将月份格式化为双数格式# 构建完整文件名，直接使用 post_titlefile_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_postmetaWHERE 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;