如何用MariaDB/PHP从wp_postmeta序列化数据提取WooCommerce未用图片名？

我需要获取WooCommerce图库中指定帖子关联的所有未使用图片文件名列表，无法使用媒体库的未选中功能（该功能无法检索图库内容）。已获取待处理的post_ID列表，现需通过MySQL（MariaDB）或PHP从这些帖子对应的wp_postmeta序列化数据中提取文件名。

示例序列化数据：

a:6:{s:5:"width";i:710;s:6:"height";i:216;s:4:"file";s:14:"/filename1.jpg";s:5:"sizes";a:9:{s:6:"medium";a:5:{s:4:"file";s:20:"filename1-300x91.jpg";s:5:"width";i:300;s:6:"height";i:91;s:9:"mime-type";s:10:"image/jpeg";s:8:"filesize";i:0;}s:9:"thumbnail";a:5:{s:4:"file";s:21:"filename1-150x150.jpg";s:5:"width";i:150;s:6:"height";i:150;s:9:"mime-type";s:10:"image/jpeg";s:8:"filesize";i:0;}s:30:"product-search-thumbnail-32x32";a:5:{s:4:"file";s:19:"filename1-32x32.jpg";s:5:"width";i:32;s:6:"height";i:32;s:9:"mime-type";s:10:"image/jpeg";s:8:"filesize";i:0;}s:21:"woocommerce_thumbnail";a:5:{s:4:"file";s:21:"filename1-324x189.jpg";s:5:"width";i:324;s:6:"height";i:189;s:9:"mime-type";s:10:"image/jpeg";s:8:"filesize";i:0;}s:18:"woocommerce_single";a:5:{s:4:"file";s:21:"filename1-416x127.jpg";s:5:"width";i:416;s:6:"height";i:127;s:9:"mime-type";s:10:"image/jpeg";s:8:"filesize";i:0;}s:29:"woocommerce_gallery_thumbnail";a:5:{s:4:"file";s:21:"filename1-100x100.jpg";s:5:"width";i:100;s:6:"height";i:100;s:9:"mime-type";s:10:"image/jpeg";s:8:"filesize";i:0;}s:12:"shop_catalog";a:5:{s:4:"file";s:21:"filename1-324x189.jpg";s:5:"width";i:324;s:6:"height";i:189;s:9:"mime-type";s:10:"image/jpeg";s:8:"filesize";i:0;}s:11:"shop_single";a:5:{s:4:"file";s:21:"filename1-416x127.jpg";s:5:"width";i:416;s:6:"height";i:127;s:9:"mime-type";s:10:"image/jpeg";s:8:"filesize";i:0;}s:14:"shop_thumbnail";a:5:{s:4:"file";s:21:"filename1-100x100.jpg";s:5:"width";i:100;s:6:"height";i:100;s:9:"mime-type";s:10:"image/jpeg";s:8:"filesize";i:0;}}s:10:"image_meta";a:11:{s:8:"aperture";s:1:"0";s:6:"credit";s:0:"";s:6:"camera";s:0:"";s:7:"caption";s:0:"";s:17:"created_timestamp";s:1:"0";s:9:"copyright";s:0:"";s:12:"focal_length";s:1:"0";s:3:"iso";s:1:"0";s:13:"shutter_speed";s:1:"0";s:5:"title";s:0:"";s:11:"orientation";s:1:"0";}s:8:"filesize";i:27713;}

期望输出格式：

filename1.jpg
filename1-150x150.jpg
filename1-32x32.jpg
filename1-324x189.jpg
filename1-416x127.jpg
filename1-100x100.jpg
filename1-324x189.jpg
filename1-416x127.jpg
filename1-100x100.jpg

一、MySQL（MariaDB）解决方案 #

通过正则匹配序列化字符串中的所有文件名，分主图和缩略图两部分提取：

查询语句 #

将(123,456,789)替换为你的目标post_ID列表：

SELECT 
    SUBSTRING_INDEX(SUBSTRING_INDEX(meta_value, '"file";s:', -1), '"', 1) AS filename
FROM 
    wp_postmeta
WHERE 
    post_id IN (123, 456, 789)
    AND meta_key = '_wp_attachment_metadata'
UNION ALL
SELECT 
    SUBSTRING_INDEX(SUBSTRING_INDEX(sizes_match, '"file";s:', -1), '"', 1) AS filename
FROM (
    SELECT 
        meta_value,
        REGEXP_SUBSTR(meta_value, '"sizes";a:[0-9]+:{(.*?)}', 1, 1, 's') AS sizes_block
    FROM 
        wp_postmeta
    WHERE 
        post_id IN (123, 456, 789)
        AND meta_key = '_wp_attachment_metadata'
) AS t1,
REGEXP_SUBSTR(t1.sizes_block, '"file";s:[0-9]+:"[^"]+"', 1, n) AS sizes_match
WHERE 
    n <= (REGEXP_COUNT(t1.sizes_block, '"file";s:[0-9]+:"[^"]+"'))

说明 #

• 第一个SELECT提取主图文件名，第二个嵌套查询提取sizes字段下的所有缩略图文件名
• 若需去重（比如不同尺寸别名对应同一张缩略图），将UNION ALL改为UNION即可

二、PHP解决方案 #

利用WordPress内置函数解析序列化数据，比正则更可靠：

代码示例 #

<?php
// 替换为你的目标post_ID列表
$target_post_ids = [123, 456, 789];

global $wpdb;

// 查询指定帖子的附件元数据
$meta_data = $wpdb->get_results(
    $wpdb->prepare(
        "SELECT meta_value FROM wp_postmeta WHERE post_id IN (" . implode(',', array_fill(0, count($target_post_ids), '%d')) . ") AND meta_key = '_wp_attachment_metadata'",
        $target_post_ids
    )
);

$all_filenames = [];

foreach ($meta_data as $item) {
    // 解析序列化数据
    $attachment_data = maybe_unserialize($item->meta_value);
    if (!is_array($attachment_data)) continue;

    // 提取主图文件名（去除开头斜杠）
    $main_file = ltrim($attachment_data['file'], '/');
    $all_filenames[] = $main_file;

    // 提取所有缩略图文件名
    if (isset($attachment_data['sizes']) && is_array($attachment_data['sizes'])) {
        foreach ($attachment_data['sizes'] as $size) {
            if (isset($size['file'])) {
                $all_filenames[] = $size['file'];
            }
        }
    }
}

// 输出结果，每行一个文件名
echo implode("\n", $all_filenames);
?>

说明 #

• 使用maybe_unserialize兼容序列化/非序列化的元数据格式
• 若需去重，添加$all_filenames = array_unique($all_filenames);即可

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