英文:
Could't convert <nil> into type ...?
问题
我尝试使用database/sql来将数据库行查询到Go类型中,我的代码片段如下:
type User struct {
user_id int64
user_name string
user_mobile string
password string
email interface{}
nickname string
level byte
locked bool
create_time string
comment string // 将<nil>转换为*string时出错
}
func TestQueryUser(t *testing.T) {
db := QueryUser(driverName, dataSourceName)
stmtResults, err := db.Prepare(queryAll)
defer stmtResults.Close()
var r *User = new(User)
arr := []interface{}{
&r.user_id, &r.user_name, &r.user_mobile, &r.password, &r.email,
&r.nickname, &r.level, &r.locked, &r.create_time, &r.comment,
}
err = stmtResults.QueryRow(username).Scan(arr...)
if err != nil {
t.Error(err.Error())
}
fmt.Println(r.email)
}
如你所见,一些字段的值为NULL
,所以我必须将interface{}
类型设置到Go的User结构体中,将NULL
转换为nil
。
--- FAIL: TestQueryUser (0.00s)
user_test.go:48: sql: Scan error on column index 9: unsupported Scan, storing driver.Value type <nil> into type *string
有人有更好的方法吗?或者我必须更改MySQL字段并将其设置为DEFAULT ''
?
英文:
I tried to using database/sql for query database row into a Go type, my codes snippet following:
type User struct {
user_id int64
user_name string
user_mobile string
password string
email interface{}
nickname string
level byte
locked bool
create_time string
comment string // convert <nil> to *string error
}
func TestQueryUser(t *testing.T) {
db := QueryUser(driverName, dataSourceName)
stmtResults, err := db.Prepare(queryAll)
defer stmtResults.Close()
var r *User = new(User)
arr := []interface{}{
&r.user_id, &r.user_name, &r.user_mobile, &r.password, &r.email,
&r.nickname, &r.level, &r.locked, &r.create_time, &r.comment,
}
err = stmtResults.QueryRow(username).Scan(arr...)
if err != nil {
t.Error(err.Error())
}
fmt.Println(r.email)
}
As your see, some fields that has NULL
value, so I have to set interface{}
type into User struct of Go, which convert NULL
to nil
.
--- FAIL: TestQueryUser (0.00s)
user_test.go:48: sql: Scan error on column index 9: unsupported Scan, storing driver.Value type <nil> into type *string
Somebody has a better way? or I must change the MySQL field and set its DEFAULT ' '
答案1
得分: 4
首先是简短的回答:在sql包中有一些类型,例如sql.NullString(用于处理表中的可空字符串),猜测还有Nullint64和NullBool等用法 你应该在你的结构体中使用它们。
长一点的回答是:在Go语言中有两个接口可用于处理数据库中的特殊类型,第一个是Scanner,另一个是Valuer。对于数据库中的任何特殊类型(例如,我在PostgreSQL中经常使用JSONB),你需要创建一个类型,并在该类型上实现这两个(或其中一个)接口。
Scanner接口用于在调用Scan
函数时使用。来自数据库驱动程序的数据通常是[]byte
类型的输入,你需要负责处理它。另一个接口在将值用作查询的输入时使用。结果通常是一个字节切片(和一个错误)。如果你只需要读取数据,Scanner就足够了;反之,如果你需要在查询中写入参数,Valuer就足够了。
关于实现的示例,我建议查看sql包中的类型。
此外,还有一个在PostgreSQL中与JSONB/JSON类型一起使用的类型示例:
// GenericJSONField用于处理PostgreSQL中的通用JSON数据
type GenericJSONField map[string]interface{}
// Scan将JSON字段转换为我们的类型
func (v *GenericJSONField) Scan(src interface{}) error {
var b []byte
switch src.(type) {
case []byte:
b = src.([]byte)
case string:
b = []byte(src.(string))
case nil:
b = make([]byte, 0)
default:
return errors.New("unsupported type")
}
return json.Unmarshal(b, v)
}
// Value尝试在数据库中获取字符串切片表示
func (v GenericJSONField) Value() (driver.Value, error) {
return json.Marshal(v)
}
驱动程序的值通常是[]byte
,但string
和nil
也是可以接受的。因此,这个示例可以处理可空字段。
英文:
First the short answer : There is some types in sql package for example sql.NullString (for nullable string in your table and guess Nullint64 and NullBool and ... usage ) and you should use them in your struct.
The long one : There is two interface for this available in go , first is Scanner and the other is Valuer for any special type in database, (for example,I use this mostly with JSONB in postgres) you need to create a type, and implement this two(or one of them) interface on that type.
the scanner is used when you call Scan
function. the data from the database driver, normally in []byte
is the input and you are responsible for handling it. the other one, is used when the value is used as input in query. the result "normally" is a slice of byte (and an error) if you need to only read data, Scanner is enough, and vice-versa, if you need to write parameter in query the Valuer is enough
for an example of implementation, I recommend to see the types in sql package.
Also there is an example of a type to use with JSONB/JSON type in postgresql
// GenericJSONField is used to handle generic json data in postgres
type GenericJSONField map[string]interface{}
// Scan convert the json field into our type
func (v *GenericJSONField) Scan(src interface{}) error {
var b []byte
switch src.(type) {
case []byte:
b = src.([]byte)
case string:
b = []byte(src.(string))
case nil:
b = make([]byte, 0)
default:
return errors.New("unsupported type")
}
return json.Unmarshal(b, v)
}
// Value try to get the string slice representation in database
func (v GenericJSONField) Value() (driver.Value, error) {
return json.Marshal(v)
}
driver value is often []byte
but string
and nil
is acceptable. so this could handle null-able fields too.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论