|
|
package gormExample
|
|
|
|
|
|
import (
|
|
|
"database/sql"
|
|
|
"fmt"
|
|
|
"gorm.io/gorm"
|
|
|
"gorm.io/gorm/clause"
|
|
|
"log"
|
|
|
"time"
|
|
|
)
|
|
|
|
|
|
func GetByPk() {
|
|
|
// migrate
|
|
|
DB.AutoMigrate(&Content{}, &ContentStrPK{})
|
|
|
|
|
|
// 查询单条
|
|
|
////c := Content{}
|
|
|
////if err := DB.First(&c, 10).Error; err != nil {
|
|
|
//// log.Println(err)
|
|
|
////}
|
|
|
//c := Content{}
|
|
|
//c.ID = 10
|
|
|
//if err := DB.First(&c).Error; err != nil {
|
|
|
// log.Println(err)
|
|
|
//}
|
|
|
//// 字符串类型的主键
|
|
|
//cStr := ContentStrPK{}
|
|
|
//if err := DB.First(&cStr, "id = ?", "some pk").Error; err != nil {
|
|
|
// //if err := DB.First(&cStr, "some pk").Error; err != nil {
|
|
|
// log.Println(err)
|
|
|
//}
|
|
|
//
|
|
|
//// 查询多条
|
|
|
//var cs []Content
|
|
|
//if err := DB.Find(&cs, []uint{10, 11, 12}).Error; err != nil {
|
|
|
// log.Println(err)
|
|
|
//}
|
|
|
// 字符串类型的主键
|
|
|
var cStrs []ContentStrPK
|
|
|
if err := DB.Find(&cStrs, []string{"some", "pk", "item"}).Error; err != nil {
|
|
|
log.Println(err)
|
|
|
}
|
|
|
}
|
|
|
|
|
|
func GetOne() {
|
|
|
c := Content{}
|
|
|
if err := DB.First(&c, "id > ?", 42).Error; err != nil {
|
|
|
log.Println(err)
|
|
|
}
|
|
|
|
|
|
o := Content{}
|
|
|
if err := DB.Last(&o, "id > ?", 42).Error; err != nil {
|
|
|
log.Println(err)
|
|
|
}
|
|
|
|
|
|
n := Content{}
|
|
|
if err := DB.Take(&n, "id > ?", 42).Error; err != nil {
|
|
|
log.Println(err)
|
|
|
}
|
|
|
|
|
|
f := Content{}
|
|
|
if err := DB.Limit(1).Find(&f, "id > ?", 42).Error; err != nil {
|
|
|
log.Println(err)
|
|
|
}
|
|
|
|
|
|
fs := Content{}
|
|
|
if err := DB.Find(&fs, "id > ?", 42).Error; err != nil {
|
|
|
log.Println(err)
|
|
|
}
|
|
|
}
|
|
|
|
|
|
func GetToMap() {
|
|
|
// 单条
|
|
|
c := map[string]any{} //map[string]interface{}{}
|
|
|
if err := DB.Model(&Content{}).First(&c, 13).Error; err != nil {
|
|
|
log.Println(err)
|
|
|
}
|
|
|
//fmt.Println(c["id"], c["id"].(uint) == 13)
|
|
|
// 需要接口类型断言，才能继续处理
|
|
|
if c["id"].(uint) == 13 {
|
|
|
fmt.Println("id bingo")
|
|
|
}
|
|
|
// time类型的处理
|
|
|
fmt.Println(c["created_at"])
|
|
|
t, err := time.Parse("2006-01-02 15:04:05.000 -0700 CST", "2023-04-10 22:00:11.582 +0800 CST")
|
|
|
if err != nil {
|
|
|
log.Println(err)
|
|
|
}
|
|
|
if c["created_at"].(time.Time) == t {
|
|
|
fmt.Println("created_at bingo")
|
|
|
}
|
|
|
|
|
|
// 多条
|
|
|
var cs []map[string]any
|
|
|
if err := DB.Model(&Content{}).Find(&cs, []uint{13, 14, 15}).Error; err != nil {
|
|
|
log.Println(err)
|
|
|
}
|
|
|
for _, c := range cs {
|
|
|
fmt.Println(c["id"].(uint), c["subject"].(string), c["created_at"].(time.Time))
|
|
|
}
|
|
|
}
|
|
|
|
|
|
func GetPluck() {
|
|
|
// 使用切片存储
|
|
|
var subjects []sql.NullString
|
|
|
//var subjects []string
|
|
|
if err := DB.Model(&Content{}).Pluck("subject", &subjects).Error; err != nil {
|
|
|
log.Println(err)
|
|
|
}
|
|
|
|
|
|
for _, subject := range subjects {
|
|
|
//NullString的使用
|
|
|
if subject.Valid {
|
|
|
fmt.Println(subject.String)
|
|
|
} else {
|
|
|
fmt.Println("[NULL]")
|
|
|
}
|
|
|
|
|
|
//
|
|
|
//fmt.Println(subject)
|
|
|
}
|
|
|
|
|
|
}
|
|
|
|
|
|
func GetPluckExp() {
|
|
|
// 使用切片存储，如果表达式可以保证NULL不会出现了，就可以不适用NullType了
|
|
|
var subjects []string
|
|
|
// 字段为表达式的结果
|
|
|
if err := DB.Model(&Content{}).Pluck("concat(coalesce(subject, '[no subject]'), '-', likes)", &subjects).Error; err != nil {
|
|
|
log.Println(err)
|
|
|
}
|
|
|
|
|
|
for _, subject := range subjects {
|
|
|
fmt.Println(subject)
|
|
|
}
|
|
|
}
|
|
|
|
|
|
func GetSelect() {
|
|
|
var c Content
|
|
|
|
|
|
// 基本的字段名
|
|
|
//if err := DB.Select("subject", "likes").First(&c, 13).Error; err != nil {
|
|
|
// log.Fatalln(err)
|
|
|
//}
|
|
|
|
|
|
// 字段表达式
|
|
|
if err := DB.Select("subject", "likes", "concat(subject,'-', views) AS sv").First(&c, 13).Error; err != nil {
|
|
|
log.Fatalln(err)
|
|
|
}
|
|
|
|
|
|
fmt.Printf("%+v\n", c)
|
|
|
}
|
|
|
|
|
|
func GetDistinct() {
|
|
|
var cs []Content
|
|
|
|
|
|
// 基本的字段名
|
|
|
if err := DB.Distinct("*").Find(&cs).Error; err != nil {
|
|
|
log.Fatalln(err)
|
|
|
}
|
|
|
|
|
|
fmt.Printf("%+v\n", cs)
|
|
|
|
|
|
}
|
|
|
|
|
|
func WhereMethod() {
|
|
|
var cs []Content
|
|
|
|
|
|
// inline条件，内联条件
|
|
|
//if err := DB.Find(&cs, "likes > ? AND subject like ?", 100, "gorm%").Error; err != nil {
|
|
|
// log.Fatalln(err)
|
|
|
//}
|
|
|
// SELECT * FROM `msb_content` WHERE (likes > 100 AND subject like 'gorm%') AND `msb_content`.`deleted_at` IS NULL
|
|
|
|
|
|
// Where,通常在动态拼凑条件时使用
|
|
|
//query := DB.Where("likes > ?", 100)
|
|
|
//subject := ""
|
|
|
//// 当前用户输出subject，不为空字符串时，才拼凑subject条件
|
|
|
//if subject != "" {
|
|
|
// query.Where("subject like ?", subject+"%")
|
|
|
//}
|
|
|
//if err := query.Find(&cs).Error; err != nil {
|
|
|
// log.Fatalln(err)
|
|
|
//}
|
|
|
// SELECT * FROM `msb_content` WHERE likes > 100 AND `msb_content`.`deleted_at` IS NULL
|
|
|
|
|
|
// OR 逻辑运算
|
|
|
//query := DB.Where("likes > ?", 100)
|
|
|
//subject := "gorm"
|
|
|
//// 当前用户输出subject，不为空字符串时，才拼凑subject条件
|
|
|
//if subject != "" {
|
|
|
// //query.Where("subject like ?", "subject"+"%")
|
|
|
// query.Or("subject like ?", subject+"%")
|
|
|
//}
|
|
|
//if err := query.Find(&cs).Error; err != nil {
|
|
|
// log.Fatalln(err)
|
|
|
//}
|
|
|
// SELECT * FROM `msb_content` WHERE (likes > 100 OR subject like 'gorm%') AND `msb_content`.`deleted_at` IS NULL
|
|
|
|
|
|
// Not 逻辑运算
|
|
|
query := DB.Where("likes > ?", 100)
|
|
|
subject := "gorm"
|
|
|
// 当前用户输出subject，不为空字符串时，才拼凑subject条件
|
|
|
if subject != "" {
|
|
|
//query.Not("subject like ?", subject+"%")
|
|
|
// SELECT * FROM `msb_content` WHERE likes > 100 AND NOT subject like 'gorm%' AND `msb_content`.`deleted_at` IS NULL
|
|
|
|
|
|
query = query.Or(DB.Not("subject like ?", subject+"%"))
|
|
|
// SELECT * FROM `msb_content` WHERE (likes > 100 OR NOT subject like 'gorm%') AND `msb_content`.`deleted_at` IS NULL
|
|
|
|
|
|
}
|
|
|
if err := query.Find(&cs).Error; err != nil {
|
|
|
log.Fatalln(err)
|
|
|
}
|
|
|
|
|
|
}
|
|
|
|
|
|
func WhereType() {
|
|
|
var cs []Content
|
|
|
|
|
|
// 嵌套分组构建复杂的逻辑运算
|
|
|
// (1 or 2) and (3 and (4 or 5))
|
|
|
// 1 or 2
|
|
|
//condA := DB.Where("likes > ?", 10).Or("likes <= ?", 100)
|
|
|
// 3 and (4 or 5)
|
|
|
//condB := DB.Where("views > ?", 20).Where(DB.Where("views <= ?", 200).Or("subject like ?", "gorm%"))
|
|
|
//query := DB.Where(condA).Where(condB)
|
|
|
// SELECT * FROM `msb_content` WHERE (likes > 10 OR likes <= 100) AND (views > 20 AND (views <= 200 OR subject like 'gorm%')) AND `msb_content`.`deleted_at` IS NULL
|
|
|
|
|
|
// map构建条件, and, = in
|
|
|
//query := DB.Where(map[string]any{
|
|
|
// "views": 100,
|
|
|
// "id": []uint{1, 2, 3, 4, 5},
|
|
|
//})
|
|
|
// SELECT * FROM `msb_content` WHERE `id` IN (1,2,3,4,5) AND `views` = 100 AND `msb_content`.`deleted_at` IS NULL
|
|
|
|
|
|
// struct条件构建, and, =
|
|
|
query := DB.Where(Content{
|
|
|
Views: 100,
|
|
|
Subject: "GORM",
|
|
|
})
|
|
|
// SELECT * FROM `msb_content` WHERE `msb_content`.`subject` = 'GORM' AND `msb_content`.`views` = 100 AND `msb_content`.`deleted_at` IS NULL
|
|
|
|
|
|
if err := query.Find(&cs).Error; err != nil {
|
|
|
log.Fatalln(err)
|
|
|
}
|
|
|
}
|
|
|
|
|
|
func PlaceHolder() {
|
|
|
var cs []Content
|
|
|
|
|
|
// 匿名
|
|
|
//query := DB.Where("likes = ? AND subject like ?", 100, "gorm%")
|
|
|
|
|
|
// 具名，绑定名字sql.Named()结构
|
|
|
//query := DB.Where("likes = @like AND subject like @subject", sql.Named("subject", "gorm%"), sql.Named("like", 100))
|
|
|
// SELECT * FROM `msb_content` WHERE (likes = 100 AND subject like 'gorm%') AND `msb_content`.`deleted_at` IS NULL
|
|
|
|
|
|
// gorm还支持使用map的形式具名绑定
|
|
|
query := DB.Where("likes = @like AND subject like @subject", map[string]any{
|
|
|
"subject": "gorm%",
|
|
|
"like": 100,
|
|
|
})
|
|
|
// SELECT * FROM `msb_content` WHERE (likes = 100 AND subject like 'gorm%') AND `msb_content`.`deleted_at` IS NULL
|
|
|
|
|
|
if err := query.Find(&cs).Error; err != nil {
|
|
|
log.Fatalln(err)
|
|
|
}
|
|
|
}
|
|
|
|
|
|
func OrderBy() {
|
|
|
var cs []Content
|
|
|
|
|
|
ids := []uint{2, 3, 1}
|
|
|
//query := DB.Order("FIELD(id, 2, 3, 1)")
|
|
|
|
|
|
query := DB.Clauses(clause.OrderBy{
|
|
|
Expression: clause.Expr{
|
|
|
SQL: "FIELD(id, ?)",
|
|
|
Vars: []any{ids},
|
|
|
WithoutParentheses: true,
|
|
|
},
|
|
|
})
|
|
|
// SELECT * FROM `msb_content` WHERE `msb_content`.`id` IN (2,3,1) AND `msb_content`.`deleted_at` IS NULL ORDER BY FIELD(id, 2,3,1)
|
|
|
|
|
|
if err := query.Find(&cs, ids).Error; err != nil {
|
|
|
log.Fatalln(err)
|
|
|
}
|
|
|
|
|
|
for _, c := range cs {
|
|
|
fmt.Println(c.ID)
|
|
|
}
|
|
|
}
|
|
|
|
|
|
// 定义分页必要数据结构
|
|
|
type Pager struct {
|
|
|
Page, PageSize int
|
|
|
}
|
|
|
|
|
|
// 默认的值
|
|
|
const (
|
|
|
DefaultPage = 1
|
|
|
DefaultPageSize = 12
|
|
|
)
|
|
|
|
|
|
// 翻页程序
|
|
|
func Pagination(pager Pager) {
|
|
|
// 确定page, offset 和 pagesize
|
|
|
|
|
|
page := DefaultPage
|
|
|
if pager.Page != 0 {
|
|
|
page = pager.Page
|
|
|
}
|
|
|
|
|
|
pagesize := DefaultPageSize
|
|
|
if pager.PageSize != 0 {
|
|
|
pagesize = pager.PageSize
|
|
|
}
|
|
|
|
|
|
// 计算offset
|
|
|
// page, pagesize, offset
|
|
|
// 1, 10, 0
|
|
|
// 2, 10, 10
|
|
|
// 3, 10, 20
|
|
|
offset := pagesize * (page - 1)
|
|
|
|
|
|
var cs []Content
|
|
|
// SELECT * FROM `msb_content` WHERE `msb_content`.`deleted_at` IS NULL LIMIT 15 OFFSET 30
|
|
|
if err := DB.Offset(offset).Limit(pagesize).Find(&cs).Error; err != nil {
|
|
|
log.Fatalln(err)
|
|
|
}
|
|
|
}
|
|
|
|
|
|
// 用于得到func(db *gorm.DB) *gorm.DB类型函数
|
|
|
// 为什么不直接定义函数，因为需要func(db *gorm.DB) *gorm.DB与分页信息产生联系。
|
|
|
func Paginate(pager Pager) func(db *gorm.DB) *gorm.DB {
|
|
|
// 计算page
|
|
|
page := DefaultPage
|
|
|
if pager.Page != 0 {
|
|
|
page = pager.Page
|
|
|
}
|
|
|
|
|
|
// 计算pagesize
|
|
|
pagesize := DefaultPageSize
|
|
|
if pager.PageSize != 0 {
|
|
|
pagesize = pager.PageSize
|
|
|
}
|
|
|
|
|
|
// 计算offset
|
|
|
// page, pagesize, offset
|
|
|
// 1, 10, 0
|
|
|
// 2, 10, 10
|
|
|
// 3, 10, 20
|
|
|
offset := pagesize * (page - 1)
|
|
|
|
|
|
return func(db *gorm.DB) *gorm.DB {
|
|
|
// 使用闭包的变量，实现翻页的业务逻辑
|
|
|
return db.Offset(offset).Limit(pagesize)
|
|
|
}
|
|
|
}
|
|
|
|
|
|
// 测试重用的分页查询
|
|
|
func PaginationScope(pager Pager) {
|
|
|
|
|
|
var cs []Content
|
|
|
// SELECT * FROM `msb_content` WHERE `msb_content`.`deleted_at` IS NULL LIMIT 15 OFFSET 30
|
|
|
if err := DB.Scopes(Paginate(pager)).Find(&cs).Error; err != nil {
|
|
|
log.Fatalln(err)
|
|
|
}
|
|
|
|
|
|
var ps []Post
|
|
|
// SELECT * FROM `msb_post` WHERE `msb_content`.`deleted_at` IS NULL LIMIT 15 OFFSET 30
|
|
|
if err := DB.Scopes(Paginate(pager)).Find(&ps).Error; err != nil {
|
|
|
log.Fatalln(err)
|
|
|
}
|
|
|
}
|
|
|
|
|
|
func Count(pager Pager) {
|
|
|
|
|
|
// 集中的条件，用于统计数量和获取某页记录
|
|
|
query := DB.Model(&Content{}).
|
|
|
Where("likes > ?", 99)
|
|
|
|
|
|
// total rows count
|
|
|
var count int64
|
|
|
if err := query.Count(&count).Error; err != nil {
|
|
|
log.Fatalln(err)
|
|
|
}
|
|
|
// SELECT count(*) FROM `msb_content` WHERE likes > 99 AND `msb_content`.`deleted_at` IS NULL
|
|
|
// 计算总页数 ceil( count / pagesize)
|
|
|
|
|
|
// rows per page
|
|
|
var cs []Content
|
|
|
if err := query.Scopes(Paginate(pager)).Find(&cs).Error; err != nil {
|
|
|
log.Fatalln(err)
|
|
|
}
|
|
|
// SELECT * FROM `msb_content` WHERE likes > 99 AND `msb_content`.`deleted_at` IS NULL LIMIT 15 OFFSET 30
|
|
|
}
|
|
|
|
|
|
func GroupHaving() {
|
|
|
DB.AutoMigrate(&Content{})
|
|
|
|
|
|
// 定义查询结构类型
|
|
|
type Result struct {
|
|
|
// 分组字段
|
|
|
AuthorID uint
|
|
|
|
|
|
// 合计字段
|
|
|
TotalViews int
|
|
|
TotalLikes int
|
|
|
AvgViews float64
|
|
|
}
|
|
|
|
|
|
// 执行分组合计过滤查询
|
|
|
var rs []Result
|
|
|
if err := DB.Model(&Content{}).
|
|
|
Select("author_id", "SUM(views) as total_views", "SUM(likes) as total_likes", "AVG(views) as avg_views").
|
|
|
Group("author_id").Having("total_views > ?", 99).
|
|
|
Find(&rs).Error; err != nil {
|
|
|
log.Fatalln(err)
|
|
|
}
|
|
|
// SQL
|
|
|
// SELECT `author_id`,SUM(views) as total_views,SUM(likes) as total_likes,AVG(views) as avg_views FROM `msb_content` WHERE `msb_content`.`deleted_at` IS NULL GROUP BY `author_id` HAVING total_views > 99
|
|
|
|
|
|
}
|
|
|
|
|
|
func Iterator() {
|
|
|
// 利用DB.Rows() 获取Rows对象
|
|
|
rows, err := DB.Model(&Content{}).Rows()
|
|
|
if err != nil {
|
|
|
log.Fatalln(err)
|
|
|
}
|
|
|
// [rows:-] SELECT * FROM `msb_content` WHERE `msb_content`.`deleted_at` IS NULL
|
|
|
|
|
|
// 注意：保证使用过后关闭rows结果集
|
|
|
defer func() {
|
|
|
_ = rows.Close()
|
|
|
}()
|
|
|
fmt.Println(rows)
|
|
|
|
|
|
// 迭代的从Rows中扫描记录到模型
|
|
|
for rows.Next() {
|
|
|
// 还有记录存在与结果集中
|
|
|
var c Content
|
|
|
if err := DB.ScanRows(rows, &c); err != nil {
|
|
|
log.Fatalln(err)
|
|
|
}
|
|
|
fmt.Println(c.Subject)
|
|
|
}
|
|
|
}
|
|
|
|
|
|
func Locking() {
|
|
|
var cs []Content
|
|
|
|
|
|
if err := DB.
|
|
|
Clauses(clause.Locking{Strength: "UPDATE"}).
|
|
|
Find(&cs).Error; err != nil {
|
|
|
log.Fatalln(err)
|
|
|
}
|
|
|
// [4.998ms] [rows:42] SELECT * FROM `msb_content` WHERE `msb_content`.`deleted_at` IS NULL FOR UPDATE
|
|
|
|
|
|
if err := DB.
|
|
|
Clauses(clause.Locking{Strength: "SHARE"}).
|
|
|
Find(&cs).Error; err != nil {
|
|
|
log.Fatalln(err)
|
|
|
}
|
|
|
// [3.006ms] [rows:42] SELECT * FROM `msb_content` WHERE `msb_content`.`deleted_at` IS NULL FOR SHARE
|
|
|
}
|
|
|
|
|
|
func SubQuery() {
|
|
|
// migrate
|
|
|
DB.AutoMigrate(&Author{}, &Content{})
|
|
|
|
|
|
// 条件型子查询
|
|
|
//select * from content where author_id in (select id from author where status=0);
|
|
|
// 子查询，不需要使用终结方法Find完成查询，只需要构建语句即可
|
|
|
whereSubQuery := DB.Model(&Author{}).Select("id").Where("status = ?", 0)
|
|
|
var cs []Content
|
|
|
if err := DB.Where("author_id IN (?)", whereSubQuery).Find(&cs).Error; err != nil {
|
|
|
log.Fatalln(err)
|
|
|
}
|
|
|
// [4.782ms] [rows:0] SELECT * FROM `msb_content` WHERE author_id IN (SELECT `id` FROM `msb_author` WHERE status = 0 AND `msb_author`.`deleted_at` IS NULL) AND `msb_content`.`deleted_at` IS NULL
|
|
|
|
|
|
// from型子查询
|
|
|
//select * from (select subject, likes from content where publish_time is null) as temp where likes > 10;
|
|
|
fromSubQuery := DB.Model(&Content{}).Where("publish_time IS NULL").Select("subject", "likes")
|
|
|
type Result struct {
|
|
|
Subject string
|
|
|
Likes int
|
|
|
}
|
|
|
var rs []Result
|
|
|
if err := DB.Table("(?) AS temp", fromSubQuery).
|
|
|
Where("likes > ?", 10).
|
|
|
Find(&rs).Error; err != nil {
|
|
|
log.Fatalln(err)
|
|
|
}
|
|
|
// [3.800ms] [rows:17] SELECT * FROM (SELECT `subject`,`likes` FROM `msb_content` WHERE publish_time IS NULL AND `msb_content`.`deleted_at` IS NULL) AS temp WHERE likes > 10
|
|
|
}
|
|
|
|
|
|
func FindHook() {
|
|
|
var c Content
|
|
|
if err := DB.First(&c, 13).Error; err != nil {
|
|
|
log.Fatalln(err)
|
|
|
}
|
|
|
|
|
|
fmt.Printf("%+v\n", c)
|
|
|
}
|
