需要的包
import (
"database/sql"
"fmt"
_ "github.com/go-sql-driver/mysql"
"github.com/jmoiron/sqlx"
"github.com/jtao539/sqlxp" // 此包是我同学(反射大佬)写的
)
sqlx查询
数据库结构
里面的数据如下
代码
package main
import (
"database/sql"
"fmt"
_ "github.com/go-sql-driver/mysql"
"github.com/jmoiron/sqlx"
)
type PersonDTO struct {
ID int64 `db:"id"`
Name string `db:"name"`
Age int64 `db:"age"`
City string `db:"city"`
}
var Db *sqlx.DB
func init() {
db, err := sqlx.Open("mysql", "root:061118@tcp(127.0.0.1:3306)/person")
if err != nil {
fmt.Println("open mysql failed,", err)
return
//panic(err)
}
Db = db
}
func main() {
defer Db.Close()
//var p []Person
var p []PersonDTO
err := Db.Select(&p, "select * from test2 ")
if err != nil {
fmt.Println("select err...", err)
return
}
fmt.Println(p)
}
错误如下:
问题分析
我们在设计数据库时,将age字段的默认值设为空,sqlx进行查询时就会出现上面的错误
解决方法
使用sql.NullXX
代码
package main
import (
"database/sql"
"fmt"
_ "github.com/go-sql-driver/mysql"
"github.com/jmoiron/sqlx"
)
type PersonTest struct {
ID int64 `db:"id"`
Name string `db:"name"`
Age sql.NullInt64 `db:"age"`
City string `db:"city"`
}
var Db *sqlx.DB
func init() {
db, err := sqlx.Open("mysql", "root:061118@tcp(127.0.0.1:3306)/person")
if err != nil {
fmt.Println("open mysql failed,", err)
return
//panic(err)
}
Db = db
}
func main() {
defer Db.Close()
var p []PersonTest
err := Db.Select(&p, "select * from test2 ")
if err != nil {
fmt.Println("select err...", err)
return
}
fmt.Println(p)
}
运行结果如下:
为什么会是这样呢?看下sql.NullInt64的结构就知道了,它包含一个value和用来判断此值是否为空的布尔类型,由于我们数据库中李四的年龄没写,所以他的Valid为false,张三的年龄写了就为true
type NullInt64 struct {
Int64 int64
Valid bool // Valid is true if Int64 is not NULL
}
使用sqlxp
sqlxp的具体使用可参考sqlxp的使用
但是上面这样不利于我们后面数据使用,下面我们导入”github.com/jtao539/sqlxp“(此包是我同学写的)
go get github.com/jtao539/sqlxp
代码
package main
import (
"database/sql"
"fmt"
_ "github.com/go-sql-driver/mysql"
"github.com/jmoiron/sqlx"
"github.com/jtao539/sqlxp"
)
type PersonDTO struct {
ID int64 `db:"id"`
Name string `db:"name"`
Age int64 `db:"age"`
City string `db:"city"`
}
type PersonTest struct {
ID int64 `db:"id"`
Name string `db:"name"`
Age sql.NullInt64 `db:"age"`
City string `db:"city"`
}
var Db *sqlx.DB
func init() {
db, err := sqlx.Open("mysql", "root:061118@tcp(127.0.0.1:3306)/person")
if err != nil {
fmt.Println("open mysql failed,", err)
return
//panic(err)
}
Db = db
}
func main() {
defer Db.Close()
var p []PersonTest
err := Db.Select(&p, "select * from test2 ")
if err != nil {
fmt.Println("select err...", err)
return
}
pe := make([]PersonDTO, len(p))
sqlxp.N2BList(p, pe)
fmt.Println(pe)
}
运行结果如下: