GORM 指南|青训营

特性

• 全功能 ORM
• 关联 (Has One，Has Many，Belongs To，Many To Many，多态，单表继承)
• Create，Save，Update，Delete，Find 中钩子方法
• 支持 Preload、Joins 的预加载
• 事务，嵌套事务，Save Point，Rollback To Saved Point
• Context、预编译模式、DryRun 模式
• 批量插入，FindInBatches，Find/Create with Map，使用 SQL 表达式、Context Valuer 进行 CRUD
• SQL 构建器，Upsert，数据库锁，Optimizer/Index/Comment Hint，命名参数，子查询
• 复合主键，索引，约束
• Auto Migration
• 自定义 Logger
• 灵活的可扩展插件 API：Database Resolver（多数据库，读写分离）、Prometheus…
• 每个特性都经过了测试的重重考验
• 开发者友好

安装

 go get -u gorm.io/gorm

快速入门

package main
​
import (
  "gorm.io/gorm"
  "gorm.io/driver/sqlite"
)
​
type Product struct {
  gorm.Model
  Code  string
  Price uint
}
​
func main() {
  db, err := gorm.Open(sqlite.Open("test.db"), &gorm.Config{})
  if err != nil {
    panic("failed to connect database")
  }
​
  // 迁移 schema
  db.AutoMigrate(&Product{})
​
  // Create
  db.Create(&Product{Code: "D42", Price: 100})
​
  // Read
  var product Product
  db.First(&product, 1) // 根据整型主键查找
  db.First(&product, "code = ?", "D42") // 查找 code 字段值为 D42 的记录
​
  // Update - 将 product 的 price 更新为 200
  db.Model(&product).Update("Price", 200)
  // Update - 更新多个字段
  db.Model(&product).Updates(Product{Price: 200, Code: "F42"}) // 仅更新非零值字段
  db.Model(&product).Updates(map[string]interface{}{"Price": 200, "Code": "F42"})
​
  // Delete - 删除 product
  db.Delete(&product, 1)
}

连接Mysql数据库

package main
​
import (
    "gorm.io/driver/mysql"
    "gorm.io/gorm"
)
​
func main() {
    // 参考 https://github.com/go-sql-driver/mysql#dsn-data-source-name 获取详情
    dsn := "name:password@tcp(127.0.0.1:3306)/dev?charset=utf8mb4&parseTime=True&loc=Local"
    db, err := gorm.Open(mysql.Open(dsn), &gorm.Config{})
    if err != nil {
        println("err")
        return
    }
    println(db)
}

插入操作

1.创建记录

user := User{Name: "Jinzhu", Age: 18, Birthday: time.Now()}
​
result := db.Create(&user) // 通过数据的指针来创建
​
user.ID             // 返回插入数据的主键
result.Error        // 返回 error
result.RowsAffected // 返回插入记录的条数

我们还可以使用 Create() 创建多项记录：

users := []*User{
    User{Name: "Jinzhu", Age: 18, Birthday: time.Now()},
    User{Name: "Jackson", Age: 19, Birthday: time.Now()},
}
​
result := db.Create(users) // pass a slice to insert multiple row
​
result.Error        // returns error
result.RowsAffected // returns inserted records count

2.用指定的字段创建记录

创建记录并为指定的字段分配值：

db.Select("Name", "Age", "CreatedAt").Create(&user)
// INSERT INTO `users` (`name`,`age`,`created_at`) VALUES ("jinzhu", 18, "2020-07-04 11:05:21.775")

创建记录并忽略要省略的传递字段的值：

db.Omit("Name", "Age", "CreatedAt").Create(&user)
// INSERT INTO `users` (`birthday`,`updated_at`) VALUES ("2020-01-01 00:00:00.000", "2020-07-04 11:05:21.775")

3.批量插入

var users = []User{{Name: "jinzhu1"}, {Name: "jinzhu2"}, {Name: "jinzhu3"}}
db.Create(&users)
for _, user := range users {  
    user.ID // 1,2,3
}

4.根据 Map 创建

db.Model(&User{}).Create(map[string]interface{}{
  "Name": "jinzhu", "Age": 18,
})
​
// batch insert from `[]map[string]interface{}{}`
db.Model(&User{}).Create([]map[string]interface{}{
  {"Name": "jinzhu_1", "Age": 18},
  {"Name": "jinzhu_2", "Age": 20},
})

5.使用 SQL 表达式、Context Valuer 创建记录

// Create from 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)"));
​
// Create from customized data type
type Location struct {
    X, Y int
}
​
// Scan implements the sql.Scanner interface
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)"))

查询操作

1. 查询

// 获取第一条记录，按主键排序
    db.First(&user)
    //// SELECT * FROM users ORDER BY id LIMIT 1;
​
    // 获取最后一条记录，按主键排序
    db.Last(&user)
    //// SELECT * FROM users ORDER BY id DESC LIMIT 1;
​
    // 获取所有记录
    db.Find(&users)
    //// SELECT * FROM users;
​
    // 使用主键获取记录
    db.First(&user, 10)
    //// SELECT * FROM users WHERE id = 10;

2. where条件查询

// 获取第一个匹配记录
    db.Where("name = ?", "jinzhu").First(&user)
    //// SELECT * FROM users WHERE name = 'jinzhu' limit 1;
​
    // 获取所有匹配记录
    db.Where("name = ?", "jinzhu").Find(&users)
    //// SELECT * FROM users WHERE name = 'jinzhu';
​
    db.Where("name <> ?", "jinzhu").Find(&users)
​
    // IN
    db.Where("name in (?)", []string{"jinzhu", "jinzhu 2"}).Find(&users)
​
    // LIKE
    db.Where("name LIKE ?", "%jin%").Find(&users)
​
    // AND
    db.Where("name = ? AND age >= ?", "jinzhu", "22").Find(&users)
​
    // Time
    db.Where("updated_at > ?", lastWeek).Find(&users)
​
    db.Where("created_at BETWEEN ? AND ?", lastWeek, today).Find(&users)

3. Not条件查询

    db.Not("name", "jinzhu").First(&user)
    //// SELECT * FROM users WHERE name <> "jinzhu" LIMIT 1;
​
    // Not In
    db.Not("name", []string{"jinzhu", "jinzhu 2"}).Find(&users)
    //// SELECT * FROM users WHERE name NOT IN ("jinzhu", "jinzhu 2");
​
    // Not In slice of primary keys
    db.Not([]int64{1,2,3}).First(&user)
    //// SELECT * FROM users WHERE id NOT IN (1,2,3);
​
    db.Not([]int64{}).First(&user)
    //// SELECT * FROM users;
​
    // Plain SQL
    db.Not("name = ?", "jinzhu").First(&user)
    //// SELECT * FROM users WHERE NOT(name = "jinzhu");
​
    // Struct
    db.Not(User{Name: "jinzhu"}).First(&user)
    //// SELECT * FROM users WHERE name <> "jinzhu";

4. 带内联条件的查询

  // 按主键获取
    db.First(&user, 23)
    //// SELECT * FROM users WHERE id = 23 LIMIT 1;
​
    // 简单SQL
    db.Find(&user, "name = ?", "jinzhu")
    //// SELECT * FROM users WHERE name = "jinzhu";
​
    db.Find(&users, "name <> ? AND age > ?", "jinzhu", 20)
    //// SELECT * FROM users WHERE name <> "jinzhu" AND age > 20;
​
    // Struct
    db.Find(&users, User{Age: 20})
    //// SELECT * FROM users WHERE age = 20;
​
    // Map
    db.Find(&users, map[string]interface{}{"age": 20})
    //// SELECT * FROM users WHERE age = 20

5. Or条件查询

    db.Where("role = ?", "admin").Or("role = ?", "super_admin").Find(&users)
    //// SELECT * FROM users WHERE role = 'admin' OR role = 'super_admin';
​
    // Struct
    db.Where("name = 'jinzhu'").Or(User{Name: "jinzhu 2"}).Find(&users)
    //// SELECT * FROM users WHERE name = 'jinzhu' OR name = 'jinzhu 2';
​
    // Map
    db.Where("name = 'jinzhu'").Or(map[string]interface{}{"name": "jinzhu 2"}).Find(&users)

6.Group & Having

    rows, err := db.Table("orders").Select("date(created_at) as date, sum(amount) as total").Group("date(created_at)").Rows()
    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()
    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)

Update操作

1.更新全部字段

 db.First(&user)
​
    user.Name = "jinzhu 2"
    user.Age = 100
    db.Save(&user)
​
    //// UPDATE users SET name='jinzhu 2', age=100, birthday='2016-01-01', updated_at = '2013-11-17 21:34:10' WHERE id=111;

2.更新更改字段

如果只想更新更改的字段，可以使用Update,Updates

 // 更新单个属性（如果更改）
    db.Model(&user).Update("name", "hello")
    //// UPDATE users SET name='hello', updated_at='2013-11-17 21:34:10' WHERE id=111;
​
    // 使用组合条件更新单个属性
    db.Model(&user).Where("active = ?", true).Update("name", "hello")
    //// UPDATE users SET name='hello', updated_at='2013-11-17 21:34:10' WHERE id=111 AND active=true;
​
    // 使用`map`更新多个属性，只会更新这些更改的字段
    db.Model(&user).Updates(map[string]interface{}{"name": "hello", "age": 18, "actived": false})
    //// UPDATE users SET name='hello', age=18, actived=false, updated_at='2013-11-17 21:34:10' WHERE id=111;
​
    // 使用`struct`更新多个属性，只会更新这些更改的和非空白字段
    db.Model(&user).Updates(User{Name: "hello", Age: 18})
    //// UPDATE users SET name='hello', age=18, updated_at = '2013-11-17 21:34:10' WHERE id = 111;
​
    // 警告:当使用struct更新时，FORM将仅更新具有非空值的字段
    // 对于下面的更新，什么都不会更新为""，0，false是其类型的空白值
    db.Model(&user).Updates(User{Name: "", Age: 0, Actived: false})

3.更新选择的字段

如果您只想在更新时更新或忽略某些字段，可以使用Select,Omit

 db.Model(&user).Select("name").Updates(map[string]interface{}{"name": "hello", "age": 18, "actived": false})
    //// UPDATE users SET name='hello', updated_at='2013-11-17 21:34:10' WHERE id=111;
​
    db.Model(&user).Omit("name").Updates(map[string]interface{}{"name": "hello", "age": 18, "actived": false})
    //// UPDATE users SET age=18, actived=false, updated_at='2013-11-17 21:34:10' WHERE id=111;

4.使用SQL表达式更新

    DB.Model(&product).Update("price", gorm.Expr("price * ? + ?", 2, 100))
    //// UPDATE "products" SET "price" = price * '2' + '100', "updated_at" = '2013-11-17 21:34:10' WHERE "id" = '2';
​
    DB.Model(&product).Updates(map[string]interface{}{"price": gorm.Expr("price * ? + ?", 2, 100)})
    //// UPDATE "products" SET "price" = price * '2' + '100', "updated_at" = '2013-11-17 21:34:10' WHERE "id" = '2';
​
    DB.Model(&product).UpdateColumn("quantity", gorm.Expr("quantity - ?", 1))
    //// UPDATE "products" SET "quantity" = quantity - 1 WHERE "id" = '2';
​
    DB.Model(&product).Where("quantity > 1").UpdateColumn("quantity", gorm.Expr("quantity - ?", 1))
    //// UPDATE "products" SET "quantity" = quantity - 1 WHERE "id" = '2' AND quantity > 1;

Delete操作

1.删除/软删除

警告删除记录时，需要确保其主要字段具有值，GORM将使用主键删除记录，如果主要字段为空，GORM将删除模型的所有记录

 // 删除存在的记录
    db.Delete(&email)
    //// DELETE from emails where id=10;
​
    // 为Delete语句添加额外的SQL选项
    db.Set("gorm:delete_option", "OPTION (OPTIMIZE FOR UNKNOWN)").Delete(&email)
    //// DELETE from emails where id=10 OPTION (OPTIMIZE FOR UNKNOWN);

2.批量删除

删除所有匹配记录

    db.Where("email LIKE ?", "%jinzhu%").Delete(Email{})
    //// DELETE from emails where email LIKE "%jinhu%";
​
    db.Delete(Email{}, "email LIKE ?", "%jinzhu%")
    //// DELETE from emails where email LIKE "%jinhu%";

3.软删除

如果模型有DeletedAt字段，它将自动获得软删除功能！ 那么在调用Delete时不会从数据库中永久删除，而是只将字段DeletedAt的值设置为当前时间。

    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;
​
    // 使用Unscoped查找软删除的记录
    db.Unscoped().Where("age = 20").Find(&users)
    //// SELECT * FROM users WHERE age = 20;
​
    // 使用Unscoped永久删除记录
    db.Unscoped().Delete(&order)
    //// DELETE FROM orders WHERE id=10;