模型是标准的 struct，由 Go 的基本数据类型、实现了 Scanner 和 Valuer 接口的自定义类型及其指针或别名组成
例如：
1 2 3 4 5 6 7 8 9 10 11
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 }
type User struct { Name string`gorm:"<-:create"`// 允许读和创建 Name string`gorm:"<-:update"`// 允许读和更新 Name string`gorm:"<-"`// 允许读和写（创建和更新） Name string`gorm:"<-:false"`// 允许读，禁止写 Name string`gorm:"->"`// 只读（除非有自定义配置，否则禁止写） Name string`gorm:"->;<-:create"`// 允许读和写 Name string`gorm:"->:false;<-:create"`// 仅创建（禁止从 db 读） Name string`gorm:"-"`// 通过 struct 读写会忽略该字段 }
如果您想要保存 UNIX（毫/纳）秒时间戳，而不是 time，您只需简单地将 time.Time 修改为 int 即可
1 2 3 4 5 6 7
type User struct { CreatedAt time.Time // Set to current time if it is zero on creating UpdatedAt int// Set to current unix seconds on updating or if it is zero on creating Updated int64`gorm:"autoUpdateTime:nano"`// Use unix nano seconds as updating time Updated int64`gorm:"autoUpdateTime:milli"`// Use unix milli seconds as updating time Created int64`gorm:"autoCreateTime"`// Use unix seconds as creating time }
嵌入结构体
对于匿名字段，GORM 会将其字段包含在父结构体中，例如：
1 2 3 4 5 6 7 8 9 10 11 12
type User struct { gorm.Model Name string } // 等效于 type User struct { ID uint`gorm:"primaryKey"` CreatedAt time.Time UpdatedAt time.Time DeletedAt gorm.DeletedAt `gorm:"index"` Name string }
对于正常的结构体字段，你也可以通过标签 embedded 将其嵌入，例如：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17
type Author struct { Name string Email string }
type Blog struct { ID int Author Author `gorm:"embedded"` Upvotes int32 } // 等效于 type Blog struct { ID int64 Name string Email string Upvotes int32 }
并且，您可以使用标签 embeddedPrefix 来为 db 中的字段名添加前缀，例如：
1 2 3 4 5 6 7 8 9 10 11 12
type Blog struct { ID int Author Author `gorm:"embedded;embeddedPrefix:author_"` Upvotes int32 } // 等效于 type Blog struct { ID int64 AuthorName string AuthorEmail string Upvotes int32 }
字段标签
声明 model 时，tag 是可选的，GORM 支持以下 tag： tag 名大小写不敏感，但建议使用 camelCase 风格
type User struct { ID string`gorm:"default:uuid_generate_v3()"`// db func FirstName string LastName string Age uint8 FullName string`gorm:"->;type:GENERATED ALWAYS AS (concat(firstname,' ',lastname));default:(-);"` }
// 在`id`冲突时，将列更新为默认值 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
// 在`id`冲突时，将列更新为新值 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
// 在冲突时，更新除主键以外的所有列到新值。 db.Clauses(clause.OnConflict{ UpdateAll: true, }).Create(&users) // INSERT INTO "users" *** ON CONFLICT ("id") DO UPDATE SET "name"="excluded"."name", "age"="excluded"."age", ...;
// 有效，因为目标 struct 是指针 db.First(&user) // SELECT * FROM `users` ORDER BY `users`.`id` LIMIT 1
// 有效，因为通过 `db.Model()` 指定了 model result := map[string]interface{}{} db.Model(&User{}).First(&result) // SELECT * FROM `users` ORDER BY `users`.`id` LIMIT 1
// 无效 result := map[string]interface{}{} db.Table("users").First(&result)
// 配合 Take 有效 result := map[string]interface{}{} db.Table("users").Take(&result)
// 未指定主键，会根据第一个字段排序(即：`Code`) type Language struct { Code string Name string } db.First(&Language{}) // SELECT * FROM `languages` ORDER BY `languages`.`code` LIMIT 1
// 获取第一条匹配的记录 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 & Map 条件
1 2 3 4 5 6 7 8 9 10 11
// 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;
内联条件
查询条件也可以被内联到 First 和 Find 之类的方法中，其用法类似于 Where。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18
// 根据主键获取记录，如果是非整型主键 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;
Not 条件
构建 NOT 条件，用法与 Where 类似
1 2 3 4 5 6 7 8 9 10 11 12 13 14
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 条件
1 2 3 4 5 6 7 8 9 10
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.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() 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)
Distinct
从模型中选择不相同的值
1
db.Distinct("name", "age").Order("name, age desc").Find(&results)
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)
// 带参数的多表连接 db.Joins("JOIN emails ON emails.user_id = users.id AND emails.email = ?", "[email protected]").Joins("JOIN credit_cards ON credit_cards.user_id = users.id").Where("credit_cards.number = ?", "411111111111").Find(&user)
Joins 预加载
您可以使用 Joins 实现单条 SQL 预加载关联记录，例如：
1 2
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`;
Join with conditions
1 2
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;
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`
查看 原生 SQL 及构造器 获取详情
子查询
子查询可以嵌套在查询中，GORM 允许在使用 *gorm.DB 对象作为参数时生成子查询
1 2 3 4 5 6
db.Where("amount > (?)", db.Table("orders").Select("AVG(amount)")).Find(&orders) // 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) // SELECT AVG(age) as avgage FROM `users` GROUP BY `name` HAVING AVG(age) > (SELECT AVG(age) FROM `users` WHERE name LIKE "name%")
From 子查询
GORM 允许您在 Table 方法中通过 FROM 子句使用子查询，例如：
1 2 3 4 5 6 7
db.Table("(?) as u", db.Model(&User{}).Select("name", "age")).Where("age = ?", 18}).Find(&User{}) // SELECT * FROM (SELECT `name`,`age` FROM `users`) as u WHERE `age` = 18
subQuery1 := db.Model(&User{}).Select("name") subQuery2 := db.Model(&Pet{}).Select("name") db.Table("(?) as u, (?) as p", subQuery1, subQuery2).Find(&User{}) // SELECT * FROM (SELECT `name` FROM `users`) as u, (SELECT `name` FROM `pets`) as p
// SELECT * FROM `pizzas` WHERE (pizza = "pepperoni" AND (size = "small" OR size = "medium")) OR (pizza = "hawaiian" AND size = "xlarge")
IN with multiple columns
Selecting IN with multiple columns
1 2
db.Where("(name, age, role) IN ?", [][]interface{}{{"jinzhu", 18, "admin"}, {"jinzhu2", 19, "user"}}).Find(&users) // SELECT * FROM users WHERE (name, age, role) IN (("jinzhu", 18, "admin"), ("jinzhu 2", 19, "user"));
Named Argument
GORM supports named arguments with sql.NamedArg or map[string]interface{}{}, for example:
1 2 3 4 5
db.Where("name1 = @name OR name2 = @name", sql.Named("name", "jinzhu")).Find(&user) // SELECT * FROM `users` WHERE name1 = "jinzhu" OR name2 = "jinzhu"
db.Where("name1 = @name OR name2 = @name", map[string]interface{}{"name": "jinzhu"}).First(&user) // SELECT * FROM `users` WHERE name1 = "jinzhu" OR name2 = "jinzhu" ORDER BY `users`.`id` LIMIT 1
GORM allows scan result to map[string]interface{} or []map[string]interface{}, don’t forget to specify Model or Table, for example:
1 2 3 4 5
result := map[string]interface{}{} db.Model(&User{}).First(&result, "id = ?", 1)
var results []map[string]interface{} db.Table("users").Find(&results)
FirstOrInit
Get first matched record or initialize a new instance with given conditions (only works with struct or map conditions)
1 2 3 4 5 6 7 8 9 10 11
// User not found, initialize it with give conditions db.FirstOrInit(&user, User{Name: "non_existing"}) // user -> User{Name: "non_existing"}
// Found user with `name` = `jinzhu` db.Where(User{Name: "jinzhu"}).FirstOrInit(&user) // user -> User{ID: 111, Name: "Jinzhu", Age: 18}
// Found user with `name` = `jinzhu` db.FirstOrInit(&user, map[string]interface{}{"name": "jinzhu"}) // user -> User{ID: 111, Name: "Jinzhu", Age: 18}
initialize struct with more attributes if record not found, those Attrs won’t be used to build SQL query
1 2 3 4 5 6 7 8 9 10 11 12 13 14
// User not found, initialize it with give conditions and Attrs db.Where(User{Name: "non_existing"}).Attrs(User{Age: 20}).FirstOrInit(&user) // SELECT * FROM USERS WHERE name = 'non_existing' ORDER BY id LIMIT 1; // user -> User{Name: "non_existing", Age: 20}
// User not found, initialize it with give conditions and Attrs db.Where(User{Name: "non_existing"}).Attrs("age", 20).FirstOrInit(&user) // SELECT * FROM USERS WHERE name = 'non_existing' ORDER BY id LIMIT 1; // user -> User{Name: "non_existing", Age: 20}
// Found user with `name` = `jinzhu`, attributes will be ignored db.Where(User{Name: "Jinzhu"}).Attrs(User{Age: 20}).FirstOrInit(&user) // SELECT * FROM USERS WHERE name = jinzhu' ORDER BY id LIMIT 1; // user -> User{ID: 111, Name: "Jinzhu", Age: 18}
Assign attributes to struct regardless it is found or not, those attributes won’t be used to build SQL query and the final data won’t be saved into database
1 2 3 4 5 6 7 8
// User not found, initialize it with give conditions and Assign attributes db.Where(User{Name: "non_existing"}).Assign(User{Age: 20}).FirstOrInit(&user) // user -> User{Name: "non_existing", Age: 20}
// Found user with `name` = `jinzhu`, update it with Assign attributes db.Where(User{Name: "Jinzhu"}).Assign(User{Age: 20}).FirstOrInit(&user) // SELECT * FROM USERS WHERE name = jinzhu' ORDER BY id LIMIT 1; // user -> User{ID: 111, Name: "Jinzhu", Age: 20}
FirstOrCreate
Get first matched record or create a new one with given conditions (only works with struct, map conditions)
1 2 3 4 5 6 7 8
// User not found, create a new record with give conditions db.FirstOrCreate(&user, User{Name: "non_existing"}) // INSERT INTO "users" (name) VALUES ("non_existing"); // user -> User{ID: 112, Name: "non_existing"}
// Found user with `name` = `jinzhu` db.Where(User{Name: "jinzhu"}).FirstOrCreate(&user) // user -> User{ID: 111, Name: "jinzhu", "Age": 18}
Create struct with more attributes if record not found, those Attrs won’t be used to build SQL query
1 2 3 4 5 6 7 8 9 10
// User not found, create it with give conditions and Attrs db.Where(User{Name: "non_existing"}).Attrs(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}
// Found user with `name` = `jinzhu`, attributes will be ignored db.Where(User{Name: "jinzhu"}).Attrs(User{Age: 20}).FirstOrCreate(&user) // SELECT * FROM users WHERE name = 'jinzhu' ORDER BY id LIMIT 1; // user -> User{ID: 111, Name: "jinzhu", Age: 18}
Assign attributes to the record regardless it is found or not and save them back to the database.
1 2 3 4 5 6 7 8 9 10 11
// User not found, initialize it with give conditions and Assign attributes 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}
// Found user with `name` = `jinzhu`, update it with Assign attributes 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}
Optimizer/Index Hints
Optimizer hints allow to control the query optimizer to choose a certain query execution plan, GORM supports it with gorm.io/hints, e.g:
1 2 3 4
import"gorm.io/hints"
db.Clauses(hints.New("MAX_EXECUTION_TIME(10000)")).Find(&User{}) // SELECT * /*+ MAX_EXECUTION_TIME(10000) */ FROM `users`
Index hints allow passing index hints to the database in case the query planner gets confused.
1 2 3 4 5 6 7
import"gorm.io/hints"
db.Clauses(hints.UseIndex("idx_user_name")).Find(&User{}) // SELECT * FROM `users` USE INDEX (`idx_user_name`)
db.Clauses(hints.ForceIndex("idx_user_name", "idx_user_id").ForJoin()).Find(&User{}) // SELECT * FROM `users` FORCE INDEX FOR JOIN (`idx_user_name`,`idx_user_id`)"
for rows.Next() { var user User // ScanRows is a method of `gorm.DB`, it can be used to scan a row into a struct db.ScanRows(rows, &user)
// do something }
FindInBatches
Query and process records in batch
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18
// batch size 100 result := db.Where("processed = ?", false).FindInBatches(&results, 100, func(tx *gorm.DB, batch int)error { for _, result := range results { // batch processing found records }
tx.Save(&results)
tx.RowsAffected // number of records in this batch
batch // Batch 1, 2, 3
// returns error will stop future batches returnnil })
result.Error // returned error result.RowsAffected // processed records count in all batches
Query Hooks
GORM allows hooks AfterFind for a query, it will be called when querying a record, refer Hooks for details
db.Scopes(AmountGreaterThan1000, PaidWithCreditCard).Find(&orders) // Find all credit card orders and amount greater than 1000
db.Scopes(AmountGreaterThan1000, PaidWithCod).Find(&orders) // Find all COD orders and amount greater than 1000
db.Scopes(AmountGreaterThan1000, OrderStatus([]string{"paid", "shipped"})).Find(&orders) // Find all paid, shipped orders that amount greater than 1000
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"}, }
当使用 Update 更新单个列时，你需要指定条件，否则会返回 ErrMissingWhereClause 错误，查看 Block Global Updates 获取详情。当使用了 Model 方法，且该对象主键有值，该值会被用于构建条件，例如：
1 2 3 4 5 6 7 8 9 10 11
// 条件更新 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;
func(u *User)BeforeUpdate(tx *gorm.DB)(err error) { if u.Role == "admin" { return errors.New("admin user not allowed to update") } return }
批量更新
如果您尚未通过 Model 指定记录的主键，则 GORM 会执行批量更新
1 2 3 4 5 6 7
// 根据 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);
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
根据子查询进行更新
使用子查询更新表
1 2 3 4 5 6
db.Model(&user).Update("company_name", db.Model(&Company{}).Select("name").Where("companies.id = users.company_id")) // UPDATE "users" SET "company_name" = (SELECT name FROM companies WHERE companies.id = users.company_id);
db.Table("users as u").Where("name = ?", "jinzhu").Update("company_name", db.Table("companies as c").Select("name").Where("c.id = u.company_id"))
db.Table("users as u").Where("name = ?", "jinzhu").Updates(map[string]interface{}{}{"company_name": db.Table("companies as c").Select("name").Where("c.id = u.company_id")})
GORM provides Changed method could be used in Before Update Hooks , it will return the field changed or not
The Changed method only works with methods Update, Updates, and it only checks if the updating value from Update / Updates equals the model value, will return true if it is changed and not omitted
func(u *User)BeforeUpdate(tx *gorm.DB)(err error) { // if Role changed if tx.Statement.Changed("Role") { return errors.New("role not allowed to change") }
if tx.Statement.Changed("Name", "Admin") { // if Name or Role changed tx.Statement.SetColumn("Age", 18) }
// if any fields changed if tx.Statement.Changed() { tx.Statement.SetColumn("RefreshedAt", time.Now()) } returnnil }
db.Where("name1 = @name OR name2 = @name", sql.Named("name", "jinzhu")).Find(&user) // SELECT * FROM `users` WHERE name1 = "jinzhu" OR name2 = "jinzhu"
db.Where("name1 = @name OR name2 = @name", map[string]interface{}{"name": "jinzhu2"}).First(&result3) // SELECT * FROM `users` WHERE name1 = "jinzhu2" OR name2 = "jinzhu2" ORDER BY `users`.`id` LIMIT 1
// 原生 SQL 及命名参数 db.Raw("SELECT * FROM users WHERE name1 = @name OR name2 = @name2 OR name3 = @name", sql.Named("name", "jinzhu1"), sql.Named("name2", "jinzhu2")).Find(&user) // SELECT * FROM users WHERE name1 = "jinzhu1" OR name2 = "jinzhu2" OR name3 = "jinzhu1"
db.Raw("SELECT * FROM users WHERE (name1 = @name AND name3 = @name) AND name2 = @name2", map[string]interface{}{"name": "jinzhu", "name2": "jinzhu2"}).Find(&user) // SELECT * FROM users WHERE (name1 = "jinzhu" AND name3 = "jinzhu") AND name2 = "jinzhu2"
type NamedArgument struct { Name string Name2 string }
db.Raw("SELECT * FROM users WHERE (name1 = @Name AND name3 = @Name) AND name2 = @Name2", NamedArgument{Name: "jinzhu", Name2: "jinzhu2"}).Find(&user) // SELECT * FROM users WHERE (name1 = "jinzhu" AND name3 = "jinzhu") AND name2 = "jinzhu2"
sql := DB.ToSQL(func(tx *gorm.DB) *gorm.DB { return tx.Model(&User{}).Where("id = ?", 100).Limit(10).Order("age desc").Find(&[]User{}) }) sql //=> SELECT * FROM "users" WHERE id = 100 AND "users"."deleted_at" IS NULL ORDER BY age desc LIMIT 10
Row & Rows
得到结果为 *sql.Row
1 2 3 4 5 6 7
// Use GORM API build SQL row := db.Table("users").Where("name = ?", "jinzhu").Select("name", "age").Row() row.Scan(&name, &age)
// Use Raw SQL row := db.Raw("select name, age, email from users where name = ?", "jinzhu").Row() row.Scan(&name, &age, &email)
得到结果为 *sql.Rows
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17
// Use GORM API build SQL rows, err := db.Model(&User{}).Where("name = ?", "jinzhu").Select("name, age, email").Rows() defer rows.Close() for rows.Next() { rows.Scan(&name, &age, &email)
// do something }
// Raw SQL rows, err := db.Raw("select name, age, email from users where name = ?", "jinzhu").Rows() defer rows.Close() for rows.Next() { rows.Scan(&name, &age, &email)
db.Offset(10).Limit(5).Find(&users) // Generated for SQL Server // SELECT * FROM "users" OFFSET 10 ROW FETCH NEXT 5 ROWS ONLY // Generated for MySQL // SELECT * FROM `users` LIMIT 5 OFFSET 10
db.Clauses(hints.New("hint")).Find(&User{}) // SELECT * /*+ hint */ FROM `users`
Belongs To
belongs to 会与另一个模型建立了一对一的连接。 这种模型的每一个实例都“属于”另一个模型的一个实例。
例如，您的应用包含 user 和 company，并且每个 user 能且只能被分配给一个 company。下面的类型就表示这种关系。 注意，在 User 对象中，有一个和 Company 一样的 CompanyID。 默认情况下， CompanyID 被隐含地用来在 User 和 Company 之间创建一个外键关系， 因此必须包含在 User 结构体中才能填充 Company 内部结构体。
1 2 3 4 5 6 7 8 9 10 11 12
// `User` 属于 `Company`，`CompanyID` 是外键 type User struct { gorm.Model Name string CompanyID int Company Company }
// 开始关联模式 var user User db.Model(&user).Association("Languages") // `user` 是源模型，它的主键不能为空 // 关系的字段名是 `Languages` // 如果匹配了上面两个要求，会开始关联模式，否则会返回错误 db.Model(&user).Association("Languages").Error
type User struct { gorm.Model Username string Orders []Order }
type Order struct { gorm.Model UserID uint Price float64 }
// 查找 user 时预加载相关 Order db.Preload("Orders").Find(&users) // SELECT * FROM users; // SELECT * FROM orders WHERE user_id IN (1,2,3,4);
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.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;
// Preload Orders with conditions 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');
自定义预加载 SQL
您可以通过传入自定义预加载 SQL func(db *gorm.DB) *gorm.DB，例如：
1 2 3 4 5
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;
// Customize Preload conditions for `Orders` // And GORM won't preload unmatched order's OrderItems then db.Preload("Orders", "state = ?", "paid").Preload("Orders.OrderItems").Find(&users)
tx.Where("age = ?", 18).Find(&users) // `tx.Where("age = ?", 18)` 会复用上面的那个 `Statement`，并向其添加条件 // `Find(&users)` 是一个 finisher 方法，它运行注册的查询回调，生成并运行下面这条 SQL： // SELECT * FROM users WHERE name = 'jinzhu' AND age = 18
tx.Where("age = ?", 28).Find(&users) // `tx.Where("age = ?", 18)` 同样会复用上面的那个 `Statement`，并向其添加条件 // `Find(&users)` 是一个 finisher 方法，它运行注册的查询回调，生成并运行下面这条 SQL： // SELECT * FROM users WHERE name = 'jinzhu' AND age = 18 AND age = 28;
tx.First(&user) // SELECT * FROM users ORDER BY id LIMIT 1
tx.First(&user, "id = ?", 10) // SELECT * FROM users WHERE id = 10 ORDER BY id
// 不带 `NewDB` 选项 tx2 := db.Where("name = ?", "jinzhu").Session(&gorm.Session{}) tx2.First(&user) // SELECT * FROM users WHERE name = "jinzhu" ORDER BY id
Index 用于提高数据检索和 SQL 查询性能。 Index Hints 向优化器提供了在查询处理过程中如何选择索引的信息。与 optimizer 相比，它可以更灵活地选择更有效的执行计划
1 2 3 4 5 6 7 8 9 10 11 12 13
import "gorm.io/hints"
db.Clauses(hints.UseIndex("idx_user_name")).Find(&User{}) // SELECT * FROM `users` USE INDEX (`idx_user_name`)
db.Clauses(hints.ForceIndex("idx_user_name", "idx_user_id").ForJoin()).Find(&User{}) // SELECT * FROM `users` FORCE INDEX FOR JOIN (`idx_user_name`,`idx_user_id`)"
db.Clauses( hints.ForceIndex("idx_user_name", "idx_user_id").ForOrderBy(), hints.IgnoreIndex("idx_user_name").ForGroupBy(), ).Find(&User{}) // SELECT * FROM `users` FORCE INDEX FOR ORDER BY (`idx_user_name`,`idx_user_id`) IGNORE INDEX FOR GROUP BY (`idx_user_name`)"
if org != "" { var organization Organization if db.Session(&Session{}).First(&organization, "name = ?", org).Error == nil { return db.Where("org_id = ?", organization.ID) } }
db.AddError("invalid organization") return db } }
db.Model(&article).Scopes(CurOrganization(r)).Update("Name", "name 1") // UPDATE articles SET name = "name 1" WHERE org_id = 111 db.Scopes(CurOrganization(r)).Delete(&Article{}) // DELETE FROM articles WHERE org_id = 111
使用 ID 作为主键
默认情况下，GORM 会使用 ID 作为表的主键。
1 2 3 4
type User struct { ID string // 默认情况下，名为 `ID` 的字段会作为表的主键 Name string }
你可以通过标签 primaryKey 将其它字段设为主键
1 2 3 4 5 6 7
// 将 `UUID` 设为主键 type Animal struct { ID int64 UUID string `gorm:"primaryKey"` Name string Age int64 }
db.Clauses(hints.New("hint")).Find(&User{}) // SELECT * /*+ hint */ FROM `users`
Index Hints
1 2 3 4 5 6 7 8 9 10 11 12 13
import "gorm.io/hints"
db.Clauses(hints.UseIndex("idx_user_name")).Find(&User{}) // SELECT * FROM `users` USE INDEX (`idx_user_name`)
db.Clauses(hints.ForceIndex("idx_user_name", "idx_user_id").ForJoin()).Find(&User{}) // SELECT * FROM `users` FORCE INDEX FOR JOIN (`idx_user_name`,`idx_user_id`)"
db.Clauses( hints.ForceIndex("idx_user_name", "idx_user_id").ForOrderBy(), hints.IgnoreIndex("idx_user_name").ForGroupBy(), ).Find(&User{}) // SELECT * FROM `users` FORCE INDEX FOR ORDER BY (`idx_user_name`,`idx_user_id`) IGNORE INDEX FOR GROUP BY (`idx_user_name`)"
Comment Hints
1 2 3 4 5 6 7 8 9 10 11 12 13
import "gorm.io/hints"
db.Clauses(hints.Comment("select", "master")).Find(&User{}) // SELECT /*master*/ * FROM `users`;
db.Clauses(hints.CommentBefore("insert", "node2")).Create(&user) // /*node2*/ INSERT INTO `users` ...;
db.Clauses(hints.CommentAfter("select", "node2")).Create(&user) // /*node2*/ INSERT INTO `users` ...;
db.Clauses(hints.CommentAfter("where", "hint")).Find(&User{}, "id = ?", 1) // SELECT * FROM `users` WHERE id = ? /* hint */
type User struct { gorm.Model CompanyID int Company Company `gorm:"constraint:OnUpdate:CASCADE,OnDelete:SET NULL;"` CreditCard CreditCard `gorm:"constraint:OnUpdate:CASCADE,OnDelete:SET NULL;"` }
type CreditCard struct { gorm.Model Number string UserID uint }
type Company struct { ID int Name string }
通过将多个字段设为主键，以创建复合主键，例如：
1 2 3 4 5 6
type Product struct { ID string `gorm:"primaryKey"` LanguageCode string `gorm:"primaryKey"` Code string Name string }
func cropImage(db *gorm.DB) { if db.Statement.Schema != nil { // 裁剪图片字段并上传到CDN，dummy code for _, field := range db.Statement.Schema.Fields { switch db.Statement.ReflectValue.Kind() { case reflect.Slice, reflect.Array: for i := 0; i < db.Statement.ReflectValue.Len(); i++ { // 从字段中获取数值 if fieldValue, isZero := field.ValueOf(db.Statement.ReflectValue.Index(i)); !isZero { if crop, ok := fieldValue.(CropInterface); ok { crop.Crop() } } } case reflect.Struct: // 从字段中获取数值 if fieldValue, isZero := field.ValueOf(db.Statement.ReflectValue); !isZero { if crop, ok := fieldValue.(CropInterface); ok { crop.Crop() } }