更新插入

本章将介绍如何向数据库中插入或更新记录。

在Kronos中，我们可以使用KPojo.upsert().execute()方法用于向数据库中插入或更新记录。

由于各个数据库的实现不同，因此在Kronos中，我们对upsert操作进行了统一的封装，以实现跨数据库的兼容性。

更新和插入路径中的版本字段行为见 📚乐观锁。upsert 恢复逻辑删除记录的行为见 📚逻辑删除。

on 设置匹配字段

on字段用于匹配已有记录。匹配到记录时执行更新；没有匹配记录时执行插入。on字段可以是单个字段，也可以是多个字段；它们用于生成匹配查询和 update 条件，不会自动创建数据库唯一约束。

如果匹配到的是已逻辑删除记录，Kronos 会更新原行，并把逻辑删除标记恢复为活动值。

普通 on { ... } upsert 会先执行匹配查询，再选择 insert 或 update 分支。未启用乐观锁策略时，匹配查询默认使用更新锁。

kotlin

val user: User = User(
        id = 1,
        name = "Kronos",
        age = 18
    )

user
  .upsert()
  .on { [it.id, it.name] }
  .execute()

Mysql

SELECT COUNT(1) FROM `user` WHERE `id` = :id and `name` = :name LIMIT 1 FOR UPDATE;
# 若记录存在，则更新
UPDATE `user` SET `id` = :id, `name` = :name, `age` = :age WHERE `id` = :id and `name` = :name;
# 若记录不存在，则插入
INSERT INTO `user` (`id`, `name`, `age`) VALUES (:id, :name, :age);

PostgreSQL

SELECT COUNT(1) FROM "user" WHERE "id" = :id and "name" = :name LIMIT 1 FOR UPDATE;
# 若记录存在，则更新
UPDATE "user" SET "id" = :id, "name" = :name, "age" = :age WHERE "id" = :id and "name" = :name;
# 若记录不存在，则插入
INSERT INTO "user" ("id", "name", "age") VALUES (:id, :name, :age);

SQLite

SELECT COUNT(1) FROM "user" WHERE "id" = :id and "name" = :name LIMIT 1;
# 若记录存在，则更新
UPDATE "user" SET "id" = :id, "name" = :name, "age" = :age WHERE "id" = :id and "name" = :name;
# 若记录不存在，则插入
INSERT INTO "user" ("id", "name", "age") VALUES (:id, :name, :age);

SQLServer

SELECT COUNT(1) FROM [user] WITH (UPDLOCK, ROWLOCK) WHERE [id] = :id and [name] = :name;
# 若记录存在，则更新
UPDATE [user] SET [id] = :id, [name] = :name, [age] = :age WHERE [id] = :id and [name] = :name;
# 若记录不存在，则插入
INSERT INTO [user] ([id], [name], [age]) VALUES (:id, :name, :age);

Oracle

SELECT COUNT(1) FROM "user" WHERE "id" = :id and "name" = :name FETCH NEXT 1 ROWS ONLY FOR UPDATE;
# 若记录存在，则更新
UPDATE "user" SET "id" = :id, "name" = :name, "age" = :age WHERE "id" = :id and "name" = :name;
# 若记录不存在，则插入
INSERT INTO "user" ("id", "name", "age") VALUES (:id, :name, :age);

upsert 设置更新字段

用于指定当记录存在时需要更新的字段。

kotlin

val user: User = User(
        id = 1,
        name = "Kronos",
        age = 18
    )

user
  .upsert { it.name }
  .on { it.id }
  .execute()

Mysql

SELECT COUNT(1) FROM `user` WHERE `id` = :id LIMIT 1 FOR UPDATE;
# 若记录存在，则更新
UPDATE `user` SET `name` = :name WHERE `id` = :id;
# 若记录不存在，则插入
INSERT INTO `user` (`id`, `name`, `age`) VALUES (:id, :name, :age);

PostgreSQL

SELECT COUNT(1) FROM "user" WHERE "id" = :id LIMIT 1 FOR UPDATE;
# 若记录存在，则更新
UPDATE "user" SET "name" = :name WHERE "id" = :id;
# 若记录不存在，则插入
INSERT INTO "user" ("id", "name", "age") VALUES (:id, :name, :age);

SQLite

SELECT COUNT(1) FROM "user" WHERE "id" = :id LIMIT 1;
# 若记录存在，则更新
UPDATE "user" SET "name" = :name WHERE "id" = :id;
# 若记录不存在，则插入
INSERT INTO "user" ("id", "name", "age") VALUES (:id, :name, :age);

SQLServer

SELECT COUNT(1) FROM [user] WITH (UPDLOCK, ROWLOCK) WHERE [id] = :id;
# 若记录存在，则更新
UPDATE [user] SET [name] = :name WHERE [id] = :id;
# 若记录不存在，则插入
INSERT INTO [user] ([id], [name], [age]) VALUES (:id, :name, :age);

Oracle

SELECT COUNT(1) FROM "user" WHERE "id" = :id FETCH NEXT 1 ROWS ONLY FOR UPDATE;
# 若记录存在，则更新
UPDATE "user" SET "name" = :name WHERE "id" = :id;
# 若记录不存在，则插入
INSERT INTO "user" ("id", "name", "age") VALUES (:id, :name, :age);

upsert - 设置排除的字段

用于指定当记录存在时不需要更新的字段。

kotlin

val user: User = User(
        id = 1,
        name = "Kronos",
        age = 18
    )

user
  .upsert { it - it.id }
  .on { it.id }
  .execute()

Mysql

SELECT COUNT(1) FROM `user` WHERE `id` = :id LIMIT 1 FOR UPDATE;
# 若记录存在，则更新
UPDATE `user` SET `name` = :name, `age` = :age WHERE `id` = :id;
# 若记录不存在，则插入
INSERT INTO `user` (`id`, `name`, `age`) VALUES (:id, :name, :age);

PostgreSQL

SELECT COUNT(1) FROM "user" WHERE "id" = :id LIMIT 1 FOR UPDATE;
# 若记录存在，则更新
UPDATE "user" SET "name" = :name, "age" = :age WHERE "id" = :id;
# 若记录不存在，则插入
INSERT INTO "user" ("id", "name", "age") VALUES (:id, :name, :age);

SQLite

SELECT COUNT(1) FROM "user" WHERE "id" = :id LIMIT 1;
# 若记录存在，则更新
UPDATE "user" SET "name" = :name, "age" = :age WHERE "id" = :id;
# 若记录不存在，则插入
INSERT INTO "user" ("id", "name", "age") VALUES (:id, :name, :age);

SQLServer

SELECT COUNT(1) FROM [user] WITH (UPDLOCK, ROWLOCK) WHERE [id] = :id;
# 若记录存在，则更新
UPDATE [user] SET [name] = :name, [age] = :age WHERE [id] = :id;
# 若记录不存在，则插入
INSERT INTO [user] ([id], [name], [age]) VALUES (:id, :name, :age);

Oracle

SELECT COUNT(1) FROM "user" WHERE "id" = :id FETCH NEXT 1 ROWS ONLY FOR UPDATE;
# 若记录存在，则更新
UPDATE "user" SET "name" = :name, "age" = :age WHERE "id" = :id;
# 若记录不存在，则插入
INSERT INTO "user" ("id", "name", "age") VALUES (:id, :name, :age);

onConflict 按数据库唯一约束处理冲突

调用onConflict()表示：插入记录；命中数据库唯一约束时更新记录。Kronos 会按当前方言生成需要的 SQL。冲突目标必须指定为某个 key 时，可以在onConflict()前调用on { ... }。没有显式调用on { ... }时，Kronos 会从 KPojo 唯一性元数据推导目标：优先使用有值的主键，其次使用字段值完整的 @TableIndex(type = "UNIQUE") / @TableIndex(method = "UNIQUE")。

策略字段会在两个路径中维护：插入时初始化创建时间、更新时间、逻辑删除和版本字段；冲突更新时刷新更新时间、恢复逻辑删除活动值并递增版本字段。

kotlin

val user: User = User(
        id = 1,
        name = "Kronos",
        age = 18
    )

user
  .upsert { [it.name, it.age] }
  .onConflict()
  .execute()

Mysql

INSERT INTO `user` (`id`, `name`, `age`)
VALUES (:id, :name, :age)
ON DUPLICATE KEY UPDATE `name` = :name, `age` = :age;

PostgreSQL

INSERT INTO "user" ("id", "name", "age")
VALUES (:id, :name, :age)
ON CONFLICT ("id") DO UPDATE SET "name" = :name, "age" = :age;

SQLite

INSERT INTO "user" ("id", "name", "age")
VALUES (:id, :name, :age)
ON CONFLICT ("id") DO UPDATE SET "name" = :name, "age" = :age;

SQLServer

MERGE INTO [user] AS [t1]
USING (SELECT :id AS [id], :name AS [name], :age AS [age]) AS [t2]
ON ([t1].[id] = [t2].[id])
WHEN MATCHED THEN UPDATE SET [t1].[name] = :name, [t1].[age] = :age
WHEN NOT MATCHED THEN INSERT ([id], [name], [age]) VALUES (:id, :name, :age);

Oracle

MERGE INTO "USER" "T1"
USING (SELECT :id AS "ID", :name AS "NAME", :age AS "AGE") "T2"
ON ("T1"."ID" = "T2"."ID")
WHEN MATCHED THEN UPDATE SET "T1"."NAME" = :name, "T1"."AGE" = :age
WHEN NOT MATCHED THEN INSERT ("ID", "NAME", "AGE") VALUES (:id, :name, :age)

显式冲突字段用于主键以外的唯一约束。例如表上存在email唯一键时，可以写成：

kotlin

User(email = "[email protected]", name = "Ada")
  .upsert { it.name }
  .on { it.email }
  .onConflict()
  .execute()

如果自增主键没有提供值，并且模型声明了唯一索引，onConflict() 可以推导该索引：

model

@Table("user")
@TableIndex("uk_user_email", ["email"], type = "UNIQUE")
data class User(
  @PrimaryKey(identity = true)
  var id: Int? = null,
  var email: String? = null,
  var name: String? = null
) : KPojo

kotlin

User(email = "[email protected]", name = "Ada")
  .upsert { it.name }
  .onConflict()
  .execute()

PostgreSQL

INSERT INTO "user" ("email", "name")
VALUES (:email, :name)
ON CONFLICT ("email") DO UPDATE SET "name" = :name;

复合唯一索引推导要求索引里的每个字段都有值。存在多个可能唯一键时，请使用显式 on { ... } 让目标明确。

使用标量子查询设置冲突更新值

set可以把标量子查询赋给onConflict冲突更新的字段。生成的冲突更新 SQL 会保留该标量子查询作为赋值表达式。

kotlin

Order(id = 1, status = 0)
  .upsert()
  .set {
      it.status = (Order()
          .select { order -> order.status }
          .where { order -> order.userId == 44 }
          .limit(1) as Int?)
  }
  .onConflict()
  .execute()

Mysql

INSERT INTO `order` (`id`, `status`)
VALUES (:id, :status)
ON DUPLICATE KEY UPDATE `status` = (
    SELECT `status`
    FROM `order`
    WHERE `order`.`user_id` = :userId
    LIMIT 1
)

PostgreSQL

INSERT INTO "order" ("id", "status")
VALUES (:id, :status)
ON CONFLICT ("id") DO UPDATE SET "status" = (
    SELECT "status"
    FROM "order"
    WHERE "order"."user_id" = :userId
    LIMIT 1
)

更多标量子查询赋值写法见 📚子查询。

使用 patch 动态设置冲突更新值

patch(...) 会把字段加入冲突更新列表，并可以提供 onConflict() 使用的更新值。冲突更新值需要动态决定时使用它。

SqlExpr and function

import com.kotlinorm.beans.dsl.KronosFunctionExpr
import com.kotlinorm.syntax.expr.SqlExpr

User(id = 7, name = "seed", count = 2)
  .upsert { it.name }
  .patch(
      "count" to SqlExpr.NumberLiteral("10"),
      "name" to KronosFunctionExpr(SqlExpr.StringLiteral("patched"), "literal")
  )
  .onConflict()
  .execute()

Mysql

INSERT INTO `user` (`id`, `name`, `count`)
VALUES (:id, :name, :count)
ON DUPLICATE KEY UPDATE `name` = 'patched', `count` = 10

冲突更新需要复用另一列表达式时，可以传入 Field。

field expression

val countField = User().__columns.single { it.name == "count" }

User(id = 8, name = "seed", count = 5)
  .upsert { it.name }
  .patch("name" to countField)
  .onConflict()
  .execute()

Mysql

INSERT INTO `user` (`id`, `name`, `count`)
VALUES (:id, :name, :count)
ON DUPLICATE KEY UPDATE `name` = `user`.`count`

更新值来自标量子查询时，可以传入 KSelectable。

scalar subquery

User(id = 1, name = "seed")
  .upsert()
  .patch(
      "name" to Order()
          .select { it.status }
          .where { it.status == 15 }
          .limit(1)
  )
  .onConflict()
  .execute()

Mysql

INSERT INTO `user` (`id`, `name`)
VALUES (:id, :name)
ON DUPLICATE KEY UPDATE `name` = (
    SELECT `status`
    FROM `order`
    WHERE `order`.`status` = :status
    LIMIT 1
)

patch(...) 的值会作为 onConflict() 路径中的冲突更新赋值。普通 upsert 路径中，相同字段会进入匹配后的 update set。

lock 设置查询时行锁

普通 on { ... } upsert 默认会给匹配查询使用更新锁。需要显式声明锁类型时，可以调用 lock(...)。

kotlin

import com.kotlinorm.syntax.statement.SqlLock

val user: User = User(
        id = 1,
        name = "Kronos",
        age = 18
    )

user
  .upsert { [it.name, it.age] }
  .on { it.id }
  .lock(SqlLock.Update())
  .execute()

Mysql

SELECT COUNT(1) FROM `user` WHERE `id` = :id LIMIT 1 FOR UPDATE;
# 若记录存在，则更新
UPDATE `user` SET `name` = :nameNew, `age` = :ageNew WHERE `id` = :id;
# 若记录不存在，则插入
INSERT INTO `user` (`id`, `name`, `age`) VALUES (:id, :name, :age);

影响行数

在Kronos中，我们可以使用upsert方法的execute方法获取影响行数。

val user: User = User(
        id = 1,
        name = "Kronos",
        age = 18
    )

val (affectedRows) = user
                .upsert()
                .on { it.id }
                .execute()

批量更新或插入记录

同一条 upsert SQL 需要用多组参数执行时，见 📚批量操作。

指定使用的数据源

在Kronos中，我们可以将KronosDataSourceWrapper传入execute方法，以实现自定义的数据库连接。

val customWrapper = CustomWrapper()

val user: User = User(
        id = 1,
        name = "Kronos",
        age = 18
    )

user
  .upsert { [it.name, it.age] }
  .on { it.id }
  .execute(customWrapper)