如何在WooCommerce中仅对下载属性为yes的产品更新pm_virtual.meta_value为'no'

重要提醒 #

操作数据库前务必先备份，避免数据丢失或损坏。

方法一：直接使用SQL语句更新 #

先运行你提供的查询语句，确认需要更新的产品列表：

SELECT
  p.ID,
  p.post_title,
  pm_downloadable.meta_value AS downloadable_trait,
  pm_virtual.meta_value AS virtual_trait
FROM
  xhdps_posts AS p
  INNER JOIN xhdps_postmeta AS pm_downloadable ON p.ID = pm_downloadable.post_id
  LEFT JOIN xhdps_postmeta AS pm_virtual ON p.ID = pm_virtual.post_id
  AND pm_virtual.meta_key = '_virtual'
WHERE
  p.post_type = 'product'
  AND p.post_status = 'publish'
  AND pm_downloadable.meta_key = '_downloadable'
  AND pm_downloadable.meta_value = 'yes'
  AND pm_virtual.meta_value = 'no'

确认无误后，执行以下更新语句：

-- 更新已有_virtual元数据的产品
UPDATE xhdps_postmeta AS pm_virtual
INNER JOIN xhdps_postmeta AS pm_downloadable 
    ON pm_virtual.post_id = pm_downloadable.post_id
INNER JOIN xhdps_posts AS p 
    ON pm_virtual.post_id = p.ID
SET pm_virtual.meta_value = 'no'
WHERE
    p.post_type = 'product'
    AND p.post_status = 'publish'
    AND pm_downloadable.meta_key = '_downloadable'
    AND pm_downloadable.meta_value = 'yes'
    AND pm_virtual.meta_key = '_virtual';

如果部分可下载产品没有_virtual元数据，需要先补充该记录再更新：

-- 为缺失_virtual元数据的可下载产品添加记录并设为no
INSERT INTO xhdps_postmeta (post_id, meta_key, meta_value)
SELECT p.ID, '_virtual', 'no'
FROM xhdps_posts AS p
INNER JOIN xhdps_postmeta AS pm_downloadable ON p.ID = pm_downloadable.post_id
LEFT JOIN xhdps_postmeta AS pm_virtual ON p.ID = pm_virtual.post_id AND pm_virtual.meta_key = '_virtual'
WHERE
    p.post_type = 'product'
    AND p.post_status = 'publish'
    AND pm_downloadable.meta_key = '_downloadable'
    AND pm_downloadable.meta_value = 'yes'
    AND pm_virtual.post_id IS NULL;

-- 再更新已有记录
UPDATE xhdps_postmeta AS pm_virtual
INNER JOIN xhdps_postmeta AS pm_downloadable 
    ON pm_virtual.post_id = pm_downloadable.post_id
INNER JOIN xhdps_posts AS p 
    ON pm_virtual.post_id = p.ID
SET pm_virtual.meta_value = 'no'
WHERE
    p.post_type = 'product'
    AND p.post_status = 'publish'
    AND pm_downloadable.meta_key = '_downloadable'
    AND pm_downloadable.meta_value = 'yes'
    AND pm_virtual.meta_key = '_virtual';

方法二：使用WooCommerce内置函数（更安全） #

这种方法依赖WooCommerce的API，避免直接操作数据库的风险，适合不熟悉SQL的用户。将以下代码添加到主题的functions.php文件或自定义插件中：

function update_downloadable_products_virtual_status() {
    // 查询所有发布状态的可下载产品
    $args = array(
        'post_type' => 'product',
        'post_status' => 'publish',
        'posts_per_page' => -1,
        'meta_query' => array(
            array(
                'key' => '_downloadable',
                'value' => 'yes',
                'compare' => '='
            )
        )
    );

    $products = new WP_Query($args);

    if ($products->have_posts()) {
        while ($products->have_posts()) {
            $products->the_post();
            $product = wc_get_product(get_the_ID());
            if ($product) {
                // 设置虚拟属性为否
                $product->set_virtual(false);
                // 保存产品更改
                $product->save();
            }
        }
        wp_reset_postdata();
    }
}

// 执行一次后请注释此行，避免重复运行
add_action('init', 'update_downloadable_products_virtual_status');

添加后访问网站前台或后台一次，代码会自动执行更新。完成后记得注释掉add_action那一行，防止每次页面加载都重复执行。

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