在用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)
}