dopuzf0898
dopuzf0898
2016-07-28 07:28

MySQL PDO - ON DUPLICATE KEY UPDATE在没有更改的情况下返回false吗?

已采纳

I'm saving data from an API, and running a ON DUPLICATE KEY UPDATE. The thing is that it saved without errors on the first go around, but on consecutive runs it returns false, with the following errorInfo()

Array ( [0] => 00000 [1] => [2] => ) 

I ran the SQL manually in phpMyAdmin, and it works (0 rows inserted), does this return false when no changes are made?

My apologies for horrible code here, the PDO instance is saved as $this->db (I'm saving some JSON in this function, so the usual prepare escaping was erroring on the :, hence the "make-due" solution under)

public function update($table, $valuePairs)
{
    $columns = '';
    $values = '';
    $updates = '';
    foreach ($valuePairs as $column => $value)
    {
        if($value !== null and $value !== '') {
            $columns .= '`'.$column.'`, ';
            $values .= $this->db->quote($value).', ';
            $updates .= '`'.$column.'` = VALUES(`'.$column.'`), ';
        }
    }
    $columns = rtrim($columns, ', ');
    $values = rtrim($values, ', ');
    $updates = rtrim($updates, ', ');

    $sql = 'INSERT INTO '.$table.' ('.$columns.')
            VALUES ('.$values.')
            ON DUPLICATE KEY UPDATE '.$updates;

    $result = $this->db->exec($sql);
    if(!$result)
    {
        print_r($this->db->errorInfo());
        echo '<br><br>';
    }

    return $result;
}
  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答

2条回答

  • dougaimian1143 dougaimian1143 5年前

    Let's look carefully at PDO::exec() manual page:

    Return Values

    PDO::exec() returns the number of rows that were modified or deleted by the SQL statement you issued. If no rows were affected, PDO::exec() returns 0.

    Warning

    This function may return Boolean FALSE, but may also return a non-Boolean value which evaluates to FALSE. Please read the section on Booleans for more information. Use the === operator for testing the return value of this function.

    But your code does not make any distinction between zero and false:

    if(!$result)
    {
        print_r($this->db->errorInfo());
        echo '<br><br>';
    }
    

    You probably want this:

    if ($result===false)
    {
        print_r($this->db->errorInfo());
        echo '<br><br>';
    }
    

    Getting zero rows updated when you run the statement twice is the expected behaviour because MySQL won't update a row if values do not change.

    I particularly prefer to forget about error handling and configure PDO to just throw exceptions.

    点赞 评论 复制链接分享
  • dongzuoyue6556 dongzuoyue6556 5年前

    PDO::query and PDO::exec return false only in case of failure.
    Inserting no row and performing a successful update instead is not a failure.

    An SQLstate of '00000' means no error.

    点赞 评论 复制链接分享

相关推荐