php - 从 Woocommerce 的数据库中删除每 30% 的优惠券

Question

我需要删除我的 woocommerce 数据库中所有具有 30% 折扣的优惠券。我的数据库是默认的wordpress/woocommerce数据库(wp_)

我找到了 this thread显示了一些在数据库中选择的方法:

SELECT p.`ID`, 
   p.`post_title`   AS coupon_code, 
   p.`post_excerpt` AS coupon_description, 
   Max(CASE WHEN pm.meta_key = 'discount_type'      AND  p.`ID` = pm.`post_id` THEN pm.`meta_value` END) AS discount_type,          -- Discount type 
   Max(CASE WHEN pm.meta_key = 'coupon_amount'      AND  p.`ID` = pm.`post_id` THEN pm.`meta_value` END) AS coupon_amount,          -- Coupon amount 
   Max(CASE WHEN pm.meta_key = 'free_shipping'      AND  p.`ID` = pm.`post_id` THEN pm.`meta_value` END) AS free_shipping,          -- Allow free shipping 
   Max(CASE WHEN pm.meta_key = 'expiry_date'        AND  p.`ID` = pm.`post_id` THEN pm.`meta_value` END) AS expiry_date,                -- Coupon expiry date 
   Max(CASE WHEN pm.meta_key = 'minimum_amount'     AND  p.`ID` = pm.`post_id` THEN pm.`meta_value` END) AS minimum_amount,         -- Minimum spend 
   Max(CASE WHEN pm.meta_key = 'maximum_amount'     AND  p.`ID` = pm.`post_id` THEN pm.`meta_value` END) AS maximum_amount,         -- Maximum spend 
   Max(CASE WHEN pm.meta_key = 'individual_use'     AND  p.`ID` = pm.`post_id` THEN pm.`meta_value` END) AS individual_use,         -- Individual use only 
   Max(CASE WHEN pm.meta_key = 'exclude_sale_items' AND  p.`ID` = pm.`post_id` THEN pm.`meta_value` END) AS exclude_sale_items,         -- Exclude sale items 
   Max(CASE WHEN pm.meta_key = 'product_ids'    AND  p.`ID` = pm.`post_id` THEN pm.`meta_value` END) AS product_ids,                -- Products 
   Max(CASE WHEN pm.meta_key = 'exclude_product_ids'AND  p.`ID` = pm.`post_id` THEN pm.`meta_value` END) AS exclude_product_ids,        -- Exclude products 
   Max(CASE WHEN pm.meta_key = 'product_categories' AND  p.`ID` = pm.`post_id` THEN pm.`meta_value` END) AS product_categories,             -- Product categories 
   Max(CASE WHEN pm.meta_key = 'exclude_product_categories' AND  p.`ID` = pm.`post_id` THEN pm.`meta_value` END) AS exclude_product_categories,-- Exclude Product categories 
   Max(CASE WHEN pm.meta_key = 'customer_email'     AND  p.`ID` = pm.`post_id` THEN pm.`meta_value` END) AS customer_email,         -- Email restrictions 
   Max(CASE WHEN pm.meta_key = 'usage_limit'    AND  p.`ID` = pm.`post_id` THEN pm.`meta_value` END) AS usage_limit,                -- Usage limit per coupon 
   Max(CASE WHEN pm.meta_key = 'usage_limit_per_user'   AND  p.`ID` = pm.`post_id` THEN pm.`meta_value` END) AS usage_limit_per_user,   -- Usage limit per user 
   Max(CASE WHEN pm.meta_key = 'usage_count'    AND  p.`ID` = pm.`post_id` THEN pm.`meta_value` END) AS total_usaged                   -- Usage count 
FROM   `wp_posts` AS p 
   INNER JOIN `wp_postmeta` AS pm ON  p.`ID` = pm.`post_id` 
WHERE  p.`post_type` = 'shop_coupon' 
   AND p.`post_status` = 'publish' 
GROUP  BY p.`ID` 
ORDER  BY p.`ID` ASC;

但我还没有能够删除所有 30% 折扣券。

我的数据库中有 16k 优惠券，我无法从 wp-admin 手动执行此操作。

Answer 1

更新 2 - 从 phpMyAdmin 中尝试以下 SQL 查询(之前一定要备份):

DELETE a, b
FROM wp_posts AS a
JOIN wp_postmeta AS b
JOIN wp_postmeta AS c
WHERE b.post_id = a.ID AND c.post_id = a.ID
AND a.post_type = 'shop_coupon'
AND b.meta_key = 'discount_type' AND b.meta_value = 'percent'
AND c.meta_key = 'coupon_amount' AND c.meta_value = '30'

经过测试并有效。

This query will remove all coupons data from wp_posts and wp_postmeta tables for coupons that have a 30% discount

---

或者您可以运行一次以下 php 代码，它会为您提供已删除优惠券的数量:

global $wpdb;
$result = $wpdb->query( "
    DELETE a, b
    FROM wp_posts AS a
    JOIN wp_postmeta AS b
    JOIN wp_postmeta AS c
    WHERE b.post_id = a.ID AND c.post_id = a.ID
    AND a.post_type = 'shop_coupon'
    AND b.meta_key = 'discount_type' AND b.meta_value = 'percent'
    AND c.meta_key = 'coupon_amount' AND c.meta_value = '30'
" );

echo '<p>Coupons deleted: ' . $result / 2  . '</p>';

经过测试并有效。