Bulk Insert

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

type YourRecord struct {
    ID        string    `gorm:"size:36;primary_key"`
	Field1    string    `gorm:"type:varchar(32);column:field1"`
	Field2    string    `gorm:"type:varchar(32);column:field2;index"`
}

func (FileFieldCopy) TableName() string {
	return "record_t"
}

func BulkCreateRecords(rs []*YourRecord) error {
	valueStrings := []string{}
	valueArgs := []interface{}{}

	for _, f := range fs {
		valueStrings = append(valueStrings, "(?, ?, ?)")

		valueArgs = append(valueArgs, f.ID)
		valueArgs = append(valueArgs, f.Field1)
		valueArgs = append(valueArgs, f.Field2)
	}

	smt := `INSERT INTO record_t(id, field1, field2) VALUES %s ON DUPLICATE KEY UPDATE field2=VALUES(field2)`

	smt = fmt.Sprintf(smt, strings.Join(valueStrings, ","))

	tx := db.Conn.Begin()
	if err := tx.Exec(smt, valueArgs...).Error; err != nil {
		tx.Rollback()
		return err
	}

	tx.Commit()
	return nil
}

Bulk Query

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
func GetRecordList(idList []string) ([]*YourRecord, error) {
	rs := []*YourRecord{}

	placeHolders := []string{}
	args := []interface{}{}
	for _, id := range idList {
		placeHolders = append(placeHolders, "?")
		args = append(args, id)
	}

	sql := `select id, field1, field2 from record_t where id in(%s)`
	sql = fmt.Sprintf(sql, strings.Join(placeHolders, ","))

	if err := db.Conn.Raw(sql, args...).Scan(&rs).Error; err != nil {
		return nil, err
	}
	return rs, nil
}