MySQL表中的PDO'ON DUPLICATE KEY UPDATE'rowCount（）返回两倍更新的记录

I'm working on a project where I upload a CSV and update a MySQL table. At the end of my sql insert statement I have an "on duplicate key update..." statement.

我正在开发一个上传CSV并更新MySQL表的项目。在我的sql insert语句结束时，我有一个“on duplicate key update ...”语句。

My problem is, PDO rowCount() seems to be returning 2x for updated rows. For example, when I upload the CSV the first time, I get a total of 100 rows (count of csv rows) and rowCount returns 100, which makes sense because I inserted 100 rows.

我的问题是，PDO rowCount（）似乎返回2x更新的行。例如，当我第一次上传CSV时，我总共得到100行（csv行的数量），而rowCount返回100，这是有意义的，因为我插入了100行。

However, if I upload the same file again, all 100 rows are updated (I update a unix timestamp), and rowCount returns 200. I assume this is because rowCount returns 2 for each update and 1 for an insert.

但是，如果我再次上传相同的文件，则更新所有100行（我更新unix时间戳），rowCount返回200.我认为这是因为rowCount为每次更新返回2，为插入返回1。

Are my assumptions correct? Has anyone run into this before and is there a solution that doesn't involve 100 separate insert statements? I would like to be able to display the total number of rows in the csv, the total new rows inserted, and the total rows updated.

我的假设是否正确？有没有人遇到过此问题，是否有一个不涉及100个单独的插入语句的解决方案？我希望能够显示csv中的总行数，插入的新行总数以及更新的总行数。

$sql = 'INSERT INTO projects (' . implode($fields,',') . ') VALUES';
    $rowCount = count($csvData);
    $tmp = array();
    for( $i = 0; $i < $rowCount; $i++ ){
        $placeholders = array();
        foreach( $fields as $key=>$val ){

            /* do some post processing for special characters */
            switch($val){
                case 'description':
                    $value = !empty($csvData[$i][$_POST[$val]]) ? $csvData[$i][$_POST[$val]] : NULL;
                    array_push($tmp,$value);
                break;
                case 'country':
                    $value = !empty( $csvData[$i][$_POST[$val]] ) ?  implode(' ',array_unique(explode(' ', $csvData[$i][$_POST[$val]]))) : NULL;
                    $value = str_replace(array(',','.','\''),'',$value);
                    array_push($tmp,$value);
                break;
                case 'add_unixtime':
                    array_push($tmp,time());
                break;
                case 'project_type':
                    array_push($tmp,strtolower($formData['project_type']));
                break;
                default:
                    $value = !empty($csvData[$i][$_POST[$val]]) ? str_replace(array(',','.','\''),'',$csvData[$i][$_POST[$val]]) : NULL;
                    array_push($tmp,$value);
                break;
            }
            array_push($placeholders,'?');
        }
        $sql .= ' (' . implode($placeholders,',') . '),';
    }
    /*  
        detect duplicate projects based on project_number & project_type
        mysql unique index created with (project_number + project_type)
        if duplicate found, update row
    */
    $sql = rtrim($sql,',');
    $sql .= 'ON DUPLICATE KEY UPDATE';
    foreach($fields as $key=>$val){
        $sql .= ' ' . $val . ' = VALUES(' . $val . '),';
    }
    $sql = rtrim($sql,',');

    /* update database */
    $query = $this->dbc->prepare($sql);
    if( $query->execute($tmp) ){
        $result = array('total_rows'=>$rowCount,'modified_rows'=>$query->rowCount());
    }

    /* return result */
    return $result;

Here is the query generated for a 3 row insert.

这是为3行插入生成的查询。

INSERT INTO projects (project_number, project_value, project_name, 
    address1, address2, city, state, zip, country, description,  
    project_type, add_unixtime ) 
VALUES (?,?,?,?,?,?,?,?,?,?,?,?), 
        (?,?,?,?,?,?,?,?,?,?,?,?), 
        (?,?,?,?,?,?,?,?,?,?,?,?)  
    ON DUPLICATE KEY UPDATE 
        project_number = VALUES(project_number), 
        project_value = VALUES(project_value), 
        project_name = VALUES(project_name), 
        address1 = VALUES(address1), address2 = VALUES(address2), 
        city = VALUES(city), state = VALUES(state), zip = VALUES(zip), 
        country = VALUES(country), description = VALUES(description), 
        project_type = VALUES(project_type), 
        add_unixtime = VALUES(add_unixtime);

1 个解决方案

#1

According to the MySQL manual:

根据MySQL手册：

With ON DUPLICATE KEY UPDATE, the affected-rows value per row is 1 if the row is inserted as a new row, 2 if an existing row is updated and 0 if the existing row is set to its current values.

使用ON DUPLICATE KEY UPDATE时，如果将行作为新行插入，则每行的受影响行值为1;如果更新现有行，则为每行受影响的行值为2;如果将现有行设置为其当前值，则为0。

http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html

#1