Description

the same question： https://github.com/go-gorm/gorm/issues/5754 https://github.com/go-gorm/gorm/issues/3810

My experimental environment is as follows： gorm version 1.24.5 mysql 5.7 go 1.19.1

At the same time, I wrote a simple CreateInBatches OnConflict function. When writing data, if the email has repeated updates to the UpdatedAt field.

type User struct {
    ID         uint64
    Email      string `gorm:"uniqueIndex:idx_email;type:varchar(256);"`
    UpdatedAt  time.Time
    CreatedAt  time.Time
}

func (u *user) Create(ctx context.Context, models []*model.User) error {
    return u.db.WithContext(ctx).Clauses(clause.OnConflict{
        Columns:   []clause.Column{{Name: "email"}},
        DoUpdates: clause.AssignmentColumns([]string{"updated_at"}),
    }).CreateInBatches(models, 100).Error
}

Assume that the current user table has the following data | ID | Emaul | UpdatedAt|CreatedAt| | :-----| ----: | :----: |:----: | | 1 | test@test.com| 2023-02-11 17:50:51.243 |2023-02-11 17:50:51.243 | | 2 | apple@apple.com | 2023-02-11 17:50:51.243 |2023-02-11 17:50:51.243 |

Now I need to insert two data, namely test@test.com and apple@apple.com. I want to update the UpdatedAt field.

gorm does the following.

INSERT INTO `users` (`email`, `created_at`, `updated_at`)
VALUES ('est@test.com', '2023-02-12 01:55:44.966', '2023-02-12 01:55:44.966'),
       ('test-mail2', '2023-02-12 01:55:44.966', '2023-02-12 01:55:44.966')
ON DUPLICATE KEY UPDATE `updated_at`=VALUES(`updated_at`)

The user id I got was wrong, and the id started from 2, which is incredible.

Sincerely hope you can help me solve this problem, thank you. @jinzhu

Comment From: a631807682

Since mysql does not support returning, we actually have no way to get the correct id when using OnConflict. Maybe in this case we can't change the value of id, because it may cause more errors.

https://github.com/go-gorm/gorm/blob/master/callbacks/create.go#L108

Comment From: black-06

Some docs I searched, I'm not too optimistic for MySQL. - https://bugs.mysql.com/bug.php?id=83139 - https://github.com/jOOQ/jOOQ/issues/6865

Comment From: a631807682

The problem also exists in postgres, refer to https://github.com/go-gorm/gorm/issues/6360#issuecomment-1569990434

Comment From: diligiant

and in sqlite (#6360 covers mysql, postgres and sqlite).

Comment From: CaiJinKen

Description

the same question： #5754 #3810

My experimental environment is as follows： gorm version 1.24.5 mysql 5.7 go 1.19.1

At the same time, I wrote a simple CreateInBatches OnConflict function. When writing data, if the email has repeated updates to the UpdatedAt field.

``go type User struct { ID uint64 Email stringgorm:"uniqueIndex:idx_email;type:varchar(256);"` UpdatedAt time.Time CreatedAt time.Time }

func (u user) Create(ctx context.Context, models []model.User) error { return u.db.WithContext(ctx).Clauses(clause.OnConflict{ Columns: []clause.Column{{Name: "email"}}, DoUpdates: clause.AssignmentColumns([]string{"updated_at"}), }).CreateInBatches(models, 100).Error } ```

Assume that the current user table has the following data

ID Emaul UpdatedAt CreatedAt 1 test@test.com 2023-02-11 17:50:51.243 2023-02-11 17:50:51.243 2 apple@apple.com 2023-02-11 17:50:51.243 2023-02-11 17:50:51.243 Now I need to insert two data, namely test@test.com and apple@apple.com. I want to update the UpdatedAt field.

gorm does the following.

sql INSERT INTO `users` (`email`, `created_at`, `updated_at`) VALUES ('est@test.com', '2023-02-12 01:55:44.966', '2023-02-12 01:55:44.966'), ('test-mail2', '2023-02-12 01:55:44.966', '2023-02-12 01:55:44.966') ON DUPLICATE KEY UPDATE `updated_at`=VALUES(`updated_at`)

The user id I got was wrong, and the id started from 2, which is incredible.

Sincerely hope you can help me solve this problem, thank you. @jinzhu

You can do with: replace Create to CreateInBatches and set batchSize = 1