如何处理来自数据库的nil返回值?

I am writing a basic program to read values from database table and print in table. The table was populated by an ancient program. Some of the fields in the row are optional and when I try to read them as string, I get the following error:

我正在编写一个从数据库表中读取值并在表中打印值的基本程序。桌子上有一个古老的程序。行中的一些字段是可选的，当我尝试将它们作为字符串读取时，会得到以下错误:

panic: sql: Scan error on column index 2: unsupported driver -> Scan pair: <nil> -> *string

After I read other questions for similar issues, I came up with following code to handle the nil values. The method works fine in practice. I get the values in plain text and empty string instead of the nil values.

在我阅读了类似问题的其他问题之后，我提出了以下代码来处理nil值。这种方法在实践中行之有效。我得到的值是纯文本和空字符串，而不是nil值。

However, I have two concerns:

然而，我有两个顾虑:

This does not look efficient. I need to handle 25+ fields like this and that would mean I read each of them as bytes and convert to string. Too many function calls and conversions. Two structs to handle the data and so on...
这看起来不太有效。我需要处理像这样的25个以上的字段，这意味着我将它们作为字节读取并转换为字符串。函数调用和转换太多。处理数据的两个结构，等等……
The code looks ugly. It is already looking convoluted with 2 fields and becomes unreadable as I go to 25+
看起来丑陋的代码。它看起来已经和两个字段纠缠在一起了，当我读到25+时，它就变得不可读了

Am I doing it wrong? Is there a better/cleaner/efficient/idiomatic golang way to read values from database?

我做错了吗?是否有更好的/更干净的/高效的/惯用的golang方法从数据库中读取值?

I find it hard to believe that a modern language like Go would not handle the database returns gracefully.

我发现很难相信像Go这样的现代语言不能优雅地处理数据库返回。

Thanks in advance!

提前谢谢!

Code snippet:

代码片段:

// DB read format
type udInfoBytes struct {
  id                     []byte
  state                  []byte
}

// output format
type udInfo struct {
  id                     string
  state                  string
}

func CToGoString(c []byte) string {
  n := -1
  for i, b := range c {
    if b == 0 {
      break
    }
    n = i
  }
  return string(c[:n+1])
}

func dbBytesToString(in udInfoBytes) udInfo {

  var out udInfo
  var s string
  var t int

  out.id = CToGoString(in.id)
  out.state = stateName(in.state)
  return out
}

func GetInfo(ud string) udInfo {

  db := getFileHandle()
  q := fmt.Sprintf("SELECT id,state FROM Mytable WHERE id='%s' ", ud)

  rows, err := db.Query(q)
  if err != nil {
    log.Fatal(err)
  }
  defer rows.Close()
  ret := udInfo{}
  r := udInfoBytes{}
  for rows.Next() {
    err := rows.Scan(&r.id, &r.state)

    if err != nil {
      log.Println(err)
    }
    break
  }
  err = rows.Err()
  if err != nil {
    log.Fatal(err)
  }

  ret = dbBytesToString(r)
  defer db.Close()
  return ret
}

edit:

编辑:

I want to have something like the following where I do no have to worry about handling NULL and automatically read them as empty string.

我想要有如下的东西，我不需要担心如何处理NULL并自动地将它们读为空字符串。

// output format
type udInfo struct {
  id                     string
  state                  string
}

func GetInfo(ud string) udInfo {

  db := getFileHandle()
  q := fmt.Sprintf("SELECT id,state FROM Mytable WHERE id='%s' ", ud)

  rows, err := db.Query(q)
  if err != nil {
    log.Fatal(err)
  }
  defer rows.Close()
  r := udInfo{}

  for rows.Next() {
    err := rows.Scan(&r.id, &r.state)

    if err != nil {
      log.Println(err)
    }
    break
  }
  err = rows.Err()
  if err != nil {
    log.Fatal(err)
  }

  defer db.Close()
  return r
}

4 个解决方案

#1

There are separate types to handle null values coming from the database such as sql.NullBool, sql.NullFloat64, etc.

有不同的类型来处理来自数据库(如sql)的空值。NullBool,sql。NullFloat64等等。

For example:

例如:

 var s sql.NullString
 err := db.QueryRow("SELECT name FROM foo WHERE id=?", id).Scan(&s)
 ...
 if s.Valid {
    // use s.String
 } else {
    // NULL value
 }

#2

go's database/sql package handle pointer of the type.

go的数据库/sql包处理类型的指针。

package main

import (
    "database/sql"
    "fmt"
    _ "github.com/mattn/go-sqlite3"
    "log"
)

func main() {
    db, err := sql.Open("sqlite3", ":memory:")
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    _, err = db.Exec("create table foo(id integer primary key, value text)")
    if err != nil {
        log.Fatal(err)
    }
    _, err = db.Exec("insert into foo(value) values(null)")
    if err != nil {
        log.Fatal(err)
    }
    _, err = db.Exec("insert into foo(value) values('bar')")
    if err != nil {
        log.Fatal(err)
    }
    rows, err := db.Query("select id, value from foo")
    if err != nil {
        log.Fatal(err)
    }
    for rows.Next() {
        var id int
        var value *string
        err = rows.Scan(&id, &value)
        if err != nil {
            log.Fatal(err)
        }
        if value != nil {
            fmt.Println(id, *value)
        } else {
            fmt.Println(id, value)
        }
    }
}

You should get like below:

你应该如下所示:

1 <nil>
2 bar

#3

An alternative solution would be to handle this in the SQL statement itself by using the COALESCE function (though not all DB's may support this).

另一种解决方案是使用COALESCE函数在SQL语句本身中处理这个问题(尽管不是所有的DB都支持这一点)。

For example you could instead use:

例如，你可以使用:

q := fmt.Sprintf("SELECT id,COALESCE(state, '') as state FROM Mytable WHERE id='%s' ", ud)

which would effectively give 'state' a default value of an empty string in the event that it was stored as a NULL in the db.

这将有效地给“state”一个空字符串的默认值，如果它被存储为db中的空字符串。

#4

-2

I've started to use the MyMySql driver as it uses a nicer interface to that of the std library.

我已经开始使用MyMySql驱动程序，因为它使用了与std库更好的接口。

https://github.com/ziutek/mymysql

I've then wrapped the querying of the database into simple to use functions. This is one such function:

然后，我将数据库的查询封装到简单的函数中。这就是这样一种功能:

import "github.com/ziutek/mymysql/mysql"
import _ "github.com/ziutek/mymysql/native"

// Execute a prepared statement expecting multiple results.
func Query(sql string, params ...interface{}) (rows []mysql.Row, err error) {
    statement, err := db.Prepare(sql)
    if err != nil {
        return
    }
    result, err := statement.Run(params...)
    if err != nil {
        return
    }
    rows, err = result.GetRows()
    return
}

To use this is as simple as this snippet:

使用它就像这段代码一样简单:

rows, err := Query("SELECT * FROM table WHERE column = ?", param)

for _, row := range rows {
    column1 = row.Str(0)
    column2 = row.Int(1)
    column3 = row.Bool(2)
    column4 = row.Date(3)
    // etc...
}

Notice the nice row methods for coercing to a particular value. Nulls are handled by the library and the rules are documented here:

请注意用于强制到特定值的漂亮的行方法。本图书馆负责处理资料的空号，规则载于以下文件:

https://github.com/ziutek/mymysql/blob/master/mysql/row.go

#1