CRUD通常指数据库的增删改查操作，本文详细介绍了如何使用GORM实现创建、查询、更新和删除操作。
首先定义模型与数据库连接：
package main
import (
"gorm.io/driver/mysql"
"gorm.io/gorm"
"gorm.io/gorm/logger"
"log"
"os"
"time"
)
type Student struct {
gorm.Model
Name string
Age uint8
Birthday time.Time
}
func main() {
// 日志配置
newLogger := logger.New(
log.New(os.Stdout, "\r\n", log.LstdFlags), // io writer（日志输出的目标，前缀和日志包含的内容——译者注）
logger.Config{
SlowThreshold: time.Second, // 慢 SQL 阈值
LogLevel: logger.Info, // 日志级别为info
IgnoreRecordNotFoundError: true, // 忽略ErrRecordNotFound（记录未找到）错误
Colorful: true, // 彩色打印
},
)
dsn := "root:123456@tcp(127.0.0.1:3306)/db1?charset=utf8mb4&parseTime=True&loc=Local"
db, err := gorm.Open(mysql.Open(dsn), &gorm.Config{
Logger: newLogger,
})
if err != nil {
panic(err) // 如果数据库不存在会报错
}
db.AutoMigrate(&Student{})
}
user := Student{Name: "贾维斯", Age: 18, Birthday: time.Now()}
result := db.Create(&user) // 通过数据的指针来创建
fmt.Println(user.ID) // 返回插入数据的主键
fmt.Println(result.Error) // 返回 error
fmt.Println(result.RowsAffected) // 返回插入记录的条数
创建记录并更新给出的字段。
student := Student{Name: "贾维斯", Age: 19, Birthday: time.Now()}
db.Select("Name", "Age", "CreatedAt").Create(&student)
// INSERT INTO `students` (`created_at`,`updated_at`,`name`,`age`) VALUES ('2024-01-10 21:05:31.507','2024-01-10 21:05:31.507','贾维斯',19)
创建一个记录且一同忽略传递给略去的字段值。
student := Student{Name: "dlrb", Age: 20, Birthday: time.Now()}
db.Omit("Name", "Age", "CreatedAt").Create(&student)
// INSERT INTO `students` (`updated_at`,`deleted_at`,`birthday`) VALUES ('2024-01-10 21:13:49.053',NULL,'2024-01-10 21:13:49.052')
要有效地插入大量记录，请将一个 slice 传递给 Create 方法。 GORM 将生成单独一条SQL语句来插入所有数据，并回填主键的值，钩子方法也会被调用。
t, _ := time.Parse("2024-01-10", "1998-08-18")
var student = []Student{
{Name: "小爱同学", Birthday: t},
{Name: "小欣同学", Birthday: t},
{Name: "小冰同学", Birthday: t},
{Name: "小芳同学", Birthday: t},
}
db.Create(&student)
for _, user := range student {
fmt.Println(user.ID)
}
/*
因为Mysql5.7版本及以上版本的datetime值不能为'0000-00-00 00:00:00',
//处理方法：
修改mysql.ini
在[mysqld]添加一项：
sql_mode=NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO
*/
使用 CreateInBatches 分批创建时，你可以指定每批的数量，例如：
var student [20]Student
t, _ := time.Parse("2024-01-10", "1998-08-18")
for i, _ := range student {
student[i].Name = fmt.Sprintf("贾维斯%d号", i)
student[i].Birthday = t
}
fmt.Println(student)
db.CreateInBatches(&student, 10)
Upsert 和 Create With Associations 也支持批量插入
注意 使用
CreateBatchSize选项初始化 GORM 时，所有的创建& 关联INSERT都将遵循该选项
// 关联插入
db, err := gorm.Open(sqlite.Open("gorm.db"), &gorm.Config{
CreateBatchSize: 1000,
})
db := db.Session(&gorm.Session{CreateBatchSize: 1000})
users = [5000]User{{Name: "jinzhu", Pets: []Pet{pet1, pet2, pet3}}...}
db.Create(&users)
// INSERT INTO users xxx (5 batches)
// INSERT INTO pets xxx (15 batches)
GORM 允许用户定义的钩子有 BeforeSave, BeforeCreate, AfterSave, AfterCreate 创建记录时将调用这些钩子方法，请参考 Hooks 中关于生命周期的详细信息
// main中
db.Create(&Student{
Name: "贾维斯",
Birthday: time.Now(),
})
// 插入之前的钩子函数
func (u *Student) BeforeCreate(tx *gorm.DB) (err error) {
fmt.Println("Student被插入了")
return
}
如果您想跳过 钩子 方法，您可以使用 SkipHooks 会话模式，例如：
db.Session(&gorm.Session{SkipHooks: true}).Create(&student)
db.Session(&gorm.Session{SkipHooks: true}).Create(&student)
db.Session(&gorm.Session{SkipHooks: true}).CreateInBatches(student, 100)
GORM 支持根据 map[string]interface{} 和 []map[string]interface{}{} 创建记录，例如：
// 此处可以不传Birthday因为它是null的
db.Model(&Student{}).Create(map[string]interface{}{
"Name": "贾维斯_001", "Age": 18,
})
// batch insert from `[]map[string]interface{}{}`
db.Model(&Student{}).Create([]map[string]interface{}{
{"Name": "贾维斯_002", "Age": 18},
{"Name": "贾维斯_003", "Age": 20},
})
注意： 根据 map 创建记录时，association 不会被调用，且主键也不会自动填充
GORM 允许使用 SQL 表达式插入数据，有两种方法实现这个目标。根据 map[string]interface{} 或 自定义数据类型 创建，例如：
// 通过 map 创建记录
db.Model(User{}).Create(map[string]interface{}{
"Name": "jinzhu",
"Location": clause.Expr{SQL: "ST_PointFromText(?)", Vars: []interface{}{"POINT(100 100)"}},
})
// INSERT INTO `users` (`name`,`location`) VALUES ("jinzhu",ST_PointFromText("POINT(100 100)"));
// 通过自定义类型创建记录
type Location struct {
X, Y int
}
// Scan 方法实现了 sql.Scanner 接口
func (loc *Location) Scan(v interface{}) error {
// Scan a value into struct from database driver
}
func (loc Location) GormDataType() string {
return "geometry"
}
func (loc Location) GormValue(ctx context.Context, db *gorm.DB) clause.Expr {
return clause.Expr{
SQL: "ST_PointFromText(?)",
Vars: []interface{}{fmt.Sprintf("POINT(%d %d)", loc.X, loc.Y)},
}
}
type User struct {
Name string
Location Location
}
db.Create(&User{
Name: "jinzhu",
Location: Location{X: 100, Y: 100},
})
// INSERT INTO `users` (`name`,`location`) VALUES ("jinzhu",ST_PointFromText("POINT(100 100)"))
创建关联数据时，如果关联值是非零值，这些关联会被 upsert，且它们的 Hook 方法也会被调用
type CreditCard struct {
gorm.Model
Number string
UserID uint
}
type User struct {
gorm.Model
Name string
CreditCard CreditCard
}
db.Create(&User{
Name: "jinzhu",
CreditCard: CreditCard{Number: "411111111111"}
})
// INSERT INTO `users` ...
// INSERT INTO `credit_cards` ...
您也可以通过 Select、 Omit 跳过关联保存，例如：
db.Omit("CreditCard").Create(&user)
// 跳过所有关联
db.Omit(clause.Associations).Create(&user)
您可以通过标签 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:(-);"`
}
使用虚拟/生成的值时，你可能需要禁用它的创建、更新权限，查看 字段级权限 获取详情
GORM 为不同数据库提供了兼容的 Upsert 支持
有时候插入数据，报主键冲突，有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", ...;
您还可以查看 高级查询 中的 FirstOrInit、FirstOrCreate
查看 原生 SQL 及构造器 获取更多细节
GORM 提供了 First、Take、Last 方法，以便从数据库中检索单个对象。当查询数据库时它添加了 LIMIT 1 条件，且没有找到记录时，它会返回 ErrRecordNotFound 错误
var student Student
// 获取第一条记录（主键升序）
db.First(&student)
// SELECT * FROM users ORDER BY id LIMIT 1;
// 获取一条记录，没有指定排序字段
db.Take(&student)
//SELECT * FROM users LIMIT 1;
// 获取最后一条记录（主键降序）
db.Last(&student)
// SELECT * FROM users ORDER BY id DESC LIMIT 1;
fmt.Println(student)
result := db.First(&student)
fmt.Println(result.RowsAffected) // 返回找到的记录数
fmt.Println(result.Error) // returns error or nil
// 检查 返回的错误是否是没找到记录的错误 ErrRecordNotFound 错误
fmt.Println(errors.Is(result.Error, gorm.ErrRecordNotFound))
如果你想避免
ErrRecordNotFound错误，你可以使用Find，比如db.Limit(1).Find(&user)，Find方法可以接受struct和slice的数据。
First 和 Last 会根据主键排序，分别查询第一条和最后一条记录。 只有在目标 struct 是指针或者通过 db.Model() 指定 model 时，该方法才有效。 此外，如果相关 model 没有定义主键，那么将按 model 的第一个字段进行排序。 例如:
var user User
var users []User
// works because destination struct is passed in
// 正常工作，因为目标结构体已经传递进去
db.First(&user)
// SELECT * FROM `users` ORDER BY `users`.`id` LIMIT 1
// works because model is specified using `db.Model()`
// 使用map，需要指定跟那个表关联
result := map[string]interface{}{}
db.Model(&User{}).First(&result)
// SELECT * FROM `users` ORDER BY `users`.`id` LIMIT 1
// doesn't work-->没指定Model，无效
result = map[string]interface{}{}
db.Table("users").First(&result)
// works with Take，使用Take就生效
result = map[string]interface{}{}
db.Table("users").Take(&result)
// no primary key defined, results will be ordered by first field (i.e., `Code`)
// 没有指定主键，就以第一个字段排序
type Language struct {
Code string
Name string
}
db.First(&Language{})
// SELECT * FROM `languages` ORDER BY `languages`.`code` LIMIT 1
如果主键是数字类型，您可以使用 内联条件 来检索对象。 传入字符串参数时，需要特别注意 SQL 注入问题，查看 安全 获取详情.
var user User
var users []User
db.First(&user, 10)
// SELECT * FROM users WHERE id = 10;
db.First(&user, "10")
// SELECT * FROM users WHERE id = 10;
db.Find(&users, []int{1,2,3})
// SELECT * FROM users WHERE id IN (1,2,3);
fmt.Println(user)
fmt.Println(users)
如果主键是字符串（例如像 uuid），查询将被写成这样：
db.First(&user, "id = ?", "1b74413f-f3b8-409f-ac47-e8c062e3472a")
// SELECT * FROM users WHERE id = "1b74413f-f3b8-409f-ac47-e8c062e3472a";
When the destination object has a primary value, the primary key will be used to build the condition, for example:
//当目标对象具有主键值时，主键将用于构建条件
var user = User{ID: 10}
db.First(&user)
// SELECT * FROM users WHERE id = 10;
var result User
db.Model(User{ID: 10}).First(&result)
// SELECT * FROM users WHERE id = 10;
var student []Student
// Get all records
result := db.Find(&student)
// SELECT * FROM `students` WHERE `students`.`deleted_at` IS NULL
fmt.Println(result.RowsAffected) // 返回找到的记录计数，等于“len（student）”
fmt.Println(result.Error) // returns error or nil
var student Student
// 定义 lastWeek 和 today
lastWeek := time.Now().AddDate(0, 0, -7) // 一周前的时间
today := time.Now() // 当前时间
// Get first matched record
db.Where("name = ?", "贾维斯").First(&student)
// SELECT * FROM `students` WHERE name = '贾维斯' AND `students`.`deleted_at` IS NULL ORDER BY `student`id` LIMIT 1
// Get all matched records
db.Where("name <> ?", "贾维斯").Find(&student)
// SELECT * FROM `students` WHERE name <> '贾维斯' AND `students`.`deleted_at` IS NULL AND `students`.` = 25
// IN
db.Where("name IN ?", []string{"贾维斯", "贾维斯 2"}).Find(&student)
// SELECT * FROM `students` WHERE name IN ('贾维斯','贾维斯 2') AND `students`.`deleted_at` IS NULL ANDents`.`id` = 25
// LIKE
db.Where("name LIKE ?", "%贾维斯%").Find(&student)
// SELECT * FROM `students` WHERE name LIKE '%贾维斯%' AND `students`.`deleted_at` IS NULL AND `student`id` = 25
// AND
db.Where("name = ? AND age >= ?", "贾维斯", "22").Find(&student)
// SELECT * FROM `students` WHERE (name = '贾维斯' AND age >= '22') AND `students`.`deleted_at` IS NULLD `students`.`id` = 25
// Time
db.Where("updated_at > ?", lastWeek).Find(&student)
// SELECT * FROM `students` WHERE updated_at > '2024-01-03 23:05:18.068' AND `students`.`deleted_at` IS NULL AND `students`.`id` = 25
// BETWEEN
db.Where("created_at BETWEEN ? AND ?", lastWeek, today).Find(&student)
// SELECT * FROM `students` WHERE (created_at BETWEEN '2024-01-03 23:05:18.068' AND '2024-01-10 23:05:18.068') AND `students`.`deleted_at` IS NULL AND `students`.`id` = 25
var student Student
// Struct
db.Where(&Student{Name: "贾维斯", Age: 20}).First(&student)
// SELECT * FROM `students` WHERE `students`.`name` = '贾维斯' AND `students`.`age` = 20 AND `students`eleted_at` IS NULL ORDER BY `students`.`id` LIMIT 1
// Map
db.Where(map[string]interface{}{"name": "贾维斯", "age": 20}).Find(&student)
// SELECT * FROM `students` WHERE `age` = 20 AND `name` = '贾维斯' AND `students`.`deleted_at` IS NULL
// Slice of primary keys
db.Where([]int64{20, 21, 22}).Find(&student)
// SELECT * FROM `students` WHERE `students`.`id` IN (20,21,22) AND `students`.`deleted_at` IS NULL
NOTE When querying with struct, GORM will only query with non-zero fields, that means if your field’s value is
0,'',falseor other zero values, it won’t be used to build query conditions, for example:
var student Student
// 当使用结构体作为查询条件，gorm只会查询非0字段，如果字段是`0`, `''`, `false` or other zero values，该字段不会被用于构建查询条件
db.Where(&Student{Name: "贾维斯", Age: 0}).Find(&student)
// SELECT * FROM `students` WHERE `students`.`name` = '贾维斯' AND `students`.`deleted_at` IS NULL
若要在查询条件中包含零值，可以使用map，该映射将包含所有键值作为查询条件，例如：
// 如果想要在查询中包含0的字段，可以使用map来做
db.Where(map[string]interface{}{"Name": "贾维斯", "Age": 0}).Find(&student)
// SELECT * FROM `students` WHERE `Age` = 0 AND `Name` = '贾维斯' AND `students`.`deleted_at` IS NULL
For more details, see Specify Struct search fields.
使用 struct 进行搜索时，可以通过将相关字段名称或 dbname 传递给 ‘Where（）’ 来指定要在查询条件中使用的结构中的哪些特定值，例如：
//在使用struct进行搜索时，可以通过将相关字段名或数据库名传递给`Where（），来指定在查询条件中使用struct中的哪些特定值`
db.Where(&Student{Name: "贾维斯"}, "name", "Age").Find(&student)
// SELECT * FROM `students` WHERE `students`.`name` = '贾维斯' AND `students`.`age` = 0 AND `students`.leted_at` IS NULL
db.Where(&Student{Name: "贾维斯"}, "Age").Find(&student)
// SELECT * FROM `students` WHERE `students`.`age` = 0 AND `students`.`deleted_at` IS NULL AND `students`.`id` = 25
查询条件可以以类似于Where的方式内联到’First’和’Find’等方法中
// 如果是非整形主键，根据主键获取记录
db.First(&Student{}, "id = ?", "string_primary_key")
// SELECT * FROM `students` WHERE id = 'string_primary_key' AND `students`.`deleted_at` IS NULL ORDER BY `students`.`id` LIMIT 1
// Plain SQL
db.Find(&Student{}, "name = ?", "jinzhu")
// SELECT * FROM `students` WHERE name = 'jinzhu' AND `students`.`deleted_at` IS NULL
db.Find(&Student{}, "name <> ? AND age > ?", "jinzhu", 20)
// SELECT * FROM `students` WHERE (name <> 'jinzhu' AND age > 20) AND `students`.`deleted_at` IS NULL
// Struct
db.Find(&Student{}, Student{Age: 20})
// SELECT * FROM `students` WHERE `students`.`age` = 20 AND `students`.`deleted_at` IS NULL
// Map
db.Find(&Student{}, map[string]interface{}{"age": 20})
// SELECT * FROM `students` WHERE `age` = 20 AND `students`.`deleted_at` IS NULL
Build NOT conditions, works similar to Where
构建NOT条件，工作原理同Where
var student Student
db.Not("name = ?", "贾维斯").First(&student)
// SELECT * FROM `students` WHERE NOT name = '贾维斯' AND `students`.`deleted_at` IS NULL ORDER BY `stuts`.`id` LIMIT 1
// Not In
db.Not(map[string]interface{}{"name": []string{"贾维斯", "贾维斯 2"}}).Find(&student)
// SELECT * FROM `students` WHERE `name` NOT IN ('贾维斯','贾维斯 2') AND `students`.`deleted_at` IS NU `students`.`id` = 1
// Struct
db.Not(Student{Name: "贾维斯", Age: 18}).First(&student)
// SELECT * FROM `students` WHERE (`students`.`name` <> '贾维斯' AND `students`.`age` <> 18) AND `stude`.`deleted_at` IS NULL AND `students`.`id` = 1 ORDER BY `students`.`id` LIMIT 1
// Not In slice of primary keys
db.Not([]int64{1, 2, 3}).First(&student)
// SELECT * FROM `students` WHERE `students`.`id` NOT IN (1,2,3) AND `students`.`deleted_at` IS NULL AND `students`.`id` = 1 ORDER BY `students`.`id` LIMIT 1
db.Where("role = ?", "admin").Or("role = ?", "super_admin").Find(&student)
// SELECT * FROM `students` WHERE (role = 'admin' OR role = 'super_admin') AND `students`.`deleted_at` IS NULL AND `students`.`id` = 4
// Struct
db.Where("name = 'jinzhu'").Or(Student{Name: "贾维斯 2", Age: 18}).Find(&student)
// SELECT * FROM `students` WHERE (name = 'jinzhu' OR (`students`.`name` = '贾维斯 2' AND `students`.`a = 18)) AND `students`.`deleted_at` IS NULL AND `students`.`id` = 4
// Map
db.Where("name = 'jinzhu'").Or(map[string]interface{}{"name": "贾维斯 2", "age": 18}).Find(&student)
// SELECT * FROM `students` WHERE (name = 'jinzhu' OR (`age` = 18 AND `name` = '贾维斯 2')) AND `studen.`deleted_at` IS NULL AND `students`.`id` = 4
For more complicated SQL queries. please also refer to Group Conditions in Advanced Query.
`Select``允许您指定要从数据库检索的字段。否则，GORM将默认选择所有字段
db.Select("name", "age").Find(&student)
// SELECT `name`,`age` FROM `students` WHERE `students`.`deleted_at` IS NULL
db.Select([]string{"name", "age"}).Find(&student)
// SELECT `name`,`age` FROM `students` WHERE `students`.`deleted_at` IS NULL
db.Table("students").Select("COALESCE(age,?)", 42).Rows()
// SELECT COALESCE(age,42) FROM `students`
Also check out Smart Select Fields
Specify order when retrieving records from the database
指定从数据库检索记录时的顺序
db.Order("age desc, name").Find(&student)
// SELECT * FROM `students` WHERE `students`.`deleted_at` IS NULL ORDER BY age desc, name
// Multiple orders
db.Order("age desc").Order("name").Find(&student)
// SELECT * FROM `students` WHERE `students`.`deleted_at` IS NULL AND `students`.`id` = 35 ORDER BY age desc,name
db.Clauses(clause.OrderBy{
Expression: clause.Expr{SQL: "FIELD(id,?)", Vars: []interface{}{[]int{1, 2, 3}}, WithoutParentheses: true},
}).Find(&Student{})
// SELECT * FROM `students` WHERE `students`.`deleted_at` IS NULL ORDER BY FIELD(id,1,2,3)
Limit specify the max number of records to retrieve Offset specify the number of records to skip before starting to return the records
Limit指定要检索的最大记录数Offset指定开始返回记录之前要跳过的记录数
db.Limit(3).Find(&users)
// SELECT * FROM users LIMIT 3;
// Cancel limit condition with -1
db.Limit(10).Find(&users1).Limit(-1).Find(&users2)
// SELECT * FROM users LIMIT 10; (users1)
// SELECT * FROM users; (users2)
db.Offset(3).Find(&users)
// SELECT * FROM users OFFSET 3;
db.Limit(10).Offset(5).Find(&users)
// SELECT * FROM users OFFSET 5 LIMIT 10;
// Cancel offset condition with -1
db.Offset(10).Find(&users1).Offset(-1).Find(&users2)
// SELECT * FROM users OFFSET 10; (users1)
// SELECT * FROM users; (users2)
Refer to Pagination for details on how to make a paginator
type result struct {
Date time.Time
Total int
}
db.Model(&User{}).Select("name, sum(age) as total").Where("name LIKE ?", "group%").Group("name").First(&result)
// SELECT name, sum(age) as total FROM `users` WHERE name LIKE "group%" GROUP BY `name` LIMIT 1
db.Model(&User{}).Select("name, sum(age) as total").Group("name").Having("name = ?", "group").Find(&result)
// SELECT name, sum(age) as total FROM `users` GROUP BY `name` HAVING name = "group"
rows, err := db.Table("orders").Select("date(created_at) as date, sum(amount) as total").Group("date(created_at)").Rows()
defer rows.Close()
for rows.Next() {
...
}
rows, err := db.Table("orders").Select("date(created_at) as date, sum(amount) as total").Group("date(created_at)").Having("sum(amount) > ?", 100).Rows()
defer rows.Close()
for rows.Next() {
...
}
type Result struct {
Date time.Time
Total int64
}
db.Table("orders").Select("date(created_at) as date, sum(amount) as total").Group("date(created_at)").Having("sum(amount) > ?", 100).Scan(&results)
Selecting distinct values from the model
从模型中选择不同的值
db.Distinct("name", "age").Order("name, age desc").Find(&results)
Distinct works with Pluck and Count too
Specify Joins conditions-指定连接条件
type result struct {
Name string
Email string
}
db.Model(&User{}).Select("users.name, emails.email").Joins("left join emails on emails.user_id = users.id").Scan(&result{})
// SELECT users.name, emails.email FROM `users` left join emails on emails.user_id = users.id
rows, err := db.Table("users").Select("users.name, emails.email").Joins("left join emails on emails.user_id = users.id").Rows()
for rows.Next() {
...
}
db.Table("users").Select("users.name, emails.email").Joins("left join emails on emails.user_id = users.id").Scan(&results)
// multiple joins with parameter
db.Joins("JOIN emails ON emails.user_id = users.id AND emails.email = ?", "jinzhu@example.org").Joins("JOIN credit_cards ON credit_cards.user_id = users.id").Where("credit_cards.number = ?", "411111111111").Find(&user)
You can use Joins eager loading associations with a single SQL, for example:
可以使用’Joins’来加载与单个SQL的关联
db.Joins("Company").Find(&users)
// SELECT `users`.`id`,`users`.`name`,`users`.`age`,`Company`.`id` AS `Company__id`,`Company`.`name` AS `Company__name` FROM `users` LEFT JOIN `companies` AS `Company` ON `users`.`company_id` = `Company`.`id`;
Join with conditions
db.Joins("Company", DB.Where(&Company{Alive: true})).Find(&users)
// SELECT `users`.`id`,`users`.`name`,`users`.`age`,`Company`.`id` AS `Company__id`,`Company`.`name` AS `Company__name` FROM `users` LEFT JOIN `companies` AS `Company` ON `users`.`company_id` = `Company`.`id` AND `Company`.`alive` = true;
For more details, please refer to Preloading (Eager Loading).
You can also use Joins to join a derived table.
type User struct {
Id int
Age int
}
type Order struct {
UserId int
FinishedAt *time.Time
}
query := db.Table("order").Select("MAX(order.finished_at) as latest").Joins("left join user user on order.user_id = user.id").Where("user.age > ?", 18).Group("order.user_id")
db.Model(&Order{}).Joins("join (?) q on order.finished_at = q.latest", query).Scan(&results)
// SELECT `order`.`user_id`,`order`.`finished_at` FROM `order` join (SELECT MAX(order.finished_at) as latest FROM `order` left join user user on order.user_id = user.id WHERE user.age > 18 GROUP BY `order`.`user_id`) q on order.finished_at = q.latest
Scanning results into a struct works similarly to the way we use Find–
将结果扫描到结构体中的工作方式，与“Find”类似
type Result struct {
Name string
Age int
}
var result Result
db.Table("users").Select("name", "age").Where("name = ?", "Antonio").Scan(&result)
// Raw SQL
db.Raw("SELECT name, age FROM users WHERE name = ?", "Antonio").Scan(&result)
GORM 允许通过 Select 方法选择特定的字段，如果您在应用程序中经常使用此功能，你也可以定义一个较小的结构体，以实现调用 API 时自动选择特定的字段，例如：
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
注意
QueryFields模式会根据当前 model 的所有字段名称进行 select。
db, err := gorm.Open(sqlite.Open("gorm.db"), &gorm.Config{
QueryFields: true,
})
db.Find(&user)
// SELECT `users`.`name`, `users`.`age`, ... FROM `users` // 带上这个选项
// Session Mode
db.Session(&gorm.Session{QueryFields: true}).Find(&user)
// SELECT `users`.`name`, `users`.`age`, ... FROM `users`
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`
db.Clauses(clause.Locking{
Strength: "UPDATE",
Options: "NOWAIT",
}).Find(&users)
// SELECT * FROM `users` FOR UPDATE NOWAIT
查看 原生 SQL 及构造器 获取详情
子查询可以嵌套在查询中，GORM 允许在使用 *gorm.DB 对象作为参数时生成子查询
db.Where("amount > (?)", db.Table("orders").Select("AVG(amount)")).Find(&orders)
// SELECT * FROM "orders" WHERE amount > (SELECT AVG(amount) FROM "orders");
subQuery := db.Select("AVG(age)").Where("name LIKE ?", "name%").Table("users")
db.Select("AVG(age) as avgage").Group("name").Having("AVG(age) > (?)", subQuery).Find(&results)
// SELECT AVG(age) as avgage FROM `users` GROUP BY `name` HAVING AVG(age) > (SELECT AVG(age) FROM `users` WHERE name LIKE "name%")
GORM 允许您在 Table 方法中通过 FROM 子句使用子查询，例如：
db.Table("(?) as u", db.Model(&User{}).Select("name", "age")).Where("age = ?", 18).Find(&User{})
// SELECT * FROM (SELECT `name`,`age` FROM `users`) as u WHERE age = 18
subQuery1 := db.Model(&User{}).Select("name")
subQuery2 := db.Model(&Pet{}).Select("name")
db.Table("(?) as u, (?) as p", subQuery1, subQuery2).Find(&User{})
// SELECT * FROM (SELECT `name` FROM `users`) as u, (SELECT `name` FROM `pets`) as p
使用 Group 条件可以更轻松的编写复杂 SQL
db.Where(
db.Where("pizza = ?", "pepperoni").Where(db.Where("size = ?", "small").Or("size = ?", "medium")),
).Or(
db.Where("pizza = ?", "hawaiian").Where("size = ?", "xlarge"),
).Find(&Pizza{}).Statement
// SELECT * FROM `pizzas` WHERE (pizza = "pepperoni" AND (size = "small" OR size = "medium")) OR (pizza = "hawaiian" AND size = "xlarge")
带多个列的 In 查询
db.Where("(name, age, role) IN ?", [][]interface{}{{"jinzhu", 18, "admin"}, {"jinzhu2", 19, "user"}}).Find(&users)
// SELECT * FROM users WHERE (name, age, role) IN (("jinzhu", 18, "admin"), ("jinzhu 2", 19, "user"));
GORM 支持 sql.NamedArg 和 map[string]interface{}{} 形式的命名参数，例如：
db.Where("name1 = @name OR name2 = @name", sql.Named("name", "jinzhu")).Find(&user)
// SELECT * FROM `users` WHERE name1 = "jinzhu" OR name2 = "jinzhu"
db.Where("name1 = @name OR name2 = @name", map[string]interface{}{"name": "jinzhu"}).First(&user)
// SELECT * FROM `users` WHERE name1 = "jinzhu" OR name2 = "jinzhu" ORDER BY `users`.`id` LIMIT 1
查看 原生 SQL 及构造器 获取详情
GORM 允许扫描结果至 map[string]interface{} 或 []map[string]interface{}，此时别忘了指定 Model 或 Table，例如：
result := map[string]interface{}{}
db.Model(&User{}).First(&result, "id = ?", 1)
var results []map[string]interface{}
db.Table("users").Find(&results)
获取第一条匹配的记录，或者根据给定的条件初始化一个实例（仅支持 sturct 和 map 条件）
// 未找到 user，则根据给定的条件初始化一条记录
db.FirstOrInit(&user, User{Name: "non_existing"})
// user -> User{Name: "non_existing"}
// 找到了 `name` = `jinzhu` 的 user
db.Where(User{Name: "jinzhu"}).FirstOrInit(&user)
// user -> User{ID: 111, Name: "Jinzhu", Age: 18}
// 找到了 `name` = `jinzhu` 的 user
db.FirstOrInit(&user, map[string]interface{}{"name": "jinzhu"})
// user -> User{ID: 111, Name: "Jinzhu", Age: 18}
如果没有找到记录，可以使用包含更多的属性的结构体初始化 user，Attrs 不会被用于生成查询 SQL
// 未找到 user，则根据给定的条件以及 Attrs 初始化 user
db.Where(User{Name: "non_existing"}).Attrs(User{Age: 20}).FirstOrInit(&user)
// SELECT * FROM USERS WHERE name = 'non_existing' ORDER BY id LIMIT 1;
// user -> User{Name: "non_existing", Age: 20}
// 未找到 user，则根据给定的条件以及 Attrs 初始化 user
db.Where(User{Name: "non_existing"}).Attrs("age", 20).FirstOrInit(&user)
// SELECT * FROM USERS WHERE name = 'non_existing' ORDER BY id LIMIT 1;
// user -> User{Name: "non_existing", Age: 20}
// 找到了 `name` = `jinzhu` 的 user，则忽略 Attrs
db.Where(User{Name: "Jinzhu"}).Attrs(User{Age: 20}).FirstOrInit(&user)
// SELECT * FROM USERS WHERE name = jinzhu' ORDER BY id LIMIT 1;
// user -> User{ID: 111, Name: "Jinzhu", Age: 18}
不管是否找到记录，Assign 都会将属性赋值给 struct，但这些属性不会被用于生成查询 SQL，也不会被保存到数据库
// 未找到 user，根据条件和 Assign 属性初始化 struct
db.Where(User{Name: "non_existing"}).Assign(User{Age: 20}).FirstOrInit(&user)
// user -> User{Name: "non_existing", Age: 20}
// 找到 `name` = `jinzhu` 的记录，依然会更新 Assign 相关的属性
db.Where(User{Name: "Jinzhu"}).Assign(User{Age: 20}).FirstOrInit(&user)
// SELECT * FROM USERS WHERE name = jinzhu' ORDER BY id LIMIT 1;
// user -> User{ID: 111, Name: "Jinzhu", Age: 20}
Get first matched record or create a new one with given conditions (only works with struct, map conditions), RowsAffected returns created/updated record’s count
// User not found, create a new record with give conditions
result := db.FirstOrCreate(&user, User{Name: "non_existing"})
// INSERT INTO "users" (name) VALUES ("non_existing");
// user -> User{ID: 112, Name: "non_existing"}
// result.RowsAffected // => 0
// Found user with `name` = `jinzhu`
result := db.Where(User{Name: "jinzhu"}).FirstOrCreate(&user)
// user -> User{ID: 111, Name: "jinzhu", "Age": 18}
// result.RowsAffected // => 0
如果没有找到记录，可以使用包含更多的属性的结构体创建记录，Attrs 不会被用于生成查询 SQL 。
// 未找到 user，根据条件和 Assign 属性创建记录
db.Where(User{Name: "non_existing"}).Attrs(User{Age: 20}).FirstOrCreate(&user)
// SELECT * FROM users WHERE name = 'non_existing' ORDER BY id LIMIT 1;
// INSERT INTO "users" (name, age) VALUES ("non_existing", 20);
// user -> User{ID: 112, Name: "non_existing", Age: 20}
// 找到了 `name` = `jinzhu` 的 user，则忽略 Attrs
db.Where(User{Name: "jinzhu"}).Attrs(User{Age: 20}).FirstOrCreate(&user)
// SELECT * FROM users WHERE name = 'jinzhu' ORDER BY id LIMIT 1;
// user -> User{ID: 111, Name: "jinzhu", Age: 18}
不管是否找到记录，Assign 都会将属性赋值给 struct，并将结果写回数据库
// 未找到 user，根据条件和 Assign 属性创建记录
db.Where(User{Name: "non_existing"}).Assign(User{Age: 20}).FirstOrCreate(&user)
// SELECT * FROM users WHERE name = 'non_existing' ORDER BY id LIMIT 1;
// INSERT INTO "users" (name, age) VALUES ("non_existing", 20);
// user -> User{ID: 112, Name: "non_existing", Age: 20}
// 找到了 `name` = `jinzhu` 的 user，依然会根据 Assign 更新记录
db.Where(User{Name: "jinzhu"}).Assign(User{Age: 20}).FirstOrCreate(&user)
// SELECT * FROM users WHERE name = 'jinzhu' ORDER BY id LIMIT 1;
// UPDATE users SET age=20 WHERE id = 111;
// user -> User{ID: 111, Name: "jinzhu", Age: 20}
优化器提示用于控制查询优化器选择某个查询执行计划，GORM 通过 gorm.io/hints 提供支持，例如：
import "gorm.io/hints"
db.Clauses(hints.New("MAX_EXECUTION_TIME(10000)")).Find(&User{})
// SELECT * /*+ MAX_EXECUTION_TIME(10000) */ FROM `users`
索引提示允许传递索引提示到数据库，以防查询计划器出现混乱。
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`)"
参考 优化器提示、索引、备注 获取详情
GORM 支持通过行进行迭代
rows, err := db.Model(&User{}).Where("name = ?", "jinzhu").Rows()
defer rows.Close()
for rows.Next() {
var user User
// ScanRows 方法用于将一行记录扫描至结构体
db.ScanRows(rows, &user)
// 业务逻辑...
}
用于批量查询并处理记录
// 每次批量处理 100 条
result := db.Where("processed = ?", false).FindInBatches(&results, 100, func(tx *gorm.DB, batch int) error {
for _, result := range results {
// 批量处理找到的记录
}
tx.Save(&results)
tx.RowsAffected // 本次批量操作影响的记录数
batch // Batch 1, 2, 3
// 如果返回错误会终止后续批量操作
return nil
})
result.Error // returned error
result.RowsAffected // 整个批量操作影响的记录数
对于查询操作，GORM 支持 AfterFind 钩子，查询记录后会调用它，详情请参考 钩子
func (u *User) AfterFind(tx *gorm.DB) (err error) {
if u.Role == "" {
u.Role = "user"
}
return
}
Pluck 用于从数据库查询单个列，并将结果扫描到切片。如果您想要查询多列，您应该使用 Select 和 Scan
var ages []int64
db.Model(&users).Pluck("age", &ages)
var names []string
db.Model(&User{}).Pluck("name", &names)
db.Table("deleted_users").Pluck("name", &names)
// Distinct Pluck
db.Model(&User{}).Distinct().Pluck("Name", &names)
// SELECT DISTINCT `name` FROM `users`
// 超过一列的查询，应该使用 `Scan` 或者 `Find`，例如：
db.Select("name", "age").Scan(&users)
db.Select("name", "age").Find(&users)
Scopes 允许你指定常用的查询，可以在调用方法时引用这些查询
func AmountGreaterThan1000(db *gorm.DB) *gorm.DB {
return db.Where("amount > ?", 1000)
}
func PaidWithCreditCard(db *gorm.DB) *gorm.DB {
return db.Where("pay_mode_sign = ?", "C")
}
func PaidWithCod(db *gorm.DB) *gorm.DB {
return db.Where("pay_mode_sign = ?", "C")
}
func OrderStatus(status []string) func (db *gorm.DB) *gorm.DB {
return func (db *gorm.DB) *gorm.DB {
return db.Where("status IN (?)", status)
}
}
db.Scopes(AmountGreaterThan1000, PaidWithCreditCard).Find(&orders)
// 查找所有金额大于 1000 的信用卡订单
db.Scopes(AmountGreaterThan1000, PaidWithCod).Find(&orders)
// 查找所有金额大于 1000 的货到付款订单
db.Scopes(AmountGreaterThan1000, OrderStatus([]string{"paid", "shipped"})).Find(&orders)
// 查找所有金额大于 1000 且已付款或已发货的订单
查看 Scopes 获取详情
Count 用于获取匹配的记录数
var count int64
db.Model(&User{}).Where("name = ?", "jinzhu").Or("name = ?", "jinzhu 2").Count(&count)
// SELECT count(1) FROM users WHERE name = 'jinzhu' OR name = 'jinzhu 2'
db.Model(&User{}).Where("name = ?", "jinzhu").Count(&count)
// SELECT count(1) FROM users WHERE name = 'jinzhu'; (count)
db.Table("deleted_users").Count(&count)
// SELECT count(1) FROM deleted_users;
// Count with Distinct
db.Model(&User{}).Distinct("name").Count(&count)
// SELECT COUNT(DISTINCT(`name`)) FROM `users`
db.Table("deleted_users").Select("count(distinct(name))").Count(&count)
// SELECT count(distinct(name)) FROM deleted_users
// Count with Group
users := []User{
{Name: "name1"},
{Name: "name2"},
{Name: "name3"},
{Name: "name3"},
}
db.Model(&User{}).Group("name").Count(&count)
count // => 3
Save 会保存所有的字段，即使字段是零值
var student Student
db.First(&student)
student.Name = "贾维斯"
student.Age = 100
db.Save(&student)
// UPDATE `students` SET `created_at`='2024-01-10 21:54:45.79',`updated_at`='2024-01-11 16:01:53.082',`deleted_at`=NULL,`name`='贾维斯',`age`=100,`birthday`='0000-00-00 00:00:00' WHERE `students`.`deleted_at` IS NULL AND ` = 1
当使用 Update 更新单个列时，你需要指定条件，否则会返回 ErrMissingWhereClause 错误，查看 Block Global Updates 获取详情。当使用了 Model 方法，且该对象主键有值，该值会被用于构建条件，例如：
var student Student
// 条件更新(如果有多条，全更新)
db.Model(&Student{}).Where("active = ?", true).Update("name", "jarvis")
// UPDATE `students` SET `name`='jarvis-01',`updated_at`='2024-01-11 16:17:39.741' WHERE `students`.`deleted_at` IS NULL AND `id` = 1;
// User 的 ID 是 `1`
db.First(&student)
db.Model(&student).Update("name", "jarvis-01")
// SELECT * FROM `students` WHERE `students`.`deleted_at` IS NULL AND `students`.`id` = 1 ORDER BY `students`.`id` LIMIT 1;
// 根据条件和 model 的值进行更新
db.First(&student)
db.Model(&student).Where("active = ?", true).Update("name", "jarvis-02")
// UPDATE `students` SET `name`='jarvis-02',`updated_at`='2024-01-11 16:17:39.746' WHERE active = true AND `students`.`deleted_at` IS NULL AND `id` = 1;
Updates 方法支持 struct 和 map[string]interface{} 参数。当使用 struct 更新时，默认情况下，GORM 只会更新非零值的字段
//根据 `struct` 更新属性，只会更新非零值的字段
db.First(&student)
db.Model(&student).Updates(Student{Name: "hello", Age: 99, Active: false})
//SELECT * FROM `students` WHERE `students`.`deleted_at` IS NULL AND `students`.`id` = 1 ORDER BY `students`.`id` LIMIT 1;
//根据 `map` 更新属性
db.First(&student)
db.Model(&student).Updates(map[string]interface{}{"name": "海绵宝宝", "age": 88, "active": false})
//UPDATE `students` SET `active`=false,`age`=88,`name`='海绵宝宝',`updated_at`='2024-01-11 16:39:56.28HERE `students`.`deleted_at` IS NULL AND `id` = 1
注意 当通过 struct 更新时，GORM 只会更新非零字段。 如果您想确保指定字段被更新，你应该使用
Select更新选定字段，或使用map来完成更新操作
如果您想要在更新时选定、忽略某些字段，您可以使用 Select、Omit
// 使用 Map 进行 Select
// Student's ID is `111`:
db.First(&student)
// 只更新name
db.Model(&student).Select("name").Updates(map[string]interface{}{"name": "jarvis_002", "age": 66, "active": false})
// UPDATE `students` SET `name`='jarvis_002',`updated_at`='2024-01-11 17:10:53.649' WHERE `students`.`deleted_at` IS NULL AND `id` = 1
db.First(&student)
// 除了name都更新
db.Model(&student).Omit("name").Updates(map[string]interface{}{"name": "hello", "age": 18, "active": true})
// UPDATE `students` SET `active`=true,`age`=18,`updated_at`='2024-01-11 17:10:53.654' WHERE `students`.`deleted_at` IS NULL AND `id` = 1;
// 使用 Struct 进行 Select（会 select 零值的字段），零值字段也会更新
// 注意 user的id不能为0
db.First(&student)
db.Model(&student).Select("Name", "Age").Updates(Student{Name: "new_name", Age: 0})
// UPDATE `students` SET `updated_at`='2024-01-11 17:10:53.658',`name`='new_name',`age`=0 WHERE `students`.`deleted_at` IS NULL AND `id` = 1
// Select 所有字段（查询包括零值字段的所有字段）
db.First(&student)
fmt.Println(student)
db.Model(&student).Select("*").Updates(Student{Name: "jarvis_004", Birthday: time.Now(), Age: 33, Active: false, Model: gorm.Model{ID: 3, CreatedAt: time.Now(), UpdatedAt: time.Now()}})
// Select 除 "CreatedAt","birthday"外的所有字段（包括零值字段的所有字段）,注意id会变成0
db.Model(&student).Select("*").Omit("CreatedAt", "birthday").Updates(Student{Name: "zhuliang", Age: 0})
对于更新操作，GORM 支持 BeforeSave、BeforeUpdate、AfterSave、AfterUpdate 钩子，这些方法将在更新记录时被调用，详情请参阅 钩子
func (u *User) BeforeUpdate(tx *gorm.DB) (err error) {
if u.Role == "admin" {
return errors.New("admin user not allowed to update")
}
return
}
// 测试
func (u *User) BeforeUpdate(tx *gorm.DB) (err error) {
fmt.Println("修改了")
return
}
如果您尚未通过 Model 指定记录的主键，则 GORM 会执行批量更新
// 根据 struct 更新--->批量更新不能用Hook
db.Model(&student).Where("active = ?", true).Updates(Student{Name: "hello", Age: 18})
// UPDATE `students` SET `updated_at`='2024-01-11 17:28:11.236',`name`='hello',`age`=18 WHERE active = true AND `students`.`deleted_at` IS NULL
// 根据 map 更新
db.Table("students").Where("id IN ?", []int{1, 2}).Updates(map[string]interface{}{"name": "jiajia_009", "age": 99})
// UPDATE `students` SET `age`=99,`name`='jiajia_009' WHERE id IN (1,2)
如果在没有任何条件的情况下执行批量更新，默认情况下，GORM 不会执行该操作，并返回 ErrMissingWhereClause 错误
对此，你必须加一些条件，或者使用原生 SQL，或者启用 AllowGlobalUpdate 模式，例如：
db.Model(&User{}).Update("name", "jinzhu").Error //报错 gorm.ErrMissingWhereClause
db.Model(&User{}).Where("1 = 1").Update("name", "jinzhu") //可以
// UPDATE users SET `name` = "jinzhu" WHERE 1=1
// 方式一：直接执行原生sql可以全局更新
db.Exec("UPDATE users SET name = ?", "jinzhu")
// UPDATE users SET name = "jinzhu"
// 方式二:使用session
db.Session(&gorm.Session{AllowGlobalUpdate: true}).Model(&Student{}).Update("name", "贾维斯_010")
// UPDATE `students` SET `name`='贾维斯_010',`updated_at`='2024-01-11 19:12:42.408' WHERE `students`.`eted_at` IS NULL
获取受更新影响的行数
// 通过 `RowsAffected` 得到更新的记录数
result := db.Model(User{}).Where("active = ?", true).Updates(User{Name: "hello", Age: 18})
fmt.Println(result.RowsAffected) // 更新的记录数
fmt.Println(result.Error) // 错误
GORM 允许使用 SQL 表达式更新列，例如：
// user 的 ID 是 `1`
db.First(&student)
db.Model(&student).Update("age", gorm.Expr("age * ? + ?", 2, 100))
// UPDATE `students` SET `age`=age * 2 + 100,`updated_at`='2024-01-11 19:29:40.712' WHERE `students`.`deleted_at` IS NULL
db.First(&student)
db.Model(&student).Updates(map[string]interface{}{"age": gorm.Expr("age - ? + ?", 2, 100)})
// UPDATE `students` SET `age`=age - 2 + 100,`updated_at`='2024-01-11 19:29:40.713' WHERE `students`.`deleted_at` IS NULL
db.First(&student)
db.Model(&student).UpdateColumn("age", gorm.Expr("age - ?", 10))
// UPDATE `students` SET `age`=age - 10 WHERE `students`.`deleted_at` IS NULL
db.First(&student)
db.Model(&student).Where("age > 100").UpdateColumn("age", gorm.Expr("age - ?", 100))
// UPDATE `students` SET `age`=age - 100 WHERE age > 100 AND `students`.`deleted_at` IS NULL
并且 GORM 也允许使用 SQL 表达式、自定义数据类型的 Context Valuer 来更新，例如：
// 根据自定义数据类型创建
type Location struct {
X, Y int
}
func (loc Location) GormValue(ctx context.Context, db *gorm.DB) clause.Expr {
return clause.Expr{
SQL: "ST_PointFromText(?)",
Vars: []interface{}{fmt.Sprintf("POINT(%d %d)", loc.X, loc.Y)},
}
}
db.Model(&User{ID: 1}).Updates(User{
Name: "jinzhu",
Location: Location{X: 100, Y: 100},
})
// UPDATE `user_with_points` SET `name`="jinzhu",`location`=ST_PointFromText("POINT(100 100)") WHERE `id` = 1
使用子查询更新表
db.Model(&user).Update("company_name", db.Model(&Company{}).Select("name").Where("companies.id = users.company_id"))
// UPDATE "users" SET "company_name" = (SELECT name FROM companies WHERE companies.id = users.company_id);
db.Table("users as u").Where("name = ?", "jinzhu").Update("company_name", db.Table("companies as c").Select("name").Where("c.id = u.company_id"))
//UPDATE users as u SET `company_name`=(SELECT name FROM companies as c WHERE c.id = u.company_id) WHERE name = 'jinzhu'
db.Table("users as u").Where("name = ?", "jinzhu").Updates(map[string]interface{}{"company_name": db.Table("companies as c").Select("name").Where("c.id = u.company_id")})
//UPDATE users as u SET `company_name`=(SELECT name FROM companies as c WHERE c.id = u.company_id) WHERE name = 'jinzhu'
如果您想在更新时跳过 Hook 方法且不追踪更新时间，可以使用 UpdateColumn、UpdateColumns，其用法类似于 Update、Updates
// 更新单个列
db.Model(&user).UpdateColumn("name", "hello")
// UPDATE users SET name='hello' WHERE id = 111;
// 更新多个列
db.Model(&user).UpdateColumns(User{Name: "hello", Age: 18})
// UPDATE users SET name='hello', age=18 WHERE id = 111;
// 更新选中的列
db.Model(&user).Select("name", "age").UpdateColumns(User{Name: "hello", Age: 0})
// UPDATE users SET name='hello', age=0 WHERE id = 111;
返回被修改的数据，仅适用于支持 Returning 的数据库，例如：
// 返回所有列
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}}
// 返回指定的列
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}}
GORM 提供了 Changed 方法，它可以被用在 Before Update Hook 里，它会返回字段是否有变更的布尔值
Changed 方法只能与 Update、Updates 方法一起使用，并且它只是检查 Model 对象字段的值与 Update、Updates 的值是否相等，如果值有变更，且字段没有被忽略，则返回 true
func (u *Student) BeforeUpdate(tx *gorm.DB) (err error) {
// 如果 age 字段有变更，不允许，直接报错
if tx.Statement.Changed("age") {
fmt.Println("xx")
return errors.New("age not allowed to change")
}
// 如果 Name 字段有变更，把年龄改为18
if tx.Statement.Changed("Name") {
tx.Statement.SetColumn("age", 18)
}
// 如果任意字段有变更，修改CreatedAt时间
if tx.Statement.Changed() {
tx.Statement.SetColumn("CreatedAt", time.Now())
}
return nil
}
//修改名字，把age 设为18
db.Model(&Student{Model: gorm.Model{ID: 1}, Name: "贾维斯"}).Updates(map[string]interface{}{"name": "贾维斯_001"})
// 名字修改，但是名字其实并没有变，所以年龄不会被修改
db.Model(&Student{Model: gorm.Model{ID: 1}, Name: "贾维斯_001"}).Updates(map[string]interface{}{"name": "贾维斯_002"})
//修改名字，把age 设为18
db.Model(&Student{Model: gorm.Model{ID: 1}, Name: "贾维斯_002"}).Updates(Student{Name: "贾维斯_01"})
//名字修改，但是名字其实并没有变，所以年龄不会被修改
db.Model(&Student{Model: gorm.Model{ID: 1}, Name: "贾维斯_01"}).Updates(Student{Name: "贾维斯_02"})
// 不允许修改age
db.Model(&Student{}).Where("id=?", 1).Updates(map[string]interface{}{"age": 100})
//任意字段有变更，更新CreatedAt时间
db.Model(&Student{}).Where("id=?", 1).Updates(map[string]interface{}{"active": false})
若要在 Before 钩子中改变要更新的值，如果它是一个完整的更新，可以使用 Save；否则，应该使用 SetColumn ，例如：
func (student *Student) BeforeSave(tx *gorm.DB) (err error) {
if pw, err := bcrypt.GenerateFromPassword(student.Password, 0); err == nil {
tx.Statement.SetColumn("EncryptedPassword", pw)
}
if tx.Statement.Changed("Code") {
student.Age += 20
tx.Statement.SetColumn("Age", student.Age)
}
return nil
}
db.Model(&student).Update("Name", "jinzhu")
db.First(&student)
db.Model(&student).Update("Name", "贾维斯_010")
// 只要更新名字，就把名字加密
func (student *Student) BeforeSave(tx *gorm.DB) (err error) {
if genName, err := bcrypt.GenerateFromPassword([]byte(student.Name), 0); err == nil {
tx.Statement.SetColumn("name", genName)
}
return nil
}
db.First(&student)
db.Model(&student).Where("id=?", 1).Updates(Student{Name: "", Code: 200}) // 仅更新非零值字段
// UPDATE `students` SET `updated_at`='2024-01-11 20:02:28.633',`code`=200 WHERE id=1 AND `students`.`deleted_at` IS NULL
// 可以看到Name字段不会更新，这是合理的，因为如果零值字段也更新，Student表中好多数据都会被更新为空
//表模型修改为
type Student struct {
gorm.Model
Name sql.NullString
Age uint8
Code int64
}
// 修改语句为
db.Model(&student).Where("id=?", 1).Updates(Student{Code: 200, Name: sql.NullString{"", true}})
// 可以看到 name 字段也更新了，这是因为 sql.NullString{"", true} 会被认为是非零值，所以会更新
//UPDATE `students` SET `updated_at`='2024-01-11 20:07:10.218',`name`='',`code`=200 WHERE id=1 AND `students`.`deleted_at` IS NULL
// 表模型修改为
type Student struct {
gorm.Model
Name *string
Age uint8
Code int64
}
// 修改语句为
db.First(&student)
var empty = ""
db.Model(&student).Updates(Student{Code: 200, Name: &empty}) // 仅更新非零值字段
// 可以name 字段更新了，这是因为 name 字段是指针类型，指针类型的零值是 nil，所以会被认为是非零值，所以会更新
// UPDATE `students` SET `updated_at`='2024-01-11 20:10:56.543',`name`='',`code`=200 WHERE `students`.`deleted_at` IS NULL
个人感觉还是用指针比较好,在平时Go开发中,遇事不决就用指针,没毛病.
删除一条记录时，删除对象需要指定主键，否则会触发 批量 Delete，例如：
// student 的 ID 是 `1`--->软删除
db.First(&student)
db.Delete(&student)
// UPDATE `students` SET `deleted_at`='2024-01-11 21:08:14.107' WHERE `students`.`deleted_at` IS NULL
// 带额外条件的删除
db.First(&student)
db.Where("id = ?", "1").Delete(&student)
// UPDATE `students` SET `deleted_at`='2024-01-11 21:10:48.182' WHERE id = '1' AND `students`.`deleted_at` IS NULL
GORM 允许通过主键(可以是复合主键)和内联条件来删除对象，它可以使用数字（如以下例子。也可以使用字符串——译者注）。查看 查询-内联条件（Query Inline Conditions） 了解详情。
// 先插入
var student = Student{Name: "灰太狼", Age: 99}
db.Create(&student)
db.Delete(&student, 3)
db.Delete(&student, "10")
db.Delete(&student, []int{0, 1, 2})
对于删除操作，GORM 支持 BeforeDelete、AfterDelete Hook，在删除记录时会调用这些方法，查看 Hook 获取详情
func (s *Student) BeforeDelete(tx *gorm.DB) (err error) {
if s.Role == "admin" {
return errors.New("admin student not allowed to delete")
}
return
}
如果指定的值不包括主属性，那么 GORM 会执行批量删除，它将删除所有匹配的记录
db.Where("name LIKE ?", "%灰太狼%").Delete(&student)
db.Delete(&student, "name LIKE ?", "%灰太狼%")
如果在没有任何条件的情况下执行批量删除，GORM 不会执行该操作，并返回 ErrMissingWhereClause错误
对此，你必须加一些条件，或者使用原生 SQL，或者启用 AllowGlobalUpdate 模式，例如：
//db.Delete(&student).Error // gorm.ErrMissingWhereClause
db.Where("1 = 1").Delete(&student)
// DELETE FROM `students` WHERE 1=1
db.Exec("DELETE FROM students")
// DELETE FROM students
db.Session(&gorm.Session{AllowGlobalUpdate: true}).Delete(&student)
// DELETE FROM students
返回被删除的数据，仅适用于支持 Returning 的数据库，例如：
// 返回所有列
var student []Student
db.Clauses(clause.Returning{}).Where("role = ?", "admin").Delete(&student)
// DELETE FROM `students` WHERE role = "admin" RETURNING *
// students => []User{{ID: 1, Name: "jinzhu", Role: "admin", Salary: 100}, {ID: 2, Name: "jinzhu.2", Role: "admin", Salary: 1000}}
// 返回指定的列
db.Clauses(clause.Returning{Columns: []clause.Column{{Name: "name"}, {Name: "salary"}}}).Where("role = ?", "admin").Delete(&student)
// DELETE FROM `students` WHERE role = "admin" RETURNING `name`, `salary`
// students => []User{{ID: 0, Name: "jinzhu", Role: "", Salary: 100}, {ID: 0, Name: "jinzhu.2", Role: "", Salary: 1000}}
如果您的模型包含了一个 gorm.deletedat 字段（gorm.Model 已经包含了该字段)，它将自动获得软删除的能力！
拥有软删除能力的模型调用 Delete 时，记录不会被数据库。但 GORM 会将 DeletedAt 置为当前时间， 并且你不能再通过普通的查询方法找到该记录。
// user 的 ID 是 `111`
db.Delete(&user)
// UPDATE users SET deleted_at="2013-10-29 10:23" WHERE id = 111;
// 批量删除
db.Where("age = ?", 20).Delete(&User{})
// UPDATE users SET deleted_at="2013-10-29 10:23" WHERE age = 20;
// 在查询时会忽略被软删除的记录
db.Where("age = 20").Find(&user)
// SELECT * FROM users WHERE age = 20 AND deleted_at IS NULL;
如果您不想引入 gorm.Model，您也可以这样启用软删除特性：
type Student struct {
ID int
Deleted gorm.DeletedAt
Name string
}
您可以使用 Unscoped 找到被软删除的记录
var student []Student
db.Unscoped().Where("name = 贾维斯").Find(&student)
fmt.Println(student)
您也可以使用 Unscoped 永久删除匹配的记录
var student []Student
db.Unscoped().Where("id = 38").Find(&student)
db.Unscoped().Delete(&student)
将 unix 时间戳作为 delete flag
// go get -u gorm.io/plugin/soft_delete
//db.AutoMigrate(&User2{})
import "gorm.io/plugin/soft_delete"
type User2 struct {
ID uint
Name string
DeletedAt soft_delete.DeletedAt
}
// 创建
var user2 User2=User2{Name: "小欣同学"}
db.Create(&user2)
// 删除
db.Delete(&user2)
// 查询
SELECT * FROM users WHERE deleted_at = 0;
// 删除
UPDATE users SET deleted_at = /* current unix second */ WHERE ID = 1;
INFO 在配合 unique 字段使用软删除时，您需要使用这个基于 unix 时间戳的
DeletedAt字段创建一个复合索引，例如：import "gorm.io/plugin/soft_delete" type User struct { ID uint Name string `gorm:"uniqueIndex:udx_name"` DeletedAt soft_delete.DeletedAt `gorm:"uniqueIndex:udx_name"` }
使用 1 / 0 作为 delete flag
import "gorm.io/plugin/soft_delete"
type User struct {
ID uint
Name string
IsDel soft_delete.DeletedAt `gorm:"softDelete:flag"`
}
// 查询
SELECT * FROM users WHERE is_del = 0;
// 删除
UPDATE users SET is_del = 1 WHERE ID = 1;
原生查询 SQL 和 Scan
type User3 struct {
ID int
Name string
Age int
}
// 创建表
db.AutoMigrate(&User3{})
// 插入数据
var student = []User3{
{Name: "小爱同学", Age: 12},
{Name: "小欣同学", Age: 13},
{Name: "小冰同学", Age: 18},
{Name: "小芳同学", Age: 192},
}
db.Create(&student)
for _, user := range student {
fmt.Println(user.ID)
}
var user User3
db.Raw("SELECT id, name, age FROM user3 WHERE name = ?", "小欣同学").Scan(&user)
fmt.Println(user)
var age int
db.Raw("SELECT SUM(age) FROM user3 WHERE name like ?", "%小欣同学%").Scan(&age)
fmt.Println(age)
var users []User3
db.Raw("SELECT * FROM user3 WHERE id > ?", 0).Scan(&users)
fmt.Println(users)
// mysql 不支持
var users []User3
db.Raw("UPDATE user3 SET name = ? WHERE age = ? RETURNING id, name", "小欣同学", 12).Scan(&users)
fmt.Println(users)
Exec 原生 SQL
db.Exec("DROP TABLE users")
db.Exec("UPDATE orders SET shipped_at = ? WHERE id IN ?", time.Now(), []int64{1, 2, 3})
// Exec with SQL Expression
db.Exec("UPDATE users SET money = ? WHERE name = ?", gorm.Expr("money * ? + ?", 10000, 1), "jinzhu")
注意 GORM 允许缓存预编译 SQL 语句来提高性能，查看 性能 获取详情
GORM 支持 sql.NamedArg、map[string]interface{}{} 或 struct 形式的命名参数，例如：
// 测试
var user []User3
db.Where("name = @name OR age = @age", sql.Named("name", "小欣同学"), sql.Named("age", "12")).Find(&user)
fmt.Println(user)
var users []User3
db.Where("name = @name OR age = @age", sql.Named("name", "小欣同学"), sql.Named("age", "12")).Find(&users)
fmt.Println(users)
// 其他一样
db.Where("name1 = @name OR name2 = @name", map[string]interface{}{"name": "jinzhu2"}).First(&result3)
// SELECT * FROM `users` WHERE name1 = "jinzhu2" OR name2 = "jinzhu2" ORDER BY `users`.`id` LIMIT 1
// 原生 SQL 及命名参数
db.Raw("SELECT * FROM users WHERE name1 = @name OR name2 = @name2 OR name3 = @name",
sql.Named("name", "jinzhu1"), sql.Named("name2", "jinzhu2")).Find(&user)
// SELECT * FROM users WHERE name1 = "jinzhu1" OR name2 = "jinzhu2" OR name3 = "jinzhu1"
db.Exec("UPDATE users SET name1 = @name, name2 = @name2, name3 = @name",
sql.Named("name", "jinzhunew"), sql.Named("name2", "jinzhunew2"))
// UPDATE users SET name1 = "jinzhunew", name2 = "jinzhunew2", name3 = "jinzhunew"
db.Raw("SELECT * FROM users WHERE (name1 = @name AND name3 = @name) AND name2 = @name2",
map[string]interface{}{"name": "jinzhu", "name2": "jinzhu2"}).Find(&user)
// SELECT * FROM users WHERE (name1 = "jinzhu" AND name3 = "jinzhu") AND name2 = "jinzhu2"
type NamedArgument struct {
Name string
Name2 string
}
db.Raw("SELECT * FROM users WHERE (name1 = @Name AND name3 = @Name) AND name2 = @Name2",
NamedArgument{Name: "jinzhu", Name2: "jinzhu2"}).Find(&user)
// SELECT * FROM users WHERE (name1 = "jinzhu" AND name3 = "jinzhu") AND name2 = "jinzhu2"
在不执行的情况下生成 SQL 及其参数，可以用于准备或测试生成的 SQL，详情请参考 Session
var user User3
//session := db.Session(&gorm.Session{DryRun: true})
//session.First(&user, 1)
//fmt.Println(user)
stmt := db.Session(&gorm.Session{DryRun: true}).First(&user, 1).Statement
fmt.Println(stmt.SQL.String()) //=> SELECT * FROM `users` WHERE `id` = $1 ORDER BY `id`
fmt.Println(stmt.Vars) //=> []interface{}{1}
返回生成的 SQL 但不执行。
GORM使用 database/sql 的参数占位符来构建 SQL 语句，它会自动转义参数以避免 SQL 注入，但我们不保证生成 SQL 的安全，请只用于调试。
var users []User3
sql := db.ToSQL(func(tx *gorm.DB) *gorm.DB {
return tx.Model(&User3{}).Where("id = ?", 100).Limit(10).Order("age desc").Find(&users)
})
fmt.Println(sql)
//SELECT * FROM `user3` WHERE id = 100 ORDER BY age desc LIMIT 10
Row & Rows获取 *sql.Row 结果
// 使用 GORM API 构建 SQL
var name string
var age int
row := db.Table("user3").Where("name = ?", "小欣同学").Select("name", "age").Row()
row.Scan(&name, &age)
fmt.Println(name)
fmt.Println(age)
// 使用原生 SQL
var name string
var age int
row := db.Raw("select name, age from user3 where name = ?", "小欣同学").Row()
row.Scan(&name, &age)
fmt.Println(name)
fmt.Println(age)
获取 *sql.Rows 结果
// 使用 GORM API 构建 SQL
var name string
var age int
rows, _ := db.Model(&User3{}).Where("name like ?", "%小欣同学%").Select("name, age").Rows()
defer rows.Close()
for rows.Next() {
rows.Scan(&name, &age)
fmt.Printf("name是：%s，age是：%d\n",name,age)
}
// 原生 SQL
var name string
var age int
rows, err := db.Raw("select name, age from user3 where name like ?", "%小欣同学%").Rows()
defer rows.Close()
for rows.Next() {
rows.Scan(&name, &age)
fmt.Printf("name是：%s，age是：%d\n",name,age)
}
转到 FindInBatches 获取如何在批量中查询和处理记录的信息， 转到 Group 条件 获取如何构建复杂 SQL 查询的信息
sql.Rows 扫描至 model使用 ScanRows 将一行记录扫描至 struct，例如：
rows, err := db.Model(&User3{}).Where("name like ?", "%小欣同学%").Select("name, age").Rows()
defer rows.Close()
var user User3
for rows.Next() {
// ScanRows 将一行扫描至 user
db.ScanRows(rows, &user)
fmt.Println(user)
}
Run mutliple SQL in same db tcp connection (not in a transaction)
db.Connection(func(tx *gorm.DB) error {
tx.Exec("SET my.role = ?", "admin")
tx.First(&User{})
})
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
You can define your own Clause and use it with GORM, it needs to implements Interface
Check out examples for reference
For different databases, Clauses may generate different SQL, for example:
db.Offset(10).Limit(5).Find(&users)
// Generated for SQL Server
// SELECT * FROM "users" OFFSET 10 ROW FETCH NEXT 5 ROWS ONLY
// Generated for MySQL
// SELECT * FROM `users` LIMIT 5 OFFSET 10
Which is supported because GORM allows database driver register Clause Builder to replace the default one, take the Limit as example
GORM defined Many Clauses, 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...);
GORM provides interface StatementModifier allows you modify statement to match your requirements, take Hints as example
import "gorm.io/hints"
db.Clauses(hints.New("hint")).Find(&User{})
// SELECT * /*+ hint */ FROM `users`