By default, we create a mysql table structure with a primary key ID that increments. After creating a record, we use that primary key ID to associate other businesses.

The table structure is as follows:

CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL.`age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`))ENGINE=InnoDB AUTO_INCREMENT=1;
Copy the code

Gorm provides the default Create method to retrieve data from previous objects after creating a piece of data, such as:

type Tests struct {
	Id   int64  `json:"id"`
	Name string `json:"name"`
	Age  int64  `json:"age"`
}

mbr := Tests{
	Name: "111",
	Age:  10,
}
err = db.Table("test").Create(&mbr).Error
Copy the code

After creation, you can get the increment number from mbr.id.

However, there is an awkward aspect to this approach. What if the Age or Name field is empty?

The following code:

type Tests struct {
	Id   int64  `json:"id"`
	Name string `json:"name"`
	Age  int64  `json:"age"`
}

mbr := Tests{
	Name: "111",
}
err = db.Table("test").Create(&mbr).Error
Copy the code

The following SQL statement is executed, which sets age to a default value of 0, but the database default value is NULL, and 0 can make business sense.

INSERT  INTO `test` (`name`,`age`) VALUES ('111', 0)Copy the code

So how can we avoid zero?

There are three methods, all of which are implemented through changes to the Model structure.

  • Method one, add to the structuredefaultField, sSql statement will not set null value when assembling, but the default value will not take effect, which is redundant.
type Tests struct {
	Id   int64  `json:"id"`
	Name string `json:"name"`
	Age  int64  `gorm:"default:'0'"json:"age"`
}

mbr := Tests{
	Name: "222",
}
err = db.Table("test").Create(&mbr).Error
Copy the code

SQL statement executed:

INSERT  INTO `test` (`name`) VALUES ('222')
Copy the code
  • Method 2: change the type of the struct variable to pointer
type Tests struct {
	Id   int64  `json:"id"`
	Name string `json:"name"`
	Age  *int64 `json:"age"`
}

mbr := Tests{
	Name: "111",
}
err = db.Table("test").Create(&mbr).Error
Copy the code

Execute the following SQL statement to convert the NULL pointer to NULL.

INSERT  INTO `test` (`name`,`age`) VALUES ('111',NULL)
Copy the code
  • Method three, usesql.NullInt64Define fields
type Tests struct {
	Id   int64  `json:"id"`
	Name string `json:"name"`
	Age  sql.NullInt64
}

mbr := Tests{
	Name: "111",
}
err = db.Table("test").Create(&mbr).Error
Copy the code

Execute the same SQL statement as method 2.

INSERT  INTO `test` (`name`.`age`) VALUES ('111'.NULL)
Copy the code

All three methods increase the complexity of the structured object to some extent, but the ideal way is to keep the structured object simple and intuitive.

Therefore, you can take the direct write SQL method, although can implement the new method, but cannot get the insert row of data increment ID.

dbRes := db.Table("test").Exec("insert into test (name) values('pengj')")
fmt.Println("value ->",dbRes.Value)
Copy the code

After looking at the gorm source code implementation, we found that both Create and Update methods call NewScope methods when they are called, so we try to use the following method:

mbr := Tests{}
db.Table("test").NewScope(mbr).Raw("insert into test (name) values('xm')").Exec()
fmt.Println( mbr)
Copy the code

At first, I thought that the entire data would be put in the MBR after the data was created, but to my disappointment, the MBR object did not increment the primary key ID.

Gorm is an orM that encapsulates the underlying database/ SQL. If it is degraded, it will solve the problem.

After continuing to open the code, I found the following implementation:

res, err := db.CommonDB().Exec("insert into test (name) values('xm')")
iferr ! =nil {
	panic(err)
}
affectId, _ := res.RowsAffected()
insertId, _ := res.LastInsertId()
fmt.Println("affectId && insertId ", affectId, insertId)
Copy the code

The final output, which solves the problem at the beginning of the article, is insertId, which is the value of the increment ID in the table structure.

affectId && insertId  1 110
Copy the code

Welcome to pay attention to the public number: programmer’s financial circle a discussion of technology, finance, making money small circle, to provide you with the most flavor of the content!