Scan error: unsupported Scan, storing driver.Value type <nil> into type *string

huangapple go评论141阅读模式
英文:

Scan error: unsupported Scan, storing driver.Value type <nil> into type *string

问题

当我尝试从MySQL数据库中获取没有照片的文章时:

func ArticlesAllText() ([]Article, error) {
    var err error
    var result []Article
    err = database.SQL.Select(&result, "SELECT * FROM Article WHERE photo IS NULL")
    if err != nil {
        log.Println(err)
    }
    return result, standardizeError(err)
}

我得到以下错误信息:

sql: Scan error on column index 10: unsupported Scan, storing driver.Value type into type *string

当字段的值为NULL时。

我该如何修复这个问题?

英文:

When I try to fetch Articles without photo from mysql database:

func ArticlesAllText() ([]Article, error) {
	var err error
	var result []Article
	err = database.SQL.Select(&amp;result, &quot;SELECT * FROM Article WHERE photo IS NULL&quot;)
	if err != nil {
		log.Println(err)
	}
	return result, standardizeError(err)
}

I get

> sql: Scan error on column index 10: unsupported Scan, storing
> driver.Value type <nil> into type *string

When the field value is NULL.

How can I fix this?

答案1

得分: 30

使用 sql.NullString

https://godoc.org/database/sql#NullString

个人而言,我不喜欢这个解决方案,所以我会对我的数据库进行规范化,以便没有 NULL 值。

要么将

var result []Article

改为

var result []sql.NullString

然后使用文档中的检查来创建一个 []Article

要么在你的结构体中将

Photo *string

改为

Photo sql.NullString

并处理它作为一个结构体而不是 *string 的麻烦。

感谢 ATN,可以在这里查看指南 https://medium.com/aubergine-solutions/how-i-handled-null-possible-values-from-database-rows-in-golang-521fb0ee267

英文:

Use sql.NullString.

https://godoc.org/database/sql#NullString

Personally, I hate this solution, which is why I normalize my DB so that there are no NULLs.

Either change

var result []Article

To

var result []sql.NullString

Then take those results and make a []Article using the checks from the documentation.

Or in your struct, change

Photo *string

To

Photo sql.NullString

And deal with the annoyance of it being a struct instead of a *string

Thanks to ATN, see here for a guide https://medium.com/aubergine-solutions/how-i-handled-null-possible-values-from-database-rows-in-golang-521fb0ee267

答案2

得分: 9

你可以使用以下两种解决方案之一:

  1. 在使用scan()之前,可以使用sql.NullString来处理字段。
  2. 你可以在查询中将所有可能的NULL值替换为所需的字符串,比如''

对于实施第一种解决方案,请参考@RayfenWindspear的答案。
对于第二种解决方案,请将查询更新如下:

SELECT colm1, colm2, COALESCE(photo, '') photo, colm4 FROM Article WHERE photo IS NULL

对于MySQL,请使用IFNULL()COALESCE()函数来在表达式为NULL时返回替代值。
对于SQL Server,请使用IFNULL()COALESCE()函数获得相同的结果。
对于MS Access,请使用IsNull函数获得相同的结果。
对于Oracle,请使用NVL()函数获得相同的结果。

参考:https://www.w3schools.com/sql/sql_isnull.asp

英文:

You can use any of the below two solutions:-

  1. You can use sql.NullString to handle the field before using scan(). OR
  2. You can replace all the possible NULL values with the desired string say &#39;&#39; from the query itself.

For implementing the 1st solution refer to the @RayfenWindspear answer.
For the 2nd solution update the query as below:-

SELECT colm1, colm2, COALESCE(photo, &#39;&#39;) photo, colm4 FROM Article WHERE photo IS NULL

For MySQL use IFNULL() or COALESCE() function to return an alternative value if an expression is NULL:

For SQL Server use IFNULL() or COALESCE() function for the same result

MS Access use IsNull function for the same result

For Oracle use NVL() function for the same result

Reference: https://www.w3schools.com/sql/sql_isnull.asp

huangapple
  • 本文由 发表于 2017年7月4日 01:20:06
  • 转载请务必保留本文链接:https://go.coder-hub.com/44891030.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定