Go uses XORM to operate MySQL traps

Traps for manipulating MySQL using XORM

Although using ORM to operate MySQL is more convenient than directly using the standard library `sql`[1] and the three-party MySQL database driver package [2], it will also encounter some pitfalls.

Type zero value

In Golang, each data type has its own type zero value, for example, the zero value of int is0, the zero value of string is''wait.

Sample code:

package main
import (
 "fmt"
 _ "/go-sql-driver/mysql"
 "/xorm"
)
func main() {
 // Create Engine engine, err := ("mysql", "root:root@/example?charset=utf8")
 defer func() {
  err = ()
  if err != nil {
   ("engine close err=%v\n", err)
   return
  }
 }()
 if err != nil {
  ("init xorm engine fail, err=%v\n", err)
  return
 }
 // Update data example := &amp;Example{
  Title: "go",
  View:  0,
 }
 condi := &amp;Example{
  Id: 2,
 }
 affected, err := (example, condi)
 if err != nil {
  ("Update err=%v\n", err)
  return
 }
 ("affected=%d\n", affected)
}
type Example struct {
 Id      int    `json:"id" form:"id"`
 Title   string `json:"title" form:"title"`
 View    int    `json:"view" form:"view"`
 Created int    `json:"created" form:"created" xorm:"created"`
 Updated int    `json:"updated" form:"updated" xorm:"updated"`
}

Read the above code and we can find that the example code willid=2The data view field is updated to0,because0is the type zero value of int, and the Update method of XORM will automatically ignore the type zero value, so the value of the view field of this data has not changed.

However, in actual project development, we may need to update the value of a certain field to the type zero value of the field type. How should we do it at this time?

affected, err := ("title", "view").Update(example, condi)

We can useCols()Method, specify the field that needs to be updated, so that even if the value of the field that needs to be updated is the zero value of the field type, it can be changed normally.

Tip: It is recommended that when designing database tables, try to use non-type zero values for the field values.

Self-increasing id

When inserting data, we may need to return the autoincrement id. Let's first look at a piece of code:

// Insert dataexample := &amp;Example{
  Title: "PHP",
  View:  90,
}
affected, err := (example)
if err != nil {
  ("Insert err=%v\n", err)
  return
}
("affected=%v\n", affected)

Reading the above code, we insert a piece of data, and the result returns affects the number of rows and error information, rather than directly returning the self-increment id of the piece of data.

Some readers may then use the query method to query the id of the latest data. In scenarios with low concurrent requests, this method can find the self-increment id of the newly inserted data.

However, in scenarios with high concurrent requests, the id of the latest data found by this method may not be the auto-increment id of the data we just inserted.

id := 
("affected=%v || id=%d\n", affected, id)

Read the above code, we want to obtain the self-increment id of the newly inserted data, and directlyYou can get it, butPrerequisitesIt is in the structure,idField usagexorm:"autoincr"Label.

Update created fields

We use labels in structurexorm:createdandxorm:updatedIt can automatically insert the current time.

However, usexorm:createdThe field of the tag is written to the current time only when the data is inserted for the first time and will no longer be changed after that; usexorm:updatedThe field of the label is written to the current time when the data is inserted for the first time. The time will change every time the Update operation thereafter.

If our business needs are to be changed, usexorm:createdCan the fields of the tag be done?

// Change the dataexample := &amp;Example{
  Title: "JavaScript",
  View:  98,
}
condi := &amp;Example{
  Id: 2,
}
affected, err := (example, condi)
if err != nil {
  ("Update err=%v\n", err)
  return
}
("affected=%d\n", affected)

Reading the above code, we found that after executing the Update method, usexorm:updatedThe value of the field of the tag is changed, and the value of the field is changed, and the value of the field is usedxorm:createdThe value of the field of the label has not been changed.

Let's change the way to update the data, the code is as follows:

// Change the datasql := "UPDATE example SET title=?, view=?, created=? WHERE id=?"
res, err := (sql, "Python", 60, ().Unix(), 2)
if err != nil {
  ("Update err=%v\n", err)
  return
}
affected, err := ()
if err != nil {
  ("RowsAffected err=%v\n", err)
  return
}
("affected=%d\n", affected)

Reading the above code, we can find that using the Exec method to execute native SQL can meet our needs.

Summarize

In this article, we have introduced the traps that novices may encounter when using XORM to operate MySQL. I hope it can bring some help to you. For more information about Go XORM to operate MySQL, please pay attention to my other related articles!