Golang 更新 PostgreSQL MySQL

• API
• 示例
• 批量更新
• 映射
• 忽略零值
• FQN

API

要查看支持方法的完整列表，请参阅 API 参考.

db.NewUpdate().
	With("cte_name", subquery).

	Model(&strct).
	Model(&slice).
	Model(&map). // only map[string]interface{}

	Column("col1", "col2"). // list of columns to update
	ExcludeColumn("col1"). // all columns except col1
	ExcludeColumn("*"). // exclude all columns

	Table("table1", "table2"). // quotes table names
	TableExpr("table1 AS t1"). // arbitrary unsafe expression
	TableExpr("(?) AS alias", subquery).
	ModelTableExpr("table1 AS t1"). // overrides model table name

	Value("col1", "expr1", arg1, arg2). // overrides column value

    // Generates `SET col1 = 'value1'`
	Set("col1 = ?", "value1").
    SetColumn("col1", "?", "value1").

	OmitZero() // don't update struct fields having zero values

	WherePK(). // where using primary keys
	Where("id = ?", 123).
	Where("name LIKE ?", "my%").
	Where("? = 123", bun.Ident("id")).
	Where("id IN (?)", bun.In([]int64{1, 2, 3})).
	Where("id IN (?)", subquery).
	Where("FALSE").WhereOr("TRUE").
	WhereGroup(" AND ", func(q *bun.SelectQuery) *bun.SelectQuery {
		return q.WhereOr("id = 1").
			WhereOr("id = 2")
	}).

	Returning("*").
	Returning("col1, col2").
	Returning("NULL"). // don't return anything

	Exec(ctx)

示例

要更新一行，请定义一个 模型 并使用 UpdateQuery

book := &Book{ID: 123, Title: "hello"}

res, err := db.NewUpdate().Model(book).WherePK().Exec(ctx)

要更新单个列

book.Title = "hello"

res, err := db.NewUpdate().
	Model(book).
	Column("title").
	Where("id = ?", 123).
	Exec(ctx)

UPDATE books SET title = 'my title' WHERE id = 123

或者

res, err := db.NewUpdate().
    Model(book).
    Set("title = ?", "hello").
    Where("id = ?", 123).
    Exec(ctx)

批量更新

要批量更新书籍，可以使用 CTE

values := db.NewValues(&[]*Book{book1, book2})

res, err := db.NewUpdate().
	With("_data", values).
	Model((*Book)(nil)).
	TableExpr("_data").
	Set("title = _data.title").
	Set("text = _data.text").
	Where("book.id = _data.id").
	Exec(ctx)

WITH _data (id, title, text) AS (
  VALUES
    (1, 'title1', 'text1'),
    (2, 'title2', 'text2')
)
UPDATE books AS book
SET title = _data.title, text = _data.text
FROM _data
WHERE book.id = _data.id

或者，您可以使用 Bulk 帮助程序，它会为您创建一个 CTE

res, err := db.NewUpdate().
	Model(&books).
	Column("title", "text").
	Bulk().
	Exec(ctx)

映射

要使用 map[string]interface{} 更新

value := map[string]interface{}{
	"title": "title1",
	"text":	 "text1",
}
res, err := db.NewUpdate().
	Model(&value).
	TableExpr("books").
	Where("id = ?", 1).
	Exec(ctx)

UPDATE books
SET title = 'title1', text = 'text2'
WHERE id = 1

忽略零值

您也可以告诉 Bun 忽略零结构字段，例如，以下查询不会更新 email 列，因为它包含一个空值

type User struct {
	ID	  int64
	Name  string
	Email string
}

res, err := db.NewUpdate().
	Model(&User{ID: 1, Name: "John Doe"}).
	OmitZero().
	WherePK().
	Exec(ctx)

UPDATE users
SET name = "John Doe"
WHERE id = 1

FQN

多表更新在 PostgreSQL 和 MySQL 中有所不同

-- PostgreSQL
UPDATE dest FROM src SET col1 = src.col1 WHERE dest.id = src.id

-- MySQL
UPDATE dest, src SET dest.col1 = src.col1 WHERE dest.id = src.id

Bun 通过提供 SetColumn 方法帮助您为这两个数据库编写查询

res, err := db.NewUpdate().
	Table("dest", "src").
	SetColumn("col1", "src.col1").
	Where("dest.id = src.id").
	Exec(ctx)

如果您要更新模型切片，请使用 Bulk 方法

res, err := db.NewUpdate().
	Model(&models).
	Column("col1").
	Bulk().
	Exec(ctx)