如何批量删除WordPress中未被其他文章使用的关联附件？

一、修复导致MySQL崩溃的SQL查询 #

你之前添加的两行SQL崩溃的核心原因是前缀带%的模糊查询（%xxx）无法利用数据库索引，会触发wp_posts和wp_postmeta全表扫描，数据量大时直接拖垮数据库。下面是优化后的查询，改用附件ID而非guid检查引用，同时优化逻辑提升执行效率：

SELECT i.ID 
FROM wp_posts i
WHERE i.post_type = 'attachment'
  AND i.ID = '10038' -- 替换为目标附件ID
  AND i.post_parent > 0
  -- 验证附件所属父文章有效
  AND EXISTS (SELECT 1 FROM wp_posts p WHERE p.ID = i.post_parent)
  -- 未被产品图片画廊引用
  AND NOT EXISTS (SELECT 1 FROM wp_postmeta pm 
                  WHERE pm.meta_key = '_product_image_gallery' 
                  AND FIND_IN_SET(i.ID, pm.meta_value))
  -- 未被其他文章设为特色图
  AND NOT EXISTS (SELECT 1 FROM wp_postmeta pm 
                  WHERE pm.meta_key = '_thumbnail_id' 
                  AND pm.meta_value = i.ID 
                  AND pm.post_id != i.post_parent)
  -- 未被其他文章内容通过标准短代码引用
  AND NOT EXISTS (SELECT 1 FROM wp_posts p 
                  WHERE p.post_type != 'attachment' 
                  AND p.ID != i.post_parent
                  AND (p.post_content LIKE CONCAT('%wp-image-', i.ID, '%') 
                       OR p.post_content LIKE CONCAT('%[gallery%ids%', i.ID, '%]%')))
  -- 未被其他自定义字段（含ACF）引用
  AND NOT EXISTS (SELECT 1 FROM wp_postmeta pm 
                  WHERE pm.post_id != i.post_parent
                  AND (pm.meta_value = CAST(i.ID AS CHAR) 
                       OR pm.meta_value LIKE CONCAT('%"', i.ID, '"%') -- 匹配JSON/数组格式字段
                       OR pm.meta_value LIKE CONCAT('%', i.ID, ',%') 
                       OR pm.meta_value LIKE CONCAT('%,', i.ID, '%')))

关键优化点： #

• 用FIND_IN_SET替代LIKE检查图片画廊的ID列表，避免全表扫描
• 检查引用时排除当前父文章，避免误判自身关联
• 文章内容仅检查WordPress标准图片短代码格式，而非全量匹配URL，大幅降低查询负载
• 自定义字段覆盖单个ID、逗号分隔列表、JSON数组三种常见存储格式

二、完整PHP实现代码 #

结合你的现有代码，以下是遍历文章、收集所有关联媒体（特色图+ACF字段）、检查引用后删除的完整逻辑：

// 遍历目标文章集合（替换为你的get_posts参数）
$posts = get_posts([
    'post_type' => 'event', // 替换为你的文章类型
    'posts_per_page' => -1,
    // 其他筛选条件按需添加
]);

foreach ($posts as $event) {
    $attachment_ids = [];

    // 1. 收集文章特色图ID
    if (has_post_thumbnail($event->ID)) {
        $attachment_ids[] = get_post_thumbnail_id($event->ID);
    }

    // 2. 收集ACF图片字段ID（示例：单个图片+图片数组字段）
    // 单个图片字段
    $acf_single_image = get_field('your_single_image_field', $event->ID);
    if ($acf_single_image && isset($acf_single_image['ID'])) {
        $attachment_ids[] = $acf_single_image['ID'];
    }
    // 图片数组/画廊字段
    $acf_image_gallery = get_field('your_image_gallery_field', $event->ID);
    if ($acf_image_gallery && is_array($acf_image_gallery)) {
        foreach ($acf_image_gallery as $image) {
            if (isset($image['ID'])) {
                $attachment_ids[] = $image['ID'];
            }
        }
    }

    // 去重，避免重复处理同一附件
    $attachment_ids = array_unique($attachment_ids);

    foreach ($attachment_ids as $attach_id) {
        global $wpdb;
        // 检查附件是否被其他文章引用
        $is_unused = $wpdb->get_var($wpdb->prepare("
            SELECT COUNT(*)
            FROM wp_posts i
            LEFT JOIN wp_postmeta pm_thumb ON pm_thumb.meta_key = '_thumbnail_id' AND pm_thumb.meta_value = i.ID AND pm_thumb.post_id != %d
            LEFT JOIN wp_postmeta pm_gallery ON pm_gallery.meta_key = '_product_image_gallery' AND FIND_IN_SET(i.ID, pm_gallery.meta_value) AND pm_gallery.post_id != %d
            LEFT JOIN wp_posts p_content ON p_content.post_type != 'attachment' AND p_content.ID != %d AND (p_content.post_content LIKE CONCAT('%%wp-image-', i.ID, '%%') OR p_content.post_content LIKE CONCAT('%%[gallery%%ids%%', i.ID, '%%]%%'))
            LEFT JOIN wp_postmeta pm_custom ON pm_custom.post_id != %d AND (pm_custom.meta_value = CAST(i.ID AS CHAR) OR pm_custom.meta_value LIKE CONCAT('%%\"', i.ID, '\"%%') OR pm_custom.meta_value LIKE CONCAT('%%', i.ID, ',%%') OR pm_custom.meta_value LIKE CONCAT('%%,', i.ID, '%%'))
            WHERE i.ID = %d
              AND i.post_type = 'attachment'
            HAVING COUNT(pm_thumb.ID) + COUNT(pm_gallery.ID) + COUNT(p_content.ID) + COUNT(pm_custom.ID) = 0
        ", $event->ID, $event->ID, $event->ID, $event->ID, $attach_id));

        if ($is_unused) {
            // 删除附件（第二个参数true表示同时删除服务器上的文件）
            wp_delete_attachment($attach_id, true);
            // 可选：清理当前文章的关联元数据
            delete_post_meta($event->ID, '_thumbnail_id');
            delete_post_meta($event->ID, 'your_single_image_field');
            delete_post_meta($event->ID, 'your_image_gallery_field');
        }
    }
}

代码说明： #

• 替换your_single_image_field和your_image_gallery_field为你的ACF字段名
• 使用$wpdb预处理查询，避免SQL注入风险
• 检查逻辑覆盖特色图、产品画廊、文章内容短代码、自定义字段四种常见引用场景
• 仅当附件无任何外部引用时才执行删除，彻底避免影响其他文章

内容的提问来源于stack exchange，提问作者Daniel Vickers