解决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) }
以上为个人经验,希望能给大家一个参考,也希望大家多多支持。如有错误或未考虑完全的地方,望不吝赐教。
上一篇: go判断文件夹是否存在并创建的实例
下一篇: PHP实现桶排序算法