gorm: CRUD

最新推荐文章于 2025-12-04 16:52:46 发布

原创最新推荐文章于 2025-12-04 16:52:46 发布 · 420 阅读

3 ·

CC 4.0 BY-SA版权

文章标签：

#服务器 #数据库开发

CRUD

在这里插入图片描述

Create

通过Create可以创建表中的数据

user := User{  
    Name:     "Andrew",  
    Age:      18,  
    Birthday: time.Now(),  
}  
result := db.Create(&user) // pass pointer of data to Create

Select 指定部分数据更新

如果只是想更新指定的部分数据可以通过Select实现

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

Omit 或略部分数据更新

如果更新想指定忽略部分数据可以通过Omit接口实现

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")

Batch insert 批量插入数据

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

Create Hooks

gorm支持用户定义hooks，支持定义的hooks有BeforeSave, BeforeCreate, AfterSave, AfterCreate，这些hooks会在创建记录的时候被依次调用，调用顺序如下：

// begin transaction  
BeforeSave
BeforeCreate
// save before associations
// insert into database  
// save after associations  
AfterCreate  
AfterSave  
// commit or rollback transaction|

type User struct {  
    gorm.Model  
    Name     string  
    Age      int  
    Birthday time.Time  
    UUID     uuid.UUID  
    Role     string  
}  
  
func (u *User) BeforeCreate(tx *gorm.DB) (err error) {  
    u.UUID = uuid.New()  
    if u.Role == "admin" {  
       return errors.New("invalid role")  
    }  
    fmt.Println("before create")  
    return  
}

如果一个结构体实现了这些功能，但是你想跳过这些Hooks的调用，可以通过设置Session mode为SkipHooks跳过

DB.Session(&gorm.Session{SkipHooks: true}).Create(&user)  
DB.Session(&gorm.Session{SkipHooks: true}).Create(&users)  
DB.Session(&gorm.Session{SkipHooks: true}).CreateInBatches(users, 100)

Create Form Map

gorm支持通过map[string]interface{} 和 []map[string]interface{}{}来创建数据记录

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}, 
})

other

创建关系型数据

type User struct {  
    gorm.Model  
    Name       string  
    Age        int  
    Birthday   time.Time  
    UUID       uuid.UUID  
    Role       string  
    CreditCard CreditCard  
}  
  
type CreditCard struct {  
    gorm.Model  
    Number string  
    UserID uint  
}

db.Create(&User{  
    Name:       "jinzhu",  
    CreditCard: CreditCard{Number: "411111111111"},  
})

Default Values

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

Upsert / On Conflict 插入/冲突

gorm为不同

  
// Do nothing on conflict  
db.Clauses(clause.OnConflict{DoNothing: true}).Create(&user)  
  
// Update columns to default value on `id` conflict  
// 向数据库插入 `users` 记录，但如果 **主键 `id` 已存在**，则不报错，而是 **更新 `role` 字段为 `"user"`**
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  
  
// Use SQL expression 
// 如果 `id` 已存在（主键冲突），则不报错，而是更新 `count` 字段
// **更新逻辑是：将 `count` 更新为 “当前值” 和 “要插入的值” 中的较大者**
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));  
  
// Update columns to new value on `id` conflict  
// **在插入数据时，如果主键 `id` 冲突，则自动更新 `name` 和 `age` 字段为本次插入的值**。
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  
  
// Update all columns to new value on conflict except primary keys and those columns having default values from sql func  
db.Clauses(clause.OnConflict{  
    UpdateAll: true,  
}).Create(&users)  
// INSERT INTO "users" *** ON CONFLICT ("id") DO UPDATE SET "name"="excluded"."name", "age"="excluded"."age", ...;  
// INSERT INTO `users` *** ON DUPLICATE KEY UPDATE `name`=VALUES(name),`age`=VALUES(age), ...; MySQL

Query

GORM 提供了 First 、 Take 、 Last 方法从数据库中检索单个对象，它在查询数据库时添加了 LIMIT 1 条件，如果没有找到记录，它将返回错误 ErrRecordNotFound 。

user := User{}  
  
// Get the first record ordered by primary key  
db.First(&user)  
// SELECT * FROM users ORDER BY id LIMIT 1;  
  
// Get one record, no specified order  
db.Take(&user)  
// SELECT * FROM users LIMIT 1;  
  
// Get last record, ordered by primary key desc  
db.Last(&user)  
// SELECT * FROM users ORDER BY id DESC LIMIT 1;  
  
result := db.First(&user)

使用主键检索对象

db.First(&user, 10)  
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);
# 如果主键是字符串
db.First(&user, "id = ?", "1b74413f-f3b8-409f-ac47-e8c062e3472a")  
// SELECT * FROM users WHERE id = "1b74413f-f3b8-409f-ac47-e8c062e3472a";|

当目标对象具有主值时，主键将用于构建条件，例如：

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;

检索所有对象

// Get all records  
result := db.Find(&users)
// SELECT * FROM users;  
result.RowsAffected 
// returns found records count, equals `len(users)`  
result.Error
// returns error

条件查询

user := User{}  
  
// Get first matched record  
db.Where("name = ?", "jinzhu").First(&user)  
// SELECT * FROM users WHERE name = 'jinzhu' ORDER BY id LIMIT 1;  
  
users := make([]User, 0)  
// 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';

结构体和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);

指定要搜索的结构体字段

db.Where(&User{Name: "jinzhu"}, "name", "Age").Find(&users)  
// SELECT * FROM users WHERE name = "jinzhu" AND age = 0;  
  
db.Where(&User{Name: "jinzhu"}, "Age").Find(&users)  
// SELECT * FROM users WHERE age = 0;

内联条件

// Get by primary key if it were a non-integer type  
db.First(&user, "id = ?", "string_primary_key")  
// SELECT * FROM users WHERE id = 'string_primary_key';  
  
// Plain 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;

非条件查询

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

或条件查询

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", Age: 18}).Find(&users)  
// SELECT * FROM users WHERE name = 'jinzhu' OR (name = 'jinzhu 2' AND age = 18);  
  
// Map  
db.Where("name = 'jinzhu'").Or(map[string]interface{}{"name": "jinzhu 2", "age": 18}).Find(&users)  
// SELECT * FROM users WHERE name = 'jinzhu' OR (name = 'jinzhu 2' AND age = 18);

查询指定字段

db.Select("name", "age").Find(&users)  
// SELECT name, age FROM users;  
  
db.Select([]string{"name", "age"}).Find(&users)  
// SELECT name, age FROM users;  
  
db.Table("users").Select("COALESCE(age,?)", 42).Rows()  
// SELECT COALESCE(age,'42') FROM users;

排序查询

db.Order("age desc, name").Find(&users)  
// SELECT * FROM users ORDER BY age desc, name;  
  
// Multiple orders  
db.Order("age desc").Order("name").Find(&users)  
// SELECT * FROM users ORDER BY age desc, name;  
  
db.Clauses(clause.OrderBy{  
    Expression: clause.Expr{SQL: "FIELD(id,?)", Vars: []interface{}{[]int{1, 2, 3}}, WithoutParentheses: true},  
}).Find(&User{})  
// SELECT * FROM users ORDER BY FIELD(id,1,2,3)

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)

joins

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)

Update

Save是一个 upsert 函数，如果存在就Update如果不存在就insert

db.Save(&User{Name: "jinzhu", Age: 100})  
// INSERT INTO `users` (`name`,`age`,`birthday`,`update_at`) VALUES ("jinzhu",100,"0000-00-00 00:00:00","0000-00-00 00:00:00")  
  
db.Save(&User{ID: 1, Name: "jinzhu", Age: 100})  
// UPDATE `users` SET `name`="jinzhu",`age`=100,`birthday`="0000-00-00 00:00:00",`update_at`="0000-00-00 00:00:00" WHERE `id` = 1

更新一列数据

// Update with conditions  
db.Model(&User{}).Where("active = ?", true).Update("name", "hello")  
// UPDATE users SET name='hello', updated_at='2013-11-17 21:34:10' WHERE active=true;  
  
// User's ID is `111`:  
db.Model(&user).Update("name", "hello")  
// UPDATE users SET name='hello', updated_at='2013-11-17 21:34:10' WHERE id=111;  
  
// Update with conditions and model value  
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;

更新多列数据

// Update attributes with `struct`, will only update non-zero fields  
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;  
  
// Update attributes with `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 with Map  
// User's ID is `111`:  
db.Model(&user).Select("name").Updates(map[string]interface{}{"name": "hello", "age": 18, "active": false})  
// UPDATE users SET name='hello' WHERE id=111;  
  
db.Model(&user).Omit("name").Updates(map[string]interface{}{"name": "hello", "age": 18, "active": false})  
// UPDATE users SET age=18, active=false, updated_at='2013-11-17 21:34:10' WHERE id=111;  
  
// Select with Struct (select zero value fields)  
db.Model(&user).Select("Name", "Age").Updates(User{Name: "new_name", Age: 0})  
// UPDATE users SET name='new_name', age=0 WHERE id=111;  
  
// Select all fields (select all fields include zero value fields)  
db.Model(&user).Select("*").Updates(User{Name: "jinzhu", Role: "admin", Age: 0})  
  
// Select all fields but omit Role (select all fields include zero value fields)  
db.Model(&user).Select("*").Omit("Role").Updates(User{Name: "jinzhu", Role: "admin", Age: 0})

Delete

// Email's ID is `10`  
db.Delete(&email)  
// DELETE from emails where id = 10;  
  
// Delete with additional conditions  
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);

批量删除

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

禁止全局删除

如果你在没有任何条件的情况下执行批量删除，GORM 将不会运行它，并将返回 ErrMissingWhereClause 错误
您必须使用一些条件或使用原始 SQL 或启用 AllowGlobalUpdate 模式，例如

db.Delete(&User{}).Error // gorm.ErrMissingWhereClause  
  
db.Delete(&[]User{{Name: "jinzhu1"}, {Name: "jinzhu2"}}).Error // gorm.ErrMissingWhereClause  
  
db.Where("1 = 1").Delete(&User{})  
// DELETE FROM `users` WHERE 1=1  
  
db.Exec("DELETE FROM users")  
// DELETE FROM users  
  
db.Session(&gorm.Session{AllowGlobalUpdate: true}).Delete(&User{})  
// DELETE FROM users

软删除
如果您的模型包含 gorm.DeletedAt 字段（包含在 gorm.Model 中），它将自动获得软删除功能！
当调用 Delete 时，记录不会从数据库中删除，但 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 查找软删除的记录

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