Clauses(clause.OnConflict{UpdateAll: true,}). The generated SQL is abnormal.

[dackh]

Your Question

Clauses(clause.OnConflict{UpdateAll: true,}). The generated SQL is abnormal.

code:

package main
import (
	"fmt"
	"log"

	"github.com/shopspring/decimal"
	"gorm.io/driver/mysql"
	"gorm.io/gorm"
	"gorm.io/gorm/clause"
	"gorm.io/gorm/logger"
)

/*
CREATE TABLE `test_table` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `column_a` decimal(23,6) NOT NULL DEFAULT '0.000000' COMMENT 'a',
  `column_b` decimal(23,6) NOT NULL DEFAULT '0.000000' COMMENT 'b',
  `column_c` decimal(23,6) DEFAULT NULL COMMENT 'c',
  `column_d` decimal(23,6) DEFAULT NULL COMMENT 'd',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
*/

type TestTable struct {
	//
	Id int64 `gorm:"column:id;type:bigint(20) AUTO_INCREMENT;primaryKey;precision:19;scale:0;not null;autoIncrement"`
	// a
	ColumnA decimal.Decimal `gorm:"column:column_a;type:decimal(23,6);default:0.000000;precision:23;scale:6;not null"`
	// b
	ColumnB decimal.Decimal `gorm:"column:column_b;type:decimal(23,6);default:0.000000;precision:23;scale:6;not null"`
	// c
	ColumnC decimal.NullDecimal `gorm:"column:column_c;type:decimal(23,6);precision:23;scale:6"`
	// d
	ColumnD decimal.NullDecimal `gorm:"column:column_d;type:decimal(23,6);precision:23;scale:6"`
}

func main() {
	// 数据库连接配置
	dsn := "username:password@tcp(127.0.0.1:3306)/test_db?charset=utf8mb4&parseTime=True&loc=Local"
	db, err := gorm.Open(mysql.Open(dsn), &gorm.Config{
		Logger: logger.Default.LogMode(logger.Info),
	})
	if err != nil {
		log.Fatalf("Failed to connect to database: %v", err)
	}

	err = db.AutoMigrate(&TestTable{})
	if err != nil {
		log.Fatalf("Failed to auto migrate: %v", err)
	}

	products := []TestTable{
		{Id: 1, ColumnA: decimal.NewFromFloat(19.99),
			ColumnB: decimal.NewFromFloat(19.99),
			ColumnC: decimal.NullDecimal{Decimal: decimal.NewFromFloat(19.99), Valid: true},
			ColumnD: decimal.NullDecimal{Decimal: decimal.NewFromFloat(19.99), Valid: true},
		},
	}

	result := db.Table("test_table").Clauses(clause.OnConflict{
		UpdateAll: true,
	}).Create(&products)

	if result.Error != nil {
		log.Fatalf("Failed to upsert products: %v", result.Error)
	}

	fmt.Println("Upserted products successfully!")
}

The document you expected this should be explained

Expected answer

The fields column_a and column_b are missing after ON DUPLICATE KEY UPDATE.

[3.089ms] [rows:1] INSERT INTO `test_table` (`column_c`,`column_d`,`column_b`,`id`,`column_a`) VALUES ('19.99','19.99','19.99',1,'19.99') ON DUPLICATE KEY UPDATE `column_c`=VALUES(`column_c`),`column_d`=VALUES(`column_d`)
Upserted products successfully!

[dackh]

github.com/shopspring/decimal v1.4.0
gorm.io/driver/mysql v1.5.7
gorm.io/gorm v1.25.5

go version: 1.22.5

[dackh]

I tried it with several colleagues here. The results of me and one of my colleagues were missing, but the execution results of the other two colleagues were not missing. They were the same gorm and mysql versions.

So I want to know why？

[noahchiu22]

Maybe you have to tell clause.OnConflict which is PK

Try this:

result := db.Table("test_table").Clauses(clause.OnConflict{
	Columns: []clause.Column{{Name: "id"}},
	UpdateAll: true,
}).Create(&products)

Or you want to use your PK to upsert

In your case:

result := db.Table("test_table").Clauses(clause.OnConflict{
	OnConstraint: "test_table_pkey",
	UpdateAll: true,
}).Create(&products)