Sqlx备忘录

很少用golang写增删改查，sqlx的用法和java差别很大，用的时候总是要从头看文档。这里写个备忘录方便以后查询，使用sqlx和go-sqlbuilder来完成增删改查。

连接

var (
    db *sqlx.DB
    err error
)
//右边是dsn格式表达式，即<用户名>:<密码>@tcp(<主机>:<端口>)/<数据库名>?<连接参数>
db, err = sqlx.Connect("mysql", "root:123456@localhost:6379/example?charset=utf8mb4&parseTime=True&loc=Local")
db.Ping()

如果只是想打开但不连接，可以用sqlx.Open. 如果从已经存在的sqlx.DB中创建一个新db对象，使用NewDb.

连接池参数：

//最大闲置连接
db.SetMaxIdleConns(1)
//最大允许打开连接，默认无限制
db.SetMaxOpenConns(5)

增删改

非查询请求都是同样的接口，即：db.Exec(sql, args...)，当然也可以用MustExec，失败就panic. 实际上这个接口也是标准sql库支持的接口。

返回值是sql.Result和error. sql.Result有LastInsertId()和RowsAffected()两个方法，但是这个返回值能不能正确获取其实取决于数据库自己的实现。MySQL可以正确获得自增id和影响的列，但是PG就要自己在sql里面加上returning才行。

sql和args可以通过go-sqlbuilder库来生成，如（以下例子来自该库的godoc）：

ib := SQLite.NewInsertBuilder()
ib.InsertIgnoreInto("demo.user")
ib.Cols("id", "name", "status", "created_at")
ib.Values(1, "Huan Du", 1, Raw("UNIX_TIMESTAMP(NOW())"))
ib.Values(2, "Charmy Liu", 1, 1234567890)

sql, args := ib.Build()

当然select同理.

如果不使用sqlbuilder，自己写的话，大致格式是：

insert or ignore into demo.user (id, name, status, created_at) values (?, ?, ?, ?, ?)

为了防止SQL注入，变量都要使用?占位，而不是直接拼写到sql中。

内置类型会正确映射成数据库支持的参数类型。如果是自定义类型，则可以通过实现driver.Valuer接口来实现。

查询

基础

首先应当知道标准sql库里如何查询：

Query(sql, args...) (*sql.Rows, error)
QueryRow(sql, args...)*sql.Row

显然前者返回多行，后者返回单行。

sql.Rows是一个游标，可以通过Next()迭代处理：

rows, err := db.Query("select age from person")
if err != nil{
    //查询错误
    return
}
for rows.Next(){
    var x int
    if err = rows.Scan(&x); err != nil{
        //scan错误
        log.Error(err)
    }
}
if err = rows.Err(); err != nil{
    //处理错误
    return
}

sql.Row可以直接Scan进行保存，Scan的参数个数显然应当与SELECT中查询的列数相当。

将数据库中获取的结果Scan到自定义类型，则需要实现sql.Scanner这个接口。

如果Query结果是空的，Scan会返回sql.ErrNoRows.

特别注意：如果使用rows.Next完成迭代，需要手动rows.Close关闭游标。

sqlx的orm

sqlx扩展了标准sql的功能，可以将数据直接映射到struct中。

Queryx(sql, args...) (*sqlx.Rows, error)
QueryRowx(sql, args...) (*sqlx.Row)

sqlx.Rows和sqlx.Row可以直接scan到struct里，sql中的字段用db的注解来映射：

var x struct{
    Age int `db:"age"`
}
row.StructScan(&p)

如果不想每个字段都手动映射，可以用db.MapperFunc(f), f是映射函数.

除了StructScan之外，还有SliceScan和MapScan，前者目标应当是[]any，后者则是map[string]any，一般很少使用这两个方法，除非不知道列的明确类型。

除了上面的方法以外，还有两个更简便的方法：

Get(dest, sql, args...) error
Select([]dest, sql, args...) error

Get直接将单行结果Scan到dest里，Select直接将多行结果scan到dest slice里。注意后者会一次性Load所有数据，对内存造成一定的压力。

sqlbuilder的orm

sqlbuilder也自带了一个类似的功能，无须使用db.queryx，而是直接使用db.query:

var ageStruct = NewStruct(new(Age))
var age Age
row.Scan(ageStruct(&age)...)

不过这个用起来太麻烦，不如直接用sqlx的功能方便。可以仅使用sqlbuilder生成SELECT语句的能力。

事务

sqlx的事务很简单：

tx, err := db.Beginx()
tx.Exec()
tx.Query()
tx.Commit()

如果事务失败，就手动tx.Rollback().

如果想要修改事务的隔离级别，使用tx.Exec直接运行sql语句进行更改即可。