MySQL 学说：关于重复密钥更新

Doctrine: ON DUPLICATE KEY UPDATE

提问by Leonardo

How can I write an INSERTdoctrine query with option ON DUPLICATE KEY UPDATE?

如何INSERT使用 option编写学说查询ON DUPLICATE KEY UPDATE？

采纳答案by DrColossos

The problem is that this is a MySQL specific problem so it will not be directly covered by Doctrine.

问题是这是一个 MySQL 特定的问题，所以它不会被 Doctrine 直接覆盖。

As a comment mentioned, you would need to write a RawSQL Query for this. This would be the easiest way.

正如评论中提到的，您需要为此编写一个 RawSQL 查询。这将是最简单的方法。

If you want it more sophisticated and truely DB independent, look into Eventsand it's possibilities. Before the actual query is executed, you can check for an existence and if it exists, act accordingly.

如果您希望它更复杂且真正独立于数据库，请查看事件及其可能性。在执行实际查询之前，您可以检查是否存在，如果存在，则采取相应措施。

An ORM/PHP independent way is to write a stored procedure/trigger that handles this problem database side.

ORM/PHP 独立的方法是编写一个存储过程/触发器来处理这个问题数据库端。

回答by artemiuz

for Symfony 2use raw sql:

对于Symfony 2，使用原始 sql：

$em->getConnection()->prepare("INSERT INTO table SET 
    some_fields = "some data", created_at = NOW() 
    ON DUPLICATE KEY UPDATE
    some_fields = "some data", updated_at = NOW()
")->execute();

回答by flu

You can't. It's not supported by Doctrine right now.

你不能。Doctrine 目前不支持它。

What you could do is to imitate what MySQL does by checking if the entity exists and update/create it accordingly:

您可以做的是通过检查实体是否存在并相应地更新/创建它来模仿 MySQL 的功能：

$em = $this->getEntityManager();

// Prevent race conditions by putting this into a transaction.
$em->transactional(function($em) use ($content, $type) {
  // Use pessimistic write lock when selecting.
  $counter = $em->createQueryBuilder()
    ->select('MyBundle:MyCounter', 'c')
    ->where('c.content = :content', 'c.type = :type')
    ->setParameters(['content' => $content, 'type' => $type])
    ->setLockMode(\Doctrine\DBAL\LockMode::PESSIMISTIC_WRITE);
    ->getQuery()
    ->getResult()
  ;

  // Update if existing.
  if ($counter) {
    $counter->increase();
  } else {
    // Create otherwise.
    $newCounter = new Counter($content, $type, 1);
    $em->persist($newCounter);
  }
});

If the record exists PESSIMISTIC_WRITEmakes sure that it's not updated by anyone (e.g., other threads) while we're updating it.

如果记录存在PESSIMISTIC_WRITE，请确保在我们更新它时它不会被任何人（例如，其他线程）更新。

Although you need to check for the entity's existence on every update, it's a simple reproduction of "update if existing and create if not".

尽管您需要在每次更新时检查实体是否存在，但它是“如果存在则更新，如果不存在则创建”的简单再现。

As pointed out in the comments this does not prevent a race condition if the record doesn't exist: If a row with the same key(s) gets inserted between the select and the insert you're running into a duplicate key exception.

正如评论中指出的那样，如果记录不存在，这并不能防止竞争条件：如果在选择和插入之间插入具有相同键的行，您将遇到重复键异常。

But given the constraints that this needs to be DB independent and thus written using Doctrine and not using native SQL it may help in some cases.

但是考虑到这需要独立于数据库并因此使用 Doctrine 而不是使用本机 SQL 编写的限制，它在某些情况下可能会有所帮助。

References:

参考：

• Database Administrators: Putting a Select statement in a transaction
• MySQL Reference Manual: Locking Reads (SELECT ... FOR UPDATE)
• Doctrine 2 ORM documentation: Transactions and Concurrency
• Stackoverflow: Doctrine2 ORM select for update

• 数据库管理员：将 Select 语句放入事务中
• MySQL 参考手册：锁定读取（SELECT ... FOR UPDATE）
• Doctrine 2 ORM 文档：事务和并发
• Stackoverflow：Doctrine2 ORM 选择更新

回答by Emmet O'Grady

I had the same problem and after investigating a bit it looks like Doctrine doesn't do it. My solution was to do a findBybefore my insert to see if any records exist with the unique fields. If this returns an entity then I update that entity and persist it instead of creating a new entity to persist.

我遇到了同样的问题，经过一番调查后，看起来 Doctrine 没有这样做。我的解决方案是在插入之前执行findBy以查看是否存在具有唯一字段的任何记录。如果这返回一个实体，那么我更新该实体并将其持久化，而不是创建一个新实体来持久化。

If you are concerned about performance then this is not ideal as we are doing a select before every insert. However since Doctrine is database agnostic it is the only alternative to locking yourself to MySQL. It's one of those tradeoffs: do you want performance or portability.

如果您担心性能，那么这并不理想，因为我们在每次插入之前都会进行选择。但是，由于 Doctrine 与数据库无关，因此它是将自己锁定到 MySQL 的唯一选择。这是权衡之一：您想要性能还是便携性。

回答by Marcin ?urek

You can use function like this to build and execute raw sql:

您可以使用这样的函数来构建和执行原始 sql：

 /**
 * 
 * insertWithDuplicate('table_name', array('unique_field_name' => 'field_value', 'field_name' => 'field_value'), array('field_name' => 'field_value'))
 * 
 * @param string $tableName
 * @param array $insertData 
 * @param array $updateData
 * 
 * @return bolean
 */
public function insertWithDuplicate($tableName, $insertData, $updateData) {
    $columnPart = '';
    $valuePart = '';
    $columnAndValue = '';
    foreach ($insertData as $key => $value) {
        $value = str_replace(array('"', "'"), array('\"', "\'"), $value);
        $columnPart .= "`" . $key . "`" . ',';
        is_numeric($value) ? $valuePart .= $value . ',' : $valuePart .= "'" . $value . "'" . ',';
    }
    foreach ($updateData as $key => $value) {
        $value = str_replace(array('"', "'"), array('\"', "\'"), $value);
        is_numeric($value) ? $columnAndValue .= $key . ' = ' . $value . ',' : $columnAndValue .= "`" . $key . "`" . ' = ' . "'" . $value . "'" . ',';
    }
    $_columnPart = substr($columnPart, 0, strlen($columnPart) - 1);
    $_valuePart = substr($valuePart, 0, strlen($valuePart) - 1);
    $_columnAndValue = substr($columnAndValue, 0, strlen($columnAndValue) - 1);
    $query = "INSERT INTO " . $tableName .
            " (" . $_columnPart . ") "
            . "VALUES" .
            " (" . $_valuePart . ") "
            . "ON DUPLICATE KEY UPDATE " .
            $_columnAndValue;
    return $this->entityManager->getConnection()
                    ->prepare($query)->execute();
}

回答by iJanki

I created a doctrine dbal wrapper to do that. It can be used with DoctrineBundle with the dbal wrapper_class option.

我创建了一个学说 dbal 包装器来做到这一点。它可以与带有 dbal wrapper_class 选项的 DoctrineBundle 一起使用。

https://github.com/iJanki/doctrine-mysql-dbal-extensions

https://github.com/iJanki/doctrine-mysql-dbal-extensions

回答by Elnur Abdurrakhimov

You have threeoptions.

你有三个选择。

The firstoption is to drop down to SQL. Then you can use all the features your RDBMS of choice provides. But many programmers don't want to drop down to SQL unless absolutely necessary.

第一个选项是下拉到 SQL。然后您可以使用您选择的 RDBMS 提供的所有功能。但是，除非绝对必要，否则许多程序员不想下降到 SQL。

The secondoption is to lock on a relatedrow in another table. For instance, if the entity you're inserting has a unique key per user, you could do a lock on the user you're inserting/updating the entity for. The problem with this solution is that it doesn't work for root entities like Useritself because you can't lock a row that doesn't exist yet.

在第二个选项是锁定在一个相关的另一个表中的行。例如，如果您插入的实体每个用户都有一个唯一的密钥，您可以锁定您正在为其插入/更新实体的用户。此解决方案的问题在于它不适用于像User它本身这样的根实体，因为您无法锁定尚不存在的行。

The thirdoption is to just catch the duplicate key error/exception. That is, you don't check if a row with a particular key already exists; instead, you just attempt to insert it. If it succeeds, all is good. If it fails with the duplicate key error/exception, you catch it and update the existing row. This solution is the best because it avoids an extra SELECTquery before each insertion that's a constant overhead for the low probability of hitting a race condition. And it's the best because it works for both root and non-root entities.

在第三个选项是正好赶上了重复键的错误/异常。也就是说，您不检查具有特定键的行是否已经存在；相反，您只是尝试插入它。如果成功了，那就一切都好了。如果它因重复键错误/异常而失败，则捕获它并更新现有行。这个解决方案是最好的，因为它避免了SELECT每次插入之前的额外查询，这是一个恒定的开销，因为遇到竞争条件的可能性很小。它是最好的，因为它适用于根实体和非根实体。

回答by Steve Clay

In case this helps, you can extend the query builder to append arbitrary SQL (obviously, this may not work across PDO engines):

如果这有帮助，您可以扩展查询构建器以附加任意 SQL（显然，这可能不适用于 PDO 引擎）：

class MyQB extends QueryBuilder {

    private $append = '';

    /**
     * {@inheritdoc}
     */
    public function getSQL() {
        return parent::getSQL() . $this->append;
    }

    /**
     * Append raw SQL to the output query
     *
     * @param string $sql SQL to append. E.g. "ON DUPLICATE ..."
     *
     * @return self
     */
    public function appendSql($sql) {
        $this->append = $sql;
        return $this;
    }
}

回答by Anton_Sh

I wrote simple solution for me. Just created AbstractRepository class which is parent class of all Repositories(for example UserRepository) and created next method:

我为我写了简单的解决方案。刚刚创建了 AbstractRepository 类，它是所有存储库（例如 UserRepository）的父类，并创建了下一个方法：

 public function onDuplicateUpdate($insertFields, $updateFields)
    {
        $table = $this->getEntityManager()->getClassMetadata($this->getEntityName())->getTableName();
        $sql = 'INSERT INTO '.$table;
        $sql .= '(`'.implode('`,`', array_flip($insertFields)).'`) ';
        $sql .= 'VALUES("'.implode('","', $insertFields).'") ';
        $sql .= 'ON DUPLICATE KEY UPDATE ';
        foreach($updateFields as $column => $value) {
            $sql .= '`'.$column . '` = "'. $value.'"';
        }
        $stmt = $this->getEntityManager()->getConnection()->prepare($sql);
        $stmt->execute();
    }

You can use this code like this:

您可以像这样使用此代码：

$this->getEntityManager()
           ->getRepository('User')
           ->onDuplicateUpdate(['column1' => 'user_reminder_1', 'column2' => 235], ['column2' => 255]);