MYSQL LOAD DATA INFILE忽略重复行（autoincrement as primary key） [英] MYSQL LOAD DATA INFILE ignore duplicate rows (autoincrement as primary key)

问题描述

我遇到了一些麻烦使用LOAD DATA INFILE命令，因为我想忽略已经在数据库中的行...如果我有一个数据表如下，

  id | name | age 
 -------------------- 
 1 | aaaa | 22 
 2 | bbbb | 21 
 3 | bbaa | 20 
 4 | abbb | 22 
 5 | aacc | 22 
  / pre> 
 
 
其中id是自动递增值。 csv文件包含如下数据：
 cccc，14
ssee 33
dddd，22
aaaa，22
abbb，22
dhgg，34
aacc，22
  
我想忽略这些行，
 aaaa，22
abbb，22
aacc，22
  
并将其余部分上传到表格。和查询我已经上传的一切到表如下，
  LOAD DATA INFILE'member.csv'
 INTO TABLE tbl_member 
由'，'
终止的字段
 ESCAPED BY''
由'\\\
'终止的行
姓名年龄）; 
  
请帮助我完成这项任务。不帮助：（
解决方案
在年龄列上创建UNIQUE索引，然后：
  LOAD DATA INFILE'member.csv'
 IGNORE INTO TABLE tbl_member 
终止于'，'
附加'''
 ESCAPED BY''
由'\\\
'终止的行
（name，age）; 
  
 
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
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 :(
解决方案
Create a UNIQUE index on the age column, then:
LOAD DATA INFILE 'member.csv'
IGNORE INTO TABLE tbl_member
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
ESCAPED BY '"'
LINES TERMINATED BY '\n'
(name, age);


                        
这篇关于MYSQL LOAD DATA INFILE忽略重复行（autoincrement as primary key）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！