原则：关于重复密钥更新

Question

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

Answer 1

对于 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();

Answer 2

问题是这是 MySQL 特定的问题，因此 Doctrine 不会直接涵盖它。

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

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

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

Answer 3

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

您可以做的是通过检查实体是否存在并相应地更新/创建它来模仿 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);
  }
});

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

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

正如评论中指出的如果记录不存在，这不会阻止竞争条件：如果在您运行的选择和插入之间插入具有相同键的行进入重复键异常。

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

参考资料：

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

Answer 4

您可以使用这样的函数来构建和执行原始 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();
}

Answer 5

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

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

Answer 6

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

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

Answer 7

您有三个选项。

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

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

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

Answer 8

如果这有帮助，您可以扩展查询生成器以附加任意 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;
    }
}

Answer 9

我为我写了简单的解决方案。刚刚创建了 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();
    }

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

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