关于golang:聊聊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