Golang 选择 PostgreSQL MySQL

• API
• 示例
• 计数行
• EXISTS
• 联接
• 子查询
• 原始查询

API

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

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

	Model(&strct).
	Model(&slice).

	Column("col1", "col2"). // quotes column names
	ColumnExpr("col1, col2"). // arbitrary unsafe expression
	ColumnExpr("count(*)").
	ColumnExpr("count(?)", bun.Ident("id")).
	ColumnExpr("(?) AS alias", subquery).
	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

	Join("JOIN table2 AS t2 ON t2.id = t1.id").
	Join("LEFT JOIN table2 AS t2").JoinOn("t2.id = t1.id").

	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")
	}).

	Group("col1", "col2"). // quotes column names
	GroupExpr("lower(col1)"). // arbitrary unsafe expression

	Order("col1 ASC", "col2 DESC"). // quotes column names
	OrderExpr("col1 ASC NULLS FIRST"). // arbitrary unsafe expression

    Having("column_name > ?", 123).

	Limit(100).
	Offset(100).

	For("UPDATE").
	For("SHARE").

	Scan(ctx)

示例

要选择到结构体中，请定义一个 模型 并使用 SelectQuery

book := new(Book)
err := db.NewSelect().Model(book).Where("id = ?", 123).Scan(ctx)

计数行

Bun 提供 Count 帮助程序来生成 count(*) 查询

count, err := db.NewSelect().Model((*User)(nil)).Count(ctx)

由于选择和计数行是一种常见的操作，Bun 还提供 ScanAndCount

var users []User
count, err := db.NewSelect().Model(&users).Limit(20).ScanAndCount(ctx)
if err != nil {
	panic(err)
}
fmt.Println(users, count)

EXISTS

您还可以使用 Exists 帮助程序来使用相应的 EXISTS SQL 运算符

exists, err := db.NewSelect().Model((*User)(nil)).Where("name LIKE '%foo%'").Exists(ctx)
if err != nil {
	panic(err)
}
if !exists {
	fmt.Println("such user does not exist")
}

SELECT EXISTS (SELECT * FROM users WHERE name LIKE '%foo%')

联接

要选择一本书并手动联接书籍作者

book := new(Book)
err := db.NewSelect().
    Model(book).
    ColumnExpr("book.*").
    ColumnExpr("a.id AS author__id, a.name AS author__name").
    Join("JOIN authors AS a ON a.id = book.author_id").
    OrderExpr("book.id ASC").
    Limit(1).
    Scan(ctx)

SELECT book.*, a.id AS author__id, a.name AS author__name
FROM books
JOIN authors AS a ON a.id = book.author_id
ORDER BY book.id ASC
LIMIT 1

要生成复杂的联接，请使用 JoinOn

q = q.
    Join("JOIN authors AS a").
    JoinOn("a.id = book.author_id").
    JoinOn("a.deleted_at IS NULL")

JOIN authors AS a ON a.id = book.author_id AND a.deleted_at IS NULL

子查询

您可以将 Bun 查询（包括 INSERT、UPDATE 和 DELETE 查询）用作子查询

subq := db.NewSelect().Model((*Book)(nil)).Where("author_id = ?", 1)

err := db.NewSelect().Model().TableExpr("(?) AS book", subq).Scan(ctx, &books)

SELECT * FROM (
  SELECT "book"."id", "book"."title", "book"."text"
  FROM "books" AS "book" WHERE (author_id = 1)
) AS book

原始查询

Bun 还允许您执行和扫描任意原始查询

type User struct {
	ID int64
	Name string
}

users := make([]User, 0)

err := db.NewRaw(
	"SELECT id, name FROM ? LIMIT ?",
	bun.Ident("users"), 100,
).Scan(ctx, &users)

SELECT id, name FROM "users" LIMIT 100