MySQL 导入 CSV 以仅更新表中的一列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10253605/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Import CSV to Update only one column in table
提问by Ryan Kempt
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:
该表中大约有 800,000 个条目。我收到了一个新的 CSV 文件，其中更新了每种产品的所有数量，例如：
productA, 12
productB, 71
productC, 92
So there are approximately 750,000 updates (50,000 products had no change in quantity).
因此大约有 750,000 次更新（50,000 种产品的数量没有变化）。
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这个只更新的基于关闭的数量product（唯一的），但离开了sku，department独自一人，和其他领域？我知道如何在 PHP 中通过循环遍历 CSV 并为每一行执行更新来执行此操作，但这似乎效率低下。
回答by Ike Walker
You can use LOAD DATA INFILEto bulk load the 800,000 rows of data into a temporary table, then use multiple-table UPDATEsyntax to join your existing table to the temporary table and update the quantity values.
您可以使用LOAD DATA INFILE将 800,000 行数据批量加载到临时表中，然后使用多表UPDATE语法将现有表连接到临时表并更新数量值。
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;
回答by DocJones
I would load the update data into a seperate table UPDATE_TABLEand 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,1to the inner SELECT.
我现在手头没有 MySQL，所以我可以完美地检查语法，可能是您需要LIMIT 0,1在内部SELECT.
