如何在WooCommerce中批量将1万+商品价格从USD转INR（修改数据库）

Hey there! Let's walk through how to convert those USD prices to INR directly in your database—since you need to update the raw values instead of just relying on front-end display tricks, here's a solid, safe approach tailored for your 10k+ product scenario:

Step 1: Always Backup Your Data First #

This is non-negotiable—you don't want to risk losing original price data if something goes wrong!

• Use your database's native backup tool to export the product table, or create a temporary backup table:

  -- MySQL example: Create a backup table
  CREATE TABLE products_backup AS SELECT * FROM products;
  
  -- PostgreSQL example: Create a backup table
  CREATE TABLE products_backup AS TABLE products;
  

Step 2: Run the Currency Conversion Update #

Assuming your product table is named products, with regular_price and sales_price as the USD price fields, and the fixed exchange rate is 64.72 (1 USD = 64.72 INR):

Basic Update Statement (With 2 Decimal Places for Currency) #

UPDATE products
SET 
  regular_price = ROUND(regular_price * 64.72, 2),
  sales_price = ROUND(sales_price * 64.72, 2)
WHERE regular_price IS NOT NULL OR sales_price IS NOT NULL;

Key Notes: #

• ROUND(..., 2) ensures prices stay formatted to two decimal places, which aligns with standard INR currency practices.
• The WHERE clause skips rows with no price data (if your table allows NULL values for prices).

Step 3: Verify the Updated Data #

Don't skip this—double-check that conversions worked as expected:

• Manually spot-check a few rows: For example, a $10.99 regular price should convert to 10.99 * 64.72 = 711.2728, rounded to 711.27 INR.
• Run a validation query to cross-check:

  SELECT 
    regular_price AS inr_regular,
    sales_price AS inr_sales,
    ROUND(regular_price / 64.72, 2) AS original_usd_regular,
    ROUND(sales_price / 64.72, 2) AS original_usd_sales
  FROM products
  LIMIT 10;
  

Additional Considerations #

• If your prices are stored as integers (e.g., cents for USD, paise for INR):
  If regular_price stores 1099 (representing $10.99), adjust the query to account for the integer-to-decimal conversion:

  UPDATE products
  SET 
    regular_price = ROUND((regular_price / 100.0) * 64.72 * 100),
    sales_price = ROUND((sales_price / 100.0) * 64.72 * 100)
  WHERE regular_price IS NOT NULL OR sales_price IS NOT NULL;
  

• Batch updates for large tables: If your database struggles with updating 10k+ rows at once, split the update into chunks to avoid long lock times:

  -- MySQL example: Update 1000 rows at a time
  UPDATE products
  SET 
    regular_price = ROUND(regular_price * 64.72, 2),
    sales_price = ROUND(sales_price * 64.72, 2)
  WHERE (regular_price IS NOT NULL OR sales_price IS NOT NULL)
    AND id BETWEEN 1 AND 1000; -- Adjust the ID range for each batch
  

• Sync all channels: After updating the database, confirm all your sales channels (website, app, POS, etc.) are set to use INR as the default currency to avoid display inconsistencies.

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