聊聊gorm的OnConflict
OnConflict
clause/on_conflict.go
type OnConflict struct {
Columns []Column
Where Where
OnConstraint string
DoNothing bool
DoUpdates Set
UpdateAll bool
}
func (OnConflict) Name() string {
return "ON CONFLICT"
}
// Build build onConflict clausefunc (onConflict OnConflict) Build(builder Builder) {
if len(onConflict.Columns) > 0 {
builder.WriteByte('(')
for idx, column := range onConflict.Columns {
if idx > 0 {
builder.WriteByte(',')
}
builder.WriteQuoted(column)
}
builder.WriteString(`) `)
}
if len(onConflict.Where.Exprs) > 0 {
builder.网站监控WriteString("WHERE ")
onConflict.Where.Build(builder)
builder.WriteByte(' ')
}
if onConflict.OnConstraint != "" {
builder.WriteString("ON CONSTRAINT ")
builder.WriteString(onConflict.OnConstraint)
builder.WriteByte(' ')
}
if onConflict.DoNothing {
builder.WriteString("DO NOTHING")
} else {
builder.WriteString("DO UPDATE SET ")
onConflict.DoUpdates.Build(builder)
}
}
// MergeClause merge onConflict clausesfunc (onConflict OnConflict) MergeClause(clause *Clause) {
clause.Expression = onConflict
}
OnConflict定义了Columns、Where、OnConstraint、DoNothing、DoUpdates、UpdateAll属性；Build方法会根据这些属性拼装sql，如果是DoNothing则追加DO NOTHING，否则追加DO UPDATE SET
Expression
clause/set.go
type Set []Assignment
type Assignment struct {
Column Column
Value interface{}
}
func (set Set) Name() string {
return "SET"
}
func (set Set) Build(builder Builder) {
if len(set) > 0 {
for idx, assignment := range set {
if idx > 0 {
builder.WriteByte(',')
}
builder.WriteQuoted(assignment.Column)
builder.WriteByte('=')
builder.AddVar(builder, assignment.Value)
}
} else {
builder.WriteQuoted(PrimaryColumn)
builder.WriteByte('=')
builder.WriteQuoted(PrimaryColumn)
}
}
// MergeClause merge assignments clauses
func (set Set) MergeClause(clause *Clause) {
copiedAssignments := make([]Assignment, len(set))
copy(copiedAssignments, set)
clause.Expression = Set(copiedAssignments)
}
func Assignments(values map[string]interface{}) Set {
keys := make([]string, 0, len(values))
for key := range values {
keys = append(keys, key)
}
sort.Strings(keys)
assignments := make([]Assignment, len(keys))
for idx, key := range keys {
assignments[idx] = Assignment{Column: Column{Name: key}, Value: values[key]}
}
return assignments
}
func AssignmentColumns(values []string) Set {
assignments := make([]Assignment, len(values))
for idx, value := range values {
assignments[idx] = Assignment{Column: Column{Name: value}, Value: Column{Table: "excluded", Name: value}}
}
return assignments
}
的DoUpdates属性是Set类型，Set类型实际是Assignment数组；其Build方法会组装assignment的sql
实例
func onConflictDemo(db *gorm.DB) {
entities := []DemoEntity{
{
Model: gorm.Model{ID: 1},
Name: "coco",
},
{
Model: gorm.Model{ID: 2},
Name: "bear",
},
}
result := db.Debug().Create(&entities)
b, _ := json.Marshal(entities)
log.Println("data:", string(b))
log.Println("result.RowsAffected:", result.RowsAffected, "result.Error:", result.Error)
if err := db.Debug().Clauses(clause.OnConflict{DoNothing: true}).Create(&entities).Error; err != nil {
panic(err)
}
}
输出
2021/01/17 20:03:31 /demo.go:53 UNIQUE constraint failed: demo_entities.id
[0.487ms] [rows:0] INSERT INTO `demo_entities` (`created_at`,`updated_at`,`deleted_at`,`name`,`id`) VALUES ("2021-01-17 20:03:31.711","2021-01-17 20:03:31.711",NULL,"coco",1),("2021-01-17 20:03:31.711","2021-01-17 20:03:31.711",NULL,"bear",2)2021/01/17 20:03:31 data: [{"ID":1,"CreatedAt":"2021-01-17T20:03:31.71143+08:00","UpdatedAt":"2021-01-17T20:03:31.71143+08:00","DeletedAt":null,"Name":"coco"},{"ID":2,"CreatedAt":"2021-01-17T20:03:31.71143+08:00","UpdatedAt":"2021-01-17T20:03:31.71143+08:00","DeletedAt":null,"Name":"bear"}]2021/01/17 20:03:31 result.RowsAffected: 0 result.Error: UNIQUE constraint failed: demo_entities.id
2021/01/17 20:03:31 /demo.go:58
[0.123ms] [rows:0] INSERT INTO `demo_entities` (`created_at`,`updated_at`,`deleted_at`,`name`,`id`) VALUES ("2021-01-17 20:03:31.711","2021-01-17 20:03:31.711",NULL,"coco",1),("2021-01-17 20:03:31.711","2021-01-17 20:03:31.711",NULL,"bear",2) ON CONFLICT DO NOTHING
小结
gorm的OnConflict定义了Columns、Where、OnConstraint、DoNothing、DoUpdates、UpdateAll属性；Build方法会根据这些属性拼装sql，如果是DoNothing则追加DO NOTHING，否则追加DO UPDATE SET。