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
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"
// SELECT * FROM pizzas WHERE (pizza = 'pepperoni' AND (size = 'small' OR size = 'medium')) OR (pizza = 'hawaiian' AND size = 'xlarge')
子查询
// Where 子查询 db.Where("amount > (?)", db.Table("orders").Select("AVG(amount)")).Find(&orders)
// From 子查询 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
db.Clauses(clause.OnConflict{ Columns: []clause.Column{{Name: "id"}}, DoUpdates: clause.Assignments(map[string]interface{}{"name": "jinzhu", "age": 18}), }).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 name="jinzhu", age=18; MySQL
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
Locking
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`
Optimizer/Index/Comment Hint
import"gorm.io/hints"
// Optimizer Hints db.Clauses(hints.New("hint")).Find(&User{}) // SELECT * /*+ hint */ FROM `users`
// Index Hints db.Clauses(hints.UseIndex("idx_user_name")).Find(&User{}) // SELECT * FROM `users` USE INDEX (`idx_user_name`)
type User struct { Name string`gorm:"<-:create"`// 允许读和创建 Name string`gorm:"<-:update"`// 允许读和更新 Name string`gorm:"<-"`// 允许读和写（创建和更新） Name string`gorm:"->:false;<-:create"`// 只创建 Name string`gorm:"->"`// 只读 Name string`gorm:"-"`// 忽略 }
支持多个字段追踪 create/update 时间（ time、unix (毫/纳) 秒）
type User struct { CreatedAt time.Time // 在创建时，如果该字段值为零值，则使用当前时间填充 UpdatedAt int// 在创建时该字段值为零值或者在更新时，使用当前时间戳的秒数填充 Updated int64`gorm:"autoUpdateTime:nano"`// 使用时间戳的纳秒数填充更新时间 Updated2 int64`gorm:"autoUpdateTime:milli"`// 使用时间戳的毫秒数填充更新时间 Created int64`gorm:"autoCreateTime"`// 使用时间戳的秒数填充创建时间 }
多数据库，读写分离
GORM 通过 DB Resolver 插件提供了多数据库，读写分离支持。该插件还支持基于当前 struct 和表自动切换数据库和表，自定义负载均衡逻辑的多 source、replica
// 除了当前子句，基于 tx 的操作会运行在同一个事务中 var role Role err := tx.First(&role, "name = ?", user.Role).Error // SELECT * FROM roles WHERE name = "admin" return err }
func(user *User) BeforeUpdate(tx *gorm.DB) error { if tx.Statement.Changed("Name", "Admin") { // if Name or Admin changed tx.Statement.SetColumn("Age", 18) }
if tx.Statement.Changed() { // 如果任何字段有变动 tx.Statement.SetColumn("Age", 18) } returnnil }
db.Model(&user).Update("Name", "Jinzhu") // update field `Name` to `Jinzhu` db.Model(&user).Updates(map[string]interface{}{"name": "Jinzhu", "admin": false}) // update field `Name` to `Jinzhu`, `Admin` to false db.Model(&user).Updates(User{Name: "Jinzhu", Admin: false}) // Update none zero fields when using struct as argument, will only update `Name` to `Jinzhu`
db.Model(&user).Select("Name", "Admin").Updates(User{Name: "Jinzhu"}) // update selected fields `Name`, `Admin`，`Admin` will be updated to zero value (false) db.Model(&user).Select("Name", "Admin").Updates(map[string]interface{}{"Name": "Jinzhu"}) // update selected fields exists in the map, will only update field `Name` to `Jinzhu`
// Attention: `Changed` will only check the field value of `Update` / `Updates` equals `Model`'s field value, it returns true if not equal and the field will be saved db.Model(&User{ID: 1, Name: "jinzhu"}).Updates(map[string]interface{"name": "jinzhu2"}) // Changed("Name") => true db.Model(&User{ID: 1, Name: "jinzhu"}).Updates(map[string]interface{"name": "jinzhu"}) // Changed("Name") => false, `Name` not changed db.Model(&User{ID: 1, Name: "jinzhu"}).Select("Admin").Updates(map[string]interface{"name": "jinzhu2", "admin": false}) // Changed("Name") => false, `Name` not selected to update
