聊聊gorm的OnConflict

序

本文主要研究一下gorm的OnConflict

OnConflict

gorm.io/gorm@v1.20.11/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
}

OnConflict定义了Columns、Where、OnConstraint、DoNothing、DoUpdates、UpdateAll这些属性。Build方法会依据这些属性来拼装sql，如果是DoNothing就追加DO NOTHING，反之则追加DO UPDATE SET

Expression

gorm.io/gorm@v1.20.11/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。

doc

• gorm