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 = 2362.wp_term_relationships优化
删除没有文章引用的termDELETE 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')
)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()yum install Python3python3 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;正文到此结束
热门推荐
相关文章
近期评论
-
文章和留言都翻到11页了 没有OOM
-
-
朋友，翻页到11页，及以后，会出现OOM，无法访问
-
-
搞个gitee的项目
-
-
版本号是多少，你可以下载哪个代码仓库，jdk选1.8 直接跑就行
-
-
-
Loading...
![[HBLOG]公众号](https://www.liuhaihua.cn/img/qrcode_gzh.jpg)
