mysql - 当我使用 perl 将 infile 加载到 mysql 数据库中时，为什么返回的计数不正确？

Question

首先，春节前来自北京的问候——“过年好!”。

我有一个perl脚本来将数据文件加载到数据库中。脚本运行良好，但返回的信息总是不正确。

问题:

代码是这样的:

     $sql_load="LOAD DATA LOCAL INFILE $FileName REPLACE INTO TABLE ".$TableName ;
     $sth=$dbi->do($sql_load) ;#or die "SQL Error: $DBI::errstr\n";
     if(DBI->err){
         print "      after connect, SQL Error:$DBI::errstr\n";
     }else{
         print "      $sth . Records Uploaded \n";
     }

输出是这样的:

     7 . Records Uploaded.

但实际上:

wc -l $FileName 为 6(包括第一行，即字段名称行)，数据线数为5。

返回的计数总是等于正确的数字加 2。

问题:
我已经多次搜索这个问题但没有运气，也没有从 mysql 文档中获得有用的信息。如何获得正确的计数？加 2 是什么意思？

Answer 1

这就是the manual says :

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.8, “REPLACE Syntax”.

REPLACE 命令以在匹配时影响 2 行而闻名，因为您得到了删除和插入:

The REPLACE statement returns a count to indicate the number of rows affected. This is the sum of the rows deleted and inserted. If the count is 1 for a single-row REPLACE, a row was inserted and no rows were deleted. If the count is greater than 1, one or more old rows were deleted before the new row was inserted. It is possible for a single row to replace more than one old row if the table contains multiple unique indexes and the new row duplicates values for different old rows in different unique indexes.

所以我怀疑您的文件中有 5 行，其中 2 行已经存在于数据库中。