MySQL 导入 CSV 以更新表中的行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21495600/
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 rows in table
提问by Irfan
There are approximately 26K products (posts) and each product has meta values like this:
大约有 26K 个产品（帖子），每个产品都有这样的元值：
The post_id column is the product id in db and the _sku (meta_key) is the unique id for each product.
post_id 列是 db 中的产品 ID，_sku (meta_key) 是每个产品的唯一 ID。
I've received a new CSV file that updates all of the values (meta_value) for _sale_price (meta_key) of each product. The CSV file looks like:
SKU, Sale Price
我收到了一个新的 CSV 文件，该文件更新了每个产品的 _sale_price (meta_key) 的所有值 (meta_value)。CSV 文件如下所示：
SKU、销售价格
How do I import this CSV to update only the _sale_price row based on the post_id (product id) & _sku value?
如何导入此 CSV 以仅根据 post_id（产品 ID）和 _sku 值更新 _sale_price 行？
Output Example:
输出示例：
I know how to do this in PHP by looping through the CSV and selecting & executing an update for each single product but this seems inefficient.
我知道如何在 PHP 中通过循环遍历 CSV 并为每个单个产品选择和执行更新来执行此操作，但这似乎效率低下。
Preferably with phpMyAdmin and by using LOAD DATA INFILE.
最好使用 phpMyAdmin 并使用 LOAD DATA INFILE。
回答by rAndom69
You can use temporary table to hold the update data and then run single update statement.
您可以使用临时表来保存更新数据，然后运行单个更新语句。
CREATE TEMPORARY TABLE temp_update_table (meta_key, meta_value)
LOAD DATA INFILE 'your_csv_pathname'
INTO TABLE temp_update_table FIELDS TERMINATED BY ';' (meta_key, meta_value);
UPDATE "table"
INNER JOIN temp_update_table on temp_update_table.meta_key = "table".meta_key
SET "table".meta_value = temp_update_table.meta_value;
DROP TEMPORARY TABLE temp_update_table;
回答by Felix Labayen
If product_id is the unique column of that table, you can do that using CSV:
如果 product_id 是该表的唯一列，则可以使用 CSV 执行此操作：
Have a CSV file of those you want to import with their unique ID. CSV file must be in same order of the table column, put all your columns and no column name
Then in phpMyAdmin, go to the table of database, click import
Select CSV in the drop-down of Format field
Make sure "Update data when duplicate keys found on import (add ON DUPLICATE KEY UPDATE)" is checked.
有一个 CSV 文件，其中包含要导入的文件及其唯一 ID。CSV 文件必须与表格列的顺序相同，放置所有列，不要列名
然后在phpMyAdmin中，进入数据库表，点击导入
在格式字段的下拉列表中选择 CSV
确保选中“在导入时发现重复键时更新数据（添加 DUPLICATE KEY UPDATE）”。
回答by David H. Bennett
You can import the new data into another table (table2). Then update your primary table (table1) using a update with a sub-select:
您可以将新数据导入到另一个表（表 2）中。然后使用带有子选择的更新更新您的主表（table1）：
UPDATE table1 t1 set
sale_price = (select meta_value from table2 t2 where t2.post_id = t1.product_id)
WHERE
(select count(*) from table2 t2 where t1.product_id = t2.post_id) > 0
This is obviously a simplification and you will most likely need to constrain your query a little further.
这显然是一种简化，您很可能需要进一步限制您的查询。
Make sure to backup your full database before attempting. I recommend you work on a non-production database until the process works flawlessly.
确保在尝试之前备份完整的数据库。我建议您在非生产数据库上工作，直到该过程完美无缺。
