SQLX 官方文档 中文翻译

SQLX 官方文档 中文翻译

本文译自:Illustrated guide to SQLX

https://blog.hao99.club 站长 Harvey 翻译,转载请注明出处。

水平有限,难免有纰漏。望评论区斧正~


sqlx 是一个 Go 语言的数据库操作包,是在内置的 database/sql 包之上提供了一系列的扩展。

本文主要讲述该库在 Go 语言的习惯用法,因此本文中的 SQL 并不是推荐用法。而且本文不会涉及如何设置 Go 开发环境、Go 语言的基本语法以及 SQL 语句本身。

最后,标准的 err 变量是用于表明返回的错误,但为了本文的简洁,将会忽略掉。在实际开发中,您应该确保检查处理好程序中的所有错误。

资源

下面的资源是有关于如何在 Go 中使用 SQL:

如果你需要入门 Go 语言本身,我推荐下面这些网站:

入门

你需要安装 sqlx 以及一个数据库驱动。在这里我推荐使用 mattnsqlite3 驱动来入门,因为其不需要基础设施。

$ go get github.com/jmoiron/sqlx
$ go get github.com/mattn/go-sqlite3

Handle Types

sqlx 旨在与 database/sql 有着相同的体验,有以下 4 种主要的 handle types

  • sqlx.DB – 与 sql.DB 相似,代表数据库
  • sqlx.Tx – 与 sql.Tx 相似,代表事务
  • sqlx.Stmt – 与 sql.Stmt 相似,代表 Prepared Statement
  • sqlx.NamedStmt – 代表具有 命名参数 支持的 Prepared Statement

所有的 handle types 都提供了 database/sql 的兼容,意味着当你调用 sqlx.DB.Query 时调用了与 sql.DB.Query 相同的代码,这使得相当容易引入到现有的项目当中。

此外,还有两个游标类型:

  • sqlx.Rows – 与 sql.Rows 相似,Queryx 所返回的游标
  • sqlx.Row – 与 sql.Row 相似,QueryRowx 所返回的结果

与 handle types 一样,sqlx.Rows 也是内嵌 sql.Rows 的。但因为底层的实现是不可访问的,所以 sqlx.Rowsql.Row 的一个不完全的重新实现,保留了标准接口。

连接你的数据库

DB 实例并不代表一个连接,而是一个数据库的抽象表示。这就是为什么创建一个 DB 并不会返回错误和造成 panic。它内部维护着一个连接池,当需要的时候才会尝试连接。你可以通过 Open 创建一个 sql.DB,也可以将一个已经存在的 sql.DB 通过 NewDb 获得 sqlx.DB 实例:

var db *sqlx.DB

// 与内建使用方法一致
db = sqlx.Open("sqlite3", ":memory:")

// 从已存在的 sql.DB 创建;需要提供驱动名称
db = sqlx.NewDb(sql.Open("sqlite3", ":memory:"), "sqlite3")

// 强制连接并测试是否正常工作
err = db.Ping()

在某些情况中,你可能想要打开一个 DB 并同时连接它。例如,为了在初始化阶段捕获配置问题,你可以使用 Connect 来一次性完成这些操作,它会打开一个新的 DB 并尝试 Ping。 下面的 MustConnect 函数则会在发生错误时进行 panic,这适合在模块级的包内使用。

var err error

// 同时打开以及连接
db, err = sqlx.Connect("sqlite3", ":memory:")

// 同时打开以及连接,遇到错误时 panic
db = sqlx.MustConnect("sqlite3", ":memory:")

初探查询

sqlx 中的 handle types 实现了相同的基本数据库查询方法:

  • Exec(...) (sql.Result, error) – 与 database/sql 一致
  • Query(...) (*sql.Rows, error) – 与 database/sql 一致
  • Query(...) *sql.Row – 与 database/sql 一致

以下是内建方法的方法:

  • MustExec() sql.Result– 原内建的 Exec,但产生错误会 panic
  • Queryx(...) (*sql.Rows, error) – 原内建的的 Query,但返回的是 sqlx.Rows
  • QueryRowx(...) *sql.Row – 原内建的 QueryRow,但返回的是 sqlx.Row

下面是特有的新方法:

  • Get(dest interface{}, ..) error
  • Select(dest interface{}, ...) error

我们将按照上表顺序,逐步解释其用法。

Exec

ExecMustExec 从连接池中获取一个连接,并在服务器上执行已提供的查询语句。对于不支持点对点(ad-hoc)查询的驱动,prepared statement 将会在后台创建并执行。在结果放回之前该连接就会归还到连接池中。

schema := `CREATE TABLE place(
contry text,
city text NULL,
telcode integer);`

// 在数据库服务器中执行查询
result, err := db.Exec(schema)

// 或者,你可以使用 MustExec,产生错误时将会 panic
cityState := `INSERT INTO place (country, telcode) VALUES (?, ?)`
countryCity := `INSERT INTO place (country, city, telcode) VALUES (?, ?, ?)`
db.MustExec(cityState, "Hong Kong", 852)
db.MustExec(cityState, "Singapore", 65)
db.MustExec(countryCity, "South Africa", "Johannesburg", 27)

结果有两个可能的数据:LastInsertId()RowsAffected(),这取决与驱动是否支持。举个例子,在 MySQL 中,LastInsertId() 在自增主键的插入中是可用的,但在 PostgreSQL,该信息只能使用 Returning 子句通过普通的行游标(row cursor)来获取。

bindvars

在 SQL 中 ? 占位符,在内部也叫 bindvars,这是非常有用的。你应该总是使用这种方式来给数据库传递参数。因为这样做可以防止 SQL 注入 攻击。database/sql 不会对查询文本进行任何验证,它会连同已编码的参数一同按原样发送到数据库当中去。除非驱动实现了一个特殊的接口,在查询执行之前首先在数据库进行准备(prepared)。不同的数据库 bindvars 是不一样的:

  • MySQL 使用 ? 语法
  • PostgreSQL 使用 $1, $2 语法
  • SQLite 使用 ?$1 语法
  • Oracle 使用 :name 语法

其它数据库有可能不同。你可以使用 ? bindvar 语法通过 sqlx.DB.Rebind(string) string 函数获得适合你当前数据库类型的查询语句。

一个常见的误解,认为 bindvars 只是作插值处理。而实际上 bindvar 只能进行参数化(parameterization),是不允许改变 SQL 语句的结构的。举个例子,使用 bindvars 来尝试参数化列名或表名是无效的:

// 无效语句
db.Query("SELECT * FROM ?", "mytable")

// 同样无效
db.Query("SELECT ?, ? FROM people", "name", "location")

Query

Query 是在 database/sql 执行查询操作的主要方式,其返回一个行结果集。Query 返回一个 sql.Rows 对象和一个 error

// 从数据库取得全部地点(places)
rows, err := db.Query("SELECT country, city FROM place")

// 迭代每一行
for rows.Next() {
var country string
var city sql.NullString
var telcode int
err = rows.Scan(&country, &city, &telcode)
}

你应该将 Rows 视作一个数据库游标(cursor),而不是具体的(materialized)结果列表。尽管驱动程序的缓冲行为可能不同,但通过 Next() 迭代大型结果集可以有效控制内存的使用,因为其一次只扫描一行。Scan() 是通过反射将 SQL 的列返回类型映射为 Go 语言的数据类型,如 string, []byte 等。要注意的是,如果你没有遍历完全部的结果集,请务必调用 rows.Close() 将连接归还给连接池。

Query 返回的 error 有可能是从准备到执行之间产生的任何错误。包括从连接池抓取了一个坏掉的连接,尽管 database/sql 会进行 10 次重试,以尝试查找和创建有效的连接。但通常情况下的 error 都是由于错误的 SQL 语法、类型不匹配或不正确的字段和表名造成的。

在大多数情况下,Rows.Scan 会拷贝从驱动获取的数据,因为它不知道驱动程序如何复用它的缓存区。一个特殊的类型 sql.RawBytes 可以用于从驱动程序返回的实际数据中获得一个 0 拷贝的字节切片。但是当下一次调用 Next() 后,该值将不再有效,因为该内存可能已经被驱动程序覆盖了。

Query 所使用的连接将保持活动状态,直到通过 Next() 迭代完全部的行,或者调用 rows.Close() 才会进行释放。更多的信息可以查看 the connection pool

sqlx 扩展的 Queryx 行为上与 Query 完全一致,只是返回的是增强了扫描行为的 sqlx.Rows 对象:

type Place struct {
Country string
City sql.NullString
TelephoneCode int `db:"telcode"`
}

rows, err := db.Queryx("SELECT * FROM place")
for rows.Next() {
var p Place
err = rows.StructScan(&p)
}

sqlx.Rows 上的主要扩展就是 StructScan(),这是一个可以自动扫描结果到结构体的字段的方法。需要注意的是结构体字段必须要可导出的(首字母大写),这样才可以使 sqlx 将结果写进去。你可以使用 db 结构体 tag 来指定该结构体字段所映射的列名,当然你也可以通过 db.MapperFunc() 指定新的默认的映射规则。默认的规则是使用 strings.Lower 来使结构体字段跟列名匹配。关于 StructScan, SliceScan 和 MapScan 参考这节 advanced scanning

QueryRow

QueryRow 只从数据库中抓取一行。它从连接池中获取一个连接,通过 Query 执行查询语句,返回一个内部中持有 RowsRow 对象。

row := db.QueryRow("SELECT * FROM place WHERE telcode=?", 852)
var telcode int
err = row.Scan(&telcode)

Query 不同的是,QueryRow 返回一个 Row 类型的结果,不带有 error,使其变得安全以至于可以链式调用 Scan。如果在执行查询的时候有错误产生,该错误会由 Scan 进行返回。如果查询结果为空,Scan 会返回 sql.ErrNoRows。如果 Scan 自身失败了,其错误也同样由Scan 返回。

QueryRow 使用的连接直到结果被扫描才会关闭。这也意味着 sql.RawBytes 在此时是不可用的,因为引用的内存属于驱动程序,并且在控制权返回给调用者时可能已经无效。

sqlx 扩展的 QueryRowx 将会返回 sqlx.Row 而不是 sql.Row,和 Rows 一样,实现了相同的扫描扩展。在上面和 [advanced scanning] 这节内容有提到。

var p Place
err := db.QueryRowx("SELECT city, telcode FROM place LIMIT 1").StructScan(&p)

Get 与 Select

GetSelect 是 handle types 的扩展,用于节省时间。它们将查询执行与灵活的扫描语法相结合。为了清晰得解释它们,我们必须讲讲 scannable 的含义:

  • 如果一个值它不是结构体,例如 string, int, 那么它是可扫描的(scannable)
  • 如果一个值它实现了 sql.Scanner ,那么它是可扫描的
  • 如果一个值它是结构体,但没有可导出的字段,那么它是可扫描的

GetSelect 对可扫描的类型(scannable types)使用 rows.Scan ,对不可以扫描的类型(non-scannable types)使用 rows.StructScan。它们大致与 QueryRowQuery 相似,Get 在抓取单个数据并扫描时很有用,而 Select 在抓取多个数据时很有用。

p := Place{}
pp := []Place{}

// 这会使第一个 place 直接写入 p
err = db.Get(&p, "SELECT * FROM place LIMIT 1")

// 这会使 telcode > 50 的 place 写入切片 pp
err = db.Select(&pp, "SELECT * FROM place WHERE telcode > ?", 50)

// 普通类型也可以使用
var id int
err = db.Get(&id, "SELECT count(*) FROM place")

// 获取最多 10 个地点名(place names)
var names []string
err = db.Select(&names, "SELECT name FROM place LIMIT 10")

GetSelect 会关闭它们在查询执行过程中创建的 Rows,并且返回在过程的任何步骤中遇到的错误。由于它们内部使用了 StructScan,所以 advanced scanning 中的细节也可用于 GetSelect

Select 可以为你节省大量的打字时间,但要注意!其语意与 Queryx 是不同的,因为它会一次加载整个结果集到内存中。如果该结果没有被你的查询语句(如 LIMIT)限制在合理的值,你最好使用经典的 Queryx / StructScan 代替。

事务

要使用事务,你必须使用 DB.Begin() 创建一个事务 handle。像下面的代码是无效的:

// 如果连接池 > 1 时是无效的
db.MustExec("BEGIN;")
db.MustExec("...")
db.MustExec("COMMIT;")

记住,Exec 和其他查询方法会向 DB 请求一个连接,且每次都会归还到连接池。这不能保证当执行 BEGIN 后每次都能接收到相同的连接。要使用事务,你因此必须使用 DB.Begin()

tx, err := db.Begin()
err = tx.Exec(...)
err = tx.Commit()

DB handle 也扩展了 Beginx()MustBegin() 方法,它们返回一个 sqlx.Tx,替代原本的 sql.Tx

tx := db.MustBegin()
tx.MustExec(...)
err = tx.Commit()

sql.Tx 具有 sql.DB 拥有的全部 handle 扩展。

由于事务是连接状态,Tx 对象必须绑定和控制连接池中的一个单独的连接。Tx 将会维护该单个连接在其整个生命周期,只有当 Commit() 或者 Rollback() 被调用才会释放。你应该要注意至少要调用其中一个方法,否则该连接被一直被占有直到被垃圾回收。

因为在一个事务中你只可以使用一个连接,所以你每次只能执行一个语句。在执行其他语句之前,游标类型 RowRows 必须分别被扫描或者关闭。如果在服务器传送信息给你的时候你尝试发送信息到服务器上,这可能会破坏连接。

最后,Tx 对象并不代表在服务器上的任何行为,它仅仅只是执行一个 BEGIN 语句,并绑定一个单独的连接。事务的实际行为,包括锁定和隔离,完全未指定,并取决于数据库。

Prepared Statements

在大多数数据库上,每次执行查询时,实际都会在后台准备语句(prepared statement)。此外,你也可以使用 sql.DB.Prepare() 显式准备要重用的语句:

stmt, err := db.Prepare(`SELECT * FROM place WHERE telcode=?`)
row = stmt.QueryRow(65)

tx, err := db.Begin()
txStmt, err := tx.Prepare(`SELECT * FROM place WHERE telcode=?`)
row = txStmt.QueryRow(852)

Prepare 实际上是在数据库上进行准备,所以它需要一个连接和连接状态。database/sql 抽象了这部分内容,通过自动在新连接上创建语句(statements),允许你在多个连接上同时从一个单独的 Stmt 对象执行语句。Preparex() 返回一个含有sqlx.DBsqlx.Tx 拥有的全部 handle 扩展的 sqlx.Stmt 实例:

stmt, err := db.Preparex(`SELECT * FROM place WHERE telcode=?`)
var p Place
err = stmt.Get(&p, 852)

标准的 sql.Tx 对象也含有一个 Stmt()方法,其从一个已存在的返回一个特定事务(transaction-specific)的语句。sql.Tx 有一个 Stmtx 的版本,用于从一个已存在的 sql.Stmtsqlx.Stmt 创建一个新的特定事务的 sql.Stmt

查询助手

原始的 database/sql 包没有为你的实际查询文本做任何的处理。这使得在代码中使用后端特定的功能变得不重要了,你可以像在数据库中一样编写查询语句。尽管这很灵活,但是在编写某些类型的查询变得很困难。

“In” 查询

因为 database/sql 不会检查你的查询语句,而是直接将参数传送到驱动中,这使得编写带有 IN 子句的查询变得十分困难:

SELECT * FROM user WHERE level IN (?);

当该语句在后台准备完成的时候,占位符 ? 将仅对应一个 单独的 参数。但通常想要的是包含一个多个数量(切片的长度)的参数,就像下面这样:

var levels = []int{4, 6, 7}
rows, err := db.Query("SELECT * FROM users WHERE level IN (?)", levels)

先通过 sqlx.In 进行处理,则可以实现这种模式:

var levels = []int{4, 6, 7}
query, args, err := sqlx.In("SELECT * FROM users WHERE level In (?)", levels)

// sqlx.In 返回带有 ? 占位符的查询,我们可以将其重新绑定到后端
query = db.Rebind(query)
rows, err := db.Query(query, args...)

sql.In 实际做的是将对应切片的 bindvar 转换成数组长度的参数数量,再将这些切片元素添加到一个新的参数列表。仅对 ? bindvar 有效,你可以使用 db.Rebind 来获得一个适合你后端的查询语句。

命名查询

命名查询(named queries)在很多数据库包中都有的。这允许你使用一个关于结构体字段的名称或者 map 的键名的 bindvar 语法,而不是根据位置的 bindvar。Struct 字段的命名遵循 StructScan 约定,使用 NameMapperdb 结构体 tag。下面是两个关于命名查询的查询方法:

  • NamedQuery(...) (*sqlx.Rows, error) – 与 Queryx 相似,但使用命名占位符(named bindvars)
  • NamedExec(...) (sql.Result, error) – 与 Exec 相似,但使用命名占位符(named bindvars)

还有一个额外的 handle type:

  • NamedStmt – 一个可准备使用命名占位符的 sqlx.Stmt
// 使用结构体的命名查询
p := Place{Country: "South Africa"}
rows, err := db.NamedQuery(`SELECT * FROM place WHERE country=:country`, p)

// 使用 map 的命名查询
m := map[string]interface{}{"city": "Johannesburg"}
result, err := db.NamedExec(`SELECT * FROM place WHERE city=:city`, m)

命名查询执行和准备可同时工作于结构体和 map。如果你想要执行完整的查询(准备 + 查询),准备一个命名语句(prepare a name statement),并使用:

p := Place{TelephoneCode: 50}
pp := []Place{}

// 选择所有 telcodes > 50
nstmt, err := db.PrepareNamed(`SELECT * FROM place WHERE telcode > :telcode`)
err = nstmt.Select(&pp, p)

命名查询的实现,是通过解析查询语句中的 :params 语法,并将其替换成底层数据库所支持的 bindvar,然后在执行语句时将结果进行映射,所以在 sqlx 所支持的数据库是可用的。你也可以使用 sqlx.Named,使用的是 ? bindvar,且可以被 sqlx.In 生成:

arg := map[sting]interface{} {
"published": true,
"authors": []{8, 19, 32, 44},
}
query, args, err := sql.Named("SELECT * FROM articles WHERE published=:published AND author_id IN (:authors)", arg)
query, args, err := sqlx.In(query, args...)
query = db.Rebind(query)
db.Query(query, args...)

高级查询

StructScan 看起来很复杂,它支持内嵌结构体,使用与 Go 用于内嵌属性和进入方法相同优先级规则分配给字段。它的普遍用法是在许多表之间共享表模型的公共部分。例如:

type AutoIncr struct {
ID uint64
Created time.Time
}

type Place struct {
Address string
AutoIncr
}

type Person struct {
name string
AutoIncr
}

观察上面的结构体,PersonPlace 都可以从 StructScan 中获得 idcreated 列,因为他们都嵌入了 AutoIncr 结构体。这项特性允许你快速创建一个用于连接的级联表。它也可以递归操作,通过 Go 的点运算和 StructScan,下表将具有 PersonNameAutoIncrID,以及 Created 字段:

type Employee struct {
BossID uint64
EmployeeID uint64
Person
}

要注意的是, sqlx 过去一直为非嵌入结构体支持这项特性,由于用户使用此功能来定义关系并将相同的结构体嵌入了两次,最终造成了混乱:

type Child struct {
Father Person
Mother Person
}

这会导致一些问题。在 Go 语言中,覆盖子代字段是合法的。如果 Employee 在嵌入式实例中定义了 Name,这会取代 Person 中的 Name。但模糊的选择器是非法的,且会导致运行时错误。如果我们想要为 PersonPlace 创建一个 JOIN 类型,我们应该把 id 列放在哪里呢(PersonPlaceid 都通过 AutoIncr 定义)?这会造成错误吗?

由于 sqlx 构建字段名称到字段地址映射的方式,因此当你扫描到结构体时,它不再知道在遍历结构树期间是否一个名字遇到两次。所以与 Go, StructScan 不同的是,它会选择遇到具有该名字的 “第一个” 字段。由于 Go 结构体字段是从上到下排序,sqlx 进行广度优先遍历以维护优先级规则,它将会在最浅、最顶层的定义中发生。例如,在类型:

type PersonPlace struct {
Person
Place
}

StructScan 将在 Person.AutoIncr.ID 中设置 id 列的值,也可以通过 Person.ID 进行访问。为避免混淆,建议在你的 SQL 中通过 AS 子句设置列别名。


未完工

评论

Your browser is out-of-date!

Update your browser to view this website correctly.&npsb;Update my browser now

×