在 Slick 中插入 [英] Upsert in Slick

问题描述

有没有办法可以在 Slick 中巧妙地执行更新插入操作?以下工作但太晦涩/冗长，我需要明确说明应该更新的字段:

Is there a way I can neatly do an upsert operation in Slick? The following works but is too obscure/verbose and I need to explicitly state the fields that should be updated:

val id = 1
val now = new Timestamp(System.currentTimeMillis)
val q = for { u <- Users if u.id === id } yield u.lastSeen 
q.update(now) match {
  case 0 => Users.insert((id, now, now))
  case _ => Unit
}

推荐答案

针对 Slick 2.1 中的原生 upsert/merge 支持进行了更新

Updated for native upsert/merge support in Slick 2.1

您必须将纯 SQL 嵌入与您的数据库本机 MERGE 语句一起使用.模拟此语句的所有试验很可能会导致不正确的结果.

You have to use plain SQL embedding with your database native MERGE statement. All trials to simulate this statement will very likely lead to incorrect results.

当您模拟 upsert/merge 语句时，Slick 将不得不使用多个语句来达到该目标(例如，先进行选择，然后是插入或更新语句).在一个 SQL 事务中运行多条语句时，它们通常没有与单个语句相同的隔离级别.不同的隔离级别，你会在海量并发的情况下体验到奇怪的效果因此，在测试期间一切正常，但在生产中会因奇怪的影响而失败.

When you simulate the upsert / merge statement, Slick will have to use multiple statements to reach that goal (e.g. fist a select and then either an insert or an update statement). When running multiple statements in a SQL transaction, they usually doe not have the same isolation level as one single statement. With different isolation levels, you will experience strange effects in massive concurrent situations. So everything will work fine during the tests and fail with strange effects in production.

在同一事务中的两个语句之间运行一个语句时，数据库通常具有更强的隔离级别.而一个正在运行的语句不会受到其他并行运行的语句的影响.数据库要么锁定语句涉及的所有内容，要么检测正在运行的语句之间的干扰，并在必要时自动重新启动有问题的语句.当执行同一事务中的下一条语句时，此级别的保护不成立.

A database usually has a stronger isolation level while running one statement as between two statements in the same transaction. While one running statement will not be affected by other statements that run in parallel. The database will either lock everything the statement touches or it will detect interefence between running statements and automatically restart the problematic statements when necessary. This level of protection does not hold, when the next statement in the same transaction is executed.

因此以下场景可能(并且将会！)发生:

So the following scenario may (and will!) happen:

1. 在第一个事务中，user.firstOption 后面的 select 语句没有找到当前用户的数据库行.
2. 并行的第二个事务为该用户插入一行
3. 第一个事务为该用户插入第二行(类似于幻读)
4. 您要么以同一用户的两行结尾，要么第一个交易失败并违反约束，尽管其检查有效(运行时)

1. In the first transaction the select statement behind user.firstOption doesn't find a database row for the current user.
2. A parallel second transaction inserts a row for that user
3. The first transaction inserts a second row for that user (similar to a phantom read)
4. You either end with two rows for the same user or the first transaction fails with a constraint violation although its check was valid (when it ran)

公平地说，隔离级别 "serializable" 不会发生这种情况.但是这种隔离级别会带来巨大的性能损失，很少在生产中使用.另外可序列化将需要您的应用程序的一些帮助:数据库管理系统通常不会真正可序列化所有事务.但它会检测违反可序列化要求的行为，并中止遇到麻烦的事务.因此，您的应用程序必须准备好重新运行被 DBMS(随机)中止的事务.

To be fair, this will not happen with the isolation level "serializable". But this isolation level is comes with a huge performance hit is rarely used in production. Additionally serializable will need some help from your application: The database management system will usually not really serializable all transaction. But it will detect violations against the serializable requeirement and just abort the transactions in trouble. So your application must be prepared for rerunning transaction that are aborted (randomly) by the DBMS.

如果您依赖于发生违反约束的情况，请设计您的应用程序，使其自动重新运行有问题的事务，而不会打扰用户.这类似于隔离级别可序列化"中的要求.

If you rely on the constraint violation to occur, design your application in a way that it will automatically rerun the transaction in question without bothering the user. This is similar to the requirement in isolation level "serializable".

在这种情况下使用纯 SQL，或者为生产中的不愉快惊喜做好准备.考虑并发性可能出现的问题.

Use plain SQL for this scenario or prepare for unpleasant surprises in production. Think twice about possible problems with concurrency.

Slick 2.1.0 现在原生支持 MERGE 语句(参见 发行说明:插入或更新支持，在可能的情况下使用本机数据库功能").

With Slick 2.1.0 there is now native support for the MERGE statement (see the release notes: "Insert-or-update support which makes use of native databases features where possible").

代码看起来像这样(取自 流畅的测试用例):

The code will look like this (taken from the Slick test cases):

  def testInsertOrUpdatePlain {
    class T(tag: Tag) extends Table[(Int, String)](tag, "t_merge") {
      def id = column[Int]("id", O.PrimaryKey)
      def name = column[String]("name")
      def * = (id, name)
      def ins = (id, name)
    }
    val ts = TableQuery[T]

    ts.ddl.create

    ts ++= Seq((1, "a"), (2, "b")) // Inserts (1,a) and (2,b)

    assertEquals(1, ts.insertOrUpdate((3, "c"))) // Inserts (3,c)
    assertEquals(1, ts.insertOrUpdate((1, "d"))) // Updates (1,a) to (1,d)

    assertEquals(Seq((1, "d"), (2, "b"), (3, "c")), ts.sortBy(_.id).run)
  }

这篇关于在 Slick 中插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！