智能选择字段

GORM 允许通过 Select 方法选择特定的字段,如果您在应用程序中经常使用此功能,你也可以定义一个较小的结构体,以实现调用 API 时自动选择特定的字段,例如:

  1. type User struct {
  2. ID uint
  3. Name string
  4. Age int
  5. Gender string
  6. // 假设后面还有几百个字段...
  7. }
  8. type APIUser struct {
  9. ID uint
  10. Name string
  11. }
  12. // 查询时会自动选择 `id`, `name` 字段
  13. db.Model(&User{}).Limit(10).Find(&APIUser{})
  14. // SELECT `id`, `name` FROM `users` LIMIT 10

注意 QueryFields 模式会根据当前 model 的所有字段名称进行 select。

  1. db, err := gorm.Open(sqlite.Open("gorm.db"), &gorm.Config{
  2. QueryFields: true,
  3. })
  4. db.Find(&user)
  5. // SELECT `users`.`name`, `users`.`age`, ... FROM `users` // 带上这个选项
  6. // Session Mode
  7. db.Session(&gorm.Session{QueryFields: true}).Find(&user)
  8. // SELECT `users`.`name`, `users`.`age`, ... FROM `users`

Locking (FOR UPDATE)

GORM 支持多种类型的锁,例如:

  1. db.Clauses(clause.Locking{Strength: "UPDATE"}).Find(&users)
  2. // SELECT * FROM `users` FOR UPDATE
  3. db.Clauses(clause.Locking{
  4. Strength: "SHARE",
  5. Table: clause.Table{Name: clause.CurrentTable},
  6. }).Find(&users)
  7. // SELECT * FROM `users` FOR SHARE OF `users`
  8. db.Clauses(clause.Locking{
  9. Strength: "UPDATE",
  10. Options: "NOWAIT",
  11. }).Find(&users)
  12. // SELECT * FROM `users` FOR UPDATE NOWAIT

查看 原生 SQL 及构造器 获取详情

子查询

子查询可以嵌套在查询中,GORM 允许在使用 *gorm.DB 对象作为参数时生成子查询

  1. db.Where("amount > (?)", db.Table("orders").Select("AVG(amount)")).Find(&orders)
  2. // SELECT * FROM "orders" WHERE amount > (SELECT AVG(amount) FROM "orders");
  3. subQuery := db.Select("AVG(age)").Where("name LIKE ?", "name%").Table("users")
  4. db.Select("AVG(age) as avgage").Group("name").Having("AVG(age) > (?)", subQuery).Find(&results)
  5. // SELECT AVG(age) as avgage FROM `users` GROUP BY `name` HAVING AVG(age) > (SELECT AVG(age) FROM `users` WHERE name LIKE "name%")

From 子查询

GORM 允许您在 Table 方法中通过 FROM 子句使用子查询,例如:

  1. db.Table("(?) as u", db.Model(&User{}).Select("name", "age")).Where("age = ?", 18}).Find(&User{})
  2. // SELECT * FROM (SELECT `name`,`age` FROM `users`) as u WHERE `age` = 18
  3. subQuery1 := db.Model(&User{}).Select("name")
  4. subQuery2 := db.Model(&Pet{}).Select("name")
  5. db.Table("(?) as u, (?) as p", subQuery1, subQuery2).Find(&User{})
  6. // SELECT * FROM (SELECT `name` FROM `users`) as u, (SELECT `name` FROM `pets`) as p

Group 条件

使用 Group 条件可以更轻松的编写复杂 SQL

  1. db.Where(
  2. db.Where("pizza = ?", "pepperoni").Where(db.Where("size = ?", "small").Or("size = ?", "medium")),
  3. ).Or(
  4. db.Where("pizza = ?", "hawaiian").Where("size = ?", "xlarge"),
  5. ).Find(&Pizza{}).Statement
  6. // SELECT * FROM `pizzas` WHERE (pizza = "pepperoni" AND (size = "small" OR size = "medium")) OR (pizza = "hawaiian" AND size = "xlarge")

带多个列的 In

带多个列的 In 查询

  1. db.Where("(name, age, role) IN ?", [][]interface{}{{"jinzhu", 18, "admin"}, {"jinzhu2", 19, "user"}}).Find(&users)
  2. // SELECT * FROM users WHERE (name, age, role) IN (("jinzhu", 18, "admin"), ("jinzhu 2", 19, "user"));

命名参数

GORM 支持 sql.NamedArgmap[string]interface{}{} 形式的命名参数,例如:

  1. db.Where("name1 = @name OR name2 = @name", sql.Named("name", "jinzhu")).Find(&user)
  2. // SELECT * FROM `users` WHERE name1 = "jinzhu" OR name2 = "jinzhu"
  3. db.Where("name1 = @name OR name2 = @name", map[string]interface{}{"name": "jinzhu"}).First(&user)
  4. // SELECT * FROM `users` WHERE name1 = "jinzhu" OR name2 = "jinzhu" ORDER BY `users`.`id` LIMIT 1

查看 原生 SQL 及构造器 获取详情

Find 至 map

GORM 允许扫描结果至 map[string]interface{}[]map[string]interface{},此时别忘了指定 ModelTable,例如:

  1. result := map[string]interface{}{}
  2. db.Model(&User{}).First(&result, "id = ?", 1)
  3. var results []map[string]interface{}
  4. db.Table("users").Find(&results)

FirstOrInit

获取第一条匹配的记录,或者根据给定的条件初始化一个实例(仅支持 sturct 和 map 条件)

  1. // 未找到 user,则根据给定的条件初始化一条记录
  2. db.FirstOrInit(&user, User{Name: "non_existing"})
  3. // user -> User{Name: "non_existing"}
  4. // 找到了 `name` = `jinzhu` 的 user
  5. db.Where(User{Name: "jinzhu"}).FirstOrInit(&user)
  6. // user -> User{ID: 111, Name: "Jinzhu", Age: 18}
  7. // 找到了 `name` = `jinzhu` 的 user
  8. db.FirstOrInit(&user, map[string]interface{}{"name": "jinzhu"})
  9. // user -> User{ID: 111, Name: "Jinzhu", Age: 18}

如果没有找到记录,可以使用包含更多的属性的结构体初始化 user,Attrs 不会被用于生成查询 SQL

  1. // 未找到 user,则根据给定的条件以及 Attrs 初始化 user
  2. db.Where(User{Name: "non_existing"}).Attrs(User{Age: 20}).FirstOrInit(&user)
  3. // SELECT * FROM USERS WHERE name = 'non_existing' ORDER BY id LIMIT 1;
  4. // user -> User{Name: "non_existing", Age: 20}
  5. // 未找到 user,则根据给定的条件以及 Attrs 初始化 user
  6. db.Where(User{Name: "non_existing"}).Attrs("age", 20).FirstOrInit(&user)
  7. // SELECT * FROM USERS WHERE name = 'non_existing' ORDER BY id LIMIT 1;
  8. // user -> User{Name: "non_existing", Age: 20}
  9. // 找到了 `name` = `jinzhu` 的 user,则忽略 Attrs
  10. db.Where(User{Name: "Jinzhu"}).Attrs(User{Age: 20}).FirstOrInit(&user)
  11. // SELECT * FROM USERS WHERE name = jinzhu' ORDER BY id LIMIT 1;
  12. // user -> User{ID: 111, Name: "Jinzhu", Age: 18}

不管是否找到记录,Assign 都会将属性赋值给 struct,但这些属性不会被用于生成查询 SQL,也不会被保存到数据库

  1. // 未找到 user,根据条件和 Assign 属性初始化 struct
  2. db.Where(User{Name: "non_existing"}).Assign(User{Age: 20}).FirstOrInit(&user)
  3. // user -> User{Name: "non_existing", Age: 20}
  4. // 找到 `name` = `jinzhu` 的记录,依然会更新 Assign 相关的属性
  5. db.Where(User{Name: "Jinzhu"}).Assign(User{Age: 20}).FirstOrInit(&user)
  6. // SELECT * FROM USERS WHERE name = jinzhu' ORDER BY id LIMIT 1;
  7. // user -> User{ID: 111, Name: "Jinzhu", Age: 20}

FirstOrCreate

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

  1. // User not found, create a new record with give conditions
  2. result := db.FirstOrCreate(&user, User{Name: "non_existing"})
  3. // INSERT INTO "users" (name) VALUES ("non_existing");
  4. // user -> User{ID: 112, Name: "non_existing"}
  5. // result.RowsAffected // => 0
  6. // Found user with `name` = `jinzhu`
  7. result := db.Where(User{Name: "jinzhu"}).FirstOrCreate(&user)
  8. // user -> User{ID: 111, Name: "jinzhu", "Age": 18}
  9. // result.RowsAffected // => 0

如果没有找到记录,可以使用包含更多的属性的结构体创建记录,Attrs 不会被用于生成查询 SQL 。

  1. // 未找到 user,根据条件和 Assign 属性创建记录
  2. db.Where(User{Name: "non_existing"}).Attrs(User{Age: 20}).FirstOrCreate(&user)
  3. // SELECT * FROM users WHERE name = 'non_existing' ORDER BY id LIMIT 1;
  4. // INSERT INTO "users" (name, age) VALUES ("non_existing", 20);
  5. // user -> User{ID: 112, Name: "non_existing", Age: 20}
  6. // 找到了 `name` = `jinzhu` 的 user,则忽略 Attrs
  7. db.Where(User{Name: "jinzhu"}).Attrs(User{Age: 20}).FirstOrCreate(&user)
  8. // SELECT * FROM users WHERE name = 'jinzhu' ORDER BY id LIMIT 1;
  9. // user -> User{ID: 111, Name: "jinzhu", Age: 18}

不管是否找到记录,Assign 都会将属性赋值给 struct,并将结果写回数据库

  1. // 未找到 user,根据条件和 Assign 属性创建记录
  2. db.Where(User{Name: "non_existing"}).Assign(User{Age: 20}).FirstOrCreate(&user)
  3. // SELECT * FROM users WHERE name = 'non_existing' ORDER BY id LIMIT 1;
  4. // INSERT INTO "users" (name, age) VALUES ("non_existing", 20);
  5. // user -> User{ID: 112, Name: "non_existing", Age: 20}
  6. // 找到了 `name` = `jinzhu` 的 user,依然会根据 Assign 更新记录
  7. db.Where(User{Name: "jinzhu"}).Assign(User{Age: 20}).FirstOrCreate(&user)
  8. // SELECT * FROM users WHERE name = 'jinzhu' ORDER BY id LIMIT 1;
  9. // UPDATE users SET age=20 WHERE id = 111;
  10. // user -> User{ID: 111, Name: "jinzhu", Age: 20}

优化器、索引提示

优化器提示用于控制查询优化器选择某个查询执行计划,GORM 通过 gorm.io/hints 提供支持,例如:

  1. import "gorm.io/hints"
  2. db.Clauses(hints.New("MAX_EXECUTION_TIME(10000)")).Find(&User{})
  3. // SELECT * /*+ MAX_EXECUTION_TIME(10000) */ FROM `users`

索引提示允许传递索引提示到数据库,以防查询计划器出现混乱。

  1. import "gorm.io/hints"
  2. db.Clauses(hints.UseIndex("idx_user_name")).Find(&User{})
  3. // SELECT * FROM `users` USE INDEX (`idx_user_name`)
  4. db.Clauses(hints.ForceIndex("idx_user_name", "idx_user_id").ForJoin()).Find(&User{})
  5. // SELECT * FROM `users` FORCE INDEX FOR JOIN (`idx_user_name`,`idx_user_id`)"

参考 优化器提示、索引、备注 获取详情

迭代

GORM 支持通过行进行迭代

  1. rows, err := db.Model(&User{}).Where("name = ?", "jinzhu").Rows()
  2. defer rows.Close()
  3. for rows.Next() {
  4. var user User
  5. // ScanRows 方法用于将一行记录扫描至结构体
  6. db.ScanRows(rows, &user)
  7. // 业务逻辑...
  8. }

FindInBatches

用于批量查询并处理记录

  1. // 每次批量处理 100 条
  2. result := db.Where("processed = ?", false).FindInBatches(&results, 100, func(tx *gorm.DB, batch int) error {
  3. for _, result := range results {
  4. // 批量处理找到的记录
  5. }
  6. tx.Save(&results)
  7. tx.RowsAffected // 本次批量操作影响的记录数
  8. batch // Batch 1, 2, 3
  9. // 如果返回错误会终止后续批量操作
  10. return nil
  11. })
  12. result.Error // returned error
  13. result.RowsAffected // 整个批量操作影响的记录数

查询钩子

对于查询操作,GORM 支持 AfterFind 钩子,查询记录后会调用它,详情请参考 钩子

  1. func (u *User) AfterFind(tx *gorm.DB) (err error) {
  2. if u.Role == "" {
  3. u.Role = "user"
  4. }
  5. return
  6. }

Pluck

Pluck 用于从数据库查询单个列,并将结果扫描到切片。如果您想要查询多列,您应该使用 SelectScan

  1. var ages []int64
  2. db.Model(&users).Pluck("age", &ages)
  3. var names []string
  4. db.Model(&User{}).Pluck("name", &names)
  5. db.Table("deleted_users").Pluck("name", &names)
  6. // Distinct Pluck
  7. db.Model(&User{}).Distinct().Pluck("Name", &names)
  8. // SELECT DISTINCT `name` FROM `users`
  9. // 超过一列的查询,应该使用 `Scan` 或者 `Find`,例如:
  10. db.Select("name", "age").Scan(&users)
  11. db.Select("name", "age").Find(&users)

Scope

Scopes 允许你指定常用的查询,可以在调用方法时引用这些查询

  1. func AmountGreaterThan1000(db *gorm.DB) *gorm.DB {
  2. return db.Where("amount > ?", 1000)
  3. }
  4. func PaidWithCreditCard(db *gorm.DB) *gorm.DB {
  5. return db.Where("pay_mode_sign = ?", "C")
  6. }
  7. func PaidWithCod(db *gorm.DB) *gorm.DB {
  8. return db.Where("pay_mode_sign = ?", "C")
  9. }
  10. func OrderStatus(status []string) func (db *gorm.DB) *gorm.DB {
  11. return func (db *gorm.DB) *gorm.DB {
  12. return db.Where("status IN (?)", status)
  13. }
  14. }
  15. db.Scopes(AmountGreaterThan1000, PaidWithCreditCard).Find(&orders)
  16. // 查找所有金额大于 1000 的信用卡订单
  17. db.Scopes(AmountGreaterThan1000, PaidWithCod).Find(&orders)
  18. // 查找所有金额大于 1000 的货到付款订单
  19. db.Scopes(AmountGreaterThan1000, OrderStatus([]string{"paid", "shipped"})).Find(&orders)
  20. // 查找所有金额大于 1000 且已付款或已发货的订单

查看 Scopes 获取详情

Count

Count 用于获取匹配的记录数

  1. var count int64
  2. db.Model(&User{}).Where("name = ?", "jinzhu").Or("name = ?", "jinzhu 2").Count(&count)
  3. // SELECT count(1) FROM users WHERE name = 'jinzhu' OR name = 'jinzhu 2'
  4. db.Model(&User{}).Where("name = ?", "jinzhu").Count(&count)
  5. // SELECT count(1) FROM users WHERE name = 'jinzhu'; (count)
  6. db.Table("deleted_users").Count(&count)
  7. // SELECT count(1) FROM deleted_users;
  8. // Count with Distinct
  9. db.Model(&User{}).Distinct("name").Count(&count)
  10. // SELECT COUNT(DISTINCT(`name`)) FROM `users`
  11. db.Table("deleted_users").Select("count(distinct(name))").Count(&count)
  12. // SELECT count(distinct(name)) FROM deleted_users
  13. // Count with Group
  14. users := []User{
  15. {Name: "name1"},
  16. {Name: "name2"},
  17. {Name: "name3"},
  18. {Name: "name3"},
  19. }
  20. db.Model(&User{}).Group("name").Count(&count)
  21. count // => 3