MYSQL LOAD DATA INFILE 忽略重复行（自动增量为主键）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12891337/
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
MYSQL LOAD DATA INFILE ignore duplicate rows (autoincrement as primary key)
提问by Hasitha Shan
I ran into some trouble using LOAD DATA INFILE command as i wanted to ignore the lines that was already in the data base..say if i have a table with data as follows,
我在使用 LOAD DATA INFILE 命令时遇到了一些麻烦，因为我想忽略数据库中已经存在的行..如果我有一个包含如下数据的表，
id |name |age
--------------------
1 |aaaa |22
2 |bbbb |21
3 |bbaa |20
4 |abbb |22
5 |aacc |22
Where id is auto increment value. an the csv file i have contains data as follows,
其中 id 是自动增量值。我拥有的 csv 文件包含如下数据，
"cccc","14"
"ssee","33"
"dddd","22"
"aaaa","22"
"abbb","22"
"dhgg","34"
"aacc","22"
I want to ignore the rows,
我想忽略行，
"aaaa","22"
"abbb","22"
"aacc","22"
and upload the rest to the table. and the query i have yet which uploads everything to the table is as follows,
并将其余的上传到表中。我还没有将所有内容上传到表的查询如下，
LOAD DATA INFILE 'member.csv'
INTO TABLE tbl_member
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
ESCAPED BY '"'
LINES TERMINATED BY '\n'
(name, age);
PLEASE help me on this task.. It will be much appreciated..i tried many links but did not help :(
请帮助我完成这项任务..将不胜感激..我尝试了很多链接，但没有帮助:(
回答by Omar
Create a UNIQUE index on the age column, then:
在 age 列上创建一个 UNIQUE 索引，然后：
LOAD DATA INFILE 'member.csv'
IGNORE INTO TABLE tbl_member
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
ESCAPED BY '"'
LINES TERMINATED BY '\n'
(name, age);
回答by david strachan
One approach is to use a temporary table. Upload to this and use SQL to update tbl_member from temp table.
一种方法是使用临时表。上传到这里并使用 SQL 从临时表更新 tbl_member。
INSERT INTO tbl_member
SELECT Field1,Field2,Field3,...
FROM temp_table
WHERE NOT EXISTS(SELECT *
FROM tbl_member
WHERE (temp_table.Field1=tbl_member.Field1 and
temp_table.Field2=tbl_member.Field2...etc.)
)
回答by RobinKay
You can create a unique index on multiple columns. LOAD DATA won't insert rows that match existing rows on all of those columns.
您可以在多个列上创建唯一索引。LOAD DATA 不会插入与所有这些列上的现有行匹配的行。
e.g. ALTER TABLE tbl_member ADD UNIQUE unique_index(name,age)
例如 ALTER TABLE tbl_member ADD UNIQUE unique_index(name,age)
