如果存在更新，否则插入CSV数据MySQL [英] if exists update else insert csv data MySQL

问题描述

我正在用从第三方来源提取的csv文件填充MySQL表。每天更新csv，如果已经存在a，b和c列，我想更新MySQL表中的行，否则插入该行。我使用加载数据infile进行初始加载，但我想针对每天的csv拉动进行更新。我熟悉INSERT ... ON DUPLICATE，但是在csv导入的上下文中却不熟悉。任何有关如何将INSERT LOAD DATA LOCAL INFILE嵌套在INSERT ... ON DUPLICATE a，b，c中的建议-甚至是最好的方法，也将不胜感激。

I am populating a MySQL table with a csv file pulled from a third party source. Every day the csv is updated and I want to update rows in MySQL table if an occurrence of column a, b and c already exists, else insert the row. I used load data infile for the initial load but I want to update against a daily csv pull. I am familiar with INSERT...ON DUPLICATE, but not in the context of a csv import. Any advice on how to nest LOAD DATA LOCAL INFILE within INSERT...ON DUPLICATE a, b, c - or if that is even the best approach would be greatly appreciated.

LOAD DATA LOCAL INFILE 'C:\\Users\\nick\\Desktop\\folder\\file.csv' 
INTO TABLE db.tbl
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"' 
LINES TERMINATED BY '\r\n' 
IGNORE 1 lines;     

推荐答案

I发现执行此操作的最佳方法是使用标准LOAD DATA LOCAL INFILE插入文件

I found that the best way to do this is to insert the file with the standard LOAD DATA LOCAL INFILE

LOAD DATA LOCAL INFILE 
INTO TABLE db.table
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"' 
LINES TERMINATED BY '\r\n' 
IGNORE 1 lines;

并使用以下内容删除重复项。请注意，以下命令通过将db.table定义为a和b来将其与自身进行比较。

And use the following to delete duplicates. Note that the below command is comparing db.table to itself by defining it as both a and b.

delete a.* from db.table a, db.table b
where a.id > b.id
and a.field1 = b.field1
and a.field2 = b.field2
and a.field3 = b.field3; 

要使用此方法，必须将id字段设为自动增量主键。然后删除在field1 AND field2 AND field3上包含重复项的行。在这种情况下，它将删除具有两个自动增量ID中较高者的行，如果我们使用<而不是>。

To use this method it is essential that the id field is an auto incremental primary key.The above command then deletes rows that contain duplication on field1 AND field2 AND field3. In this case it will delete the row with the higher of the two auto incremental ids, this works just as well if we were to use < instead of >.

这篇关于如果存在更新，否则插入CSV数据MySQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！