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

Question

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;
}

Answer 1

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.