如何处理数据库返回的空值?

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

How do I handle nil return values from database?

问题

我正在编写一个基本程序,从数据库表中读取值并以表格形式打印出来。该表格是由一个古老的程序填充的。行中的某些字段是可选的,当我尝试将它们作为字符串读取时,会出现以下错误:

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

在阅读其他类似问题的解答后,我想出了以下代码来处理空值。这种方法在实践中运行良好。我可以得到纯文本和空字符串的值,而不是空值。

然而,我有两个问题:

  1. 这看起来不够高效。我需要处理25个以上的字段,这意味着我需要将它们都作为字节读取并转换为字符串。太多的函数调用和转换。还有两个结构体来处理数据等等...
  2. 代码看起来很丑陋。已经用两个字段变得混乱不堪,当我处理25个以上的字段时,就变得难以阅读了。

我做错了吗?有没有更好/更清晰/更高效/更符合Go语言习惯的方法来从数据库中读取值?

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

提前感谢!

代码片段:

// DB读取格式
type udInfoBytes struct {
  id                     []byte
  state                  []byte
}

// 输出格式
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
}

编辑:

我希望像下面这样,不必担心处理NULL,并自动将它们读取为空字符串。

// 输出格式
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
}
英文:

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 -&gt; Scan pair: &lt;nil&gt; -&gt; *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.

However, I have two concerns:

  1. 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...
  2. The code looks ugly. It is already looking convoluted with 2 fields and becomes unreadable as I go to 25+

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

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

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(&quot;SELECT id,state FROM Mytable WHERE id=&#39;%s&#39; &quot;, 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(&amp;r.id, &amp;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.

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

func GetInfo(ud string) udInfo {

  db := getFileHandle()
  q := fmt.Sprintf(&quot;SELECT id,state FROM Mytable WHERE id=&#39;%s&#39; &quot;, ud)

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

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

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

  defer db.Close()
  return r
}

答案1

得分: 24

从数据库中获取的null值有不同的类型来处理,例如sql.NullBoolsql.NullFloat64等。

例如:

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

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

For example:

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

答案2

得分: 8

go的database/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)
		}
	}
}

你应该得到以下结果:

1 <nil>
2 bar
英文:

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

package main

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

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

	_, err = db.Exec(&quot;create table foo(id integer primary key, value text)&quot;)
	if err != nil {
		log.Fatal(err)
	}
	_, err = db.Exec(&quot;insert into foo(value) values(null)&quot;)
	if err != nil {
		log.Fatal(err)
	}
	_, err = db.Exec(&quot;insert into foo(value) values(&#39;bar&#39;)&quot;)
	if err != nil {
		log.Fatal(err)
	}
	rows, err := db.Query(&quot;select id, value from foo&quot;)
	if err != nil {
		log.Fatal(err)
	}
	for rows.Next() {
		var id int
		var value *string
		err = rows.Scan(&amp;id, &amp;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 &lt;nil&gt;
2 bar

答案3

得分: 2

另一种解决方案是在 SQL 语句中使用 COALESCE 函数来处理(尽管并非所有的数据库都支持此函数)。

例如,你可以使用以下方式:

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

这样,如果数据库中的 state 字段存储为 NULL,它将被默认设置为一个空字符串。

英文:

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).

For example you could instead use:

q := fmt.Sprintf(&quot;SELECT id,COALESCE(state, &#39;&#39;) as state FROM Mytable WHERE id=&#39;%s&#39; &quot;, 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.

答案4

得分: 0

两种处理空值的方法:

使用 sql.NullString

if value.Valid {
   return value.String
}

使用 *string

if value != nil {
   return *value
}

参考链接:https://medium.com/@raymondhartoyo/one-simple-way-to-handle-null-database-value-in-golang-86437ec75089

英文:

Two ways to handle those nulls:

Using sql.NullString

if value.Valid { 
   return value.String
} 

Using *string

if value != nil {
   return *value
}

https://medium.com/@raymondhartoyo/one-simple-way-to-handle-null-database-value-in-golang-86437ec75089

答案5

得分: -2

我已经开始使用MyMySql驱动程序,因为它使用了比标准库更好的接口。

https://github.com/ziutek/mymysql

然后,我将对数据库的查询封装成了易于使用的函数。以下是其中一个函数的示例:

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

// 执行一个预处理语句,期望多个结果。
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
}

使用这个函数非常简单,就像这个片段一样:

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)
    // 等等...
}

注意到了吗?这里有很好的行方法可以将值转换为特定类型。空值由库处理,并且规则在这里有文档说明:

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

英文:

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

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 &quot;github.com/ziutek/mymysql/mysql&quot;
import _ &quot;github.com/ziutek/mymysql/native&quot;

// 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(&quot;SELECT * FROM table WHERE column = ?&quot;, 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

huangapple
  • 本文由 发表于 2015年2月21日 14:20:56
  • 转载请务必保留本文链接:https://go.coder-hub.com/28642838.html
匿名

发表评论

匿名网友

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

确定