「GoCN酷Go推薦」postgresql ORM 框架 go-pg系列(三）
之前的兩篇都是入門篇，今天我們深入了解下go-pg的select,insert,delete,update的多種玩法。
【GoCN酷Go推薦】postgresql ORM 框架 go-pg系列(一）
【GoCN酷Go推薦】postgresql ORM 框架 go-pg系列(二）
零、設計理念
我們的目標是幫助您編寫SQL，而不是用自定義方言隱藏或替換它。Go-pg查詢生成器有以下幫助:
?將長查詢分割為邏輯上分離的塊;?用正確的轉(zhuǎn)義值來替換?占位符?通過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 stringerr := 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 []Bookerr := 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：統(tǒng)計books的總數(shù)
count, err := db.Model((*Book)(nil)).Count()等于如下的sql語句
SELECT count(*) FROM "books"例子9：選擇20本書并計算books總數(shù)
count, err := db.Model(&books).Limit(20).SelectAndCount()等于如下的sql語句的結(jié)合
SELECT "book"."id", "book"."title", "book"."text"FROM "books" LIMIT 20;SELECT count(*) FROM "books";
例子10：選擇所有book的author_id和圖書的數(shù)量
var res []struct {AuthorId intBookCount int}err := db.Model((*Book)(nil)).Column("author_id").ColumnExpr("count(*) AS book_count").Group("author_id").Order("book_count DESC").Select(&res)
例子11：根據(jù)多個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 插入結(jié)構(gòu)體
例子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 插入切片
例子：單條語句中插入切片數(shù)據(jù)
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操作（很實用）
例子：根據(jù)name來查詢已存在的book，或者創(chuàng)建新book:
_, err := db.Model(book).Where("title = ?title").OnConflict("DO NOTHING"). // optionalSelectOrInsert()、
等同于如下sql語句
SELECT * FROM "books" WHERE title = 'my title';INSERT INTO "books" (title, text) VALUES ('my title', 'my text') RETURNING "id";
三、Update更新
3、1 更新結(jié)構(gòu)體
例子1：除了主鍵，更新所有列數(shù)據(jù)
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列數(shù)據(jù)
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默認把結(jié)構(gòu)體中的大寫字段名轉(zhuǎn)換為小寫的postgresql數(shù)據(jù)庫字段名。
等同于如下sql語句
UPDATE books SET title = 'my title' WHERE id = 13、2 更新切片
使用單個語句更新多個books記錄
err := db.Model(book1, book2).WherePK().Update()等同于以下sql語句
UPDATE books AS book SET title = _data.title, text = _data.textFROM (VALUES (1, 'title1', 'text1'), (2, 'title2', 'text2')) AS _data (id, title, text)WHERE book.id = _data.id
3、3 更新map映射表
使用map映射表來更新數(shù)據(jù)
values := map[string]interface{}{"title": "title1","text": "text1",}_, err := db.Model(&values).TableExpr("books").Where("id = ?", 1).Update()
其中TableExpr表示選擇的是名稱為books的數(shù)據(jù)庫表。
等同于如下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結(jié)構(gòu)體來刪除多條book記錄
books := []*Book{book1, book2} // slice of books with idsres, err := db.Model(&books).WherePK().Delete()
等同于如下sql語句
DELETE FROM "books" WHERE id IN (1, 2)五、Joins聯(lián)合
將book和authors表joins聯(lián)合查詢
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__nameFROM booksJOIN authors AS a ON a.id = book.author_idORDER 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同學，最近我們社區(qū)打算推出一個類似GoCN每日新聞的新欄目《酷Go推薦》，主要是每周推薦一個庫或者好的項目，然后寫一點這個庫使用方法或者優(yōu)點之類的，這樣可以真正的幫助到大家能夠?qū)W習到
新的庫，并且知道怎么用。
大概規(guī)則和每日新聞類似，如果報名人多的話每個人一個月輪到一次，歡迎大家報名！戳「閱讀原文」，即可報名
掃碼也可以加入 GoCN 的大家族喲～