Golang——GORM CRUD

Author：二滑大魔王
发布时间：July 23, 2024
300views
No comments
13215字数
Categories： Basis Golang

创建

结构体

type User struct {
    ID           uint
    Name         string
    Email        *string
    Age          uint8
    Birthday     *time.Time
    MemberNumber sql.NullString
    ActivatedAt  sql.NullTime
    CreatedAt    time.Time
    UpdatedAt    time.Time
}

单条创建

package main

func main() {
    // 定义全局日志
    newLogger := logger.New()

    // 参考 https://github.com/go-sql-driver/mysql#dsn-data-source-name 获取详情
    dsn := "root@tcp(127.0.0.1:3306)/gorm?charset=utf8mb4&parseTime=True&loc=Local"
    db, _ := gorm.Open(mysql.Open(dsn), &gorm.Config{
        Logger: newLogger,
    })

    // 自动迁移
    db.AutoMigrate(&User{})

    // 创建
    empty := ""
    user := User{
        Name:  "Gyi",
        Email: &empty,
    }
    result := db.Create(&user)
    fmt.Println(user.ID) // 创建的主键id
    fmt.Println(result.Error) // 执行错误
    fmt.Println(result.RowsAffected) // 执行成功行数
}

批量插入

package main

func main() {
    // 定义全局日志
    newLogger := logger.New()

    // 参考 https://github.com/go-sql-driver/mysql#dsn-data-source-name 获取详情
    dsn := "root@tcp(127.0.0.1:3306)/gorm?charset=utf8mb4&parseTime=True&loc=Local"
    db, _ := gorm.Open(mysql.Open(dsn), &gorm.Config{
        Logger: newLogger,
    })

    // 自动迁移
    db.AutoMigrate(&User{})

    // 批量创建
    var users = []User{{Name: "Gyi"}, {Name: "Yna"}}
    db.Create(&users)

    // 打印结果
    for _, user := range users {
        fmt.Println(user.ID) // 循环输出批量插入用户主键
    }
}

查询

单条查询

// 获取第一条记录（主键升序）
db.First(&user)
// SELECT * FROM users ORDER BY id LIMIT 1;

// 获取一条记录，没有指定排序字段
db.Take(&user)
// SELECT * FROM users LIMIT 1;

// 获取最后一条记录（主键降序）
db.Last(&user)
// SELECT * FROM users ORDER BY id DESC LIMIT 1;

result := db.First(&user)
result.RowsAffected // 返回找到的记录数
result.Error        // returns error or nil

// 检查 ErrRecordNotFound 错误
errors.Is(result.Error, gorm.ErrRecordNotFound)

多条查询

// 查询
var users []User

result := db.Find(&users, []int{1, 2, 3, 4, 5})
fmt.Printf("len: %d\n", result.RowsAffected)
for _, user := range users {
    fmt.Printf("user: %d, name: %s\n", user.ID, user.Name)
}

条件

string

// Get first matched record
db.Where("name = ?", "jinzhu").First(&user)
// SELECT * FROM users WHERE name = 'jinzhu' ORDER BY id LIMIT 1;

// Get all matched records
db.Where("name <> ?", "jinzhu").Find(&users)
// SELECT * FROM users WHERE name <> 'jinzhu';

// IN
db.Where("name IN ?", []string{"jinzhu", "jinzhu 2"}).Find(&users)
// SELECT * FROM users WHERE name IN ('jinzhu','jinzhu 2');

// LIKE
db.Where("name LIKE ?", "%jin%").Find(&users)
// SELECT * FROM users WHERE name LIKE '%jin%';

// AND
db.Where("name = ? AND age >= ?", "jinzhu", "22").Find(&users)
// SELECT * FROM users WHERE name = 'jinzhu' AND age >= 22;

// Time
db.Where("updated_at > ?", lastWeek).Find(&users)
// SELECT * FROM users WHERE updated_at > '2000-01-01 00:00:00';

// BETWEEN
db.Where("created_at BETWEEN ? AND ?", lastWeek, today).Find(&users)
// SELECT * FROM users WHERE created_at BETWEEN '2000-01-01 00:00:00' AND '2000-01-08 00:00:00';

Struct&Map

// Struct
db.Where(&User{Name: "jinzhu", Age: 20}).First(&user)
// SELECT * FROM users WHERE name = "jinzhu" AND age = 20 ORDER BY id LIMIT 1;

// Map
db.Where(map[string]interface{}{"name": "jinzhu", "age": 20}).Find(&users)
// SELECT * FROM users WHERE name = "jinzhu" AND age = 20;

// Slice of primary keys
db.Where([]int64{20, 21, 22}).Find(&users)
// SELECT * FROM users WHERE id IN (20, 21, 22);

一对一

结构体

type User struct {
    gorm.Model
    Name      string
    CompanyID int     // 数据库存储的字段
    Company   Company // 外建关联, 没有实质性作用.
}

type Company struct {
    ID   int
    Name string
}

查询

package main


func main() {
    // 定义全局日志
    newLogger := logger.New()

    // 参考 https://github.com/go-sql-driver/mysql#dsn-data-source-name 获取详情
    dsn := "root@tcp(127.0.0.1:3306)/gorm?charset=utf8mb4&parseTime=True&loc=Local"
    db, _ := gorm.Open(mysql.Open(dsn), &gorm.Config{
        Logger: newLogger,
    })

    // 自动迁移. 会先创建 company 再创建 user
    db.AutoMigrate(&User{})

    // 创建数据
    db.Create(&User{
        Name: "Gyi",
        Company: Company{
            ID:   1,
            Name: "Test",
        },
    })

    // 预加载关联查询
    var user User
    db.Preload("Company").First(&user)
    fmt.Println(user.Name, user.Company.Name)

    // join 查询
    user = User{}
    db.Joins("Company").First(&user)
    fmt.Println(user.Name, user.Company.Name)
}

一对多

结构体

// User 有多张 CreditCard，UserID 是外键
type User struct {
    gorm.Model
    CreditCards []CreditCard
}

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

查询

func main() {
    newLogger := logger.New()
    // 参考 https://github.com/go-sql-driver/mysql#dsn-data-source-name 获取详情
    dsn := "root@tcp(127.0.0.1:3306)/gorm?charset=utf8mb4&parseTime=True&loc=Local"
    db, _ := gorm.Open(mysql.Open(dsn), &gorm.Config{
        Logger: newLogger,
    })
  
    // 自动迁移. 会先创建 company 再创建 user
    db.AutoMigrate(&User{})
    db.AutoMigrate(&CreditCard{})

    // 创建数据
    user := User{}
    db.Create(&user)
    db.Create(&CreditCard{
        Number: "34",
        UserID: 1,
    })

    // 查询
    var user User
    db.Preload("CreditCards").First(&user, 1)
    for _, card := range user.CreditCards {
        fmt.Println(card.ID)
    }
}

多对多

结构体

// User 拥有并属于多种 language，`user_languages` 是连接表
type User struct {
    gorm.Model
    Languages []Language `gorm:"many2many:user_languages;"`
}

type Language struct {
    gorm.Model
    Name string
}

查询

方式一

// 第一种方式: 查询
var user User
db.Preload("Languages").First(&user, 1)
fmt.Println(user.ID)
for _, language := range user.Languages {
    fmt.Printf("User: %d, language: %s\n", user.ID, language.Name)
}

方式二

查找关联, 先查找出用户, 后对用户进行关联

// 1. 查询用户
user = User{}
db.First(&user, 2)
// 2. 查找关联
var languages []Language
_ = db.Model(&user).Association("Languages").Find(&languages)
for _, language := range languages {
    fmt.Println(language.ID, language.Name)
}

高级查询

whereIn

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

子查询

// 简单的子查询
db.Where("amount > (?)", db.Table("orders").Select("AVG(amount)")).Find(&orders)
// SQL: 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)
// SQL: SELECT AVG(age) as avgage FROM `users` GROUP BY `name` HAVING AVG(age) > (SELECT AVG(age) FROM `users` WHERE name LIKE "name%")

// 在 FROM 子句中使用子查询
db.Table("(?) as u", db.Model(&User{}).Select("name", "age")).Where("age = ?", 18).Find(&User{})
// SQL: SELECT * FROM (SELECT `name`,`age` FROM `users`) as u WHERE `age` = 18

// 在 FROM 子句中结合多个子查询
subQuery1 := db.Model(&User{}).Select("name")
subQuery2 := db.Model(&Pet{}).Select("name")
db.Table("(?) as u, (?) as p", subQuery1, subQuery2).Find(&User{})
// SQL: SELECT * FROM (SELECT `name` FROM `users`) as u, (SELECT `name` FROM `pets`) as p

注入参数条件

提高SQL 查询的可读性和可维护性。此功能使查询结构更加清晰、更加有条理，尤其是在有多个参数的复杂查询中。命名参数可以使用 sql.NamedArg 或 map[string]interface{}{}}

// SQL: SELECT * FROM `users` WHERE name1 = "jinzhu" OR name2 = "jinzhu"
db.Where("name1 = @name OR name2 = @name", sql.Named("name", "jinzhu")).Find(&user)


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

pluck

当需要从模型中检索特定字段时, Pluck 方法用于从数据库中查询单列并扫描结果到片段。如果需要查询多个列，可以使用 Select 配合 Scan 或者 Find 来代替

// 检索所有用户的 age
var ages []int64
db.Model(&User{}).Pluck("age", &ages)

// 检索所有用户的 name
var names []string
db.Model(&User{}).Pluck("name", &names)

// 从不同的表中检索 name
db.Table("deleted_users").Pluck("name", &names)

// 使用Distinct和Pluck
db.Model(&User{}).Distinct().Pluck("Name", &names)
// SQL: SELECT DISTINCT `name` FROM `users`

// 多列查询
db.Select("name", "age").Scan(&users)
db.Select("name", "age").Find(&users)

count

使用count检索符合条件的记录数，可以对不同的值进行计数并对结果分组

var count int64

// 计数 有着特定名字的 users
db.Model(&User{}).Where("name = ?", "jinzhu").Or("name = ?", "jinzhu 2").Count(&count)
// SQL: SELECT count(1) FROM users WHERE name = 'jinzhu' OR name = 'jinzhu 2'

// 为不同 name 计数
db.Model(&User{}).Distinct("name").Count(&count)
// SQL: SELECT COUNT(DISTINCT(`name`)) FROM `users`

// 使用自定义选择（custom select）计数不同的值
db.Table("deleted_users").Select("count(distinct(name))").Count(&count)
// SQL: SELECT count(distinct(name)) FROM deleted_users

// 分组记录计数
users := []User{
  {Name: "name1"},
  {Name: "name2"},
  {Name: "name3"},
  {Name: "name3"},
}

db.Model(&User{}).Group("name").Count(&count)
// 按名称分组后计数
// count => 3

锁

UPDATE：准备在事务中更新一些行时，并且想要在本事务完成前，阻止其他的事务修改你准备更新的选中行使用

SHARE : 只允许其他事务读取被锁定的内容，而无法修改或者删除

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

分页查询

func Paginate(page, pageSize int) func(db *gorm.DB) *gorm.DB {
    return func(db *gorm.DB) *gorm.DB {
        if page == 0 {
            page = 1
        }
        // 限制每页最大100条
        switch {
        case pageSize > 100:
            pageSize = 100
        case pageSize <= 0:
            pageSize = 10
        }

        offset := (page - 1) * pageSize
        return db.Offset(offset).Limit(pageSize)
    }
}

更新

所有字段

save会更新所有字段，包括零值, 如果值不包含主键会执行 create

db.First(&user)

user.Name = "jinzhu 2"
user.Age = 100
db.Save(&user)
// UPDATE users SET name='jinzhu 2', age=100 WHERE id=1;

单个列

默认开启禁止全局更新，如update未指定where条件会抛出异常

// 根据条件更新
db.Model(&User{}).Where("active = ?", true).Update("name", "hello")
// UPDATE users SET name='hello' WHERE active=true;

// User 的 ID 是 `111`
db.Model(&user).Update("name", "hello")
// UPDATE users SET name='hello' WHERE id=1;

// 根据条件和 model 的值进行更新
db.Model(&user).Where("active = ?", true).Update("name", "hello")
// UPDATE users SET name='hello' WHERE id=111 AND active=true;

多个列

Updates 方法支持 struct 和 map[string]interface{} 参数。当使用 struct 更新时，默认情况下只会更新非零值的字段

// 根据 `struct` 更新属性，只会更新非零值的字段
db.Model(&user).Updates(User{Name: "hello", Age: 18, Active: false})
// UPDATE users SET name='hello', age=18, updated_at = '2013-11-17 21:34:10' WHERE id = 111;

// 根据 `map` 更新属性
db.Model(&user).Updates(map[string]interface{}{"name": "hello", "age": 18, "active": false})
// UPDATE users SET name='hello', age=18, active=false, updated_at='2013-11-17 21:34:10' WHERE id=111;

通过Select更新指定字段Omit过滤更新字段

// 选择 Map 的字段
// User 的 ID 是 `111`:
db.Model(&user).Select("name").Updates(map[string]interface{}{"name": "hello", "age": 18})
// UPDATE users SET name='hello' WHERE id=1;

db.Model(&user).Omit("name").Updates(map[string]interface{}{"name": "hello", "age": 18, "active": false})
// UPDATE users SET age=18, active=false, WHERE id=1;

// 选择 Struct 的字段（会选中零值的字段）
db.Model(&user).Select("Name", "Age").Updates(User{Name: "new_name", Age: 0})
// UPDATE users SET name='new_name', age=0 WHERE id=1;

// 选择所有字段（选择包括零值字段的所有字段）
db.Model(&user).Select("*").Updates(User{Name: "jinzhu", Role: "admin", Age: 0})

// 选择除 Role 外的所有字段（包括零值字段的所有字段）
db.Model(&user).Select("*").Omit("Role").Updates(User{Name: "jinzhu", Role: "admin", Age: 0})

删除

删除一条记录时，删除对象需要指定主键，否则会触发批量删除

永久删除

// Email 的 ID 是 `10`
db.Delete(&email)
// DELETE from emails where id = 10;

// 带额外条件的删除
db.Where("name = ?", "jinzhu").Delete(&email)
// DELETE from emails where id = 10 AND name = "jinzhu";

db.Delete(&User{}, 10)
// DELETE FROM users WHERE id = 10;

db.Delete(&User{}, "10")
// DELETE FROM users WHERE id = 10;

db.Delete(&users, []int{1,2,3})
// DELETE FROM users WHERE id IN (1,2,3);

软删除

结构体中包含gorm.DeletedAt字段后自动使用软删除功能,查询会过滤被软删除的数据

// user's ID is `111`
db.Delete(&user)
// UPDATE users SET deleted_at="2013-10-29 10:23" WHERE id = 111;

// Batch Delete
db.Where("age = ?", 20).Delete(&User{})
// UPDATE users SET deleted_at="2013-10-29 10:23" WHERE age = 20;

// Soft deleted records will be ignored when querying
db.Where("age = 20").Find(&user)
// SELECT * FROM users WHERE age = 20 AND deleted_at IS NULL;

软删除的记录可以通过Unscoped查询到，使用Unscoped永久删除记录

db.Unscoped().Where("age = 20").Find(&users)
// SELECT * FROM users WHERE age = 20;

db.Unscoped().Delete(&order)
// DELETE FROM orders WHERE id=10;

Last modification：July 23rd, 2024 at 02:00 pm

Golang——GORM CRUD

二滑大魔王 • 2024 年 07 月 23 日

创建

结构体

type User struct {
    ID           uint
    Name         string
    Email        *string
    Age          uint8
    Birthday     *time.Time
    MemberNumber sql.NullString
    ActivatedAt  sql.NullTime
    CreatedAt    time.Time
    UpdatedAt    time.Time
}

单条创建

package main

func main() {
    // 定义全局日志
    newLogger := logger.New()

    // 参考 https://github.com/go-sql-driver/mysql#dsn-data-source-name 获取详情
    dsn := "root@tcp(127.0.0.1:3306)/gorm?charset=utf8mb4&parseTime=True&loc=Local"
    db, _ := gorm.Open(mysql.Open(dsn), &gorm.Config{
        Logger: newLogger,
    })

    // 自动迁移
    db.AutoMigrate(&User{})

    // 创建
    empty := ""
    user := User{
        Name:  "Gyi",
        Email: &empty,
    }
    result := db.Create(&user)
    fmt.Println(user.ID) // 创建的主键id
    fmt.Println(result.Error) // 执行错误
    fmt.Println(result.RowsAffected) // 执行成功行数
}

批量插入

package main

func main() {
    // 定义全局日志
    newLogger := logger.New()

    // 参考 https://github.com/go-sql-driver/mysql#dsn-data-source-name 获取详情
    dsn := "root@tcp(127.0.0.1:3306)/gorm?charset=utf8mb4&parseTime=True&loc=Local"
    db, _ := gorm.Open(mysql.Open(dsn), &gorm.Config{
        Logger: newLogger,
    })

    // 自动迁移
    db.AutoMigrate(&User{})

    // 批量创建
    var users = []User{{Name: "Gyi"}, {Name: "Yna"}}
    db.Create(&users)

    // 打印结果
    for _, user := range users {
        fmt.Println(user.ID) // 循环输出批量插入用户主键
    }
}

查询

单条查询

// 获取第一条记录（主键升序）
db.First(&user)
// SELECT * FROM users ORDER BY id LIMIT 1;

// 获取一条记录，没有指定排序字段
db.Take(&user)
// SELECT * FROM users LIMIT 1;

// 获取最后一条记录（主键降序）
db.Last(&user)
// SELECT * FROM users ORDER BY id DESC LIMIT 1;

result := db.First(&user)
result.RowsAffected // 返回找到的记录数
result.Error        // returns error or nil

// 检查 ErrRecordNotFound 错误
errors.Is(result.Error, gorm.ErrRecordNotFound)

多条查询

// 查询
var users []User

result := db.Find(&users, []int{1, 2, 3, 4, 5})
fmt.Printf("len: %d\n", result.RowsAffected)
for _, user := range users {
    fmt.Printf("user: %d, name: %s\n", user.ID, user.Name)
}

条件

string

// Get first matched record
db.Where("name = ?", "jinzhu").First(&user)
// SELECT * FROM users WHERE name = 'jinzhu' ORDER BY id LIMIT 1;

// Get all matched records
db.Where("name <> ?", "jinzhu").Find(&users)
// SELECT * FROM users WHERE name <> 'jinzhu';

// IN
db.Where("name IN ?", []string{"jinzhu", "jinzhu 2"}).Find(&users)
// SELECT * FROM users WHERE name IN ('jinzhu','jinzhu 2');

// LIKE
db.Where("name LIKE ?", "%jin%").Find(&users)
// SELECT * FROM users WHERE name LIKE '%jin%';

// AND
db.Where("name = ? AND age >= ?", "jinzhu", "22").Find(&users)
// SELECT * FROM users WHERE name = 'jinzhu' AND age >= 22;

// Time
db.Where("updated_at > ?", lastWeek).Find(&users)
// SELECT * FROM users WHERE updated_at > '2000-01-01 00:00:00';

// BETWEEN
db.Where("created_at BETWEEN ? AND ?", lastWeek, today).Find(&users)
// SELECT * FROM users WHERE created_at BETWEEN '2000-01-01 00:00:00' AND '2000-01-08 00:00:00';

Struct&Map

// Struct
db.Where(&User{Name: "jinzhu", Age: 20}).First(&user)
// SELECT * FROM users WHERE name = "jinzhu" AND age = 20 ORDER BY id LIMIT 1;

// Map
db.Where(map[string]interface{}{"name": "jinzhu", "age": 20}).Find(&users)
// SELECT * FROM users WHERE name = "jinzhu" AND age = 20;

// Slice of primary keys
db.Where([]int64{20, 21, 22}).Find(&users)
// SELECT * FROM users WHERE id IN (20, 21, 22);

一对一

结构体

type User struct {
    gorm.Model
    Name      string
    CompanyID int     // 数据库存储的字段
    Company   Company // 外建关联, 没有实质性作用.
}

type Company struct {
    ID   int
    Name string
}

查询

package main


func main() {
    // 定义全局日志
    newLogger := logger.New()

    // 参考 https://github.com/go-sql-driver/mysql#dsn-data-source-name 获取详情
    dsn := "root@tcp(127.0.0.1:3306)/gorm?charset=utf8mb4&parseTime=True&loc=Local"
    db, _ := gorm.Open(mysql.Open(dsn), &gorm.Config{
        Logger: newLogger,
    })

    // 自动迁移. 会先创建 company 再创建 user
    db.AutoMigrate(&User{})

    // 创建数据
    db.Create(&User{
        Name: "Gyi",
        Company: Company{
            ID:   1,
            Name: "Test",
        },
    })

    // 预加载关联查询
    var user User
    db.Preload("Company").First(&user)
    fmt.Println(user.Name, user.Company.Name)

    // join 查询
    user = User{}
    db.Joins("Company").First(&user)
    fmt.Println(user.Name, user.Company.Name)
}

一对多

结构体

// User 有多张 CreditCard，UserID 是外键
type User struct {
    gorm.Model
    CreditCards []CreditCard
}

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

查询

func main() {
    newLogger := logger.New()
    // 参考 https://github.com/go-sql-driver/mysql#dsn-data-source-name 获取详情
    dsn := "root@tcp(127.0.0.1:3306)/gorm?charset=utf8mb4&parseTime=True&loc=Local"
    db, _ := gorm.Open(mysql.Open(dsn), &gorm.Config{
        Logger: newLogger,
    })
  
    // 自动迁移. 会先创建 company 再创建 user
    db.AutoMigrate(&User{})
    db.AutoMigrate(&CreditCard{})

    // 创建数据
    user := User{}
    db.Create(&user)
    db.Create(&CreditCard{
        Number: "34",
        UserID: 1,
    })

    // 查询
    var user User
    db.Preload("CreditCards").First(&user, 1)
    for _, card := range user.CreditCards {
        fmt.Println(card.ID)
    }
}

多对多

结构体

// User 拥有并属于多种 language，`user_languages` 是连接表
type User struct {
    gorm.Model
    Languages []Language `gorm:"many2many:user_languages;"`
}

type Language struct {
    gorm.Model
    Name string
}

查询

方式一

// 第一种方式: 查询
var user User
db.Preload("Languages").First(&user, 1)
fmt.Println(user.ID)
for _, language := range user.Languages {
    fmt.Printf("User: %d, language: %s\n", user.ID, language.Name)
}

方式二

查找关联, 先查找出用户, 后对用户进行关联

// 1. 查询用户
user = User{}
db.First(&user, 2)
// 2. 查找关联
var languages []Language
_ = db.Model(&user).Association("Languages").Find(&languages)
for _, language := range languages {
    fmt.Println(language.ID, language.Name)
}

高级查询

whereIn

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

子查询

// 简单的子查询
db.Where("amount > (?)", db.Table("orders").Select("AVG(amount)")).Find(&orders)
// SQL: 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)
// SQL: SELECT AVG(age) as avgage FROM `users` GROUP BY `name` HAVING AVG(age) > (SELECT AVG(age) FROM `users` WHERE name LIKE "name%")

// 在 FROM 子句中使用子查询
db.Table("(?) as u", db.Model(&User{}).Select("name", "age")).Where("age = ?", 18).Find(&User{})
// SQL: SELECT * FROM (SELECT `name`,`age` FROM `users`) as u WHERE `age` = 18

// 在 FROM 子句中结合多个子查询
subQuery1 := db.Model(&User{}).Select("name")
subQuery2 := db.Model(&Pet{}).Select("name")
db.Table("(?) as u, (?) as p", subQuery1, subQuery2).Find(&User{})
// SQL: SELECT * FROM (SELECT `name` FROM `users`) as u, (SELECT `name` FROM `pets`) as p

注入参数条件

// SQL: SELECT * FROM `users` WHERE name1 = "jinzhu" OR name2 = "jinzhu"
db.Where("name1 = @name OR name2 = @name", sql.Named("name", "jinzhu")).Find(&user)


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

pluck

// 检索所有用户的 age
var ages []int64
db.Model(&User{}).Pluck("age", &ages)

// 检索所有用户的 name
var names []string
db.Model(&User{}).Pluck("name", &names)

// 从不同的表中检索 name
db.Table("deleted_users").Pluck("name", &names)

// 使用Distinct和Pluck
db.Model(&User{}).Distinct().Pluck("Name", &names)
// SQL: SELECT DISTINCT `name` FROM `users`

// 多列查询
db.Select("name", "age").Scan(&users)
db.Select("name", "age").Find(&users)

count

使用count检索符合条件的记录数，可以对不同的值进行计数并对结果分组

var count int64

// 计数 有着特定名字的 users
db.Model(&User{}).Where("name = ?", "jinzhu").Or("name = ?", "jinzhu 2").Count(&count)
// SQL: SELECT count(1) FROM users WHERE name = 'jinzhu' OR name = 'jinzhu 2'

// 为不同 name 计数
db.Model(&User{}).Distinct("name").Count(&count)
// SQL: SELECT COUNT(DISTINCT(`name`)) FROM `users`

// 使用自定义选择（custom select）计数不同的值
db.Table("deleted_users").Select("count(distinct(name))").Count(&count)
// SQL: SELECT count(distinct(name)) FROM deleted_users

// 分组记录计数
users := []User{
  {Name: "name1"},
  {Name: "name2"},
  {Name: "name3"},
  {Name: "name3"},
}

db.Model(&User{}).Group("name").Count(&count)
// 按名称分组后计数
// count => 3

锁

UPDATE：准备在事务中更新一些行时，并且想要在本事务完成前，阻止其他的事务修改你准备更新的选中行使用

SHARE : 只允许其他事务读取被锁定的内容，而无法修改或者删除

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

分页查询

func Paginate(page, pageSize int) func(db *gorm.DB) *gorm.DB {
    return func(db *gorm.DB) *gorm.DB {
        if page == 0 {
            page = 1
        }
        // 限制每页最大100条
        switch {
        case pageSize > 100:
            pageSize = 100
        case pageSize <= 0:
            pageSize = 10
        }

        offset := (page - 1) * pageSize
        return db.Offset(offset).Limit(pageSize)
    }
}

更新

所有字段

save会更新所有字段，包括零值, 如果值不包含主键会执行 create

db.First(&user)

user.Name = "jinzhu 2"
user.Age = 100
db.Save(&user)
// UPDATE users SET name='jinzhu 2', age=100 WHERE id=1;

单个列

默认开启禁止全局更新，如update未指定where条件会抛出异常

// 根据条件更新
db.Model(&User{}).Where("active = ?", true).Update("name", "hello")
// UPDATE users SET name='hello' WHERE active=true;

// User 的 ID 是 `111`
db.Model(&user).Update("name", "hello")
// UPDATE users SET name='hello' WHERE id=1;

// 根据条件和 model 的值进行更新
db.Model(&user).Where("active = ?", true).Update("name", "hello")
// UPDATE users SET name='hello' WHERE id=111 AND active=true;

多个列

Updates 方法支持 struct 和 map[string]interface{} 参数。当使用 struct 更新时，默认情况下只会更新非零值的字段

// 根据 `struct` 更新属性，只会更新非零值的字段
db.Model(&user).Updates(User{Name: "hello", Age: 18, Active: false})
// UPDATE users SET name='hello', age=18, updated_at = '2013-11-17 21:34:10' WHERE id = 111;

// 根据 `map` 更新属性
db.Model(&user).Updates(map[string]interface{}{"name": "hello", "age": 18, "active": false})
// UPDATE users SET name='hello', age=18, active=false, updated_at='2013-11-17 21:34:10' WHERE id=111;

通过Select更新指定字段Omit过滤更新字段

// 选择 Map 的字段
// User 的 ID 是 `111`:
db.Model(&user).Select("name").Updates(map[string]interface{}{"name": "hello", "age": 18})
// UPDATE users SET name='hello' WHERE id=1;

db.Model(&user).Omit("name").Updates(map[string]interface{}{"name": "hello", "age": 18, "active": false})
// UPDATE users SET age=18, active=false, WHERE id=1;

// 选择 Struct 的字段（会选中零值的字段）
db.Model(&user).Select("Name", "Age").Updates(User{Name: "new_name", Age: 0})
// UPDATE users SET name='new_name', age=0 WHERE id=1;

// 选择所有字段（选择包括零值字段的所有字段）
db.Model(&user).Select("*").Updates(User{Name: "jinzhu", Role: "admin", Age: 0})

// 选择除 Role 外的所有字段（包括零值字段的所有字段）
db.Model(&user).Select("*").Omit("Role").Updates(User{Name: "jinzhu", Role: "admin", Age: 0})

删除

删除一条记录时，删除对象需要指定主键，否则会触发批量删除

永久删除

// Email 的 ID 是 `10`
db.Delete(&email)
// DELETE from emails where id = 10;

// 带额外条件的删除
db.Where("name = ?", "jinzhu").Delete(&email)
// DELETE from emails where id = 10 AND name = "jinzhu";

db.Delete(&User{}, 10)
// DELETE FROM users WHERE id = 10;

db.Delete(&User{}, "10")
// DELETE FROM users WHERE id = 10;

db.Delete(&users, []int{1,2,3})
// DELETE FROM users WHERE id IN (1,2,3);

软删除

结构体中包含gorm.DeletedAt字段后自动使用软删除功能,查询会过滤被软删除的数据

// user's ID is `111`
db.Delete(&user)
// UPDATE users SET deleted_at="2013-10-29 10:23" WHERE id = 111;

// Batch Delete
db.Where("age = ?", 20).Delete(&User{})
// UPDATE users SET deleted_at="2013-10-29 10:23" WHERE age = 20;

// Soft deleted records will be ignored when querying
db.Where("age = 20").Find(&user)
// SELECT * FROM users WHERE age = 20 AND deleted_at IS NULL;

软删除的记录可以通过Unscoped查询到，使用Unscoped永久删除记录

db.Unscoped().Where("age = 20").Find(&users)
// SELECT * FROM users WHERE age = 20;

db.Unscoped().Delete(&order)
// DELETE FROM orders WHERE id=10;

创建

单条创建

批量插入

查询

单条查询

多条查询

条件

string

Struct&Map

一对一

一对多

多对多

方式一

方式二

高级查询

whereIn

子查询

注入参数条件

pluck

count

锁

分页查询

更新

所有字段

单个列

多个列

删除

永久删除

软删除