Last active
August 2, 2023 09:51
-
-
Save z1076/2f879be729ed822e8771588321529094 to your computer and use it in GitHub Desktop.
[GORM常用CURD] #gorm使用笔记
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
user := User{Name: "Jinzhu", Age: 18, Birthday: time.Now()} | |
result := db.Create(&user) // 通过数据的指针来创建 | |
user.ID // 返回插入数据的主键 | |
result.Error // 返回 error | |
result.RowsAffected // 返回插入记录的条数 | |
//创建一个记录且一同忽略传递给略去的字段值。 | |
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") | |
//批量插入 | |
var users = []User{{Name: "jinzhu1"}, {Name: "jinzhu2"}, {Name: "jinzhu3"}} | |
db.Create(&users) | |
//根据 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}, | |
}) | |
//FirstOrCreate=========================================================================== | |
// 未找到 user,则根据给定条件创建一条新纪录 | |
db.FirstOrCreate(&user, User{Name: "non_existing"}) | |
// INSERT INTO "users" (name) VALUES ("non_existing"); | |
// user -> User{ID: 112, Name: "non_existing"} | |
// 找到了 `name` = `jinzhu` 的 user | |
db.Where(User{Name: "jinzhu"}).FirstOrCreate(&user) | |
// 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} | |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
// 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); | |
//批量删除 | |
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%"; | |
//软删除 | |
//查找被软删除的记录 | |
db.Unscoped().Where("age = 20").Find(&users) | |
// SELECT * FROM users WHERE age = 20; | |
//永久删除 | |
db.Unscoped().Delete(&order) | |
// DELETE FROM orders WHERE id=10; | |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
// 获取第一条记录(主键升序) | |
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) | |
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"; | |
// 获取全部记录 | |
result := db.Find(&users) | |
// SELECT * FROM users; | |
result.RowsAffected // 返回找到的记录数,相当于 `len(users)` | |
result.Error // returns error | |
//Find 至 map | |
var result map[string]interface{} | |
db.Model(&User{}).First(&result, "id = ?", 1) | |
var results []map[string]interface{} | |
db.Table("users").Find(&results) | |
//Where============================================================================================================ | |
// 获取第一条匹配的记录 | |
db.Where("name = ?", "jinzhu").First(&user) | |
// SELECT * FROM users WHERE name = 'jinzhu' ORDER BY id LIMIT 1; | |
// 获取全部匹配的记录 | |
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 | |
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; | |
// 主键切片条件 | |
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; | |
//Not============================================================================================================ | |
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; | |
// 不在主键切片中的记录 | |
db.Not([]int64{1,2,3}).First(&user) | |
// SELECT * FROM users WHERE id NOT IN (1,2,3) ORDER BY id LIMIT 1; | |
//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", 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; | |
//智能选择字段 | |
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 | |
//Order========================================================================================================= | |
db.Order("age desc, name").Find(&users) | |
// SELECT * FROM users ORDER BY age desc, name; | |
// 多个 order | |
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) | |
//Pluck============================================================================================================ | |
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============================================================================================================ | |
//Scopes 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 的 COD 订单 | |
db.Scopes(AmountGreaterThan1000, OrderStatus([]string{"paid", "shipped"})).Find(&orders) | |
// 查找所有金额大于1000 的已付款或已发货订单 | |
//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 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
//Save | |
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; | |
//更新单个列 | |
// 条件更新 | |
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 的 ID 是 `111` | |
db.Model(&user).Update("name", "hello") | |
// UPDATE users SET name='hello', updated_at='2013-11-17 21:34:10' WHERE id=111; | |
// 根据条件和 model 的值进行更新 | |
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; | |
//更新多列 | |
// 根据 `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; | |
//更新选定字段 | |
// 使用 Map 进行 Select | |
// 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; | |
// 使用 Struct 进行 Select(会 select 零值的字段) | |
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 所有字段(查询包括零值字段的所有字段) | |
db.Model(&user).Select("*").Update(User{Name: "jinzhu", Role: "admin", Age: 0}) | |
// Select 除 Role 外的所有字段(包括零值字段的所有字段) | |
db.Model(&user).Select("*").Omit("Role").Update(User{Name: "jinzhu", Role: "admin", Age: 0}) | |
//批量更新 | |
// 根据 struct 更新 | |
db.Model(User{}).Where("role = ?", "admin").Updates(User{Name: "hello", Age: 18}) | |
// UPDATE users SET name='hello', age=18 WHERE role = 'admin'; | |
// 根据 map 更新 | |
db.Table("users").Where("id IN ?", []int{10, 11}).Updates(map[string]interface{}{"name": "hello", "age": 18}) | |
// UPDATE users SET name='hello', age=18 WHERE id IN (10, 11); | |
//获取受更新影响的行数 | |
// 通过 `RowsAffected` 得到更新的记录数 | |
result := db.Model(User{}).Where("role = ?", "admin").Updates(User{Name: "hello", Age: 18}) | |
// UPDATE users SET name='hello', age=18 WHERE role = 'admin'; | |
result.RowsAffected // 更新的记录数 | |
result.Error // 更新的错误 | |
//使用 SQL 表达式更新 | |
// product 的 ID 是 `3` | |
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" = 3; | |
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" = 3; | |
db.Model(&product).UpdateColumn("quantity", gorm.Expr("quantity - ?", 1)) | |
// UPDATE "products" SET "quantity" = quantity - 1 WHERE "id" = 3; | |
db.Model(&product).Where("quantity > 1").UpdateColumn("quantity", gorm.Expr("quantity - ?", 1)) | |
// UPDATE "products" SET "quantity" = quantity - 1 WHERE "id" = 3 AND quantity > 1; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
type Result struct { | |
ID int | |
Name string | |
Age int | |
} | |
//查询 | |
var result Result | |
db.Raw("SELECT id, name, age FROM users WHERE name = ?", 3).Scan(&result) | |
db.Raw("SELECT id, name, age FROM users WHERE name = ?", 3).Scan(&result) | |
var age int | |
db.Raw("SELECT SUM(age) FROM users WHERE role = ?", "admin").Scan(&age) | |
var users []User | |
db.Raw("UPDATE users SET name = ? WHERE age = ? RETURNING id, name", "jinzhu", 20).Scan(&users) | |
//操作 | |
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") | |
//将 sql.Rows 扫描至 model | |
rows, err := db.Model(&User{}).Where("name = ?", "jinzhu").Select("name, age, email").Rows() // (*sql.Rows, error) | |
defer rows.Close() | |
var user User | |
for rows.Next() { | |
// ScanRows 将一行扫描至 user | |
db.ScanRows(rows, &user) | |
// 业务逻辑... | |
} | |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
//FOR UPDATE | |
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.Transaction(func(tx *gorm.DB) error { | |
// 在事务中执行一些 db 操作(从这里开始,您应该使用 'tx' 而不是 'db') | |
if err := tx.Create(&Animal{Name: "Giraffe"}).Error; err != nil { | |
// 返回任何错误都会回滚事务 | |
return err | |
} | |
if err := tx.Create(&Animal{Name: "Lion"}).Error; err != nil { | |
return err | |
} | |
// 返回 nil 提交事务 | |
return nil | |
}) | |
//GORM 支持嵌套事务,您可以回滚较大事务内执行的一部分操作,例如: | |
db.Transaction(func(tx *gorm.DB) error { | |
tx.Create(&user1) | |
tx.Transaction(func(tx2 *gorm.DB) error { | |
tx2.Create(&user2) | |
return errors.New("rollback user2") // Rollback user2 | |
}) | |
tx.Transaction(func(tx2 *gorm.DB) error { | |
tx2.Create(&user3) | |
return nil | |
}) | |
return nil | |
}) | |
// Commit user1, user3 | |
//手动事务 | |
// 开始事务 | |
tx := db.Begin() | |
// 在事务中执行一些 db 操作(从这里开始,您应该使用 'tx' 而不是 'db') | |
tx.Create(...) | |
// ... | |
// 遇到错误时回滚事务 | |
tx.Rollback() | |
// 否则,提交事务 | |
tx.Commit() |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment