导入CSV以仅更新表中的一列

I have a table that looks like this:

我有一张这样的桌子:

products
--------
id, product, sku, department, quantity

There are approximately 800,000 entries in this table. I have received a new CSV file that updates all of the quantities of each product, for example:

该表中大约有80万条目。我收到了一个新的CSV文件，它更新了每个产品的所有数量，例如:

productA, 12
productB, 71
productC, 92

So there are approximately 750,000 updates (50,000 products had no change in quantity).

因此，大约有75万次更新(5万件产品的数量没有变化)。

My question is, how do I import this CSV to update only the quantity based off of the product (unique) but leave the sku, department, and other fields alone? I know how to do this in PHP by looping through the CSV and executing an update for each single line but this seems inefficient.

我的问题是，如何导入这个CSV以只更新基于产品的数量(惟一的)，而不涉及sku、部门和其他字段?我知道如何在PHP中这样做，通过循环使用CSV并为每一行执行更新，但这似乎是低效的。

2 个解决方案

#1

108

You can use LOAD DATA INFILE to bulk load the 800,000 rows of data into a temporary table, then use multiple-table UPDATE syntax to join your existing table to the temporary table and update the quantity values.

可以使用LOAD DATA INFILE将80万行数据批量加载到临时表中，然后使用多表更新语法将现有表连接到临时表，并更新数量值。

For example:

例如:

CREATE TEMPORARY TABLE your_temp_table LIKE your_table;

LOAD DATA INFILE '/tmp/your_file.csv'
INTO TABLE your_temp_table
FIELDS TERMINATED BY ','
(id, product, sku, department, quantity); 

UPDATE your_table
INNER JOIN your_temp_table on your_temp_table.id = your_table.id
SET your_table.quantity = your_temp_table.quantity;

DROP TEMPORARY TABLE your_temp_table;

#2

I would load the update data into a seperate table UPDATE_TABLE and perform an update within MySQL using:

我将把更新数据加载到一个独立的表UPDATE_TABLE中，并在MySQL内执行更新:

UPDATE PRODUCTS P SET P.QUANTITY=(
    SELECT UPDATE_QUANTITY
    FROM UPDATE_TABLE
    WHERE UPDATE_PRODUCT=P.PRODUCT
)

I dont have a MySQL at hand right now, so I can check the syntax perfectly, it might be you need to add a LIMIT 0,1 to the inner SELECT.

我现在手头没有MySQL，所以我可以很好地检查语法，您可能需要在内部选择中添加一个LIMIT 0,1。

#1

108