如何将csv文件内容加载到mysql数据库并避免重复输入？

问题描述：

What I was trying to do is to load a csv file into the database. But I am having a problem because of duplicate entry.

I am using PHP script to load the file.

$sql = "LOAD DATA INFILE '" . $target_path;
        $sql.= "' INTO TABLE dummy ";   
        $sql.= "COLUMNS TERMINATED BY ','";
        $sql.= "OPTIONALLY ENCLOSED BY '\"'";
        $sql.= "ESCAPED BY '\"'";
        $sql.= "LINES TERMINATED BY '
'";
        $sql.= "IGNORE 1 LINES;";

Database Table Structure:

id | firstname | lastname

CSV File content..

id | firstname | lastname

1  John Doe
2  James Carry
3  Luke Borrow

As expected 'id' is the primary key. Is there a way to skip values that violates primary key constraint on the fly?

我要做的是将csv文件加载到数据库中。但由于重复输入，我遇到了问题。 p>

我正在使用PHP脚本加载文件。 p>

  $ sql =“  LOAD DATA INFILE'“。  $ target_path; 
 $ sql。=“'INTO TABLE dummy”;  
 $ sql。=“COLUMNS TERMINATED BY'，'”; 
 $ sql。=“OPTIONALLY ENCLOSED by'\”'“; 
 $ sql。=”ESCAPED BY'\“'”; 
 $ sql  。=“由'
'终止的行;”
 $ sql。=“IGNORE 1 LINES;”; 
  code>  pre> 
 
 数据库表结构： p>  
 
 
  id | 名字|  lastname 
  code>  pre> 
 
  CSV文件内容..  p> 
 
 
  id | 名字|  lastname 
 
1 John Doe 
2 James Carry 
3 Luke Borrow 
  code>  pre> 
 
 正如预期的那样，'id'是主键。 有没有办法跳过违反主键约束的值？ p> 
  div>

答

LOAD DATA INFILE 'somefile' IGNORE

From the manual:

If you specify IGNORE, input rows that duplicate an existing row on a unique key value are skipped.

如何将csv文件内容加载到mysql数据库并避免重复输入？

相关推荐