doubeng1278
2013-08-13 22:52
浏览 131
已采纳

如何将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?

图片转代码服务由CSDN问答提供 功能建议

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

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

  $ 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;”; 
   
 
 

数据库表结构:

  id | 名字|  lastname 
   
 
 

CSV文件内容..

  id | 名字|  lastname 
 
1 John Doe 
2 James Carry 
3 Luke Borrow 
   
 
 

正如预期的那样,'id'是主键。 有没有办法跳过违反主键约束的值?

  • 写回答
  • 好问题 提建议
  • 追加酬金
  • 关注问题
  • 收藏
  • 邀请回答

1条回答 默认 最新

  • douwulu2576 2013-08-13 22:58
    已采纳
    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.

    评论
    解决 无用
    打赏 举报

相关推荐 更多相似问题