咨询WooCommerce订阅信息数据库存储位置及缺失表问题

一、纠正表名误解 #

WooCommerce Subscriptions 5.2.0 并没有单独的shop_subscription或wp_shop_subscription表，订阅本质是自定义订单类型，核心数据分散存储在以下标准WordPress/WooCommerce表中：

• wp_posts：存储订阅基础信息（订单号、状态、创建时间等），post_type字段值为shop_subscription
• wp_postmeta：存储订阅详细元数据（订阅周期、金额、下一次付款时间、关联父订单ID等）
• wp_users & wp_usermeta：存储用户基础信息及用户元数据

二、直接数据库查询获取数据 #

1. 批量获取订阅+关联用户信息 #

通过联表查询一次性拉取订阅和对应用户的完整数据：

SELECT
    p.ID AS subscription_id,
    p.post_title AS subscription_number,
    p.post_status AS subscription_status,
    u.ID AS user_id,
    u.user_login,
    u.user_email,
    pm.meta_key,
    pm.meta_value
FROM wp_posts p
JOIN wp_users u ON p.post_author = u.ID
LEFT JOIN wp_postmeta pm ON p.ID = pm.post_id
WHERE p.post_type = 'shop_subscription'
ORDER BY p.post_date DESC;

2. 精准获取订阅核心参数 #

如果只需要订阅的关键业务数据，可针对性查询：

SELECT
    p.ID AS subscription_id,
    p.post_title AS subscription_number,
    MAX(CASE WHEN pm.meta_key = '_subscription_period' THEN pm.meta_value END) AS period,
    MAX(CASE WHEN pm.meta_key = '_subscription_interval' THEN pm.meta_value END) AS interval,
    MAX(CASE WHEN pm.meta_key = '_subscription_total' THEN pm.meta_value END) AS total_amount,
    MAX(CASE WHEN pm.meta_key = '_next_payment_date' THEN pm.meta_value END) AS next_payment_date,
    u.user_email AS user_email
FROM wp_posts p
JOIN wp_users u ON p.post_author = u.ID
LEFT JOIN wp_postmeta pm ON p.ID = pm.post_id
WHERE p.post_type = 'shop_subscription'
GROUP BY p.ID, p.post_title, u.user_email;

注意：将wp_替换为你的数据库实际表前缀，可在wp-config.php中查看$table_prefix的值。

三、推荐使用WooCommerce内置函数（更安全） #

直接操作数据库易受版本更新影响，推荐用官方提供的函数获取数据：

1. 获取所有订阅 #

// 获取所有订阅对象集合
$subscriptions = wcs_get_subscriptions();

foreach ($subscriptions as $subscription) {
    $subscription_id = $subscription->get_id();
    $status = $subscription->get_status();
    $user_id = $subscription->get_user_id();
    $user = get_user_by('id', $user_id);
    $total = $subscription->get_total();
    $next_payment = $subscription->get_date('next_payment');
    
    // 按需处理输出
    echo "订阅ID: {$subscription_id}, 用户邮箱: {$user->user_email}, 状态: {$status}";
}

2. 根据用户ID筛选订阅 #

$target_user_id = 123; // 替换为目标用户ID
$user_subscriptions = wcs_get_users_subscriptions($target_user_id);

foreach ($user_subscriptions as $subscription) {
    // 处理单条订阅数据
    echo "订阅编号: {$subscription->get_order_number()}, 周期: {$subscription->get_subscription_period()}";
}

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