GOLANG：使用SQLX库执行简单的SQL语句

October 30, 2020 (最后修改: August 22, 2021)

虽然 GOLANG 自带 SQL 库 database/sql，但为了简化代码提高编程效率，笔者推荐使用第三方库来处理数据库相关操作。

本文介绍如何使用 SQLX 库连接 MySQL 数据库并执行简单的 SELECT 查询语句。

示例代码来自 nwpc-oper/nmc-typhoon-db-client 项目，从 NMC 台风数据库中检索台风数据，计划用于 CMA-PI 上的业务系统。

安装

连接 MySQL 数据库需要下载数据库驱动 Go-MySQL-Driver。

go get -u github.com/go-sql-driver/mysql

下载 SQLX 库

go get github.com/jmoiron/sqlx

在源码中载入上述两个库

import (
  	// ...skip...
	_ "github.com/go-sql-driver/mysql"
	"github.com/jmoiron/sqlx"
)

创建数据库连接

SQLX 使用的数据库连接地址字符串与 SQLAlchemy 不同，格式如下所示

user:password@tcp(host:port)/database?param1=value1

为了解析时间字段，增加 parseTime=true 参数

var db *sqlx.DB

conn := fmt.Sprintf(
		"%s:%s@tcp(%s)/%s?charset=utf8&parseTime=true&loc=Local",
		config.Auth.User,
		config.Auth.Password,
		config.Host,
		config.DatabaseName,
)

db, err = sqlx.Open("mysql", conn)

if err != nil {
		log.Fatal("open db connection has error:", err)
		return nil, err
}

defer db.Close()

执行查询

使用 sqlx.DB 对象的 Queryx() 函数执行 SQL 语句，返回值中的 rows 是一个迭代器。

querySQL := fmt.Sprintf("SELECT %s FROM %s WHERE %s AND %s",
		queryColumnsString,
		tableName,
		datetimeQuery,
		forecastHourQuery,
)

rows, err := db.Queryx(querySQL)

if err != nil {
		log.Fatal("query db has error:", err)
		return nil, err
}

defer rows.Close()

处理结果

在标准库 database/sql 中，对每行对象使用 Scan() 方法获取具体的字段值。

该方法需要为每个字段设置一个变量。例如

var (
  name string,
  number int,
  speed float,
)
row.Scan(&name, &number, &speed)

如果检索结果中字段很多，就可能会导致代码不够简洁。

SQLX 库提供 StructScan() 方法，可以将所有字段放入一个结构体中。

下面首先定义结构体 Record 代表每行数据，再将检索结果全部转为 Record。

定义记录结构体

为每个字段都创建对应的成员，可以使用名称为 db 的 struct tag，为字段指定对应的列名。

type Record struct {
	Xuhao     int          `csv:"xuhao"`
	Center    string       `csv:"center"`
	Bwtype    DataString   `csv:"bwtype"`
	FcstType  string       `db:"FCSTType" csv:"FCSTType"`
	Zone      DataString   `csv:"zone"`
	Tfbh      DataString   `csv:"tfbh"`
	Tfbhbabj  DataString   `csv:"tfbhbabj"`
	Engname   DataString   `csv:"engname"`
	Datetime  NullDateTime `csv:"datetime"`
	Fcsthour  int          `csv:"fcsthour"`
	Lat       DataFloat64  `csv:"lat"`
	Lon       DataFloat64  `csv:"lon"`
	Pressure  DataInt32    `csv:"pressure"`
	Windv     DataFloat64  `csv:"windv"`
	Gusts     DataFloat64  `csv:"gusts"`
	Strength  DataString   `csv:"strength"`
	Windclass DataInt32    `csv:"windclass"`
	Movedir   DataString   `csv:"movedir"`
	Movespeed DataFloat64  `csv:"movespeed"`
	Wind7v1   DataFloat64  `csv:"wind7v1"`
	Wind7v2   DataFloat64  `csv:"wind7v2"`
	Wind7v3   DataFloat64  `csv:"wind7v3"`
	Wind7v4   DataFloat64  `csv:"wind7v4"`
	Wind10v1  DataFloat64  `csv:"wind10v1"`
	Wind10v2  DataFloat64  `csv:"wind10v2"`
	Wind10v3  DataFloat64  `csv:"wind10v3"`
	Wind10v4  DataFloat64  `csv:"wind10v4"`
	Wind12v1  DataFloat64  `csv:"wind12v1"`
	Wind12v2  DataFloat64  `csv:"wind12v2"`
	Wind12v3  DataFloat64  `csv:"wind12v3"`
	Wind12v4  DataFloat64  `csv:"wind12v4"`
}

注：上面的代码中使用了自定义的数据类型 DataString，DataFloat64，DataInt32 和 NullDateTime，并定义名为 csv 的 struct tag，这都是为了将结果输出到 CSV 格式文件，后面会介绍。

解析检索数据

使用 StructScan 方法根据每行结果填充 Record 对象，将所有结果放到 records 数组中。

records := []Record{}
for rows.Next() {
		var record Record
		err = rows.StructScan(&record)

		if err != nil {
			log.Fatal("scan row has error:", err)
			return nil, err
		}

		records = append(records, record)
}

err = rows.Err()
if err != nil {
		log.Fatal("query rows has error:", err)
		return nil, err
}

输出CSV文件

使用 csvutil 库将 Records 对象输出到 CSV 文件中

自定义类型

因为数据库中的部分字段可以为空，所以不能使用内置的格式，而是使用类似 mysql.NullTime，sql.NullString 等特殊类型。

csvutil 库在输出 CSV 文件时会调用对象的 MarshalCSV() 方法，上述这些特殊类型都没有定义该方法，所以无法直接使用。

nmc-typhoon-db-client 项目使用自定义类型封装上述类型，并为这些自定义类型创建 MarshalCSV() 方法。这就是上面提到的 DataString，DataFloat64，DataInt32 和 NullDateTime 几种类型。

以 DataInt32 为例说明。MarshalCSV() 方法会检测是否为空，只有不为空的情况下才会返回字节数组。

type DataInt32 struct {
	sql.NullInt32
}

func (d DataInt32) MarshalCSV() ([]byte, error) {
	if d.Valid {
		return []byte(fmt.Sprintf("%d", d.Int32)), nil
	} else {
		return nil, nil
	}
}

另外，这种方式也可以用于自定义日期和时间的序列化。

DateTime 是对 time.Time 的封装，序列化时返回 YYYYMMDDHH 格式的字符串

type DateTime struct {
	time.Time
}

func (date DateTime) MarshalCSV() ([]byte, error) {
	return []byte(date.Format("200601021504")), nil
}

输出

csvutil.Marshal() 函数返回序列化后的字节数组，可以直接写入到文件中。

默认设置下，输出的 CSV 文件使用逗号分隔，并包含表头。

b, err := csvutil.Marshal(records)
if err != nil {
		fmt.Println("error:", err)
		return err
}

f, err := os.Create(filePath)
if err != nil {
		fmt.Println("crate file error:", err)
		return err
}
f.Write(b)
f.Close()

讨论

当前代码还有改进空间，queryColumnsString 字符串由列名字符串数组拼接而成，与 Record 类型的成员变量名称有重复。后续可以考虑使用反射机制，直接从 Record 类型生成 queryColumnsString 字符串。

参考

nwpc-oper/nmc-typhoon-db-client

https://github.com/nwpc-oper/nmc-typhoon-db-client

《SQLAlchemy：执行简单的SQL语句》介绍使用 Python 的 SQLAlchemy 库实现同样的功能。