Golang 插入 PostgreSQL MySQL

• API
• 示例
• 批量插入
• Upsert
• 映射
• INSERT ... SELECT

API

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

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

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

    Column("col1", "col2"). // list of columns to insert
    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 subq", subquery).
    ModelTableExpr("table1 AS t1"). // overrides model table name

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

    On("CONFLICT (id) DO UPDATE").
	Set("col1 = EXCLUDED.col1").

    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)

示例

要插入数据，请定义一个 模型 并使用 InsertQuery

book := &Book{Title: "hello"}

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

批量插入

要批量插入模型，请使用切片

books := []Book{book1, book2}
res, err := db.NewInsert().Model(&books).Exec(ctx)
if err != nil {
    panic(err)
}

for _, book := range books {
    fmt.Println(book.ID) // book id is scanned automatically
}

Upsert

要插入新书或更新现有书

_, err := db.NewInsert().
	Model(&book).
	On("CONFLICT (id) DO UPDATE").
	Set("title = EXCLUDED.title").
	Exec(ctx)

INSERT INTO "books" ("id", "title") VALUES (100, 'my title')
ON CONFLICT (id) DO UPDATE SET title = EXCLUDED.title

对于 MySQL，请使用

_, err := db.NewInsert().
	Model(&book).
	On("DUPLICATE KEY UPDATE").
	Exec(ctx)

INSERT INTO `books` (`id`, `title`) VALUES (100, 'my title')
ON DUPLICATE KEY UPDATE `title` = VALUES(`title`)

要忽略重复项，请对所有数据库使用 Ignore

_, err := db.NewInsert().
	Model(&book).
	Ignore().
	Exec(ctx)

-- MySQL
INSERT IGNORE INTO `books` (`id`, `title`) VALUES (100, 'my title');

-- PostgreSQL
INSERT INTO `books` (`id`, `title`) VALUES (100, 'my title')
ON CONFLICT DO NOTHING;

映射

要插入 map[string]interface{}

values := map[string]interface{}{
    "title": "title1",
    "text":  "text1",
}
_, err := db.NewInsert().Model(&values).TableExpr("books").Exec()

INSERT INTO "books" ("title", "text") VALUES ('title1', 'text2')

INSERT ... SELECT

要复制表之间的行

_, err := db.NewInsert().
    Table("books_backup").
	Table("books").
	Exec(ctx)

INSERT INTO "books_backup" SELECT * FROM "books"

您还可以指定要复制的列

_, err := db.NewInsert().
    ColumnExpr("id, name").
    Table("dest").
    Table("src").
    Exec(ctx)

INSERT INTO "dest" (id, name) SELECT id, name FROM "src"