ShippingAddress Address // One-To-One (属于 - 本表的ShippingAddressID作外键) ShippingAddressID int
IgnoreMe int`gorm:"-"`// 忽略这个字段 Languages []Language `gorm:"many2many:user_languages;"`// Many-To-Many , 'user_languages'是连接表 }
type Email struct { ID int UserID int`gorm:"index"`// 外键 (属于), tag `index`是为该列创建索引 Email string`gorm:"type:varchar(100);unique_index"`// `type`设置sql类型, `unique_index` 为该列设置唯一索引 Subscribed bool }
type Address struct { ID int Address1 string`gorm:"not null;unique"`// 设置字段为非空并唯一 Address2 string`gorm:"type:varchar(100);unique"` Post sql.NullString `gorm:"not null"` }
type Language struct { ID int Name string`gorm:"index:idx_name_code"`// 创建索引并命名，如果找到其他相同名称的索引则创建组合索引 Code string`gorm:"index:idx_name_code"`// `unique_index` also works }
type CreditCard struct { gorm.Model UserID uint Number string }
// 开始关联模式 var user User db.Model(&user).Association("Languages") // `user`是源，它需要是一个有效的记录（包含主键） // `Languages`是关系中源的字段名。 // 如果这些条件不匹配，将返回一个错误，检查它： // db.Model(&user).Association("Languages").Error
db.Where("name <> ?","jinzhu").Where("age >= ? and role <> ?",20,"admin").Find(&users) //// SELECT * FROM users WHERE name <> 'jinzhu' AND age >= 20 AND role <> 'admin';
// 为Select语句添加扩展SQL选项 db.Set("gorm:query_option", "FOR UPDATE").First(&user, 10) //// SELECT * FROM users WHERE id = 10 FOR UPDATE;
FirstOrInit
获取第一个匹配的记录，或者使用给定的条件初始化一个新的记录（仅适用于struct，map条件）
1 2 3 4 5 6 7 8 9
// Unfound db.FirstOrInit(&user, User{Name: "non_existing"}) //// user -> User{Name: "non_existing"}
// Found db.Where(User{Name: "Jinzhu"}).FirstOrInit(&user) //// user -> User{Id: 111, Name: "Jinzhu", Age: 20} db.FirstOrInit(&user, map[string]interface{}{"name": "jinzhu"}) //// user -> User{Id: 111, Name: "Jinzhu", Age: 20}
Attrs
如果未找到记录，则使用参数初始化结构
1 2 3 4 5 6 7 8 9 10 11 12 13
// Unfound db.Where(User{Name: "non_existing"}).Attrs(User{Age: 20}).FirstOrInit(&user) //// SELECT * FROM USERS WHERE name = 'non_existing'; //// user -> User{Name: "non_existing", Age: 20}
db.Where(User{Name: "non_existing"}).Attrs("age", 20).FirstOrInit(&user) //// SELECT * FROM USERS WHERE name = 'non_existing'; //// user -> User{Name: "non_existing", Age: 20}
// Found db.Where(User{Name: "Jinzhu"}).Attrs(User{Age: 30}).FirstOrInit(&user) //// SELECT * FROM USERS WHERE name = jinzhu'; //// user -> User{Id: 111, Name: "Jinzhu", Age: 20}
// Found db.Where(User{Name: "Jinzhu"}).Assign(User{Age: 30}).FirstOrInit(&user) //// SELECT * FROM USERS WHERE name = jinzhu'; //// user -> User{Id: 111, Name: "Jinzhu", Age: 30}
FirstOrCreate
获取第一个匹配的记录，或创建一个具有给定条件的新记录（仅适用于struct, map条件）
1 2 3 4 5 6 7 8
// Unfound db.FirstOrCreate(&user, User{Name: "non_existing"}) //// INSERT INTO "users" (name) VALUES ("non_existing"); //// user -> User{Id: 112, Name: "non_existing"}
// Found db.Where(User{Name: "Jinzhu"}).FirstOrCreate(&user) //// user -> User{Id: 111, Name: "Jinzhu"}
Attrs
如果未找到记录，则为参数分配结构
1 2 3 4 5 6 7 8 9 10
// Unfound db.Where(User{Name: "non_existing"}).Attrs(User{Age: 20}).FirstOrCreate(&user) //// SELECT * FROM users WHERE name = 'non_existing'; //// INSERT INTO "users" (name, age) VALUES ("non_existing", 20); //// user -> User{Id: 112, Name: "non_existing", Age: 20}
// Found db.Where(User{Name: "jinzhu"}).Attrs(User{Age: 30}).FirstOrCreate(&user) //// SELECT * FROM users WHERE name = 'jinzhu'; //// user -> User{Id: 111, Name: "jinzhu", Age: 20}
Assign
将其分配给记录，而不管它是否被找到，并保存回数据库。
1 2 3 4 5 6 7 8 9 10 11
// Unfound db.Where(User{Name: "non_existing"}).Assign(User{Age: 20}).FirstOrCreate(&user) //// SELECT * FROM users WHERE name = 'non_existing'; //// INSERT INTO "users" (name, age) VALUES ("non_existing", 20); //// user -> User{Id: 112, Name: "non_existing", Age: 20}
// Found db.Where(User{Name: "jinzhu"}).Assign(User{Age: 30}).FirstOrCreate(&user) //// SELECT * FROM users WHERE name = 'jinzhu'; //// UPDATE users SET age=30 WHERE id = 111; //// user -> User{Id: 111, Name: "jinzhu", Age: 30}
Select
指定要从数据库检索的字段，默认情况下，将选择所有字段;
1 2 3 4 5 6 7 8
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;
Order
在从数据库检索记录时指定顺序，将重排序设置为true以覆盖定义的条件
1 2 3 4 5 6 7 8 9 10 11
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;
// ReOrder db.Order("age desc").Find(&users1).Order("age", true).Find(&users2) //// SELECT * FROM users ORDER BY age desc; (users1) //// SELECT * FROM users ORDER BY age; (users2)
Limit
指定要检索的记录数
1 2 3 4 5 6 7
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)
Offset
指定在开始返回记录之前要跳过的记录数
1 2 3 4 5 6 7
db.Offset(3).Find(&users) //// SELECT * FROM users OFFSET 3;
// Cancel offset condition with -1 db.Offset(10).Find(&users1).Offset(-1).Find(&users2) //// SELECT * FROM users OFFSET 10; (users1) //// SELECT * FROM users; (users2)
Count
获取模型的记录数
1 2 3 4 5 6 7 8 9
db.Where("name = ?", "jinzhu").Or("name = ?", "jinzhu 2").Find(&users).Count(&count) //// SELECT * from USERS WHERE name = 'jinzhu' OR name = 'jinzhu 2'; (users) //// SELECT count(*) FROM users WHERE name = 'jinzhu' OR name = 'jinzhu 2'; (count)
db.Model(&User{}).Where("name = ?", "jinzhu").Count(&count) //// SELECT count(*) FROM users WHERE name = 'jinzhu'; (count)
db.Table("deleted_users").Count(&count) //// SELECT count(*) FROM deleted_users;
Group & Having
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
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)
Join
指定连接条件
1 2 3 4 5 6 7 8 9
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)
// 多个连接与参数 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)
var deleted_users []User db.Table("deleted_users").Find(&deleted_users) //// SELECT * FROM deleted_users;
db.Table("deleted_users").Where("name = ?", "jinzhu").Delete() //// DELETE FROM deleted_users WHERE name = 'jinzhu';
预加载 {p}
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17
db.Preload("Orders").Find(&users) //// SELECT * FROM users; //// SELECT * FROM orders WHERE user_id IN (1,2,3,4);
db.Preload("Orders", "state NOT IN (?)", "cancelled").Find(&users) //// SELECT * FROM users; //// SELECT * FROM orders WHERE user_id IN (1,2,3,4) AND state NOT IN ('cancelled');
db.Where("state = ?", "active").Preload("Orders", "state NOT IN (?)", "cancelled").Find(&users) //// SELECT * FROM users WHERE state = 'active'; //// SELECT * FROM orders WHERE user_id IN (1,2) AND state NOT IN ('cancelled');
db.Preload("Orders").Preload("Profile").Preload("Role").Find(&users) //// SELECT * FROM users; //// SELECT * FROM orders WHERE user_id IN (1,2,3,4); // has many //// SELECT * FROM profiles WHERE user_id IN (1,2,3,4); // has one //// SELECT * FROM roles WHERE id IN (4,5,6); // belongs to
db.Preload("Orders", func(db *gorm.DB) *gorm.DB { return db.Order("orders.amount DESC") }).Find(&users) //// SELECT * FROM users; //// SELECT * FROM orders WHERE user_id IN (1,2,3,4) order by orders.amount DESC;
嵌套预加载
1 2
db.Preload("Orders.OrderItems").Find(&users) db.Preload("Orders", "state = ?", "paid").Preload("Orders.OrderItems").Find(&users)
// 更新单个属性，类似于`Update` db.Model(&user).UpdateColumn("name", "hello") //// UPDATE users SET name='hello' WHERE id = 111;
// 更新多个属性，与“更新”类似 db.Model(&user).UpdateColumns(User{Name: "hello", Age: 18}) //// UPDATE users SET name='hello', age=18 WHERE id = 111;
Batch Updates 批量更新
Callbacks在批量更新时不会运行
1 2 3 4 5 6 7 8 9
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);
// 删除存在的记录 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);
批量删除
删除所有匹配记录
1 2 3 4 5
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%";
// begin transaction 开始事务 BeforeSave BeforeCreate // save before associations 保存前关联 // update timestamp `CreatedAt`, `UpdatedAt` 更新`CreatedAt`, `UpdatedAt`时间戳 // save self 保存自己 // reload fields that have default value and its value is blank 重新加载具有默认值且其值为空的字段 // save after associations 保存后关联 AfterCreate AfterSave // commit or rollback transaction 提交或回滚事务
更新对象
更新过程中可用的回调
1 2 3 4 5 6 7 8 9 10
// begin transaction 开始事务 BeforeSave BeforeUpdate // save before associations 保存前关联 // update timestamp `UpdatedAt` 更新`UpdatedAt`时间戳 // save self 保存自己 // save after associations 保存后关联 AfterUpdate AfterSave // commit or rollback transaction 提交或回滚事务
删除对象
删除过程中可用的回调
1 2 3 4 5
// begin transaction 开始事务 BeforeDelete // delete self 删除自己 AfterDelete // commit or rollback transaction 提交或回滚事务
查询对象 {querying-an-object}
查询过程中可用的回调
1 2 3
// load data from database 从数据库加载数据 // Preloading (edger loading) 预加载（加载） AfterFind
回调示例
1 2 3 4 5 6 7 8 9 10 11 12 13 14
func(u *User) BeforeUpdate() (err error) { if u.readonly() { err = errors.New("read only user") } return }
// 如果用户ID大于1000，则回滚插入 func(u *User) AfterCreate() (err error) { if (u.Id > 1000) { err = errors.New("user id is already greater than 1000") } return }
