INSERT SET：如果行不存在则为id，UPDATE WHERE：否则为id

I have a code which creates a row in database with a specified id if it does not exist and updates that row otherwise. It is a little bit slow (it checks if a row exists and then inserts/updates it) and I want to optimize it. How can I consolidate the whole thing into one SQL query? I think I should use INSERT ... ON DUPLICATE KEY UPDATE but I do not understand how it works. Thank you.

我有一个代码,如果它不存在,则在具有指定id的数据库中创建一行,否则更新该行。它有点慢(它检查一行是否存在,然后插入/更新它),我想优化它。如何将整个事物合并到一个SQL查询中?我想我应该使用INSERT ... ON DUPLICATE KEY UPDATE但我不明白它是如何工作的。谢谢。

$objectExists = true;
try {
    $object = new Object();
    $object->loadFromDatabaseById($this->id, $dbh); // tries to load object from database, throws
                                         // ExceptionNotFound if object with specified id does not exist
}
catch(ExceptionNotFound $e) {
    $objectExists = false;
}

if($objectExists)
    $sth = $dbh->prepare(
        'UPDATE objects ' .
        'SET property1 = :property1, ' .
            'property2 = :property2, ' .
            'property3 = :property3 ' .
        'WHERE id = :id'
    );
else
    $sth = $dbh->prepare(
        'INSERT INTO objects ' .
        'SET id = :id, ' .
            'property1 = :property1, ' .
            'property2 = :property2, ' .
            'property3 = :property3'
    );
$sth->bindParam(':id', $this->id, \PDO::PARAM_INT);
$sth->bindParam(':property1', $this->property1, \PDO::PARAM_STR);
$sth->bindParam(':property2', $this->property2, \PDO::PARAM_STR);
$sth->bindParam(':property3', $this->property3, \PDO::PARAM_INT);
$sth->execute();
$this->id = (int)$this->id;

2 个解决方案

#1

MySQL has a REPLACE statement that does exactly what you’re looking for:

MySQL有一个REPLACE语句,可以完全满足您的需求:

REPLACE works exactly like INSERT, except that if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted.

REPLACE与INSERT的工作方式完全相同,只是如果表中的旧行与PRIMARY KEY或UNIQUE索引的新行具有相同的值,则在插入新行之前删除旧行。

An alternative without deletion would be the INSERT ... ON DUPLICATE KEY UPDATE statement:

没有删除的替代方案是INSERT ... ON DUPLICATE KEY UPDATE语句:

If you specify ON DUPLICATE KEY UPDATE, and a row is inserted that would cause a duplicate value in a UNIQUE index or PRIMARY KEY, MySQL performs an UPDATE of the old row.

如果指定ON DUPLICATE KEY UPDATE,并且插入的行将导致UNIQUE索引或PRIMARY KEY中出现重复值,则MySQL将执行旧行的UPDATE。

#2

As long as id is either a Primary Key or Unique Key/Index, you can shorten it to something like -

只要id是主键或唯一键/索引,您可以将其缩短为类似 -

$sth = $dbh->prepare(
    'INSERT INTO objects ( id, property1, property2, property3) ' .
    'VALUES ( :id,:property1,:property2,:property3) '. 
    'ON DUPLICATE KEY UPDATE ' .
        'property1 = :property1, ' .
        'property2 = :property2, ' .
        'property3 = :property3 '
);
$sth->bindParam(':id', $this->id, \PDO::PARAM_INT);
$sth->bindParam(':property1', $this->property1, \PDO::PARAM_STR);
$sth->bindParam(':property2', $this->property2, \PDO::PARAM_STR);
$sth->bindParam(':property3', $this->property3, \PDO::PARAM_INT);
$sth->execute();
$this->id = (int)$this->id;

#1