解决Go语言数据库中null值的问题

本文主要介绍如何使用go语言database/sql库从数据库中读取null值的问题，以及如何向数据库中插入null值。本文在这里使用的是sql.nullstring, sql.nullint64, sql.nullfloat64等结构体，为了方便书写，它们的泛指我会使用sql.null***来表示

要点

从数据库读取可能为null值得值时，可以选择使用sql.null***来读取；或者使用ifnull、coalesce等命令让数据库查询值返回不为”“或者null

若需要往数据库中插入null值，则依然可以使用sql.null***存储所需的值，然后进行插入null值

直接使用sql.null***类型容易出现valid遗漏设置等问题，普通int、string与其转换时，请写几个简单的get、set函数

本demo使用的数据库表以及数据如下

mysql> desc person;
+------------+--------------+------+-----+---------+----------------+
| field   | type     | null | key | default | extra     |
+------------+--------------+------+-----+---------+----------------+
| id     | int(11)   | no  | pri | null  | auto_increment |
| first_name | varchar(100) | no  |   | null  |        |
| last_name | varchar(40) | yes |   | null  |        |
| age    | int(11)   | yes |   | null  |        |
+------------+--------------+------+-----+---------+----------------+
mysql> select * from person;
+----+------------+-----------+------+
| id | first_name | last_name | age |
+----+------------+-----------+------+
| 1 | yousa   | null   | null |
+----+------------+-----------+------+
mysql> show create table person;
+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| table | create table                                                                                                                      |
+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| person | create table `person` (
 `id` int(11) not null auto_increment,
 `first_name` varchar(100) not null,
 `last_name` varchar(40) default null,
 `age` int(11) default null,
 primary key (`id`)
) engine=innodb auto_increment=2 default charset=utf8 |
+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

从数据库中读取null值

如果不作处理直接从数据库中读取null值到string/int，会发生如下错误错误

scan null值到string的报错

sql: scan error on column index 1: unsupported scan, storing driver.value type <nil> into type *string

scan null值到int的报错

sql: scan error on column index 1: converting driver.value type <nil> ("<nil>") to a int: invalid syntax

使用如下的struct来读取数据库内容

type person struct {
  firstname        string
  lastname        string 
  age           int
}
  //由于只有一行，直接使用queryrow
  row := db.queryrow("select first_name, last_name from person where first_name='yousa'")
  err = row.scan(&hello.firstname, &hello.lastname)
  if err != nil {
    fmt.println(err)
  }
  fmt.println(hello)
  row1 := db.queryrow("select first_name, age from person where first_name='yousa'")
  err = row1.scan(&hello.firstname, &hello.age)
  if err != nil {
    fmt.println(err)
  }
  fmt.println(hello)

运行代码，可以通过日志看出来，错误来自scan将null值赋值给int或者string时，报错；解决这个问题可以使用sql原生结构体sql.null***来解决

使用sqlnull***

sql.null***在sql库中声明如下，在读取时，（比如读取的值存储到nullint64），假如发现存储的值是null，则会将nullint64的valid设置为false，然后不会将值存储到int64中，int64值默认为0，如果是nullstring则string值时nil；如果是正常值，则会将valid赋值为true，将值存储到int64中。

type nullint64 struct {
  int64 int64
  valid bool // valid is true if int64 is not null
}
func (n *nullint64) scan(value interface{}) error
func (n nullint64) value() (driver.value, error)
type nullstring struct {
  string string
  valid bool // valid is true if string is not null
}
func (ns *nullstring) scan(value interface{}) error
func (ns nullstring) value() (driver.value, error)

代码修改为如下：

type person struct {
  firstname        string
  lastnullname      sql.nullstring
  nullage         sql.nullint64
}
  rownull := db.queryrow("select first_name, last_name from person where first_name='yousa'")
  err = rownull.scan(&hello.firstname, &hello.lastnullname)
  if err != nil {
    fmt.println(err)
  }
  fmt.println(hello)
  rownull1 := db.queryrow("select first_name, age from person where first_name='yousa'")
  err = rownull1.scan(&hello.firstname, &hello.nullage)
  if err != nil {
    fmt.println(err)
  }
  fmt.println(hello)

输出结果

{yousa 0 { false} {0 false}}

{yousa 0 { false} {0 false}}

使用ifnull或者coalesce

coalesce()解释:返回参数中的第一个非空表达式（从左向右依次类推）

ifnull(expr1,expr2):如果expr1不是null，ifnull()返回expr1，否则它返回expr2。ifnull()返回一个数字或字符串值，取决于它被使用的上下文环境。

查询语句使用一个默认值来替换null即可

select first_name, coalesce(age, 0) from person;//

select first_name, ifnull(age, 0) from person;//

往数据库中插入null值

前面我们对select语句使用了sql.null***类型，同理，insert、update语句也可以通过使用这种类型来插入nil值

代码如下：

  hello := person {
    firstname: "",
    lastname: "",
    age: 0,
    lastnullname: sql.nullstring{string:"", valid:false},
    nullage: sql.nullint64{int64:0, valid:false}}
  _, err = db.exec(
    "insert into person (first_name, last_name) values (?, ?)", "yousa1", hello.lastname)
  if err != nil {
    fmt.println(err)
  }
  _, err = db.exec(
    "insert into person (first_name, last_name) values (?, ?)", "yousa2", hello.lastnullname)
  if err != nil {
    fmt.println(err)
  }
//数据库插入结果
mysql> select * from person;
+----+------------+-----------+------+
| id | first_name | last_name | age |
+----+------------+-----------+------+
| 1 | yousa   | null   | null |
| 2 | yousa1   |      | null |
| 3 | yousa2   | null   | null |
+----+------------+-----------+------+

解释下db.exec操作hello.lastnullname的过程：

首先它会调用hello.lastnullname的value方法，获取到driver.value，然后检验valid值是true还是false，如果是false则会返回一个nil值（nil值传给sql driver会被认为是null值），如果是true则会将hello.lastnullname.string的值传过去。

ps: 为了保证你所插入的值能如你所期望是null值，一定记得要将sql.null***中valid值置为false

使用null还是有很多危害的，再回顾下数据库中使用null值的危害

为什么不建议使用null

所有使用null值的情况，都可以通过一个有意义的值的表示，这样有利于代码的可读性和可维护性，并能从约束上增强业务数据的规范性。

null值在timestamp类型下容易出问题，特别是没有启用参数explicit_defaults_for_timestamp

not in、!= 等负向条件查询在有 null 值的情况下返回永远为空结果，查询容易出错

null 列需要更多的存储空间：需要一个额外字节作为判断是否为 null 的标志位

null值到非null的更新无法做到原地更新，更容易发生索引分裂，从而影响性能。

ps：但把null列改为not null带来的性能提示很小，除非确定它带来了问题，否则不要把它当成优先的优化措施，最重要的是使用的列的类型的适当性。

当然有些情况是不得不使用null值进行存储，或者在查询时由于left/right join等导致null值，但总体来说，能少用就少用。

helper func（提升效率/减少错误）

如果使用sql.null***的话，由于其有两个字段，如果直接手动赋值的话还是很容易遗漏，所以还是需要简单的转换函数，这里给了两个简单的helper fuc，分别是将int64转换成nullint64和将string转换成nullstring

//tonullstring invalidates a sql.nullstring if empty, validates if not empty
func tonullstring(s string) sql.nullstring {
  return sql.nullstring{string : s, valid : s != ""}
}
//tonullint64 validates a sql.nullint64 if incoming string evaluates to an integer, invalidates if it does not
func tonullint64(s string) sql.nullint64 {
  i, err := strconv.atoi(s)
  return sql.nullint64{int64 : int64(i), valid : err == nil}
}

补充：golang 处理mysql数据库中的null， nil，time类型的值

在用golang获取数据库的数据的时候，难免会遇到可控field。这个时候拿到的数据如果直接用string, time.time这样的类型来解析的话会遇到panic。

下面的方法会解决这种问题：

表结构：

show create table checksum_mengyao;

create table `checksum_mengyao` (
 `db` char(64) not null,
 `tbl` char(64) not null,
 `chunk` int(11) not null,
 `chunk_time` float default null,
 `chunk_index` varchar(200) default null,
 `lower_boundary` text,
 `upper_boundary` text,
 `this_crc` char(40) not null,
 `this_cnt` int(11) not null,
 `master_crc` char(40) default null,
 `master_cnt` int(11) default null,
 `ts` timestamp not null default current_timestamp on update current_timestamp,
 primary key (`db`,`tbl`,`chunk`),
 key `ts_db_tbl` (`ts`,`db`,`tbl`)
) engine=innodb default charset=utf8 

表中的一条记录：

+------------+-----------------+-------+------------+-------------+----------------+----------------+----------+----------+------------+------------+---------------------+
| db     | tbl       | chunk | chunk_time | chunk_index | lower_boundary | upper_boundary | this_crc | this_cnt | master_crc | master_cnt | ts         |
+------------+-----------------+-------+------------+-------------+----------------+----------------+----------+----------+------------+------------+---------------------+
| db_kb | admin_info |   1 |  0.007406 | null    | null      | null      | 33d5c5be |    1 | 33d5c5be  |     1 | 2019-12-11 10:39:03 |
+------------+-----------------+-------+------------+-------------+----------------+----------------+----------+----------+------------+------------+---------------------+

定义一个struct originaldata 用于接收表中的数据

type originaldata struct {
 db11      string
 tbl11      string
 chunk1     int
 chunk_time1   float64
 chunk_index1  sql.nullstring
 lower_boundary1 sql.nullstring
 upper_boundary1 sql.nullstring
 this_crc1    sql.nullstring
 this_cnt1    int
 master_crc1   sql.nullstring
 master_cnt1   int
 ts1       mysql.nulltime   //"github.com/go-sql-driver/mysql"
}

拿到表中数据将其转换格式后用另一个struct datacheckinfo 去接收，这便于操作这些数据

type datacheckinfo struct {
 db1      string
 tbl1      string
 chunk     int
 chunk_time   float64
 chunk_index  string
 lower_boundary string
 upper_boundary string
 this_crc    string
 this_cnt    int
 master_crc   string
 master_cnt   int
 ts       string
}

golang获取表中原始数据

func savealldata(rows *sql.rows) []datacheckinfo {
 var test originaldata   //保存表中元数据
 var datalist []datacheckinfo  //保存元数据转换后的数据
 for rows.next() {
 var datainfo datacheckinfo
 rows.scan(&test.db11, &test.tbl11, &test.chunk1, &test.chunk_time1, &test.chunk_index1, &test.lower_boundary1,
  &test.upper_boundary1, &test.this_crc1, &test.this_cnt1, &test.master_crc1, &test.master_cnt1, &test.ts1)
 datainfo.db1 = test.db11
 datainfo.tbl1 = test.tbl11
 datainfo.chunk = test.chunk1
 datainfo.chunk_time = test.chunk_time1
 //fmt.println(test.chunk_time1)
 
 if test.chunk_index1.valid {     //true 非null值
  datainfo.chunk_index = test.chunk_index1.string
 }else{                //false null值
  datainfo.chunk_index = "null"
 }
 if test.lower_boundary1.valid{
  datainfo.lower_boundary = test.lower_boundary1.string
 }else {
  datainfo.lower_boundary = "null"
 }
 if test.upper_boundary1.valid{
  datainfo.upper_boundary = test.upper_boundary1.string
 }else {
  datainfo.upper_boundary = "null"
 }
 if test.this_crc1.valid{
  datainfo.this_crc = test.this_crc1.string
 }else {
  datainfo.this_crc = "null"
 }
 datainfo.this_cnt = test.this_cnt1
 if test.master_crc1.valid{
  datainfo.master_crc = test.master_crc1.string
 }else {
  datainfo.master_crc = "null"
 }
 datainfo.master_cnt = test.master_cnt1
 
 //fmt.println(test.ts1, reflect.typeof(test.ts1.valid), reflect.typeof(test.ts1.time))
 if test.ts1.valid {
  datainfo.ts = test.ts1.time.format("2006-01-02 15:04:05")
 }else{
  datainfo.ts = "null"
 }
 datalist = append(datalist,datainfo)
 fmt.println(datainfo)
 } 
 return datalist
}
 
func selectalldata(sdb *sql.db, ipval string){  //checkdatadiffsendding()
  //*******省略连接数据库的操作
 rows, err := sdb.query("select * from checksum_mengyao")
 defer rows.close()
 datainfo := savealldata(rows) 
}

以上为个人经验，希望能给大家一个参考，也希望大家多多支持。如有错误或未考虑完全的地方，望不吝赐教。