将数据库中的所有 WooCommerce 产品价格更新为 2 位小数 [英] Update all WooCommerce product prices to 2 decimals in database

问题描述

我的许多产品的价格都类似于 £3.4560 等.我需要将它们四舍五入并在数据库中精简为 2.

Many of my products have prices like £3.4560 etc. I need them to rounded and stripped to just 2 in the database.

WooCommerce 中 2 位小数的输出是不够的，并且无法与 Tax Toggle 插件配合使用.

The output of 2 decimal places in WooCommerce is not enough and fails to work with a Tax Toggle plugin.

是否有可以执行此操作的数据库查询?

Is there a database query that can do this?

我看过一些关于舍入和截断的内容，但不知道如何执行，因为我的知识很差.

I've seen some bits on Rounding and Truncate but not sure how to execute this as my knowledge is poor.

感谢任何帮助.

推荐答案

更新 2: (添加了一些代码来清除所有相关的产品瞬态缓存)

这里有一些代码会更新所有产品的价格(之前做一个数据库备份):

Here is some code that will update all the product prices (make a database backup before):

global $wpdb;
$postmeta = $wpdb->prefix . "postmeta";
$posts = $wpdb->prefix . "posts";

// 1. First query: Get all prices
$results = $wpdb->get_results( "
    SELECT $postmeta.*
    FROM $postmeta
    INNER JOIN $posts ON $postmeta.post_id = $posts.ID
    WHERE $posts.post_type LIKE '%product%'
    AND $postmeta.meta_key LIKE '%price%'
    AND $postmeta.meta_value != ''
    ORDER BY $postmeta.meta_id ASC
" );

// iterating through each price and update it
foreach($results as $result){
    $meta_id    = $result->meta_id;
    $post_id    = $result->post_id;
    $meta_key   = $result->meta_key;
    $meta_value = number_format( $result->meta_value, 2 );

    // 2. Udating prices query
    $wpdb->query( $wpdb->prepare( "
        UPDATE $postmeta
        SET meta_id = $meta_id, post_id = $post_id, meta_key = '$meta_key', meta_value = '$meta_value'
        WHERE $postmeta.meta_id = %d
    ", $meta_id ) );

     // 3. Clear all related product transient cached data (refresh prices)
     wc_delete_product_transients($post_id);
}

代码位于活动子主题(或主题)的 function.php 文件或任何插件文件中.

一旦保存，只需浏览您网站的任何页面，检查您的数据库 wp_postmeta 表，搜索 ‰price‰ LIKE meta_key.现在您可以删除此代码.

Once saved, just browse any page of your site, check your database wp_postmeta table searching for ‰price‰ LIKE meta_key. Now you can remove this code.

这篇关于将数据库中的所有 WooCommerce 产品价格更新为 2 位小数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！