wordpress 批量修改产品的价格

有需求因为整体的价格需要改动固定的值，听起来可以直接批量的处理，于是查边进行查看；

发现了其中的两个表在处理：

**

在wp_posts表中查到产品和他对应的变种的ID

select ID from wp_posts where post_type = 'product' and post_parent = 0 \G;  #获取产品的id

 然后根据主品的ID查出其自己变种的ID

select ID from wp_posts where post_type ='product_variation' and post_parent = 1165 \G;  #获取产品变种的id

 

 

 

一、wp_postmeta表:

select * from wp_postmeta where post_id in(1165,1171,1172,1173,1174) and meta_key like '%price%' \G;

注意这里的post_id, 其中包括主品的id和变种的id

 

然后进新update操作

ids='1165,1171,1172,1173,1174'

update wp_postmeta set meta_value = round(meta_value*0.88, 0) where post_id in($ids) and meta_key in('_price','_regular_price','_sale_price') and meta_value != '';

 

二、wp_wc_product_meta_lookup表:

 select * from wp_wc_product_meta_lookup where product_id in(1165,1171,1172,1173,1174) \G;

 

 

 

update 操作

update  wp_wc_product_meta_lookup set min_price = round(min_price*0.1, 2), max_price = round(max_price*0.1, 2) where product_id in(1165,1171,1172,1173,1174) \G;

 

三、wp_post表，这个表中没有找到可以修改价格的部分，但是如下的页面中的价格在更新后始终是更新之前的

 

 

 

最后发现在wp_post变种发现一个 option_name 为_transient_wc_var_prices_1165， 起到一个缓存的作用，左右将对应的找到删除即可

 

 

 

delete from wp_options where option_name like '%_transient_wc_var_prices_1165%';

 

全部shell执行代码：

#!/bin/bash

DB_INFO='/var/html/www.test.com/wp-config.php'
WEBSITE_DB=$(cat $DB_INFO| grep DB_NAME | awk '{print $3}'  | tr -d "'")
HOST_INFO=$(cat  $DB_INFO| grep DB_HOST | awk '{print $3}'  | tr -d "'")
HOST_PWSD=$(cat  $DB_INFO| grep DB_PASSWORD | awk '{print $3}'  | tr -d "'")

echo $WEBSITE_DB


#导出主产品ID
BaseSql='use '$WEBSITE_DB';'
GetPid="select ID from wp_posts where post_type = 'product' and post_parent = 0 ;";
OPTSQL=$BaseSql$GetPid
mysql -h$HOST_INFO -uroot -p$HOST_PWSD -e "${OPTSQL}" > /data/chage_product_price/pidtxt
sed -i '1d' /data/chage_product_price/pidtxt
pidArr=$(cat /data/chage_product_price/pidtxt)

#循环处理主品
for x in ${pidArr[@]}
do
        #获取对应的变种，并且以逗号隔开
      　 getVar="SET SESSION group_concat_max_len=102400; select GROUP_CONCAT(ID) from wp_posts where post_type ='product_variation' and post_parent = $x;"

        getVarSql=$BaseSql$getVar
        mysql -h$HOST_INFO -uroot -p$HOST_PWSD -e "${getVarSql}" > /data/chage_product_price/$x'_txt'
        sed -i '1d' /data/chage_product_price/$x'_txt'
        varArr=$(cat /data/chage_product_price/$x'_txt')
        ids=$x','$varArr

        echo $x'开始处理'
        #更新post_meta
        metaSql="update wp_postmeta set meta_value = round(meta_value*0.88, 0) where post_id in($ids) and meta_key in('_price','_regular_price','_sale_price') and meta_value != '';"
        
        #更新meta_lookup
        metalookSql="update  wp_wc_product_meta_lookup set min_price = round(min_price*0.88, 0), max_price = round(max_price*0.88, 0) where product_id in($ids);"
        
        #删除options数据
        optionSql="delete from wp_options where option_name like '%_transient_wc_var_prices_$x%';"

        runSql=$BaseSql$metaSql$metalookSql$optionSql
        mysql -h$HOST_INFO -uroot -p$HOST_PWSD -e "${runSql}"
        echo $x'处理完成'
        rm /data/chage_product_price/$x'_txt'
done