之前的两篇都是入门篇,今天我们深入了解下go-pg的select,insert,delete,update的多种玩法。
【GoCN酷Go推荐】postgresql ORM 框架 go-pg系列(一)
【GoCN酷Go推荐】postgresql ORM 框架 go-pg系列(二)
零、设计理念
我们的目标是帮助您编写SQL,而不是用自定义方言隐藏或替换它。Go-pg查询生成器有以下帮助:
•将长查询分割为逻辑上分离的块;•用正确的转义值来替换?占位符•通过go models生成列的列表和一些join连接
举例有如下go代码:
err := db.Model(book).
ColumnExpr("lower(name)").
Where("? = ?", pg.Ident("id"), "some-id").
Select()
生成如下的sql语句
SELECT lower(name)
FROM "books"
WHERE "id" = 'some-id'
一、Select查询
1、1 sql和go-pg对照表
SQL | go-pg |
SELECT col1, col2 | Column("col1", "col2") |
SELECT col1, col2 | ColumnExpr("col1, col2") |
SELECT count() | ColumnExpr("count()") |
SELECT count("id") | ColumnExpr("count(?)", pg.Ident("id")) |
FROM "table1", "table2" | Table("table1", "table2") |
FROM table1, table2 | TableExpr("table1, table2") |
JOIN table1 ON col1 = 'value1' | Join("JOIN table1 ON col1 = ?", "value1") |
JOIN table1 ON col1 = 'value1' | Join("JOIN table1").JoinOn("col1 = ?", "value1") |
LEFT JOIN table1 ON col1 = 'value1' | Join("LEFT JOIN table1 ON col1 = ?", "value1") |
WHERE id = 1 | Where("id = ?", 1) |
WHERE "foo" = 'bar' | Where("? = ?", pg.Ident("foo"), "bar") |
WHERE id = 1 OR foo = 'bar' | Where("id = ?", 1).WhereOr("foo = ?", "bar") |
GROUP BY "col1", "col2" | Group("col1", "col2") |
GROUP BY col1, col2 | GroupExpr("col1, col2") |
GROUP BY "col1", "col2" | GroupExpr("?, ?", pg.Ident("col1"), pg.Ident("col2")) |
ORDER BY "col1" ASC | Order("col1 ASC") |
ORDER BY col1 ASC | OrderExpr("col1 ASC") |
ORDER BY "col1" ASC | OrderExpr("? ASC", pg.Ident("col1")) |
LIMIT 10 | Limit(10) |
OFFSET 1000 | Offset(1000) |
1、2 示例
例子1:使用主键来查找book
book := new(Book)
err := db.Model(book).Where("id = ?", 1).Select()
等于如下的sql语句
SELECT "book"."id", "book"."title", "book"."text"
FROM "books" WHERE id = 1
例子2:只查询book的title和text
err := db.Model(book).
Column("title", "text").
Where("id = ?", 1).
Select()
等于如下的sql语句
SELECT "title", "text" FROM "books" WHERE id = 1
例子3:只查询book的title和text,并将其写入变量
var title, text string
err := db.Model((*Book)(nil)).
Column("title", "text").
Where("id = ?", 1).
Select(&title, &text)
例子4:使用WHERE ... AND ...语句来查询book
err := db.Model(book).
Where("id > ?", 100).
Where("title LIKE ?", "my%").
Limit(1).
Select()
等于如下的sql语句
SELECT "book"."id", "book"."title", "book"."text"
FROM "books"
WHERE (id > 100) AND (title LIKE 'my%')
LIMIT 1
例子5:使用WHERE ... OR ...语句来查询book
err := db.Model(book).
Where("id > ?", 100).
WhereOr("title LIKE ?", "my%").
Limit(1).
Select()
等于如下的sql语句
SELECT "book"."id", "book"."title", "book"."text"
FROM "books"
WHERE (id > 100) OR (title LIKE 'my%')
LIMIT 1
例子6:使用WHERE ... AND (... OR ...)语句来查询book
err := db.Model(book).
Where("title LIKE ?", "my%").
WhereGroup(func(q *pg.Query) (*pg.Query, error) {
q = q.WhereOr("id = 1").
WhereOr("id = 2")
return q, nil
}).
Limit(1).
Select()
等于如下的sql语句
SELECT "book"."id", "book"."title", "book"."text"
FROM "books"
WHERE (title LIKE 'my%') AND (id = 1 OR id = 2)
LIMIT 1
例子7:查询前20的books
var books []Book
err := db.Model(&books).Order("id ASC").Limit(20).Select()
等于如下的sql语句
SELECT "book"."id", "book"."title", "book"."text"
FROM "books"
ORDER BY id ASC LIMIT 20
例子8:统计books的总数
count, err := db.Model((*Book)(nil)).Count()
等于如下的sql语句
SELECT count(*) FROM "books"
例子9:选择20本书并计算books总数
count, err := db.Model(&books).Limit(20).SelectAndCount()
等于如下的sql语句的结合
SELECT "book"."id", "book"."title", "book"."text"
FROM "books" LIMIT 20;
SELECT count(*) FROM "books";
例子10:选择所有book的author_id和图书的数量
var res []struct {
AuthorId int
BookCount int
}
err := db.Model((*Book)(nil)).
Column("author_id").
ColumnExpr("count(*) AS book_count").
Group("author_id").
Order("book_count DESC").
Select(&res)
例子11:根据多个ids来查询books
ids := []int{1, 2, 3}
err := db.Model((*Book)(nil)).
Where("id in (?)", pg.In(ids)).
Select()
等同于如下sql语句
SELECT * FROM books WHERE id IN (1, 2, 3)
例子12:查询books用于更新
book := &Book{}
err := db.Model(book).
Where("id = ?", 1).
For("UPDATE").
Select()
等同于如下sql语句
SELECT * FROM books WHERE id = 1 FOR UPDATE
二 、Insert插入
2、1 插入结构体
例子1:插入新book返回主键
err := db.Model(book).Insert()
等同于如下sql语句
INSERT INTO "books" (title, text) VALUES ('my title', 'my text') RETURNING "id"
例子2:插入新book返回所有列
err := db.Model(book).Returning("*").Insert()
等同于如下sql语句
INSERT INTO "books" (title, text) VALUES ('my title', 'my text') RETURNING *
例子3:插入新book 或 更新已存在的记录:
_, err := db.Model(book).
OnConflict("(id) DO UPDATE").
Set("title = EXCLUDED.title").
Insert()
等同于如下sql语句
INSERT INTO "books" ("id", "title") VALUES (100, 'my title')
ON CONFLICT (id) DO UPDATE SET title = 'title version #1'
2、2 插入切片
例子:单条语句中插入切片数据
books := []*Book{book1, book2}
_, err := db.Model(&books).Insert()
等同于如下sql语句
INSERT INTO "books" (title, text) VALUES ('title1', 'text2'), ('title2', 'text2') RETURNING "id"
相同的操作:
_, err := db.Model(book1, book2).Insert()
INSERT INTO "books" (title, text) VALUES ('title1', 'text2'), ('title2', 'text2') RETURNING *
2、3 插入map映射表
Insert map[string]interface{}
:
values := map[string]interface{}{
"title": "title1",
"text": "text1",
}
_, err := db.Model(&values).TableExpr("books").Insert()
等同于如下sql语句
INSERT INTO books (title, text) VALUES ('title1', 'text2')
2、4 Select or insert操作(很实用)
例子:根据name来查询已存在的book,或者创建新book:
_, err := db.Model(book).
Where("title = ?title").
OnConflict("DO NOTHING"). // optional
SelectOrInsert()、
等同于如下sql语句
SELECT * FROM "books" WHERE title = 'my title';
INSERT INTO "books" (title, text) VALUES ('my title', 'my text') RETURNING "id";
三、Update更新
3、1 更新结构体
例子1:除了主键,更新所有列数据
book := &Book{
ID: 1,
Title: "my title",
Text: "my text",
}
err := db.Model(book).WherePK().Update()
等同于如下sql语句
UPDATE books SET title = 'my title', text = 'my text' WHERE id = 1
例子2:仅仅更新title列数据
book := &Book{
ID: 1,
Title: "my title",
Text: "my text",
}
res, err := db.Model(book).
Column("title").
Where("id = ?", 1).
Update()
换另一种写法
res, err := db.Model(book).Set("title = ?title").Where("id = ?id").Update()
其中的"?title"对应变量中的title字段,"?id"对应变量中的id字段,go-pg默认把结构体中的大写字段名转换为小写的postgresql数据库字段名。
等同于如下sql语句
UPDATE books SET title = 'my title' WHERE id = 1
3、2 更新切片
使用单个语句更新多个books记录
err := db.Model(book1, book2).WherePK().Update()
等同于以下sql语句
UPDATE books AS book SET title = _data.title, text = _data.text
FROM (VALUES (1, 'title1', 'text1'), (2, 'title2', 'text2')) AS _data (id, title, text)
WHERE book.id = _data.id
3、3 更新map映射表
使用map映射表来更新数据
values := map[string]interface{}{
"title": "title1",
"text": "text1",
}
_, err := db.Model(&values).TableExpr("books").Where("id = ?", 1).Update()
其中TableExpr表示选择的是名称为books的数据库表。
等同于如下sql语句
UPDATE books SET title = 'title1', text = 'text2' WHERE id = 1
四、Delete删除
例子1:通过主键来删除book记录
res, err := db.Model(book).Where("id = ?", 1).Delete()
等同于如下sql语句
DELETE FROM "books" WHERE id = 1
例子2:通过title来删除book记录
res, err := db.Model(book).Where("title = ?title").Delete()
等同于如下sql语句
DELETE FROM "books" WHERE title = 'my title'
例子2:通过ids来删除多条book记录
res, err := db.Model((*Book)(nil)).
Where("id IN (?)", pg.In([]int{1, 2})).
Delete()
等同于如下sql语句
DELETE FROM "books" WHERE id IN (1, 2)
例子3:通过struct结构体来删除多条book记录
books := []*Book{book1, book2} // slice of books with ids
res, err := db.Model(&books).WherePK().Delete()
等同于如下sql语句
DELETE FROM "books" WHERE id IN (1, 2)
五、Joins联合
将book和authors表joins联合查询
book := new(Book)
err := db.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").
First()
等同于如下sql语句
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 id LIMIT 1
可以使用JoinOn分割连接条件
q.Join("LEFT JOIN authors AS a").
JoinOn("a.id = book.author_id").
JoinOn("a.active = ?", true)
参考资料
https://pg.uptrace.dev/
https://medium.com/tunaiku-tech/go-pg-golang-postgre-orm-2618b75c0430
《酷Go推荐》招募:
各位Gopher同学,最近我们社区打算推出一个类似GoCN每日新闻的新栏目《酷Go推荐》,主要是每周推荐一个库或者好的项目,然后写一点这个库使用方法或者优点之类的,这样可以真正的帮助到大家能够学习到
新的库,并且知道怎么用。
大概规则和每日新闻类似,如果报名人多的话每个人一个月轮到一次,欢迎大家报名!戳「阅读原文」,即可报名
扫码也可以加入 GoCN 的大家族哟~