在gorm中执行批量更新操作

本篇文章向大家介绍《在gorm中执行批量更新操作》，主要包括，具有一定的参考价值，需要的朋友可以参考一下。

我有一个需求，更新bulk中的数据，而这个数据中的一些字段经过二次处理后，变成了不同的。比如

[{"id":83,"ip":"10.215.14.216","test1":24,"test2":126,"test3":300},
{"id":82,"ip":"10.215.14.215","test1":6,"test2":100,"test3":600}
...]

官方gorm批量更新是将部分字段改为相同的值。

最简单的方法是使用loop一次更新一个，不过个人感觉in高效。

for _,v := range bulkdata{
  // update single
}

有没有批量更新的方法来提高效率？我真的很感谢任何对此的帮助。

正确答案

1. 当 类似时尝试使用 case

update table_name
set ip = case id
    when 83 then '10.215.14.216'
    when 82 then '10.215.14.215'
end, test1 = case id
    when 83 then 24,
    when 82 then 6
end
where id in (83, 82)

package main

import (
    "bytes"
    "fmt"
    "math"
    "reflect"
    "strconv"
    "strings"
)

func genBatchUpdateSQL(tableName string, dataList interface{}) ([]string, error) {
    fieldValue := reflect.ValueOf(dataList)
    fieldType := reflect.TypeOf(dataList).Elem().Elem()
    sliceLength := fieldValue.Len()
    fieldNum := fieldType.NumField()
     
    // TODO add validate struct. struct must have primaryKey and gorm tag

    var IDList []string
    updateMap := make(map[string][]string)
    for i := 0; i < sliceLength; i++ {
        structValue := fieldValue.Index(i).Elem()
        for j := 0; j < fieldNum; j++ {
            elem := structValue.Field(j)

            var tid string
            switch elem.Kind() {
            case reflect.Int64:
                tid = strconv.FormatInt(elem.Int(), 10)
            case reflect.String:
                if strings.Contains(elem.String(), "'") {
                    tid = fmt.Sprintf("'%v'", strings.ReplaceAll(elem.String(), "'", "\\'"))
                } else {
                    tid = fmt.Sprintf("'%v'", elem.String())
                }
            case reflect.Float64:
                tid = strconv.FormatFloat(elem.Float(), 'f', -1, 64)
            case reflect.Bool:
                tid = strconv.FormatBool(elem.Bool())
            default:
                return nil, fmt.Errorf("type conversion error, param is %v", fieldType.Field(j).Tag.Get("json"))
            }

            gormTag := fieldType.Field(j).Tag.Get("gorm")
            fieldTag := getFieldName(gormTag)

            if strings.HasPrefix(fieldTag, "id;") {
                id, err := strconv.ParseInt(tid, 10, 64)
                if err != nil {
                    return nil, err
                }

                if id < 1 {
                    return nil, fmt.Errorf("this structure should have a primary key and gt 0")
                }
                IDList = append(IDList, tid)
                continue
            }

            valueList := append(updateMap[fieldTag], tid)
            updateMap[fieldTag] = valueList
        }
    }

    length := len(IDList)
    // Length of each batch submission
    size := 200
    SQLQuantity := getSQLQuantity(length, size)
    var SQLArray []string
    k := 0

    for i := 0; i < SQLQuantity; i++ {
        count := 0
        var record bytes.Buffer
        record.WriteString("UPDATE " + tableName + " SET ")

        for fieldName, fieldValueList := range updateMap {
            record.WriteString(fieldName)
            record.WriteString(" = CASE " + "id")

            for j := k; j < len(IDList) && j < len(fieldValueList) && j < size+k; j++ {
                record.WriteString(" WHEN " + IDList[j] + " THEN " + fieldValueList[j])
            }

            count++
            if count != fieldNum-1 {
                record.WriteString(" END, ")
            }
        }

        record.WriteString(" END WHERE ")
        record.WriteString("id" + " IN (")
        min := size + k
        if len(IDList) < min {
            min = len(IDList)
        }
        record.WriteString(strings.Join(IDList[k:min], ","))
        record.WriteString(");")

        k += size
        SQLArray = append(SQLArray, record.String())
    }
    return SQLArray, nil
}

func getSQLQuantity(length, size int) int {
    return int(math.Ceil(float64(length) / float64(size)))
}

func getFieldName(fieldTag string) string {
    fieldTagArr := strings.Split(fieldTag, ":")
    if len(fieldTagArr) == 0 {
        return ""
    }

    return fieldTagArr[len(fieldTagArr)-1]
}


type Ts struct {
    ID    int64   `gorm:"column:id;primaryKey" json:"id"`
    IP    string  `gorm:"column:ip" json:"ip"`
    Test1 float64 `gorm:"column:test1" json:"test1"`
}

func main() {
    var t []*Ts
    demo1 := &Ts{1, "11.215.14.216", 11.0}
    demo2 := &Ts{2, "12.215.14.216", 12.0}
    demo3 := &Ts{3, "13.215.14.216", 13.0}
    demo4 := &Ts{4, "14.215.14.216", 14.0}
    demo5 := &Ts{5, "15.215.14.216", 15.0}

    t = append(t, demo1, demo2, demo3, demo4, demo5)
    res, err := genBatchUpdateSQL("table_name", t)
    if err != nil {
        return
    }
    fmt.Println(res)
    // [UPDATE table_name SET ip = CASE id WHEN 1 THEN 'Test001' WHEN 2 THEN 'Test002' WHEN 3 THEN 'Test003' WHEN 4 THEN 'Test004' WHEN 5 THEN 'Test005' END, test1 = CASE id WHEN 1 THEN 11 WHEN 2 THEN 12 WHEN 3 THEN 13 WHEN 4 THEN 14 WHEN 5 THEN 15 END WHERE id IN (1,2,3,4,5);]
}

1. 现在您可以使用原始 sql 进行批量更新

好了，本文到此结束，带大家了解了《在gorm中执行批量更新操作》，希望本文对你有所帮助！关注golang学习网公众号，给大家分享更多Golang知识！