This article does not do a comprehensive comparison, but only compares an important difference between the GORM library and the SQL standard library when null fields are present in the tables. Suppose mysql has the following table:

CREATE TABLE `student` (
  `id` int NOT NULL,
  `name` varchar(20) DEFAULT '',
  `score` int DEFAULT NULL,
  `classes` int DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3
Copy the code
mysql> select * from student; +----+-------+-------+---------+ | id | name | score | classes | +----+-------+-------+---------+ | 1 | test1 | 100 | 3 | | 2 | test2 | | NULL | + 100 - + -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 2 rows in the set (0.00 SEC)Copy the code

Not NULL default 0: not null 0: not null 0: not null 0: not null 0: not null 0 This is just to demonstrate the difference between the two libraries.

  1. gorm
package main import ( "fmt" "gorm.io/driver/mysql" "gorm.io/gorm" ) type Student struct { Name string Id int Score Float64 Classes int AvgScore float64} func main() {DSN := "root:12345678@tcp(127.0.0.1:3306)/test? charset=utf8mb4&parseTime=True&loc=Local" db, err := gorm.Open(mysql.Open(dsn), &gorm.Config{}) if err ! = nil { panic(err) } var stu Student db.Select("id,name,score,classes,score/classes as avg_score").Table("student").Where("id=?" , 2).Find(&stu) fmt.Println(stu) }Copy the code

Output:

{test2 2 100 0 0}
Copy the code

You can see that the null value is converted to 0, and no exceptions are thrown.

  1. sql
package main import ( "fmt" "database/sql" _ "github.com/go-sql-driver/mysql" ) type Student struct { Name string Id int  Score float64 Classes int AvgScore float64 } func main() { db, err := sql.Open("mysql", "Root: 12345678 @ TCP (127.0.0.1:3306)/test") if err! = nil { panic(err) } rows, err := db.Query("select id,name,score,classes,score/classes as avg_score from student") if err ! = nil { panic(err) } var stu Student for rows.Next() { err := rows.Scan(&stu.Id, &stu.Name, &stu.Score, &stu.Classes, &stu.AvgScore) if err ! = nil { panic(err) } fmt.Println(stu) } }Copy the code

Output:

{test1 1 100 3 33.3333}
panic: sql: Scan error on column index 3, name "classes": converting NULL to int is unsupported

goroutine 1 [running]:
main.main()
        /xxx/core/sqls/yyy/test2.go:34 +0x2d9
exit status 2
Copy the code

An exception was thrown during the cast. Look at the Scan code, which contains a convertAssignRows call:

switch dv.Kind() { case reflect.Ptr: if src == nil { dv.Set(reflect.Zero(dv.Type())) return nil } dv.Set(reflect.New(dv.Type().Elem())) return ConvertAssignRows (dv.interface (), SRC, rows) // Target type: case reflect.int, reflect.int8, reflect.int16, reflect.int32, reflect.Int64: If SRC == nil {return fmt.Errorf("converting NULL to %s is unsupported", dv.Kind()) } s := asString(src) i64, err := strconv.ParseInt(s, 10, dv.Type().Bits()) if err ! = nil { err = strconvErr(err) return fmt.Errorf("converting driver.Value type %T (%q) to a %s: %v", src, s, dv.Kind(), err) } dv.SetInt(i64) return nil case reflect.Uint, reflect.Uint8, reflect.Uint16, reflect.Uint32, reflect.Uint64: if src == nil { return fmt.Errorf("converting NULL to %s is unsupported", dv.Kind()) } s := asString(src) u64, err := strconv.ParseUint(s, 10, dv.Type().Bits()) if err ! = nil { err = strconvErr(err) return fmt.Errorf("converting driver.Value type %T (%q) to a %s: %v", src, s, dv.Kind(), err) } dv.SetUint(u64) return nil case reflect.Float32, reflect.Float64: if src == nil { return fmt.Errorf("converting NULL to %s is unsupported", dv.Kind()) } s := asString(src) f64, err := strconv.ParseFloat(s, dv.Type().Bits()) if err ! = nil { err = strconvErr(err) return fmt.Errorf("converting driver.Value type %T (%q) to a %s: %v", src, s, dv.Kind(), err) } dv.SetFloat(f64) return nil case reflect.String: if src == nil { return fmt.Errorf("converting NULL to %s is unsupported", dv.Kind()) } switch v := src.(type) { case string: dv.SetString(v) return nil case []byte: dv.SetString(string(v)) return nil } }Copy the code

When the query result type is nil and the target type is int, the cast exception is thrown. However, if the target type is a pointer type, no exception will be thrown, so change the code to:

package main import ( "fmt" "database/sql" _ "github.com/go-sql-driver/mysql" ) type Student struct { Name string Id int  Score float64 Classes *int AvgScore *float64 } func main() { db, err := sql.Open("mysql", "Root: 12345678 @ TCP (127.0.0.1:3306)/test") if err! = nil { panic(err) } rows, err := db.Query("select id,name,score,classes,score/classes as avg_score from student") if err ! = nil { panic(err) } var stu Student for rows.Next() { err := rows.Scan(&stu.Id, &stu.Name, &stu.Score, &stu.Classes, &stu.AvgScore) if err ! = nil { panic(err) } fmt.Println(stu) } }Copy the code

Output:

{test1 1 100 0xc000016358 0xc000016360}
{test2 2 100 <nil> <nil>}
Copy the code

Conclusion: The query based on SQL library should pay attention to the case that the result field is NULL, and the corresponding structure field should be declared as a pointer type. So, why don’t gorM based libraries need to consider this situation?

// assign stmt.ReflectValue if stmt.Dest ! = nil { stmt.ReflectValue = reflect.ValueOf(stmt.Dest) for stmt.ReflectValue.Kind() == reflect.Ptr { if stmt.ReflectValue.IsNil() && stmt.ReflectValue.CanAddr() { stmt.ReflectValue.Set(reflect.New(stmt.ReflectValue.Type().Elem())) } stmt.ReflectValue = stmt.ReflectValue.Elem() } if ! stmt.ReflectValue.IsValid() { db.AddError(ErrInvalidValue) } }Copy the code

Because the GORM library itself already does this compatibility processing.