LOAD DATA INFILE忽略重复的行，自动递增字段作为主键

问题描述：

我使用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,

id  |name   |age
--------------------
1   |aaaa   |22
2   |bbbb   |21
3   |bbaa   |20
4   |abbb   |22
5   |aacc   |22

其中id是自动递增值.我拥有的csv文件包含以下数据，

Where id is auto increment value. an the csv file i have contains data as follows,

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

答

首先，您必须设置列''名称"作为唯一键
使用关键字REPLACE或IGNORE

First you have to set column ''name'' to be UNIQUE KEY
Use keyword REPLACE or IGNORE

LOAD DATA INFILE 'member.csv'

IGNORE

        INTO TABLE tbl_member
        FIELDS TERMINATED BY ','
               ENCLOSED BY '"'
               ESCAPED BY '"'
               LINES TERMINATED BY '\n'
        (name, age);

报价:

REPLACE和IGNORE关键字控制输入行的处理，这些输入行复制唯一键值上的现有行:

如果指定REPLACE，则输入行将替换现有行.换句话说，主键或唯一索引的值与现有行的值相同的行.请参见第13.2.7节"REPLACE语法".

如果指定IGNORE，将跳过在唯一键值上复制现有行的输入行.如果您未指定任何一个选项，则行为取决于是否指定了LOCAL关键字.如果没有LOCAL，则在找到重复的键值时会发生错误，而文本文件的其余部分将被忽略.使用LOCAL时，默认行为与指定了IGNORE相同.这是因为服务器无法在操作过程中停止文件的传输.

The REPLACE and IGNORE keywords control handling of input rows that duplicate existing rows on unique key values:

If you specify REPLACE, input rows replace existing rows. In other words, rows that have the same value for a primary key or unique index as an existing row. See Section 13.2.7, "REPLACE Syntax".

If you specify IGNORE, input rows that duplicate an existing row on a unique key value are skipped. If you do not specify either option, the behavior depends on whether the LOCAL keyword is specified. Without LOCAL, an error occurs when a duplicate key value is found, and the rest of the text file is ignored. With LOCAL, the default behavior is the same as if IGNORE is specified; this is because the server has no way to stop transmission of the file in the middle of the operation.

http://dev.mysql.com/doc/refman/5.1/en/load- data.html [^ ]

http://dev.mysql.com/doc/refman/5.1/en/load-data.html[^]

LOAD DATA INFILE忽略重复的行，自动递增字段作为主键

相关推荐