本文主要介绍如何使用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使用的数据库表以及数据如下
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
|
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来读取数据库内容
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
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中。
|
1
2
3
4
5
6
7
8
9
10
11
12
|
type NullInt64 struct { Int64 int64 Valid bool // Valid is true if Int64 is not NULL}func (n *NullInt64) Scan(value interface{}) errorfunc (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{}) errorfunc (ns NullString) Value() (driver.Value, error) |
代码修改为如下：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
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值
代码如下：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
|
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
|
1
2
3
4
5
6
7
8
9
|
//ToNullString invalidates a sql.NullString if empty, validates if not emptyfunc 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 notfunc 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;
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
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 |
表中的一条记录：
|
1
2
3
4
5
|
+------------+-----------------+-------+------------+-------------+----------------+----------------+----------+----------+------------+------------+---------------------+| 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 用于接收表中的数据
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
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 去接收，这便于操作这些数据
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
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获取表中原始数据
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
|
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) } |
以上为个人经验，希望能给大家一个参考，也希望大家多多支持服务器之家。如有错误或未考虑完全的地方，望不吝赐教。
原文链接：https://blog.csdn.net/qq_15437667/article/details/78780945
