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

我有一张看起来像这样的表格:

产品--------id、产品、sku、部门、数量

此表中大约有 800,000 个条目.我收到了一个新的 CSV 文件，其中更新了每种产品的所有数量，例如:

productA, 12产品B, 71产品C, 92

因此大约有 750,000 次更新(50,000 件产品的数量没有变化).

我的问题是，如何导入此 CSV 以仅更新基于 product(唯一)的数量，但保留 sku、department 和其他字段?我知道如何在 PHP 中通过循环遍历 CSV 并为每一行执行更新来执行此操作，但这似乎效率低下.

解决方案

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

例如:

创建临时表 your_temp_table LIKE your_table;加载数据文件'/tmp/your_file.csv'INTO TABLE your_temp_table以 ',' 结尾的字段(id、产品、sku、部门、数量)；更新 your_table在 your_temp_table.id = your_table.id 上 INNER JOIN your_temp_table设置 your_table.quantity = your_temp_table.quantity;删除临时表 your_temp_table;

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:

productA, 12
productB, 71
productC, 92

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

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.

解决方案

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.

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;