Talk about gorm’s OnConflict

sequence

This paper mainly studies OnConflict of GORM

OnConflict

Gorm. IO/[email protected] / 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 clause func (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 clauses func (onConflict OnConflict) MergeClause(clause *Clause) { clause.Expression = onConflict }Copy the code

OnConflict defines Columns, Where, OnConstraint, DoNothing, DoUpdates, UpdateAll. The Build method assembles the SQL from these properties, appending DO NOTHING if it is DoNothing and DO UPDATE SET otherwise

Expression

Gorm. IO/[email protected] / 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
}
Copy the code

The DoUpdates attribute is Set, which is actually Assignment array. The Build method assembles the ASSIGNMENT’s SQL

The instance

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) } }Copy the code

The output

2021/01/17 20:03:31 /demo.go:53 UNIQUE constraint failed: Id [rows:0] INSERT INTO 'entities' (' created_at', 'updated_at', 'deleted_at', 'name', 'id') VALUES (" 2021-01-17 20:03:31. 711 ", "the 2021-01-17 20:03:31. 711", NULL, "coco", 1), (" the 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 ":" the 2021-01-17 T20:03:31. 71143 + 08:00 ", "UpdatedAt" : "the 2021-01-17 T20:03:31. 71143 + 08:00", "DeletedAt:" null, "N Ame ":" coco "}, {" ID ": 2," CreatedAt ":" the 2021-01-17 T20:03:31. 71143 + 08:00 ", "UpdatedAt" : "the 2021-01-17 T20:03:31. 71143 + 08:00", "Delete dAt":null,"Name":"bear"}] 2021/01/17 20:03:31 result.RowsAffected: 0 result.Error: UNIQUE constraint failed: Id 2021/01/17 20:03:31 /demo.go:58 [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 NOTHINGCopy the code

summary

Gorm OnConflict defines Columns, Where, OnConstraint, DoNothing, DoUpdates, and UpdateAll. The Build method assembles the SQL from these properties, appending DO NOTHING if it is DoNothing and DO UPDATE SET otherwise.

doc

• gorm