CRUD

Kesa...大约 28 分钟golanggorm

5. CRUD

5.1 Create

5.1.1 创建记录

package main

import (
	"fmt"
	"log"
	"time"

	"gorm.io/gorm/logger"

	"gorm.io/driver/sqlite"
	"gorm.io/gorm"
)

type User struct {
	gorm.Model
	Name     string
	Age      int
	Birthday time.Time
}

const (
	timeFormat = "2006-01-02 15:04:05.000"
)

func (u User) String() string {
	return fmt.Sprintf("ID: %d, Name: %s, Age: %d, Birthday: %s\nCreatedAy: %s, UpadtedAt: %s, DeletedAt: %s",
		u.ID, u.Name, u.Age, u.Birthday.Format(timeFormat), u.CreatedAt.Format(timeFormat), u.UpdatedAt.Format(timeFormat),
		u.DeletedAt.Time.Format(timeFormat))
}

func main() {
	db, err := gorm.Open(sqlite.Open("create.db"), &gorm.Config{
		Logger: logger.Default.LogMode(logger.Info),
	})
	if err != nil {
		panic("failed to connect database")
	}
	sqlDB, _ := db.DB()
	defer sqlDB.Close()
	db.AutoMigrate(&User{})

	user := User{Name: "kesa", Age: 18, Birthday: time.Now()}
	result := db.Create(&user)
	log.Printf("New record ID: %d", user.ID)
	log.Printf("Error %v", result.Error)
	log.Printf("Rows affected: %d", result.RowsAffected)
}
  • db.Create: 通过数据的指针创建记录,会将插入的数据的主键写入到对象中，所以这里必须是指针类型
  • result.Error: 执行错误，nil 为成功
  • result.RowsAffected: 影响记录数

result 的类型为 tx *DB:

// DB GORM DB definition
type DB struct {
	*Config
	Error        error
	RowsAffected int64
	Statement    *Statement
	clone        int
}

5.1.2 用指定字段创建记录

// ...
func main() {
	// ...
	user := User{Name: "kesa", Age: 18, Birthday: time.Now()}
	result := db.Select("Name", "Age", "CreatedAt").Create(&user)
	log.Printf("New record ID: %d", user.ID)
	log.Printf("Error %v", result.Error)
	log.Printf("Rows affected: %d", result.RowsAffected)

	user1 := User{Name: "kesa", Age: 18, Birthday: time.Now()}
	result = db.Omit("Name", "Age", "CreatedAt").Create(&user1)
	log.Printf("New record ID: %d", user.ID)
	log.Printf("Error %v", result.Error)
	log.Printf("Rows affected: %d", result.RowsAffected)
}
  • Select: 创建记录并更新指定字段
  • omit: 创建记录但忽略指定字段

执行程序，并查询数据：

sqlite> select * from users where id=11 or id = 12;
id  created_at                           updated_at                           deleted_at  name  age  birthday                           
--  -----------------------------------  -----------------------------------  ----------  ----  ---  -----------------------------------
11  2021-11-25 17:29:25.000032484+08:00  2021-11-25 17:29:25.000032484+08:00              kesa  18                                      
12                                       2021-11-25 17:29:26.071051452+08:00                         2021-11-25 17:29:26.070793799+08:00

5.1.3 批量插入

批量插入数据可以将slice作为参数传给Create方法， GORM 将会单独生成一条 SQL 来执行并回填主键值，钩子方法也将被调用

// ...
func main() {
	// ...
	var users = []User{{Name: "user_1"}, {Name: "user_2"}, {Name: "user_3"}}
	result := db.Create(&users)
	log.Printf("Rows affected: %d,Error: %s", result.RowsAffected, result.Error.Error())
	for _, user := range users {
		log.Printf("Inserted ID: %d", user.ID)
	}

}

通过 GORM 的日志可以看到，Create 方法通过一条 SQL 插入了所有数据

INSERT INTO `users` (`created_at`,`updated_at`,`deleted_at`,`name`,`age`,`birthday`) VALUES ("2021-11-25 17:54:34.823","2021-11-25 17:54:34.823",NULL,"user_1",0,"0000-00-00 00:00:00"),("2021-11-25 17:54:34.823","2021-11-25 17:54:34.823",NULL,"user_2",0,"0000-00-00 00:00:00"),("2021-11-25 17:54:34.823","2021-11-25 17:54:34.823",NULL,"user_3",0,"0000-00-00 00:00:00") RETURNING `id`

使用CreateInBatches进行分批创建，可以指定每批的数量

var users1 = []User{{Name: "user_1"}, {Name: "user_2"}, {Name: "user_3"}, {Name: "user_4"}, {Name: "user_5"}, {Name: "user_6"}}
	result = db.CreateInBatches(users1, 2)
	log.Printf("Rows affected: %d,Error: %v", result.RowsAffected, result.Error)
	for _, user := range users1 {
		log.Printf("Inserted ID: %d", user.ID)
	}

启动后查看 GORM 日志可以看到 SQL 分为了三条执行

Upsert 和 Create With Associations也支持批量插入

注意

使用 CreateBatchSize 选项初始化 GORM 时，所有的创建和关联 INSERT 遵循该选项

修改上例中的 GORM 配置，添加 CreateBatchSize 为 3

db, err := gorm.Open(sqlite.Open("create-in-batches.db"), &gorm.Config{
		Logger:          logger.Default.LogMode(logger.Info),
		CreateBatchSize: 3,
	})
// ...
var users = []User{{Name: "user_1"}, {Name: "user_2"}, {Name: "user_3"}}
	result := db.Create(&users)
	log.Printf("Rows affected: %d,Error: %s", result.RowsAffected, result.Error.Error())
	for _, user := range users {
		log.Printf("Inserted ID: %d", user.ID)
	}
// ...

再次执行程序可以看到 Create 语句插入三条记录时只会执行一条 SQL 了

5.1.4 钩子函数

GORM 创建相关的钩子函数有 BeforeSave, BeforeCreate, AfterSave, AfterCreate ，创建记录时将调用这些钩子函数

钩子（Hook）是在创建，查询，更新，删除等操作之前，之后调用的函数

若函数返回错误，GORM 将停止后续的操作并回滚事务

钩子函数的签名为 func(*gorm.DB) error

创建操作时 hook 的调用顺序如下：

// 开始事务
BeforeSave
BeforeCreate
// 关联前的 save
// 插入记录至 db
// 关联后的 save
AfterCreate
AfterSave
// 提交或回滚事务

示例：

// ...
func (User) BeforeCreate(db *gorm.DB) error {
	log.Println("Before Create")
	return nil
}

func (User) BeforeSave(db *gorm.DB) error {
	log.Println("Before Save")
	return nil
}

func (User) AfterCreate(db *gorm.DB) error {
	log.Println("After Create")
	return nil
}

func (u User) AfterSave(db *gorm.DB) error {
	log.Println("After Save")
	if u.Age < 21 {
		return errors.New("illegal age,roll back")
	}
	return nil
}

func main() {
	//...
	user := User{Name: "kesa", Age: 18, Birthday: time.Now()}
	result := db.Create(&user)
	log.Printf("Rows affected: %d, Error: %v", result.RowsAffected, result.Error)
	log.Printf("New record ID: %d", user.ID)
}

上例在 AfterSave 钩子中加入了返回错误的条件，此时再次插入数据，将会返回错误，GORM 会将事务回滚

查询数据库可以看到数据库是没有数据的

5.1.5 根据 Map 创建

GORM 支持根据 map[string]interface{} 和 []map[string]interface{} 创建记录

	data := map[string]interface{}{
		"Name": "kesa-map",
		"Age":  18,
	}
	result := db.Model(&User{}).Create(data)
	log.Printf("Error %v", result.Error)
	log.Printf("Rows affected: %d", result.RowsAffected)

	datas := []map[string]interface{}{
		{"Name": "kesa_map_1", "Age": 10},
		{"Name": "kesa_map_2", "Age": 11},
		{"Name": "kesa_map_3", "Age": 12},
	}
	result = db.Model(&User{}).Create(datas)
	log.Printf("Error %v", result.Error)
	log.Printf("Rows affected: %d", result.RowsAffected)

注意

使用 map 创建记录时， association 不会调用，map 中未涉及的字段也不会被更新

TODO:: 5.1.6 使用 SQL 表达式 Context Valuer 创建记录

// TODO

5.1.7 关联创建

创建关联数据时，若关联值为非零值，这些关联会被 upsert ，且它们的 Hook 方法也会被调用

package main

import (
	"log"

	"gorm.io/gorm/clause"

	"gorm.io/gorm/logger"

	"gorm.io/driver/sqlite"
	"gorm.io/gorm"
)

type User struct {
	gorm.Model
	Name       string
	CreditCard CreditCard
}

type CreditCard struct {
	gorm.Model
	Number string
	UserID uint
}

func main() {
	// ... db connection

	db.AutoMigrate(&User{})
	db.AutoMigrate(&CreditCard{})

	user := User{Name: "kesa", CreditCard: CreditCard{Number: "12346"}}
	result := db.Create(&user)
	log.Printf("New user ID: %d, CreditCard ID: %d", user.ID, user.CreditCard.ID)
	log.Printf("Rows affected: %d, Error: %v", result.RowsAffected, result.Error)

	user1 := User{Name: "kesa", CreditCard: CreditCard{Number: "78910"}}
	result = db.Omit(clause.Associations).Create(&user1)
	log.Printf("New user ID: %d, CreditCard ID: %d", user1.ID, user1.CreditCard.ID)
	log.Printf("Rows affected: %d, Error: %v", result.RowsAffected, result.Error)

}
  • db.Omit(clause.Associations).Create(&user1): 跳过所有的关联

也以通过 Select,Omit 方法来跳过关联保存

5.1.8 默认值

可以通过标签 default 为字段定义默认值

type User struct {
  ID   int64
  Name string `gorm:"default:galeone"`
  Age  int64  `gorm:"default:18"`
}

插入记录到数据库时，默认值会被用于填充值为零值的字段

注意

像0,'',false等零值，不会将这些字段定义的默认值保存到数据库，需要使用指针类型或 Scanner/Valuer 来避免这个问题，例如：

type User struct {
  gorm.Model
  Name string
  Age  *int           `gorm:"default:18"`
  Active sql.NullBool `gorm:"default:true"`
}

若要数据库有默认值，必须为字段设置 default 标签，使用 default:(-) 在迁移时跳过默认值定义

type User struct {
  ID        string `gorm:"default:uuid_generate_v3()"` // db func
  FirstName string
  LastName  string
  Age       uint8
  FullName  string `gorm:"->;type:GENERATED ALWAYS AS (concat(firstname,' ',lastname));default:(-);"`
}

使用 virtual/generate 值时，需要禁用其创建，更新的权限

5.1.9 Upset 及冲突

GORM 为不同数据库提供了兼容的 Upsert 支持

import "gorm.io/gorm/clause"

// 在冲突时，什么都不做
db.Clauses(clause.OnConflict{DoNothing: true}).Create(&user)

// 在`id`冲突时，将列更新为默认值
db.Clauses(clause.OnConflict{
  Columns:   []clause.Column{{Name: "id"}},
  DoUpdates: clause.Assignments(map[string]interface{}{"role": "user"}),
}).Create(&users)
// MERGE INTO "users" USING *** WHEN NOT MATCHED THEN INSERT *** WHEN MATCHED THEN UPDATE SET ***; SQL Server
// INSERT INTO `users` *** ON DUPLICATE KEY UPDATE ***; MySQL

// 使用SQL语句
db.Clauses(clause.OnConflict{
  Columns:   []clause.Column{{Name: "id"}},
  DoUpdates: clause.Assignments(map[string]interface{}{"count": gorm.Expr("GREATEST(count, VALUES(count))")}),
}).Create(&users)
// INSERT INTO `users` *** ON DUPLICATE KEY UPDATE `count`=GREATEST(count, VALUES(count));

// 在`id`冲突时，将列更新为新值
db.Clauses(clause.OnConflict{
  Columns:   []clause.Column{{Name: "id"}},
  DoUpdates: clause.AssignmentColumns([]string{"name", "age"}),
}).Create(&users)
// MERGE INTO "users" USING *** WHEN NOT MATCHED THEN INSERT *** WHEN MATCHED THEN UPDATE SET "name"="excluded"."name"; SQL Server
// INSERT INTO "users" *** ON CONFLICT ("id") DO UPDATE SET "name"="excluded"."name", "age"="excluded"."age"; PostgreSQL
// INSERT INTO `users` *** ON DUPLICATE KEY UPDATE `name`=VALUES(name),`age=VALUES(age); MySQL

// 在冲突时，更新除主键以外的所有列到新值。
db.Clauses(clause.OnConflict{
  UpdateAll: true,
}).Create(&users)
// INSERT INTO "users" *** ON CONFLICT ("id") DO UPDATE SET "name"="excluded"."name", "age"="excluded"."age", ...;

5.2 Read

5.2.1 检索单个对象

GORM 提供了 First, Take, Last 方法，以便从数据库中检索单个对象。

查询数据库时会添加 LIMIT 1 条件，若未找到记录会返回 ErrRecordNotFound

package main

import (
	"log"
	"strconv"

	"gorm.io/driver/sqlite"
	"gorm.io/gorm"
	"gorm.io/gorm/logger"
)

type User struct {
	ID   uint `gorm:"primaryKey"`
	Name string
	Age  int
}

func main() {
	db, err := gorm.Open(sqlite.Open("single-obj.db"), &gorm.Config{
		Logger: logger.Default.LogMode(logger.Info),
	})
	if err != nil {
		log.Fatal("failed to connect db: ", err.Error())
	}
	sqlDB, _ := db.DB()
	defer sqlDB.Close()

	db.AutoMigrate(&User{})
	CreateUsers(db)

	var user, user1, user2 User
	db.First(&user)
	db.Take(&user1)
	db.Last(&user2)

	log.Printf("First user: %+v", user)
	log.Printf("One  of users: %+v", user1)
	log.Printf("Last user: %+v", user2)
}

func CreateUsers(db *gorm.DB) {
	users := make([]User, 10)
	for i := 1; i < 11; i++ {
		name := "user_" + strconv.Itoa(i)
		age := 10 + i
		users[i-1] = User{Name: name, Age: age}
	}
	db.Create(&users)
}
  • First: 获取第一条记录，主键升序 SQL: SELECT * FROM users ORDER BY id LIMIT 1;
  • Take: 获取一条记录，无排序字段 SQL: SELECT * FROM users LIMIT 1;
  • Last:获取最后一条记录，主键降序 SQL: SELECT * FROM users ORDER BY id DESC LIMIT 1;

若想要避免 ErrRecordNotFound 错误，可以使用 Find,可以接收 strcut 或 slice 参数

First 和 Last 会根据主键排序，分别查询第一条和最后一条记录，只有在 struct 是指针或通过 db.Model()指定 model 时才有效，若相关的 model 没有定义主键将按照第一个字段排序

// ...
func main() {
 	// ... db init
	var user3 User
	// SELECT * FROM users ORDER BY id LIMIT 1;
	db.First(&user3)
	// SELECT * FROM users ORDER BY id LIMIT 1;
	ret := map[string]interface{}{}
	db.Model(&User{}).First(&ret)
	// invalid
	ret1 := map[string]interface{}{}
	db.Table("users").First(&ret1)
	// SELECT * FROM users LIMIT 1;
	ret2 := map[string]interface{}{}
	db.Table("users").Take(&ret2)
	// SELECT * FROM languages ORDER BY code LIMIT 1;
	type Language struct {
		Code string
		Name string
	}
	db.First(&Language{})
}

5.2.2 用主键检索

若主键是数字型，可以使用内联条件检索，传入字符串参数时，需要注意 SQL 注入问题

// ... 
func main() {
    // ...
	// SELECT * FROM users WHERE id = 10 ORDER BY id LIMIT 1;
	var user User
	db.First(&user, 10)
	log.Printf("Record: %+v", user)
	// SELECT * FROM users WHERE id = 10 ORDER BY id LIMIT 1;
	var user2 User
	db.First(&user2, "10")
	log.Printf("Record: %+v", user2)
	// SELECT * FROM users WHERE id IN (1,2,3)
	var users []User
	db.Find(&users, []int{1, 2, 3})
	log.Printf("Record: %+v", users)
}

如果主键是字符串型：

// SELECT * FROM users WHERE id = '1b74413f-f3b8-409f-ac47-e8c062e3472a'
db.First(&user,"id = ?","1b74413f-f3b8-409f-ac47-e8c062e3472a")

5.2.3 检索全部对象

使用 Find 可以查询所有数据

result := db.Find(&users)

reuslt.RowsAffected 返回找到的记录数，result.Error返回出现的错误

5.2.4 条件查询

String

// ...
type User struct {
	ID   uint `gorm:"primaryKey"`
	Name string
	Age  int
}

func main() {
	// ...
	db.AutoMigrate(&User{})
	CreateUsers(db, 10)

	// String
	StringCondition(db)
}

func CreateUsers(db *gorm.DB, num int) {
	var count int64
	db.Model(&User{}).Count(&count)
	if count > 0 {
		return
	}
	users := make([]User, num)
	for i := 0; i < num; i++ {
		name := "user_" + strconv.Itoa(i+1)
		age := 10 + i
		users[i] = User{Name: name, Age: age}
	}
	db.Create(&users)
}

func PrintRecord(record interface{}) {
	switch val := record.(type) {
	case User:
		log.Printf("Record: %+v", val)
	case []User:
		log.Printf("Records: %+v", val)
	}
}

func StringCondition(db *gorm.DB) {
	// SELECT * FROM users WHERE name = 'user' ORDER BY id LIMIT 1;
	var user User
	db.Where("name = ?", "user").First(&user)
	PrintRecord(user)
	// SELECT * FROM users WHERE name <> 'user';
	var users []User
	db.Where("name <> ?", "user").Find(&users)
	PrintRecord(users)
	// SELECT * FROM users WHERE name IN ('user_1','user_2','user_3')
	var users1 []User
	db.Where("name IN ?", []string{"user_1", "user_2", "user_3"}).Find(&users1)
	PrintRecord(users1)
	// SELECT * FROM users WHERE name LIKE '%user%';
	var users3 []User
	db.Where("name LIKE ?", "%user%").Find(&users3)
	PrintRecord(users3)
	// SELECT * FROM users WHERE name = 'user_1' AND age = 11;
	var users4 []User
	db.Where("name = ? AND age = ?", "user_1", 11).Find(&users4)
	PrintRecord(users4)
	// SELECT * FROM users WHERE age <= 10;
	var users5 []User
	db.Where("age <= ?", 15).Find(&users5)
	PrintRecord(users5)
	// SELECT * FROM users WHERE age BETWEEN 10 AND 16;
	var users6 []User
	db.Where("age BETWEEN ? AND ?", 10, 16).Find(&users6)
	PrintRecord(users6)
}

Where 方法中可以直接使用 SQL 和占位符来构成条件查询

Struct and Map

// ...
func main() {
	// ...
	// Struct and Map
	StructMapCondition(db)
}
// ...
func StructMapCondition(db *gorm.DB) {
	// Struct
	// SELECT * FROM users WHERE name = 'user_1' AND age = 10 ORDER BY id LIMIT 1;
	var user User
	db.Where(&User{Name: "user_1", Age: 10}).First(&user)
	PrintRecord(user)
	// SELECT * FROM users WHERE name = 'user_2' AND age = 11;
	var user2 User
	cond := map[string]interface{}{"Name": "user_2", "Age": 11}
	db.Where(cond).Find(&user2)
	PrintRecord(user2)
	// SELECT * FROM users WHERE id IN (1,2,3)
	var users []User
	db.Where([]int{1, 2, 3}).Find(&users)
	PrintRecord(users)

	// SELECT * FROM users WHERE name = 'user_1' LIMIT 1;
	var user3 User
	db.Where(&User{Name: "user_1", Age: 0}).Take(&user3)
	PrintRecord(user3)
	// SELECT * FROM users WHERE name = 'user_2' AND age = 0 LIMIT 1;
	var user4 User
	db.Where(map[string]interface{}{"Name": "user_2", "Age": 0}).Take(&user4)
	PrintRecord(user4)
	// SELECT * FROM users WHERE name = 'user_3' AND age = 12 LIMIT 1;
	var user5 User
	db.Where(&User{Name: "user_3", Age: 12}, "name", "age").Take(&user5)
	PrintRecord(user5)
	// SELECT * FROM users WHERE age = 0 LIMIT 1;
	var user6 User
	db.Where(&User{Name: "user_3"}, "age").Take(&user6)
	PrintRecord(user6)
}

也可以使用 Struct 和 Map 来构建查询条件

注意： 当使用 Struct 作为查询条件时，只能查询非零值字段，零值字段将不会用于构建查询条件

若需要使用零值字段查询，可以使用 Map 或 指定 Struct 查询字段

  • db.Where(map[string]interface{}{"Name": "user_2", "Age": 0}):使用 map 构建查询条件
  • db.Where(&User{Name: "user_3"}, "age"): 指定 Struct 字段构建查询条件

内联条件

// ...
func main() {
	// ...
	// Inline Conditions
	// SELECT * FROM users WHERE id = 1 ORDER BY id LIMIT 1;
	var user User
	db.First(&user, "id = ?", 1)
	utils.PrintRecord(user)
	// SELECT * FROM users WHERE name = 'user_1' LIMIT 1;
	var user1 User
	db.Take(&user1, "name = ?", "user_1")
	utils.PrintRecord(user1)
	// SELECT * FROM users WHERE name <> 'user_2' AND age >= 15;
	var users []User
	db.Find(&users, "name <> ? AND age >= ?", "user_2", 15)
	utils.PrintRecord(users)
	// SELECT * FROM users WHERE age = 16 LIMIT 1;
	var user2 User
	db.Take(&user2, User{Age: 16})
	utils.PrintRecord(user2)
	// SELECT * FROM users WHERE age = 13 LIMIT 1;
	var user3 User
	db.Take(&user3, map[string]interface{}{"Age": 13})
	utils.PrintRecord(user3)
}
// ...

Not&Or Conditions

// ...
func main() {
	// ...
	// Not Conditions
	// SELECT * FROM users WHERE NOT name = 'user_1' LIMIT 1;
	var users []User
	db.Not("name = ?", "user_1").Find(&users)
	utils.PrintRecord(users)
	// SELECT * FROM users WHERE name NOT IN ('user_2','user_3','user_4');
	var users1 []User
	db.Not(map[string]interface{}{"name": []string{"user_2", "user_3", "user_4"}}).Find(&users1)
	utils.PrintRecord(users1)
	// SELECT * FROM users WHERE name <> 'user_5' AND age <> 11;
	var users2 []User
	db.Not(User{Name: "user_5", Age: 11}).Find(&users2)
	utils.PrintRecord(users2)
	// SELECT * FROM users WHERE id NOT IN (1,2,3);
	var users3 []User
	db.Not([]int64{1, 2, 3}).Find(&users3)
	utils.PrintRecord(users3)

	// Or Conditions
	// SELECT * FROM users WHERE name = 'user_1' OR age = 16;
	var users4 []User
	db.Where("name = ?", "user_1").Or("age = ?", 16).Find(&users4)
	utils.PrintRecord(users4)
	// SELECT * FROM user WHERE name = 'user_2' OR (name = 'user_3' AND age = 13) ;
	var users5 []User
	db.Where("name = 'user_2'").Or(User{Name: "user_3", Age: 13}).Find(&users5)
	utils.PrintRecord(users5)
	// SELECT * FROM users WHERE age = 10 OR (name = 'user_0' AND age = 10);
	var users6 []User
	db.Where("age = ?", 10).Or(map[string]interface{}{"name": "user_0", "age": 10}).Find(&users6)
	utils.PrintRecord(users6)
}
// ...

Not和 Or 和Where 的构造类似

5.2.5 选择特定字段

使用 Select 可以指定查询字段，默认情况会查询所有的字段

// ...
func main() {
	// ...
	// SELECT name FROM users ;
	var users []User
	db.Select("name").Find(&users)
	utils.PrintRecord(users)
	// SELECT name,age FROM users ;
	var users2 []User
	db.Select([]string{"name", "age"}).Find(&users2)
	utils.PrintRecord(users2)
}
// ...

5.2.6 Order

指定查询的排列方式

// ...
func main() {
	// ...
	// SELECT name,age FROM users ORDER BY age desc, name ;
	var users []User
	db.Select("name", "age").Order("age desc,name").Find(&users)
	utils.PrintRecord(users)
	// SELECT name,age FROM users ORDER BY age asc, name desc ;
	var users2 []User
	db.Select("name", "age").Order("age asc").Order("name desc").Find(&users2)
	utils.PrintRecord(users2)
}
// ...

5.2.7 Limit&Offset

// ...
var (
	UserAllFields = []string{"id", "name", "age"}
)
// ...
func main() {
	// ...
	// Limit&Offset
	// SELECT id,name,age FROM users LIMIT 3 ;
	var users []User
	db.Select(UserAllFields).Limit(3).Find(&users)
	utils.PrintRecord(users)
	// SELECT id,name,age FROM users LIMIT 10 ;
	// SELECT id,name,age FROM users ;
	var users1, users2 []User
	db.Select(UserAllFields).Limit(5).Find(&users1).Limit(-1).Find(&users2)
	utils.PrintRecord(users1, users2)
	// SELECT id,name,age FROM users OFFSET 3 ;
	var users3 []User
	db.Select(UserAllFields).Offset(3).Find(&users3)
	utils.PrintRecord(users3)
	// SELECT id,name,age FROM users OFFSET 3 LIMIT 4 ;
	var users4 []User
	db.Select(UserAllFields).Offset(3).Limit(4).Find(&users4)
	utils.PrintRecord(users4)
	// SELECT id,name,age FROM users OFFSET 3;
	// SELECT id,name,age FROM users ;
	var users5, users6 []User
	db.Select(UserAllFields).Offset(3).Find(&users5).Offset(-1).Find(&users6)
	utils.PrintRecord(users5, users6)

}
  • Limit(-1):可取消 LIMIT 条件
  • Offset(-1): 可取消 OFFSET 条件
  • db.Select(UserAllFields).Offset(3).Find(&users5).Offset(-1).Find(&users6) : GORM 可以进行多次查询，调用两次 Find 可以查询两次，并且可以修改查询条件

为了能够打印多个结果，修改utils:

package utils

import (
	"log"
	"reflect"
)

func PrintRecord(record ...interface{}) {
	for _, r := range record {
		printSingle(r)
	}
}

func printSingle(record interface{}) {
	v := reflect.ValueOf(record)
	k := v.Kind()
	switch k {
	case reflect.Struct:
		log.Printf("Record: %+v", v)
	case reflect.Slice:
		log.Printf("Records: %+v", v)
	default:
		log.Printf("%#v", v)
	}
}

5.2.8 Group By & Having

// ...
var (
	GroupAndAvgAge = []string{"group", "AVG(age) as avg_age"}
)

type User struct {
	ID    uint `gorm:"primaryKey"`
	Name  string
	Group string
	Age   int
}

type GrpByResult struct {
	Group  string
	AvgAge float64
}

func main() {
	db, err := gorm.Open(sqlite.Open(DBName), &gorm.Config{
		Logger: logger.Default.LogMode(logger.Info),
	})
	if err != nil {
		log.Fatal("connect db failed: ", err.Error())
	}
	sqlDB, _ := db.DB()
	defer sqlDB.Close()
	db.AutoMigrate(&User{})
	CreateUsers(db, UserCount)

	// Group By & Having
	// SELECT group,AVG(age) as avg_age FROM users GROUP BY `group` ;
	var results []GrpAvgAge
	db.Model(&User{}).Select(GroupAndAvgAge).Group("group").Find(&results)
	utils.PrintRecord(results)
	// SELECT group,AVG(age) as avg_age FROM users GROUP BY `group` HAVING avg_age > 14 ;
	var results1 []GrpAvgAge
	db.Model(&User{}).Select(GroupAndAvgAge).Group("group").Having("avg_age > ?", 14).Find(&results1)
	utils.PrintRecord(results1)
}

func CreateUsers(db *gorm.DB, num int) {
	var count int64
	db.Model(&User{}).Count(&count)
	if count > 0 {
		return
	}
	users := make([]User, num)
	for i := 0; i < num; i++ {
		grp := "group_" + strconv.Itoa(i%3)
		name := "user_" + strconv.Itoa(i)
		age := 10 + i
		users[i] = User{Name: name, Age: age, Group: grp}
	}
	db.Create(&users)
}

5.2.9 Distinct

// ...
	// Distinct
	// SELECT DISTINCT name FROM users ;
	var result []map[string]interface{}
	db.Model(&User{}).Distinct("group").Find(&result)
	utils.PrintRecord(result)
// ...

5.2.10 Joins

// ...
var (
	GroupAndAvgAge = []string{"group", "AVG(age) as avg_age"}
)

type User struct {
	ID    uint `gorm:"primaryKey"`
	Name  string
	Group string
	Age   int
	Email Email
}

type Email struct {
	ID     uint `gorm:"primaryKey"`
	UserID uint
	Email  string
}

func main() {
	db, err := gorm.Open(sqlite.Open(DBName), &gorm.Config{
		Logger: logger.Default.LogMode(logger.Info),
	})
	if err != nil {
		log.Fatal("connect db failed: ", err.Error())
	}
	sqlDB, _ := db.DB()
	defer sqlDB.Close()
	db.AutoMigrate(&User{})
	db.AutoMigrate(&Email{})
	CreateUsers(db, UserCount)

	// JOINS
	// SELECT users.name,emails.email FROM users INNER JOIN emails ON users.id = emails.user_id;
	var results []map[string]interface{}
	db.Model(&User{}).Select("users.name", "emails.email").Joins("INNER JOIN emails ON users.id = emails.user_id").Find(&results)
	utils.PrintRecord(results)	

}

func CreateUsers(db *gorm.DB, num int) {
	var count int64
	db.Model(&User{}).Count(&count)
	if count > 0 {
		return
	}
	users := make([]User, num)
	for i := 0; i < num; i++ {
		grp := "group_" + strconv.Itoa(i%3)
		name := "user_" + strconv.Itoa(i)
		age := 10 + i
		email := name + "@example.com"
		users[i] = User{Name: name, Age: age, Group: grp, Email: Email{Email: email}}
	}
	db.Create(&users)
}

5.2.11 Scan

Scan 和 Find 类似都是将结果解析至 struct/map 中,两者的区别在于 Find 会调用所有注册的钩子函数，而Scan则不会

5.2.12 智能选择字段

GORM 可以通过 Select 选择特定字段，也可以通过结构体来选择字段

type User struct {
  ID     uint
  Name   string
  Age    int
  Gender string
  // 假设后面还有几百个字段...
}

type APIUser struct {
  ID   uint
  Name string
}

// 查询时会自动选择 `id`, `name` 字段
db.Model(&User{}).Limit(10).Find(&APIUser{})
// SELECT `id`, `name` FROM `users` LIMIT 10

5.2.13 Locking

GORM 支持多种类型的锁，例如：

db.Clauses(clause.Locking{Strength: "UPDATE"}).Find(&users)
// SELECT * FROM `users` FOR UPDATE

db.Clauses(clause.Locking{
  Strength: "SHARE",
  Table: clause.Table{Name: clause.CurrentTable},
}).Find(&users)
// SELECT * FROM `users` FOR SHARE OF `users`

5.2.14 SubQuery

A subquery can be nested within a query, GORM can generate subquery when using a *gorm.DB object as param

// ...
	// SubQuery
	// SELECT name,age FROM users WHERE age > (SELECT AVG(age) FROM users) ;
	var results []map[string]interface{}
	subQuery := db.Model(&User{}).Select("AVG(age)")
	db.Model(&User{}).Select("name", "age").Where("age > (?)", subQuery).Find(&results)
	utils.PrintRecord(results)
// ...

5.2.15 From SubQuery

GORM allows you using subquery in FROM clause with method Table：

// SELECT * FROM (SELECT name,age FROM users) as u WHERE age < 15 ;
	var result []map[string]interface{}
	db.Table("(?) as u", db.Model(&User{}).Select("name", "age")).Where("age < ?", 15).Find(&result)
	utils.PrintRecord(result)

5.2.16 Group Conditions

Easier to write complicated SQL query with Group Conditions

// SELECT * FROM users WHERE (name = 'user_0' AND age = 10) OR (age > 15) ;
	var results2 []map[string]interface{}
	db.Model(&User{}).Where(
		db.Where("name = ?", "user_0").Where("age = ?", 10),
	).Or(
		db.Where("age > 15"),
	).Find(&results2)
	utils.PrintRecord(results2)

5.2.17 IN with multiple colums

Selecting IN with multiple columns

	// SELECT * FROM users WHERE (name,age) IN (('user_0', 10),('user_1', 11),('user_2', 12));
	var results3 []map[string]interface{}
	db.Model(&User{}).Where("(name,age) IN ?", [][]interface{}{{"user_0", 10}, {"user_1", 11}, {"user_2", 12}}).Find(results3)
	utils.PrintRecord(results3)

注意：sqlite3 不支持上述写法，在 sqlite3 中要写成

SELECT * FROM users WHERE (name,age) IN (VALUES ('user_0', 10),('user_1', 11),('user_2', 12));

5.2.18 Named Argument

GORM supports named arguments with sql.NamedArg or map[string]interface{}

	// SELECT * FROM users WHERE name = 'user_1' OR age = 16;
	var results4 []map[string]interface{}
	db.Model(&User{}).
		Select("name", "age").
		Where("name = @name OR age = @age", sql.Named("name", "user_0"), sql.Named("age", 16)).
		Find(&results4)
	utils.PrintRecord(results4)
	// SELECT * FROM users WHERE name = 'user_1' OR age = 16;
	var results5 []map[string]interface{}
	db.Model(&User{}).
		Select("name", "age").
		Where("name = @name OR age = @age", map[string]interface{}{"name": "user_1", "age": 16}).
		Find(&results5)
	utils.PrintRecord(results5)

5.2.19 Find To Map

GORM allows scan result to map[string]interface{} or []map[string]interface{}, don't forget to specify Model or Table,

result := map[string]interface{}{}
db.Model(&User{}).First(&result, "id = ?", 1)

var results []map[string]interface{}
db.Table("users").Find(&results)

5.2.20 FirstOrInit

Get first matched record or initialize a new instance with given conditions (only works with struct or map conditions)

	// FirstOrInit
	var user User
	db.FirstOrInit(&user, User{Name: "non_existing"})
	utils.PrintRecord(user)
	//
	var user2 User
	db.FirstOrInit(&user2, map[string]interface{}{"name": "user_0"})
	utils.PrintRecord(user2)

initialize struct with more attributes if record not found, thoes Attrs won't be used to build SQL query

	// SELECT * FROM users WHERE name = 'non_existing' ORDER BY id LIMIT 1 ;
	var user3 User
	db.Where(User{Name: "non_existing"}).Attrs(User{Age: 20}).FirstOrInit(&user3)
	utils.PrintRecord(user3)

Assign attributes to struct regardless it is found or not, thoes attributes won't be used to build SQL query and the final data won't be saved into database

	// SELECT * FROM users WHERE name = 'user_0' ORDER BY id LIMIT 1 ;	var user4 User
	var user4 User
	db.Where(User{Name: "user_0"}).Assign(User{Age: 20}).FirstOrInit(&user4)
	utils.PrintRecord(user4)

5.2.21 FirstOrCreate

此方法和FirstOrInit 类似，未找到记录则会根据条件和属性新增记录

	// FirstOrCreate
	// SELECT * FROM users WHERE name = 'non_existing' ORDER BY id LIMIT 1 ;
	var user User
	db.FirstOrCreate(&user, User{Name: "non_existing"})
	utils.PrintRecord(user)
	// SELECT * FROM users WHERE name = 'user_0' ORDER BY id LIMIT 1 ;
	var user2 User
	db.FirstOrCreate(&user2, map[string]interface{}{"name": "user_0"})
	utils.PrintRecord(user2)
	// SELECT * FROM users WHERE name = 'non_existing' ORDER BY id LIMIT 1 ;
	var user3 User
	db.Where(User{Name: "non_existing"}).Attrs(User{Age: 20}).FirstOrCreate(&user3)
	utils.PrintRecord(user3)
	// SELECT * FROM users WHERE name = 'user_0' ORDER BY id LIMIT 1 ;	var user4 User
	var user4 User
	db.Where(User{Name: "user_0"}).Assign(User{Age: 20}).FirstOrCreate(&user4)
	utils.PrintRecord(user4)

5.2.22 Optimizer/Index Hints

Optimizer hints allow to control the query optimizer to choose a certain query execution plan, GORM supports it with gorm.io/hints

import "gorm.io/hints"

db.Clauses(hints.New("MAX_EXECUTION_TIME(10000)")).Find(&User{})
// SELECT * /*+ MAX_EXECUTION_TIME(10000) */ FROM `users`

Index hints allow passing index hints to the database in case the query planner gets confused.

import "gorm.io/hints"

db.Clauses(hints.UseIndex("idx_user_name")).Find(&User{})
// SELECT * FROM `users` USE INDEX (`idx_user_name`)

db.Clauses(hints.ForceIndex("idx_user_name", "idx_user_id").ForJoin()).Find(&User{})
// SELECT * FROM `users` FORCE INDEX FOR JOIN (`idx_user_name`,`idx_user_id`)"

5.2.23 Iteration

GORM supports iterating through Rows

	// Iteration
	rows, err := db.Model(&User{}).Where("age > ?", 12).Rows()
	if err != nil {
		log.Println("query record error: ", err)
	}
	defer rows.Close()
	for i := 1; rows.Next(); i++ {
		log.Printf("[%d] Row", i)
		var user User
		db.ScanRows(rows, &user)
		utils.PrintRecord(user)
	}

5.2.24 Hooks

Available hooks for querying

// load data from database
// Preloading (eager loading)
AfterFind

// ...
func (u *User) AfterFind(*gorm.DB) (err error) {
	u.Age += 10
	return
}
func main(){
	// Hooks
	var user User
	db.Where("`group` = ?", "group_0").Take(&user)
	utils.PrintRecord(user)
}

5.2.25 Pluck

Query single column from databases and scan into a slice, if you want to query multiple columns, use Select with Scan instead

	// Pluck
	// SELECT age FROM users ;
	var ages []int
	db.Model(&User{}).Pluck("age", &ages)
	utils.PrintRecord(ages)
	// SELECT age FROM users ;
	var ages2 []int
	db.Model(&User{}).Select("age").Find(&ages2)
	utils.PrintRecord(ages2)
	// SELECT name FROM users ;
	var names []string
	db.Model(&User{}).Pluck("name", &names)
	utils.PrintRecord(names)
	// SELECT DISTINCT `group` FROM users ;
	var groups []string
	db.Model(&User{}).Distinct().Pluck("group", &groups)
	utils.PrintRecord(groups)

5.2.26 Scopes

Scopes allows you to specify commonly-used queries which can be referenced as method calls

// ...
func main() {
	// ...
	// Scopes
	// SELECT id,name,age,group FROM users WHERE age > 15 ;
	var users []User
	db.Select(UserAllFields).
		Scopes(AgeGreaterThan15).
		Find(&users)
	utils.PrintRecord(users)
	// SELECT id,name,age,group FROM users WHERE group IN ('group_0','group_2') ;
	var users2 []User
	db.Select(UserAllFields).
		Scopes(GroupIn([]string{"group_0", "group_2"})).
		Find(&users2)
	utils.PrintRecord(users2)
}

func AgeGreaterThan15(db *gorm.DB) *gorm.DB {
	return db.Where("age > ?", 15)
}

func GroupIn(groups []string) func(db *gorm.DB) *gorm.DB {
	return func(db *gorm.DB) *gorm.DB {
		return db.Where("`group` IN (?)", groups)
	}
}
// ...

5.2.27 Count

Get matched records count

	// Count
	// SELECT COUNT(DISTINCT(`group`)) FROM users ;
	var count int64
	db.Model(&User{}).Distinct("group").Count(&count)
	log.Printf("Groups count: %d", count)
	// SELECT COUNT(*) FROM
	var count2 int64
	db.Model(&User{}).Count(&count2)
	log.Printf("Records count: %d", count2)

5.2.28 FindInBatches

Query and process records in batch

// batch size 100
result := db.Where("processed = ?", false).FindInBatches(&results, 100, func(tx *gorm.DB, batch int) error {
  for _, result := range results {
    // batch processing found records
  }

  tx.Save(&results)

  tx.RowsAffected // number of records in this batch

  batch // Batch 1, 2, 3

  // returns error will stop future batches
  return nil
})

result.Error // returned error
result.RowsAffected // processed records count in all batches

5.3 Update

5.3.1 Save All Fields

Save will save all fields when performing the Updating SQL

	// INSERT INTO users (name,group,age) VALUES ('user_0','',100) ;
	user := User{Name: "user_0", Age: 100}
	db.Save(&user)
	utils.PrintRecord(user)
	// UPDATE users SET name = 'user_0',age = 100,group = 'group_0' WHERE id =1 ;
	var user2 User
	db.Where(User{Name: "user_0"}).First(&user2)
	log.Println("Before update")
	utils.PrintRecord(user2)
	user2.Age *= 10
	db.Save(&user2)
	log.Println("After update")
	utils.PrintRecord(user2)
  • Save: Save update value in database, if the value doesn't have primary key, will insert it

5.3.2 Update single column

When updating a single column with Update, it needs to have any conditions or it will raise error ErrMissingWhereClause,

When using the Model method and its value has a primary value, the primary key will be used to build the condition

	// Update single column
	// Update with conditions
	// UPDATE users SET name = 'user_0_edited' WHERE name = 'user_0' ;
	db.Model(&User{}).Where("name = ?", "user_0").Update("name", "user_0_edited")
	// UPDATE users SET name = 'user_1_edited' WHERE id = 2 ;
	user := User{ID: 2}
	db.Model(&user).Update("name", "user_1_edited")
	utils.PrintRecord(user)
	// Update with conditions and model value
	// UPDATE users SET name = 'user_2_edited' WHERE id = 3 AND name = 'user_2'
	user2 := User{ID: 3, Group: "group_2"}
	db.Model(&user2).Where("name = ?", "user_2").Update("name", "user_2_edited")
	utils.PrintRecord(user2)

注意：使用 Model 时，Update 之后会将更新的列值回写到对象中，故需要使用指针作为参数

Model 中若 主键 字段之外的字段有值，也不会作为 UPDATE 的条件，即 更新时只会将 model 中的主键字段作为条件

5.3.3 Update multiple columns

Updates supports update with struct or map[string]interface{}, when updating with struct it will only update non-zero fileds by default

Note

When update with struct, GORM will only update non-zero fields, you might want to user map to update attributes or use Select to specify fields to udpate

// Update multiple columns
	// Update with struct
	// UPDATE users SET name = 'user_0_edited' WHERE id = 1;
	db.Model(&User{}).Where("id = ?", 1).Updates(User{Name: "user_0_edited", Age: 0})
	// Update with map
	// UPDATE users SET name = 'user_1_edited',age = 0 WHERE id = 2 ;
	user := User{ID: 2}
	db.Model(&user).Updates(map[string]interface{}{"name": "user_1_edited", "age": 0})
	utils.PrintRecord(user)
	// Update specified fields
	// UPDATE users SET name = 'user_2_edited',age = 0 WHERE id = 3 ;
	db.Model(&User{}).Where("id = ?", 3).Select("name", "age").Updates(User{Name: "user_2_edited"})

5.3.4 Udpate Selected Fields

If you want to update selected fields or ignore some fields when updating, you can use Selcet,Omit

	// Update selected fields
	// UPDATE users SET name = 'user_0_new' WHERE id = 1 ;
	db.Model(&User{}).
		Where("id = ?", 1).
		Select("name").
		Updates(User{Name: "user_0_new"})
	// UPDATE users SET age = 10 WHERE id = 2 ;
	db.Table("users").Where("id = ?", 2).
		Omit("age").Updates(User{Name: "user_1_new", Age: 10})
	// Select all fields
	// UPDATE users SET id = 0,name = 'user_2_new',age = 0,group = '' WHERE id = 3;
	db.Table("users").Where("id = ?", 3).Select("*").
		Updates(User{Name: "user_2_new"})
	// Select all fields but omit name
	// UPDATE users SET id = 0,age = 0,group = '' WHERE id = 4;
	db.Table("users").Where("id = ?", 4).Select("*").
		Omit("name").Updates(User{Name: "user_3_new"})

注意 ：使用 Select("*") 后，主键也可能被修改

5.3.5 Update Hooks

Available hooks for updating

// begin transaction
BeforeSave
BeforeUpdate
// save before associations
// update database
// save after associations
AfterUpdate
AfterSave
// commit or rollback transaction

func (User) BeforeUpdate(db *gorm.DB) (err error) {
	time.Sleep(1 * time.Second)
	fmt.Println("Before update")
	return
}

func (User) BeforeSave(db *gorm.DB) (err error) {
	time.Sleep(1 * time.Second)
	fmt.Println("Before save")
	return
}

func (User) AfterUpdate(db *gorm.DB) (err error) {
	time.Sleep(1 * time.Second)
	fmt.Println("After update")
	return
}

func (User) AfterSave(db *gorm.DB) (err error) {
	time.Sleep(1 * time.Second)
	fmt.Println("After save")
	return
}
// ...
func main() {
	// ...

	// Update hooks
	db.Model(&User{}).Where("id = ?", 1).Update("name", "user_0_new")
}
// ...

5.3.6 Batch Updates

If we haven't specified a record having primary key value with Model, GORM will perform a batch updates

// Update with struct
db.Model(User{}).Where("role = ?", "admin").Updates(User{Name: "hello", Age: 18})
// UPDATE users SET name='hello', age=18 WHERE role = 'admin';

// Update with map
db.Table("users").Where("id IN ?", []int{10, 11}).Updates(map[string]interface{}{"name": "hello", "age": 18})
// UPDATE users SET name='hello', age=18 WHERE id IN (10, 11);

5.3.7 Block Global Updates

If you perform a batch update without any conditions, GORM WON'T run it and will return ErrMissingWhereClause error by default

You have to use some conditions or use raw SQL or enable the AllowGlobalUpdate mode

// ...
	// Global Update
	// ErrMissingWhereClause
	err = db.Model(&User{}).Update("name", "new_name").Error
	log.Println(err.Error())
	// UPDATE users SET name = 'new_name' WHERE 1 = 1;
	db.Model(&User{}).Where("1 = 1").Update("name", "new_name")
	// UPDATE users SET name = 'name_global_update' ;
	db.Session(&gorm.Session{AllowGlobalUpdate: true}).Model(&User{}).Update("name", "name_global_update")
// ...

5.3.8 Updated Records Count

Get the number of rows affected by a update

// Get updated records count with `RowsAffected`
result := db.Model(User{}).Where("role = ?", "admin").Updates(User{Name: "hello", Age: 18})
// UPDATE users SET name='hello', age=18 WHERE role = 'admin';

result.RowsAffected // returns updated records count
result.Error        // returns updating error

5.3.9 Update with SQL Expression

GORM allows updates column with SQL expression

	// Update with SQL expression
	// UPDATE users SET age = age * 2 + 100 WHERE id = 1;
	db.Model(&User{}).Where("id = ?", 1).Update("age", gorm.Expr("age * ? + ?", 2, 100))
	// UPDATE users SET name = name || '123' WHERE id = 2 ;
	db.Model(&User{}).Where("id = ?", 2).Updates(map[string]interface{}{"name": gorm.Expr("name || ?", "_123")})
	// UPDATE users SET age = age * 100 WHERE id = 3 ;
	db.Model(&User{}).Where("id = ?", 3).UpdateColumn("age", gorm.Expr("age * ?", 100))

这里出现了 UpdateColumn,作用和Update 是一样的，那么有什么区别呢

这里看下源码

Update:

// Update update attributes with callbacks, refer: https://gorm.io/docs/update.html#Update-Changed-Fields
func (db *DB) Update(column string, value interface{}) (tx *DB) {
   tx = db.getInstance()
   tx.Statement.Dest = map[string]interface{}{column: value}
   return tx.callbacks.Update().Execute(tx)
}

UpdateColumn:

func (db *DB) UpdateColumn(column string, value interface{}) (tx *DB) {
	tx = db.getInstance()
	tx.Statement.Dest = map[string]interface{}{column: value}
	tx.Statement.SkipHooks = true
	return tx.callbacks.Update().Execute(tx)
}

可以看到 UpdateColumn 会跳过 hooks，后续的文档还会告诉 UpdateColumn 将不会追踪 update time，这里接着看源码


// ConvertToAssignments convert to update assignments
func ConvertToAssignments(stmt *gorm.Statement) (set clause.Set) {
		// ...
		if !stmt.SkipHooks && stmt.Schema != nil {
			for _, dbName := range stmt.Schema.DBNames {
				field := stmt.Schema.LookUpField(dbName)
				if field.AutoUpdateTime > 0 && value[field.Name] == nil && value[field.DBName] == nil {
					if v, ok := selectColumns[field.DBName]; (ok && v) || !ok {
						now := stmt.DB.NowFunc()
						assignValue(field, now)

						if field.AutoUpdateTime == schema.UnixNanosecond {
							set = append(set, clause.Assignment{Column: clause.Column{Name: field.DBName}, Value: now.UnixNano()})
						} else if field.AutoUpdateTime == schema.UnixMillisecond {
							set = append(set, clause.Assignment{Column: clause.Column{Name: field.DBName}, Value: now.UnixNano() / 1e6})
						} else if field.GORMDataType == schema.Time {
							set = append(set, clause.Assignment{Column: clause.Column{Name: field.DBName}, Value: now})
						} else {
							set = append(set, clause.Assignment{Column: clause.Column{Name: field.DBName}, Value: now.Unix()})
						}
					}
				}
			}
		}
 // ...
}

可以看到当 skipHooks 为 false 时，更新操作将不会追踪更新时间

5.3.10 Update from SubQuery

Update a table by using SubQuery

	// Update From SubQuery
	// UPDATE users SET name = name || (SELECT email FROM emails WHERE emails.user_id = users.id) WHERE id = 1 ;
	db.Model(&User{}).Where("id = ?", 1).
		Update("name", gorm.Expr("name || (?)", db.Model(&Email{}).Select("email").
			Where("emails.user_id = users.id ")))

5.3.11 Without Hooks/Time Tracking

If you want to skip Hooks methods and don’t track the update time when updating, you can use UpdateColumn,UpdateColumns, it works like Update,Updates

5.3.12 Returning Data From Modified Rows

Return changed data, only works for database support Returning, for example:

// return all columns
var users []User
DB.Model(&users).Clauses(clause.Returning{}).Where("role = ?", "admin").Update("salary", gorm.Expr("salary * ?", 2))
// UPDATE `users` SET `salary`=salary * 2,`updated_at`="2021-10-28 17:37:23.19" WHERE role = "admin" RETURNING *
// users => []User{{ID: 1, Name: "jinzhu", Role: "admin", Salary: 100}, {ID: 2, Name: "jinzhu.2", Role: "admin", Salary: 1000}}

// return specified columns
DB.Model(&users).Clauses(clause.Returning{Columns: []clause.Column{{Name: "name"}, {Name: "salary"}}}).Where("role = ?", "admin").Update("salary", gorm.Expr("salary * ?", 2))
// UPDATE `users` SET `salary`=salary * 2,`updated_at`="2021-10-28 17:37:23.19" WHERE role = "admin" RETURNING `name`, `salary`
// users => []User{{ID: 0, Name: "jinzhu", Role: "", Salary: 100}, {ID: 0, Name: "jinzhu.2", Role: "", Salary: 1000}}

5.3.13 Check Field has changed

GORM provides Changed method could be used in BeforeUpdate hooks, it will return the field changed or not

The Changed method only works with methods Update,Updates, and it only checks if the updating value from Update/Updates equals the model value, will return true if it is changed and not omitted

// ...
func (u *User) BeforeUpdate(tx *gorm.DB) (err error) {
	// if age changed
	if tx.Statement.Changed("name") {
		fmt.Println("name changed")
		tx.Statement.SetColumn("age", 100)
		return
	}
	// if name or age changed
	if tx.Statement.Changed("name", "age") {
		fmt.Println("name or age changed")
		return
	}
	// if any fields changed
	if tx.Statement.Changed() {
		fmt.Println("any fields changed")
		return
	}
	return
}
// ...
	// Check Field has changed
	// UPDATE users SET name = 'new_name',age = 100  WHERE id = 1 ;
	db.Model(&User{ID: 1, Name: "user_0"}).Update("name", "new_name")
	// UPDATE users SET age = age * 2 WHERE id =2  ;
	db.Model(&User{ID: 2, Age: 11}).Update("age", gorm.Expr("age * ?", 2))
	// UPDATE users SET name = 'new_name',age = 1300 WHERE id = 3 ;
	db.Model(&User{ID: 3, Age: 12, Name: "user_2"}).Updates(User{Name: "new_name", Age: 1300})

5.3.14 Change Updating Values

To change updating values in Before Hooks, you should use SetColumn unless it is a full updates with Save

func (user *User) BeforeSave(tx *gorm.DB) (err error) {
  if pw, err := bcrypt.GenerateFromPassword(user.Password, 0); err == nil {
    tx.Statement.SetColumn("EncryptedPassword", pw)
  }

  if tx.Statement.Changed("Code") {
    s.Age += 20
    tx.Statement.SetColumn("Age", s.Age+20)
  }
}

db.Model(&user).Update("Name", "jinzhu")

5.4 Delete

5.4.1 Delete a Record

When deleting a record, the deleted value needs to have primary key or it will trigger a batch delete

	// Delete a Record
	// DELETE FROM users WHERE id = 1;
	user := User{ID: 1, Name: "user_0"}
	db.Delete(&user)
	utils.PrintRecord(user)
	// Error: WHERE conditions required
	user2 := User{Name: "user_1"}
	db.Delete(&user2)
	utils.PrintRecord(user2)
	// DELETE FROM users WHERE id = 3 AND name = 'user_2' ;
	user3 := User{ID: 3}
	db.Where("name = ?", "user_2").Delete(&user3)
	utils.PrintRecord(user3)
	// DELETE FROM users WHERE name = 'user_3' ;
	db.Where("name = ?", "user_3").Delete(&User{})

删除时需要传入的 struct 中包含主键值，若没有且未定义其他条件则会返错误；

若 struct 中包含主键值之外的字段值，除主键之外的字段将不会用于构成删除条件，这点和 update 类似

可以使用 Where 手动添加条件

5.4.2 Delete with primary key

GORM allows to delete objects using primary key(s) with inline condition, it works with numbers

	// Delete with primary key
	// DELETE FROM users WHERE id = 1;
	db.Delete(&User{}, 1)
	// DELETE FROM users WHERE id = 2 ;
	db.Delete(&User{}, "2")
	// DELETE FROM users WHERE id IN (3,4,5) ;
	db.Delete(&User{}, []int{3, 4, 5})

5.4.3 Delete Hooks

// begin transaction
BeforeDelete
// delete from database
AfterDelete
// commit or rollback transaction

func (User) BeforeDelete(tx *gorm.DB) (err error) {
	fmt.Println("Before Delete")
	return
}

func (User) AfterDelete(tx *gorm.DB) (err error) {
	fmt.Println("After Delete")
	return
}
// ...
db.Delete(&User{}, 1)

5.4.4 Batch Delete

The specified values has no primary value, GORM will perform a batch delete, it will delete all mathed records

// DELETE FROM users WHERE age > 10 ;
db.Where("age > ?",10).Delete(&User{})

5.4.5 Block Global Delete

If you perform a batch delete without any conditions, GORM WON’T run it, and will return ErrMissingWhereClause error

You have to use some conditions or use raw SQL or enable AllowGlobalUpdate mode

// DELTE FROM users ;
db.Session(&gorm.Session{AllowGlobalUpdate: true}).Delete(&User{})

5.4.6 Returning Data From Deleted Rows

Return deleted data, only works for database support Returning, for example:

// return all columns
var users []User
DB.Clauses(clause.Returning{}).Where("role = ?", "admin").Delete(&users)
// DELETE FROM `users` WHERE role = "admin" RETURNING *
// users => []User{{ID: 1, Name: "jinzhu", Role: "admin", Salary: 100}, {ID: 2, Name: "jinzhu.2", Role: "admin", Salary: 1000}}

// return specified columns
DB.Clauses(clause.Returning{Columns: []clause.Column{{Name: "name"}, {Name: "salary"}}}).Where("role = ?", "admin").Delete(&users)
// DELETE FROM `users` WHERE role = "admin" RETURNING `name`, `salary`
// users => []User{{ID: 0, Name: "jinzhu", Role: "", Salary: 100}, {ID: 0, Name: "jinzhu.2", Role: "", Salary: 1000}}

5.4.7 Soft Delete

If your model includes a gorm.DeletedAt field (which is included in gorm.Model), it will get soft delete ability automatically

When calling Delete, the record WON’T be removed from the database, but GORM will set the DeletedAt's value to the current time, and the data is not findable with normal Query methods anymore

If you don’t want to include grom.Model, you can enable the soft delete feature like:

type User struct {
    ID int 
    DeletedAt gorm.DeletedAt
    Name string
}

Find soft deleted records

You can find soft deleted records with Unscoped

type User struct {
	// ...
	DeletedAt gorm.DeletedAt
    // ...
}
// ...
	// Soft Delete
	db.Delete(&User{}, []int{1, 2, 3})
	// cannot find
	var users []User
	db.Find(&users, []int{1, 2, 3})
	utils.PrintRecord(users)
	// find with unscoped
	var users2 []User
	db.Unscoped().Find(&users2, []int{1, 2, 3})
	utils.PrintRecord(users2)

5.4.8 Delete permanetly

You can delete matched records permanetly with Unscoped

db.Unscoped().Delete(&order)

5.4.9 Delete Flag

Use unix second as delete flag

when using unique field with soft delete, you should create a composite index with the unix second based DeletedAt

type User struct {
	// ...
	DeletedAt soft_delete.DeletedAt `gorm:"uniqueIndex:udx_name"`
	// ...
}
	// delete flag
	db.Delete(&User{}, []int{1, 2, 3})
	// cannot find
	var users []User
	db.Find(&users, []int{1, 2, 3})
	utils.PrintRecord(users)
	// find with unscoped
	var users2 []User
	db.Unscoped().Find(&users2, []int{1, 2, 3})
	utils.PrintRecord(users2)

Use 1 / 0 as delete flag

import "gorm.io/plugin/soft_delete"

type User struct {
  ID    uint
  Name  string
  IsDel soft_delete.DeletedAt `gorm:"softDelete:flag"`
}

// Query
SELECT * FROM users WHERE is_del = 0;

// Delete
UPDATE users SET is_del = 1 WHERE ID = 1;

6. Raw SQL and SQL Builder

6.1 Raw SQL

Query Raw SQL with Scan, Exec with Raw SQL

	// Raw SQL
	// Scan
	var result map[string]interface{}
	db.Raw("SELECT id,name,age FROM users WHERE id = ?", 1).Scan(&result)
	utils.PrintRecord(result)
	var results []map[string]interface{}
	db.Raw("SELECT id,name FROM users WHERE age > ?", 10).Scan(&results)
	utils.PrintRecord(results)
	var sumAge int
	db.Raw("SELECT SUM(age) FROM users").Scan(&sumAge)
	log.Println("Sum age: ", sumAge)
	// Exec
	db.Exec("UPDATE users SET age = age + ?", 10)

NOTE

GORM allows cache prepared statement to increase performance

6.2 Named Argument

GORM supports named argument with sql.NamedArg, map[string]interface{} or struct

	// Named argument
	// SELECT * FROM users WHERE name = 'user_0' LIMIT 1;
	var user User
	db.Where("name = @name", sql.Named("name", "user_0")).Take(&user)
	utils.PrintRecord(user)
	// SELECT * FROM users WHERE age = 12 LIMIT 1 ;
	var user2 User
	db.Where("age = @age ", map[string]interface{}{"age": 12}).Take(&user2)
	utils.PrintRecord(user2)
	// Named Argument with Raw SQL
	var result []map[string]interface{}
	db.Raw("SELECT id,name FROM users WHERE age = @age OR id = @id ", sql.Named("age", 15), sql.Named("id", 3)).
		Scan(&result)
	utils.PrintRecord(result)
	var result2 map[string]interface{}
	db.Raw("SELECT name,age FROM users WHERE id = @id OR age > @age", map[string]interface{}{"id": 2, "age": 13}).
		Scan(&result2)
	utils.PrintRecord(result2)
	// struct
	type NameArg struct {
		Name string
		Age  int
	}
	var result3 map[string]interface{}
	db.Raw("SELECT * FROM users WHERE name = @Name AND age = @Age", NameArg{Name: "user_0", Age: 10}).Scan(&result3)
	utils.PrintRecord(result3)

6.3 DryRun Mode

Generate SQL and its arguments without executing, can be used to prepare or test generated SQL

	// DryRun
	stmt := db.Session(&gorm.Session{DryRun: true}).Where("id = ?", 1).Take(&User{}).Statement
	log.Printf("SQL: %s, VAL: %v", stmt.SQL.String(), stmt.Vars)

6.4 ToSQL

Returns generated SQL without executing

GORM uses the databases/sql's argument placeholders to construct the SQL statement, which will automatically escape arguments to avoid SQL injection, but the generated SQL don't provide the safety gurantees, please only use it for debugging

	// DryRun
	sql := db.ToSQL(func(tx *gorm.DB) *gorm.DB {
		return tx.Model(&User{}).Where("age > ?", 10).First(&User{})
	})
	log.Println("SQL: ", sql)

6.5 Row and Rows

// Row and Rows
	// Row
	var (
		name string
		age  int
	)
	row := db.Model(&User{}).Where("age > 10", "user_0").Order("age desc").
		Select("name", "age").Row()
	row.Scan(&name, &age)
	log.Printf("Name: %s,Age: %d", name, age)
	// Rows
	rows, _ := db.Model(&User{}).Where("age > ?", 15).Select("name", "age").Rows()
	defer rows.Close()
	for rows.Next() {
		var name string
		var age int
		rows.Scan(&name, &age)
		log.Printf("Row[name: %s,Age: %d]", name, age)
		// Scan rows to struct
		var user User
		db.ScanRows(rows, &user)
		utils.PrintRecord(user)
	}
  • Row(): get result as *sql.Row
  • Rows(): get result as *sql.Rows
  • ScanRows: scan rows into struct

6.6 Clauses

GORM uses SQL builder generates SQL internally, for each operation, GORM creates a *gorm.Statement object, all GORM APIs add/change Clause for the Statement, at last, GORM generated SQL based on those clauses

For example, when querying with First, it adds the following clauses to the Statement

clause.Select{Columns: "*"}
clause.From{Tables: clause.CurrentTable}
clause.Limit{Limit: 1}
clause.OrderByColumn{
  Column: clause.Column{Table: clause.CurrentTable, Name: clause.PrimaryKey},
}

Then GORM build finally querying SQL in the Query callbacks like:

Statement.Build("SELECT", "FROM", "WHERE", "GROUP BY", "ORDER BY", "LIMIT", "FOR")

Which generate SQL:

SELECT * FROM `users` ORDER BY `users`.`id` LIMIT 1

6.7 Clause Options

GORM defined Many Clausesopen in new window, and some clauses provide advanced options can be used for your application

Although most of them are rarely used, if you find GORM public API can’t match your requirements, may be good to check them out, for example:

db.Clauses(clause.Insert{Modifier: "IGNORE"}).Create(&user)
// INSERT IGNORE INTO users (name,age...) VALUES ("jinzhu",18...);

6.8 StatementModifier

GORM provides interface StatementModifieropen in new window allows you modify statement to match your requirements, take Hintsopen in new window as example

import "gorm.io/hints"

db.Clauses(hints.New("hint")).Find(&User{})
// SELECT * /*+ hint */ FROM `users`

Reference

  1. gorm
  2. What is the difference between Find and Scan， Can I replace Scan with Find ？open in new window grom issue
  3. What is the syntax for SELECT IN statement for SQLITE?open in new window stackoverflow
  4. SQLite FULL OUTER JOIN Emulationopen in new window sqlitetutorial
上次编辑于:
0  字
评论
  • 按正序
  • 按倒序
  • 按热度
Powered by Waline v2.15.2