search
GitHub

Gorm.excalidraw

==⚠ Switch to EXCALIDRAW VIEW in the MORE OPTIONS menu of this document. ⚠== You can decompress Drawing data with the command palette: 'Decompress current Excalidraw file'. For more info check in plugin settings under 'Saving'

hashtag
Excalidraw Data

hashtag
Text Elements

Gorm ^EKbpggNK

查询 ^futmFLx7

单条查询 ^SVhrjaPS

First ^p5Tcc8Y0

Take ^hLl1sSWC

Last ^SYE6XZ12

查询按主键排序的第一条记录 ^BwJUTacJ

db.First(&user) // SELECT * FROM users ORDER BY id LIMIT 1; ^GHF8UriC

随机获取一条数据，没有指定排序字段 ^MR9eo5Nn

db.First(&user) // SELECT * FROM users ORDER BY id LIMIT 1; ^IhByW7NL

查询按主键排序的最后一条记录 ^BoykNzQQ

db.Last(&user) // SELECT * FROM users ORDER BY id DESC LIMIT 1; ^FbESQDBB

内联条件 ^EQpy6B0p

ErrRecordNotFound ^5EySwuSN

没有找到记录时，它会返回 ErrRecordNotFound 错误 ^dAQaCgNI

查询按主键排序的第一条记录 ^nNcQXrnf

全表查询 ^5S4zKoRm

Find ^5mfZjVs4

result := db.Find(&users) // SELECT * FROM users; ^81ejhsvw

内联条件可以在Frist和Find方法中插入查询条件 ^6shKAiJZ

db.First(&user, "id = ?", "string_primary_key") // SELECT * FROM users WHERE id = 'string_primary_key'; ^86u9RiaZ

如果主键不是数字类型 ^DFTV13Gc

如果主键是数字类型 ^GVvnnBt2

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

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;

^wJpa4Unk

结构体主键有值，会用主键值来构建条件 且和条件查询是AND关系 ^9GnjEzHu

其他字段 ^bUuvfyNM

// 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; ^rZu3narH

条件查询 ^gW1Yl66A

String条件 ^yuHxj9OM

// 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'; ^MDuXEtAf

Struct & Map 条件 ^Ct5riyjs

// 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(&[]fun.Teacher{{Tno: 1}, {Tno: 2}}).Find(&teacher) // SELECT * FROM teacher WHERE (teacher.tno = 1 AND teacher.tno = 2) AND teacher.deleted_at IS NULL AND teacher.id = 1 ^nGdKMcFK

当结构体字段值为0，''，false或者其他空值，不会将其作为条件构建,可以使用map进行映射 db.Where(map[string]interface{}{"Name": "jinzhu", "Age": 0}).Find(&users) // SELECT * FROM users WHERE name = "jinzhu" AND age = 0; ^fGbnTd91

Not条件 ^dbBGnEnN

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; ^KnUOMwBg

OR条件 ^wso9LlbZ

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); ^HCWw0K3B

选择特定字段 ^fqldI5lG

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;

//不能连续使用select，否则会被后面的select覆盖 ^AWBF03yx

选择特定字段 ^yrTu65nJ

// Omit指定选定字段之外的其他字段 db.Omit("Tno").Where("Tno <> ?", 1).Find(&teacher) ^nZgQ7ijQ

选择特定字段的其他字段 ^FK7T9Xsu

排序 ^W5sn0Znb

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) ^J0CyjqDH

Limit & Offset ^hAxDqW49

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) ^MvPxOWFK

Scopes ^JqV5Gen4

Group By & Having ^8989Qy8L

type result struct { Date time.Time Total int }

db.Model(&User{}).Select("name, sum(age) as total").Where("name LIKE ?", "group%").Group("name").First(&result) // SELECT name, sum(age) as total FROM users WHERE name LIKE "group%" GROUP BY name LIMIT 1

db.Model(&User{}).Select("name, sum(age) as total").Group("name").Having("name = ?", "group").Find(&result) // SELECT name, sum(age) as total FROM users GROUP BY name HAVING name = "group"

rows, err := db.Table("orders").Select("date(created_at) as date, sum(amount) as total").Group("date(created_at)").Rows() defer rows.Close() 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() defer rows.Close() 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) // Scan可以映射随意结构体 ^Qza9NESI

Distinct ^4uvmvqU4

db.Distinct("name", "age").Order("name, age desc").Find(&results) ^qAmIVMdT

Distinct ^UA0E2Tq9

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) ^e9CsnOsd

Join ^Oq4PLrdO

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) ^qLSHfIb4

db.Joins("Company").Find(&users) // SELECT users.id,users.name,users.age,Company.id AS Company__id,Company.name AS Company__name FROM users LEFT JOIN companies AS Company ON users.company_id = Company.id;

// inner join db.InnerJoins("Company").Find(&users) // SELECT users.id,users.name,users.age,Company.id AS Company__id,Company.name AS Company__name FROM users INNER JOIN companies AS Company ON users.company_id = Company.id;

// Join with conditions db.Joins("Company", db.Where(&Company{Alive: true})).Find(&users) // SELECT users.id,users.name,users.age,Company.id AS Company__id,Company.name AS Company__name FROM users LEFT JOIN companies AS Company ON users.company_id = Company.id AND Company.alive = true; ^MP4K8egX

join连接 ^pjVUwsr4

预加载 ^ngHH6xFr

type User struct { Id int Age int }

type Order struct { UserId int FinishedAt *time.Time }

query := db.Table("order").Select("MAX(order.finished_at) as latest").Joins("left join user user on order.user_id = user.id").Where("user.age > ?", 18).Group("order.user_id") db.Model(&Order{}).Joins("join (?) q on order.finished_at = q.latest", query).Scan(&results) // SELECT order.user_id,order.finished_at FROM order join (SELECT MAX(order.finished_at) as latest FROM order left join user user on order.user_id = user.id WHERE user.age > 18 GROUP BY order.user_id) q on order.finished_at = q.latest ^Zp4KBFUr

连接一个衍生表 ^tEkZK8eI

Scan ^389bC0Xw

type Result struct { Name string Age int }

var result Result db.Table("users").Select("name", "age").Where("name = ?", "Antonio").Scan(&result)

// Raw SQL // 执行原生sql db.Raw("SELECT name, age FROM users WHERE name = ?", "Antonio").Scan(&result) // 可以用任意变量、结构体或 map ^IeWYvKti

使用方式跟Find类似 ^gzh1aR3z

删除 ^fwYtzh7h

// 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";

// 如果不带 ^q52t5OCN

删除一条记录 ^2uNPE5v1

删除也支持使用内联条件删除 ^IiazFcH6

// 如果指定的值不包括主属性，那么 GORM 会执行批量删除，它将删除所有匹配的记录 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%";

// 可以将一个主键切片传递给Delete 方法，以便更高效的删除数据量大的记录 var users = []User{{ID: 1}, {ID: 2}, {ID: 3}} db.Delete(&users) // DELETE FROM users WHERE id IN (1,2,3);

db.Delete(&users, "name LIKE ?", "%jinzhu%") // DELETE FROM users WHERE name LIKE "%jinzhu%" AND id IN (1,2,3);

^PdGOmTam

批量删除 ^DCDhC2h4

// 当你试图执行不带任何条件的批量删除时，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

// Exec执行原生SQL db.Exec("DELETE FROM users") // DELETE FROM users

db.Session(&gorm.Session{AllowGlobalUpdate: true}).Delete(&User{}) // DELETE FROM users ^JH5CWgc8

ErrMissingWhereClause ^k1TEFt5S

// 当模型包含了gorm.DeleteAt字段时，该模型自动获得软删除能力，不会删除数据，而是更新DeleteAt字段 // 只能使用Unsoped方法来查找该记录 db.Unscoped().Where("age = 20").Find(&users) // SELECT * FROM users WHERE age = 20;

// 永久删除 db.Unscoped().Delete(&order) // DELETE FROM orders WHERE id=10; ^tXs2PmE6

软删除 ^cC8pEPK2

更新 ^tmXr9f6U

// 根据条件更新 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; ^Q9itOgof

更新单列 ^05oDqtiX

// 根据 struct 更新属性，只会更新非零值的字段 // 使用 struct 更新时, GORM 将只更新非零值字段。 你可能想用 map 来更新属性，或者使用 Select 声明字段来更新 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; ^yWauBtHx

更新多列字段 ^4yOynvDJ

// 选择 Map 的字段 // User 的 ID 是 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 的字段（会选中零值的字段） db.Model(&user).Select("Name", "Age").Updates(User{Name: "new_name", Age: 0}) // UPDATE users SET name='new_name', age=0 WHERE id=111;

// 选择所有字段（选择包括零值字段的所有字段） 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}) ^pwliiNCR

更新选定字段 ^jGvyoeMB

ErrMissingWhereClause ^lQoatAVi

更新也默认阻止不带任何条件的变更 db.Model(&User{}).Update("name", "jinzhu").Error // gorm.ErrMissingWhereClause

db.Model(&User{}).Where("1 = 1").Update("name", "jinzhu") // UPDATE users SET name = "jinzhu" WHERE 1=1

db.Exec("UPDATE users SET name = ?", "jinzhu") // UPDATE users SET name = "jinzhu"

db.Session(&gorm.Session{AllowGlobalUpdate: true}).Model(&User{}).Update("name", "jinzhu") // UPDATE users SET name = "jinzhu" ^jgp73ys9

// product's ID is 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;

// 可以使用SQL函数/数学表达式/引用其他字段/条件CASE赋值 db.Model(&user).Update("status", gorm.Expr( CASE WHEN score > 90 THEN 'A' WHEN score > 60 THEN 'B' ELSE 'C' END)) ^veTblhGx

// Create from customized data type type Location struct { X, Y int }

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

db.Model(&User{ID: 1}).Updates(User{ Name: "jinzhu", Location: Location{X: 100, Y: 100}, }) // UPDATE user_with_points SET name="jinzhu",location=ST_PointFromText("POINT(100 100)") WHERE id = 1 ^72j5dqx6

SQL表达式更新 ^ZDuoYvDJ

自定义表达式 ^DD0QNMeK

多对多关系操作 ^J6jIUiOO

// 方式1：将多对多关系的示例对象查询并赋值即可 var c = []fun.Course{} db.Where("name like ?", "数%").Find(&c) //fmt.Println(c) var s = fun.Student{Name: "李四", ClassID: 3, Courses: c} db.Create(&s)

添加多对多关系 ^smfC6qKo

关联关系预加载 ^tyoUAvni

c := fun.Class{} //t := fun.Teacher{} db.Where("name = ?", 3).First(&c)

// class表中有对Teacher表的一对多关系 //db.Model(&c).Association("Teacher").Find(&t) db.Preload("Teacher").Where("name = ?", 1).Find(&c) fmt.Println(c) ^gzVF1Wtg

type Student struct { gorm.Model Name string Sno int Pwd string gorm:"type:varchar(100);not null" Tel string gorm:"type:char(11)" Gender byte gorm:"default:1" Birth *time.Time Remark string

}

s := []fun.Student{} db.Preload("Courses").Where("name = ?", "ds").Find(&s)

db.Preload("Class").Preload("Courses").Where("class_id = ?", 10).Find(&s) ^2D6LQG6S

多对多 ^jJ8IWdY6

多对一 ^c07beL7q

Association与Preload区别 ^U800Snoy

Preload查的是整个关联关系的值，如把学生选的课程和学生信息一起查出来

而Association查的是关联表的信息，也就是根据学生查学生所选课程信息 ^E5yVpnBV

db.Preload("Class").Preload("Class.Teacher").Preload("Courses").Where("class_id = ?", 10).Find(&s)

// clause.Associations指代所有直接关联 db.Preload(clause.Associations).Preload("Class.Teacher").Where("class_id = ?", 10).Find(&s) ^ymHlmBbV

嵌套预加载 ^ZOuwHuse

%%

hashtag
Drawing

%%

上一页Excalidraw下一页web

最后更新于