如何用CockroachDB+Go+pgx实现Todos批量更新/插入（无ID生成UUID）

针对你的需求，我们可以利用CockroachDB兼容PostgreSQL的CTE（公共表表达式）特性，实现优先更新、失败则插入的逻辑，同时支持批量处理多个任务。

单条任务处理 #

通过CTE先执行更新操作，捕获成功更新的记录ID，再仅对未更新成功的情况执行插入：

WITH updated AS (
  UPDATE todos
  SET text = $2
  WHERE id = $1
  RETURNING id
)
INSERT INTO todos (text)
SELECT $2
WHERE NOT EXISTS (SELECT 1 FROM updated);

逻辑说明： #

• 如果传入的$1（UUID）存在，UPDATE会修改对应记录，updated表会返回该ID，后续INSERT因NOT EXISTS条件不满足而跳过；
• 如果$1不存在或传入空值，UPDATE无返回结果，INSERT会执行并自动生成UUID主键。

批量任务处理 #

对于多条任务（混合含ID的更新请求和无ID的插入请求），可以通过构造输入数据的方式批量处理：

WITH input_data AS (
  SELECT * FROM (
    VALUES
      ($1::UUID, $2::STRING),
      ($3::UUID, $4::STRING),
      (NULL, $5::STRING)
  ) AS t(id, text)
),
updated AS (
  UPDATE todos
  SET text = input_data.text
  FROM input_data
  WHERE todos.id = input_data.id AND input_data.id IS NOT NULL
  RETURNING todos.id
)
INSERT INTO todos (text)
SELECT input_data.text
FROM input_data
WHERE input_data.id IS NULL OR NOT EXISTS (
  SELECT 1 FROM updated WHERE updated.id = input_data.id
);

逻辑说明： #

1. input_data：批量传入所有任务数据，含ID的记录传入UUID，无ID的记录传入NULL；
2. updated：批量更新所有ID存在的记录，返回成功更新的ID；
3. INSERT：插入两类记录：无ID的新任务，以及有ID但未找到对应记录的任务（自动生成UUID）。

Go + pgx 实现示例 #

结合pgx驱动，我们可以动态构造批量SQL语句，处理任意数量的任务：

import (
  "context"
  "fmt"
  "strings"

  "github.com/google/uuid"
  "github.com/jackc/pgx/v5"
)

type Todo struct {
  ID   uuid.UUID
  Text string
}

func BatchUpsertTodos(ctx context.Context, conn *pgx.Conn, todos []Todo) error {
  var args []interface{}
  var valueClauses []string
  paramIdx := 1

  for _, todo := range todos {
    if todo.ID == uuid.Nil {
      // 无ID的插入任务
      valueClauses = append(valueClauses, fmt.Sprintf("(NULL, $%d)", paramIdx))
      args = append(args, todo.Text)
      paramIdx++
    } else {
      // 有ID的更新任务
      valueClauses = append(valueClauses, fmt.Sprintf("($%d, $%d)", paramIdx, paramIdx+1))
      args = append(args, todo.ID, todo.Text)
      paramIdx += 2
    }
  }

  sqlQuery := fmt.Sprintf(`
WITH input_data AS (
  SELECT * FROM (
    VALUES
      %s
  ) AS t(id UUID, text STRING)
),
updated AS (
  UPDATE todos
  SET text = input_data.text
  FROM input_data
  WHERE todos.id = input_data.id AND input_data.id IS NOT NULL
  RETURNING todos.id
)
INSERT INTO todos (text)
SELECT input_data.text
FROM input_data
WHERE input_data.id IS NULL OR NOT EXISTS (
  SELECT 1 FROM updated WHERE updated.id = input_data.id
);`, strings.Join(valueClauses, ", "))

  _, err := conn.Exec(ctx, sqlQuery, args...)
  return err
}

使用说明： #

• 传入的Todo切片中，ID为uuid.Nil的会被视为新任务，自动生成UUID；
• pgx驱动会自动处理UUID与Go类型的转换，无需额外序列化；
• 该方式仅需一次数据库请求，相比循环执行单条语句性能更优。

内容的提问来源于stack exchange，提问作者dgh