Gorm 批量从创建的时候的数据越界问题

在批量创建的时候会报数组越界的错误，但是我不知道为什么会这样。

我的想法是写这样一个sql：

INSERT INTO tag (`name`, num) VALUES ('奇幻', 1), ('杂志', 1), ('男同', 1) ON DUPLICATE KEY UPDATE num=num+1;

源码是这样的：

        for _, tag := range ani.Tags {
            ani_tag := model.Tag{Name: tag}
            tags = append(tags, ani_tag)
        }

        batchLength := len(tags)
        if tagErr := utils.Db.Clauses(clause.OnConflict{
            Columns:   []clause.Column{{Name: "name"}},
            DoUpdates: clause.Assignments(map[string]interface{}{"num": "VALUES(num)+1"}),
        }).CreateInBatches(&tags, batchLength).Error; tagErr != nil {
            return utils.DbError(tagErr.Error())
        }

但是当我运行上面的代码的时候就会下面这个错误
reflect: Field index out of range
[/usr/local/Cellar/go/1.18/libexec/src/reflect/value.go:1224]() (0x10ba6a6)
    Value.Field: panic("reflect: Field index out of range")
[/Users/black/go/pkg/mod/gorm.io/gorm]()@v1.22.4[/schema/field.go:413]() (0x17bfe24)
    (*Field).setupValuerAndSetter.func1: fieldValue := reflect.Indirect(value).Field(field.StructField.Index[0])
[/Users/black/go/pkg/mod/gorm.io/gorm]()@v1.22.4[/callbacks/create.go:222]() (0x185b2ad)
    ConvertToCreateValues: if values.Values[i][idx], isZero = field.ValueOf(rv); isZero {
[/Users/black/go/pkg/mod/gorm.io/gorm]()@v1.22.4[/callbacks/create.go:63]() (0x1856ae9)
    Create.func1: db.Statement.AddClause(ConvertToCreateValues(db.Statement))
[/Users/black/go/pkg/mod/gorm.io/gorm]()@v1.22.4[/callbacks.go:130]() (0x17e0af2)
    (*processor).Execute: f(db)
[/Users/black/go/pkg/mod/gorm.io/gorm]()@v1.22.4[/finisher_api.go:47]() (0x17e8b93)
    (*DB).CreateInBatches.func1: subtx.callbacks.Create().Execute(subtx)
[/Users/black/go/pkg/mod/gorm.io/gorm]()@v1.22.4[/finisher_api.go:548]() (0x17efbdd)
    (*DB).Transaction: err = fc(tx)
[/Users/black/go/pkg/mod/gorm.io/gorm]()@v1.22.4[/finisher_api.go:59]() (0x17e88de)
    (*DB).CreateInBatches: tx.AddError(tx.Transaction(callFc))
[/Users/black/Desktop/Person/go/bbc_dark/routers/apis/v1/upload.go:74]() (0x188020d)
    create_ani: }).CreateInBatches(&tags, batchLength).Error; tagErr != nil {
[/Users/black/Desktop/Person/go/bbc_dark/routers/apis/v1/upload.go:29]() (0x187fab7)
    Upload_animation: if create_err := create_ani(ani); create_err == nil {
[/Users/black/go/pkg/mod/github.com/gin-gonic/gin]()@v1.7.7[/context.go:168]() (0x17497fc)
    (*Context).Next: c.handlers[c.index](c)
[/Users/black/go/pkg/mod/github.com/gin-gonic/gin]()@v1.7.7[/recovery.go:99]() (0x175bf17)
    CustomRecoveryWithWriter.func1: c.Next()
[/Users/black/go/pkg/mod/github.com/gin-gonic/gin]()@v1.7.7[/context.go:168]() (0x17497fc)
    (*Context).Next: c.handlers[c.index](c)
[/Users/black/go/pkg/mod/github.com/gin-gonic/gin]()@v1.7.7[/logger.go:241]() (0x175a911)
    LoggerWithConfig.func1: c.Next()
[/Users/black/go/pkg/mod/github.com/gin-gonic/gin]()@v1.7.7[/context.go:168]() (0x17497fc)
    (*Context).Next: c.handlers[c.index](c)
[/Users/black/go/pkg/mod/github.com/gin-gonic/gin]()@v1.7.7[/gin.go:555]() (0x1758568)
    (*Engine).handleHTTPRequest: c.Next()
[/Users/black/go/pkg/mod/github.com/gin-gonic/gin]()@v1.7.7[/gin.go:511]() (0x175803a)
    (*Engine).ServeHTTP: engine.handleHTTPRequest(c)
[/usr/local/Cellar/go/1.18/libexec/src/net/http/server.go:2916]() (0x14571f3)
    serverHandler.ServeHTTP: handler.ServeHTTP(rw, req)
[/usr/local/Cellar/go/1.18/libexec/src/net/http/server.go:1966]() (0x1450cfb)
    (*conn).serve: serverHandler{c.server}.ServeHTTP(w, w.req)
[/usr/local/Cellar/go/1.18/libexec/src/runtime/asm_amd64.s:1571]() (0x106b200)
    goexit: BYTE    $0x90   // NOP

但是如果我把

.CreateInBatches(&tags, batchLength)

换成

.Create(&tag)

这里就不会有问题

我想知道这是为什么，是我的写法有什么错误的地方吗？

Comment From: github-actions[bot]

The issue has been automatically marked as stale as it missing playground pull request link, which is important to help others understand your issue effectively and make sure the issue hasn't been fixed on latest master, checkout https://github.com/go-gorm/playground for details. it will be closed in 30 days if no further activity occurs. if you are asking question, please use the Question template, most likely your question already answered https://github.com/go-gorm/gorm/issues or described in the document https://gorm.io ✨ Search Before Asking ✨

Comment From: Heliner

see the docs https://gorm.io/docs/create.html#Batch-Insert ,

var users = []User{{Name: "jinzhu_1"}, ...., {Name: "jinzhu_10000"}}

// batch size 100
db.CreateInBatches(users, 100)

Comment From: github-actions[bot]

Comment From: BlackCatHehe

@Heliner 感谢回复我的提问。但是我在实际编程中确实遇到了让我非常迷惑的问题。我想用gorm的playground，但是我是新手，用点儿用不明白。我新建了一个简单的demo，代码非常少

package main

import (
    "github.com/gin-gonic/gin"
    "gorm.io/driver/mysql"
    "gorm.io/gorm"
    "gorm.io/gorm/clause"
)

type Animation struct {
    Id   int64  `gorm:"column:id;primary_key;AUTO_INCREMENT" json:"id"`
    Name string `gorm:"column:name;not null;unique" json:"name"`
}

type Tag struct {
    Id      int64  `gorm:"column:id;primary_key;AUTO_INCREMENT" json:"id"`
    TagName string `gorm:"column:tag_name;unique;not null" json:"tag_name"`
    Num     int64  `gorm:"column:num;default:0" json:"num"`
}

type InputAnimation struct {
    Name string   `json:"title" binding:"required"`
    Tags []string `json:"tags" binding:"required"`
}

func main() {
    dsn := "root:123456@tcp(localhost:3337)/sql_test?charset=utf8&parseTime=True&loc=Local"
    db, err := gorm.Open(mysql.New(mysql.Config{DSN: dsn}), &gorm.Config{})
    if err != nil {
        panic(err)
    }
    db = db.Set("gorm:table_options", "ENGINE=InnoDB CHARSET=utf8")

    db.AutoMigrate(&Animation{}, &Tag{})
    r := gin.Default()
    r.POST("/api/v1/upload", func(c *gin.Context) {
        params := InputAnimation{}
        c.ShouldBind(&params)

        ani := Animation{Name: params.Name}
        db.Clauses(clause.OnConflict{
            DoNothing: true,
        }).Create(&ani)

        tags := []Tag{}
        for _, tag := range params.Tags {
            tags = append(tags, Tag{TagName: tag, Num: 1})
        }

        db.Clauses(clause.OnConflict{
                         Columns:   []clause.Column{{Name: "tag_name"}},
            DoUpdates: clause.Assignments(map[string]interface{}{"num": gorm.Expr("num+1")}),
        }).Create(&tags)
    })
    r.Run(":3308")

}

上面就是我所有的代码。我在使用的时候仍然会遇到下边这样的问题。

Error 1054: Unknown column 'num' in 'field list'
[4.985ms] [rows:1] INSERT INTO `animations` (`name`) VALUES ('奇幻'),('冒险') ON DUPLICATE KEY UPDATE `num`=num+1
[GIN] 2022[/03/30]() - 14:11:06 | 200 |   14.460623ms |       127.0.0.1 | POST     "[/api/v1/upload]()"

我不明白为什么会生成这样的sql语句。下面的语句明明是创建tag的时候冲突才会执行，但是它似乎也被用在了创建animations上面。

db.Clauses(clause.OnConflict{
                         Columns:   []clause.Column{{Name: "tag_name"}},
            DoUpdates: clause.Assignments(map[string]interface{}{"num": gorm.Expr("num+1")}),
        }).Create(&tags)

这个问题已经困扰了我好几天的时间，我一直在尝试，文档也一直在看，但都没有得到很好的解决。如果你们能给我提供帮助的话，非常感谢。

Comment From: BlackCatHehe

另外我也进行了别的尝试,我在执行数据库操作时，在前面添加了对应的表的名字Table("animations")，就像下面这样。

ani := Animation{Name: params.Name}
db.Table("animations").Clauses(clause.OnConflict{
    DoNothing: true,
}).Create(&ani)

tags := []Tag{}
for _, tag := range params.Tags {
    tags = append(tags, Tag{TagName: tag, Num: 1})
}

db.Table("tags").Clauses(clause.OnConflict{
    Columns:   []clause.Column{{Name: "tag_name"}},
    DoUpdates: clause.Assignments(map[string]interface{}{"num": gorm.Expr("num+1")}),
}).Create(&tags)

这样似乎不会有上面的问题，但是仍然会像下面这样

Error 1054: Unknown column 'name' in 'field list'
[4.379ms] [rows:1] INSERT INTO `tags` (`name`) VALUES ('奇幻'),('冒险') ON DUPLICATE KEY UPDATE `num`=num+1
[GIN] 2022[/03/30]() - 14:23:48 | 200 |   11.328406ms |       127.0.0.1 | POST     "[/api/v1/upload]()"

看起来是插入了错误的field，这里应该是

INSERT INTO `tags` (`tag_name`) VALUES ('奇幻'),('冒险') ON DUPLICATE KEY UPDATE `num`=num+1

Comment From: BlackCatHehe

这个是没有gin的版本

func main() {
    dsn := "root:123456@tcp(localhost:3337)/sql_test?charset=utf8&parseTime=True&loc=Local"
    db, err := gorm.Open(mysql.New(mysql.Config{DSN: dsn}), &gorm.Config{})
    if err != nil {
        panic(err)
    }
    db = db.Set("gorm:table_options", "ENGINE=InnoDB CHARSET=utf8")

    db.AutoMigrate(&Animation{}, &Tag{})

    input := InputAnimation{
        Name: "hello",
        Tags: []string{"奇幻", "科幻"},
    }

    ani := Animation{Name: input.Name}
    db.Table("animations").Clauses(clause.OnConflict{
        DoNothing: true,
    }).Create(&ani)

    tags := []Tag{}
    for _, tag := range input.Tags {
        tags = append(tags, Tag{TagName: tag, Num: 1})
    }

    db.Table("tags").Clauses(clause.OnConflict{
        Columns:   []clause.Column{{Name: "tag_name"}},
        DoUpdates: clause.Assignments(map[string]interface{}{"num": gorm.Expr("num+1")}),
    }).Create(&tags)
}

Comment From: github-actions[bot]

Comment From: a631807682

// wrong
db = db.Set("gorm:table_options", "ENGINE=InnoDB CHARSET=utf8")
// right
db.Set("gorm:table_options", "ENGINE=InnoDB CHARSET=utf8")

Can not replace db to db.Set as this issues said. #5182 @jinzhu Seems we need to distinguish between internal and external users Set api Because if user replace gobal db to db.Set, the Statement will be change by other finisher api. This is very difficult to troubleshoot for users unfamiliar with the source code, so I think the API provided to users should be, and we use other internal api like localSet.

func (db *DB) Set(key string, value interface{}) {
    tx := db.getInstance()
    tx.Statement.Settings.Store(key, value)
}

Comment From: BlackCatHehe

@a631807682 非常感谢！！我修改了之后果然可以正常运行了。😭😭，这个问题困扰了我好几天。我之前每次进行数据库操作时，都重新指定Table和Model = nil,这样似乎也能正常工作了。但是总觉的哪里不太对劲儿。

if err := utils.Db.
        Table("animations").
        Model(nil).
        Clauses(
            clause.OnConflict{
                UpdateAll: true,
            }).
        Create(&animation).Error; err != nil {
        return utils.DbError(err.Error())
    }

总之多谢了,解开了心中的疑惑。

Comment From: github-actions[bot]

Comment From: likebeta

@Heliner 感谢回复我的提问。但是我在实际编程中确实遇到了让我非常迷惑的问题。我想用gorm的playground，但是我是新手，用点儿用不明白。我新建了一个简单的demo，代码非常少

```go package main

import ( "github.com/gin-gonic/gin" "gorm.io/driver/mysql" "gorm.io/gorm" "gorm.io/gorm/clause" )

type Animation struct { Id int64 gorm:"column:id;primary_key;AUTO_INCREMENT" json:"id" Name string gorm:"column:name;not null;unique" json:"name" }

type Tag struct { Id int64 gorm:"column:id;primary_key;AUTO_INCREMENT" json:"id" TagName string gorm:"column:tag_name;unique;not null" json:"tag_name" Num int64 gorm:"column:num;default:0" json:"num" }

type InputAnimation struct { Name string json:"title" binding:"required" Tags []string json:"tags" binding:"required" }

func main() { dsn := "root:123456@tcp(localhost:3337)/sql_test?charset=utf8&parseTime=True&loc=Local" db, err := gorm.Open(mysql.New(mysql.Config{DSN: dsn}), &gorm.Config{}) if err != nil { panic(err) } db = db.Set("gorm:table_options", "ENGINE=InnoDB CHARSET=utf8")

db.AutoMigrate(&Animation{}, &Tag{}) r := gin.Default() r.POST("/api/v1/upload", func(c *gin.Context) { params := InputAnimation{} c.ShouldBind(&params)
  ani := Animation{Name: params.Name}
  db.Clauses(clause.OnConflict{
      DoNothing: true,
  }).Create(&ani)

  tags := []Tag{}
  for _, tag := range params.Tags {
      tags = append(tags, Tag{TagName: tag, Num: 1})
  }

  db.Clauses(clause.OnConflict{
                     Columns:   []clause.Column{{Name: "tag_name"}},
      DoUpdates: clause.Assignments(map[string]interface{}{"num": gorm.Expr("num+1")}),
  }).Create(&tags)
}) r.Run(":3308")

} ```

上面就是我所有的代码。我在使用的时候仍然会遇到下边这样的问题。

go Error 1054: Unknown column 'num' in 'field list' [4.985ms] [rows:1] INSERT INTO `animations` (`name`) VALUES ('奇幻'),('冒险') ON DUPLICATE KEY UPDATE `num`=num+1 [GIN] 2022[/03/30]() - 14:11:06 | 200 | 14.460623ms | 127.0.0.1 | POST "[/api/v1/upload]()"

我不明白为什么会生成这样的sql语句。下面的语句明明是创建tag的时候冲突才会执行，但是它似乎也被用在了创建animations上面。

go db.Clauses(clause.OnConflict{ Columns: []clause.Column{{Name: "tag_name"}}, DoUpdates: clause.Assignments(map[string]interface{}{"num": gorm.Expr("num+1")}), }).Create(&tags)

这个问题已经困扰了我好几天的时间，我一直在尝试，文档也一直在看，但都没有得到很好的解决。如果你们能给我提供帮助的话，非常感谢。

我也是相同的问题，

go // wrong db = db.Set("gorm:table_options", "ENGINE=InnoDB CHARSET=utf8") // right db.Set("gorm:table_options", "ENGINE=InnoDB CHARSET=utf8")

Can not replace db to db.Set as this issues said. #5182 @jinzhu Seems we need to distinguish between internal and external users Set api Because if user replace gobal db to db.Set, the Statement will be change by other finisher api. This is very difficult to troubleshoot for users unfamiliar with the source code, so I think the API provided to users should be, and we use other internal api like localSet.

go func (db *DB) Set(key string, value interface{}) { tx := db.getInstance() tx.Statement.Settings.Store(key, value) }

@BlackCatHehe 我也遇到了相同的问题，并且如果加上debug测试也没问题，去掉Debug有时候报错reflect: Field index out of range, 有时候是因为表名解析不对导致插入问题，这两天搞得很崩溃

db = db.Set("gorm:table_options", "ENGINE=InnoDB CHARSET=utf8").Debug()