mysql infile ignore_MYSQL LOAD DATA INFILE忽略重復行（自動增量作為主鍵）

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 :(

請幫助我完成這項任務..我將非常感謝..我試過很多鏈接，但沒有幫助:(

3 个解决方案

#1

31

Create a UNIQUE index on the age column, then:

在年齡列上創建一個UNIQUE索引，然后：

LOAD DATA INFILE 'member.csv'

IGNORE INTO TABLE tbl_member

FIELDS TERMINATED BY ','

ENCLOSED BY '"'

ESCAPED BY '"'

LINES TERMINATED BY '\n'

(name, age);

#2

5

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.)

)

#3

0

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)