Illustrated guid to SQLX

SQLX 图解指南

原谅链接: http://jmoiron.github.io/sqlx/

sqlx is a package for Go which provides a set of extensions on top of the excellent built-in database/sql package.

sqlx 是一个适用于 Go 语言的包，它在出色的内置 database/sql 包的基础上提供了一组扩展。

Examining Go idioms is the focus of this document, so there is no presumption being made that any SQL herein is actually a recommended way to use a database. It will not cover setting up a Go development environment, basic Go information about syntax or semantics, or SQL itself.

本文档重点探讨 Go 语言的习惯用法，因此并不假定其中的任何 SQL 实际上是推荐的数据库使用方式。本文档不涉及 Go 开发环境的搭建、Go 语法或语义的基础信息，也不涉及 SQL 本身。

Finally, the standard err variable will be used to indicate that errors are being returned, but for brevity they will be ignored. You should make sure to check all errors in an actual program.

最后，标准的 err 变量将用于表明正在返回错误，但为简洁起见，这些错误将被忽略。在实际程序中，你应确保检查所有错误。

Resources

相关资料

There are other resources of excellent information about using SQL in Go:

关于在 Go 语言中使用 SQL，还有其他优质信息资源：

If you need help getting started with Go itself, I recommend these resources:

如果你在 Go 语言入门方面需要帮助，我推荐这些资源：

Because the database/sql interface is a subset of sqlx, all of the advice in these documents about database/sql usage also apply to usage of sqlx.

因为 database/sql 接口是 sqlx 的一个子集，所以这些文档中关于 database/sql 使用的所有建议也适用于 sqlx 的使用。

Getting Started

入门指南

You will want to install sqlx and a database driver. Since it's infrastructureless, I recommend mattn's sqlite3 driver to start out with:

你需要安装 sqlx 和一个数据库驱动程序。由于它无基础设施，我建议从 mattn 的 sqlite3 驱动程序开始：

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

Handle Types

执行器类型

sqlx is intended to have the same feel as database/sql. There are 4 main handle types:

sqlx 的设计理念是在使用体验上与 database/sql 保持一致。它主要有4种 句柄 类型：

Handle types all embed their database/sql equivalents, meaning that when you call sqlx.DB.Query, you are calling the same code as sql.DB.Query. This makes it easy to introduce into an existing codebase.

所有的句柄类型都 嵌入 了它们对应的 database/sql 类型，这意味着当你调用sqlx.DB.Query 时，你调用的代码与 sql.DB.Query 相同。这使得将其引入现有代码库变得很容易。

In addition to these, there are 2 cursor types: 除此之外，还有2种 游标 类型：

As with the handle types, sqlx.Rows embeds sql.Rows. Because the underlying implementation was inaccessible, sqlx.Row is a partial re-implementation of sql.Row that retains the standard interface.

与句柄一样，sqlx.Rows 嵌入了 sql.Rows。由于无法访问底层实现，sqlx.Row 是 sql.Row 的部分重新实现，它保留了标准接口。

Connecting to Your Database

连接到你的数据库

A DB instance is not a connection, but an abstraction representing a Database. This is why creating a DB does not return an error and will not panic. It maintains a connection pool internally, and will attempt to connect when a connection is first needed. You can create an sqlx.DB via Open or by creating a new sqlx DB handle from an existing sql.DB via NewDb:

一个 DB 实例并非连接，而是代表数据库的一种抽象。这就是创建数据库不会返回错误且不会引发恐慌的原因。它在内部维护一个 连接池，并会在首次需要连接时尝试进行连接。你可以通过 Open 创建一个 sqlx.DB，或者通过 NewDb 从现有的 sql.DB 创建一个新的 sqlx 数据库句柄：

var db *sqlx.DB

// exactly the same as the built-in
db = sqlx.Open("sqlite3", ":memory:")

// from a pre-existing sql.DB; note the required driverName
db = sqlx.NewDb(sql.Open("sqlite3", ":memory:"), "sqlite3")

// force a connection and test that it worked
err = db.Ping()

In some situations, you might want to open a DB and connect at the same time; for instance, in order to catch configuration issues during your initialization phase. You can do this in one go with Connect, which Opens a new DB and attempts a Ping. The MustConnect variant will panic when encountering an error, suitable for use at the module level of your package.

在某些情况下，你可能希望同时打开数据库并建立连接；例如，为了在初始化阶段捕获配置问题。你可以使用 Connect 一次性完成此操作，它会打开一个新数据库并尝试执行 Ping。MustConnect 变体在遇到错误时会引发恐慌，适用于在你的包的模块级别使用。

var err error
// open and connect at the same time:
db, err = sqlx.Connect("sqlite3", ":memory:")

// open and connect at the same time, panicing on error
db = sqlx.MustConnect("sqlite3", ":memory:")

Querying 101

查询基础101

The handle types in sqlx implement the same basic verbs for querying your database:

sqlx 中的句柄类型实现了用于查询数据库的相同基本操作：

These extensions to the built-in verbs:

这些对内置方法的扩展：

And these new semantics:

以及这些全新的方法:

Let's go from the unchanged interface through the new semantics, explaining their use.

让我们从不变的接口入手，再讲解新的方法，并说明其用法。

Exec

Exec and MustExec get a connection from the connection pool and executes the provided query on the server. For drivers that do not support ad-hoc query execution, a prepared statement may be created behind the scenes to be executed. The connection is returned to the pool before the result is returned.

Exec 和 MustExec 从连接池中获取一个连接，并在服务器上执行提供的查询。对于不支持即席查询执行的驱动程序，可能会在幕后创建一个预编译语句来执行。在返回结果之前，连接会被返回到连接池中。

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

// execute a query on the server
result, err := db.Exec(schema)

// or, you can use MustExec, which panics on error
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)

The result has two possible pieces of data: LastInsertId() or RowsAffected(), the availability of which is driver dependent. In MySQL, for instance, LastInsertId() will be available on inserts with an auto-increment key, but in PostgreSQL, this information can only be retrieved from a normal row cursor by using the RETURNING clause.

结果 可能有两类数据：LastInsertId() 或 RowsAffected()，具体取决于所使用的驱动程序。例如，在 MySQL 中，使用自动递增键进行插入操作时，LastInsertId() 可用；但在 PostgreSQL 中，只能通过使用 RETURNING 子句从普通行游标中检索此信息。

bindvars

The ? query placeholders, called bindvars internally, are important; you should always use these to send values to the database, as they will prevent SQL injection attacks. database/sql does not attempt any validation on the query text; it is sent to the server as is, along with the encoded parameters. Unless drivers implement a special interface, the query is prepared on the server first before execution. Bindvars are therefore database specific:

? 查询占位符（在内部称为 bindvars）非常重要；你 始终 应该使用它们将值发送到数据库，因为它们可以防止 SQL 注入 攻击。database/sql 不会对查询文本进行 任何 验证；它会原样连同编码后的参数一起发送到服务器。除非驱动程序实现了特殊接口，否则查询会在执行前先在服务器上进行预处理。因此，绑定变量是特定于数据库的：

Other databases may vary. You can use the sqlx.DB.Rebind(string) string function with the ? bindvar syntax to get a query which is suitable for execution on your current database type.

其他数据库可能有所不同。你可以使用 sqlx.DB.Rebind(string) string 函数和 ? 绑定变量语法，来获取适合在当前数据库类型上执行的查询。

A common misconception with bindvars is that they are used for interpolation. They are only for parameterization, and are not allowed to change the structure of an SQL statement. For instance, using bindvars to try and parameterize column or table names will not work:

关于绑定变量，一个常见的误解是认为它们用于插值。它们仅用于 参数化，并且不允许 更改 SQL 语句的结构。例如，使用绑定变量尝试对列名或表名进行参数化是行不通的：

// doesn't work
db.Query("SELECT * FROM ?", "mytable")

// also doesn't work
db.Query("SELECT ?, ? FROM people", "name", "location")

Query

Query is the primary way to run queries with database/sql that return row results. Query returns an sql.Rows object and an error:

Query 是使用 database/sql 运行查询以返回行结果的主要方式。Query 返回一个 sql.Rows 对象和一个错误：

// fetch all places from the db
rows, err := db.Query("SELECT country, city, telcode FROM place")

// iterate over each row
for rows.Next() {
    var country string
    // note that city can be NULL, so we use the NullString type
    var city    sql.NullString
    var telcode int
    err = rows.Scan(&country, &city, &telcode)
}
// check the error from rows
err = rows.Err()

You should treat the Rows like a database cursor rather than a materialized list of results. Although driver buffering behavior can vary, iterating via Next() is a good way to bound the memory usage of large result sets, as you're only scanning a single row at a time. Scan() uses reflect to map sql column return types to Go types like string, []byte, et al. If you do not iterate over a whole rows result, be sure to call rows.Close() to return the connection back to the pool!

你应该将 Rows 视为数据库游标，而不是物化的结果列表。尽管驱动程序的缓冲行为可能有所不同，但通过 Next() 进行迭代是限制大型结果集内存使用的好方法，因为一次只扫描一行。Scan() 使用 反射 将 SQL 列返回类型映射到 Go 类型，如 string、[]byte 等。如果没有遍历完整个 rows 结果集，请务必调用 rows.Close() 将连接返回给连接池！

The error returned by Query is any error that might have happened while preparing or executing on the server. This can include grabbing a bad connection from the pool, although database/sql will retry 10 times to attempt to find or create a working connection. Generally, the error will be due to bad SQL syntax, type mismatches, or incorrect field and table names.

Query 返回的错误是在服务器上准备或执行时可能发生的任何错误。这可能包括从连接池中获取到不良连接，尽管 database/sql 会 重试10次，尝试找到或创建一个可用的连接。通常，错误将是由于错误的 SQL 语法、类型不匹配或不正确的字段和表名导致的。

In most cases, Rows.Scan will copy the data it gets from the driver, as it is not aware of how the driver may reuse its buffers. The special type sql.RawBytes can be used to get a zero-copy slice of bytes from the actual data returned by the driver. After the next call to Next(), such a value is no longer valid, as that memory might have been overwritten by the driver.

在大多数情况下，Rows.Scan 会复制从驱动程序获取的数据，因为它不知道驱动程序可能如何重用其缓冲区。特殊类型 sql.RawBytes 可用于从驱动程序返回的实际数据中获取 零拷贝 的字节切片。在下一次调用 Next() 之后，这样的值将不再有效，因为该内存可能已被驱动程序覆盖。

The connection used by the Query remains active until either all rows are exhausted by the iteration via Next, or rows.Close() is called, at which point it is released. For more information, see the section on the connection pool.

查询所使用的连接会保持活动状态，直到通过 Next 迭代耗尽所有行，或者调用 rows.Close()，此时连接将被释放。有关更多信息，请参阅 “连接池” 部分。

The sqlx extension Queryx behaves exactly as Query does, but returns an sqlx.Rows, which has extended scanning behaviors:

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

The primary extension on sqlx.Rows is StructScan(), which automatically scans results into struct fields. Note that the fields must be exported (capitalized) in order for sqlx to be able to write into them, something true of all marshallers in Go. You can use the db struct tag to specify which column name maps to each struct field, or set a new default mapping with db.MapperFunc(). The default behavior is to use strings.Lower on the field name to match against the column names. For more information about StructScan, SliceScan, and MapScan, see the section on advanced scanning.

sqlx.Rows 上的主要扩展是 StructScan()，它会自动将结果扫描到结构体字段中。请注意，字段必须是 导出的（大写字母开头），这样 sqlx 才能写入它们，这对 Go 中的所有序列器来说都是如此。你可以使用 db 结构体标签来指定每个结构体字段映射到哪个列名，或者使用 db.MapperFunc() 设置新的默认映射。默认行为是对字段名使用 strings.Lower 来与列名进行匹配。有关 StructScan、SliceScan 和 MapScan 的更多信息，请参阅 高级扫描部分。

QueryRow

QueryRow fetches one row from the server. It takes a connection from the connection pool and executes the query using Query, returning a Row object which has its own internal Rows object:

QueryRow 从服务器获取一行数据。它从连接池中获取一个连接，并使用 Query 执行查询，返回一个 Row 对象，该对象有自己的内部 Rows 对象：

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

Unlike Query, QueryRow returns a Row type result with no error, making it safe to chain the Scan off of the return. If there was an error executing the query, that error is returned by Scan. If there are no rows, Scan returns sql.ErrNoRows. If the scan itself fails (eg. due to type mismatch), that error is also returned.

与 Query 不同，QueryRow 返回一个无错误的 Row 类型结果，因此可以安全地在返回结果上链式调用 Scan。如果执行查询时出现错误，该错误将由 Scan 返回。如果没有行，Scan 返回 sql.ErrNoRows。如果扫描本身失败（例如由于类型不匹配），也会返回该错误。

The Rows struct internal to the Row result is Closed upon Scan, meaning that the connection used by QueryRow is kept open until the result is scanned. It also means that sql.RawBytes is not usable here, since the referenced memory belongs to the driver and may already be invalid by the time control is returned to the caller.

Rows 结构体在 Row 结果内部，在扫描时是封闭的，这意味着 QueryRow 使用的连接会一直保持打开状态，直到结果被扫描。这也意味着此处不能使用 sql.RawBytes，因为所引用的内存属于驱动程序，并且在控制权返回给调用者时可能已经无效。

The sqlx extension QueryRowx will return an sqlx.Row instead of an sql.Row, and it implements the same scanning extensions as Rows, outlined above and in the advanced scanning section:

sqlx 扩展 QueryRowx 将返回一个 sqlx.Row 而不是 sql.Row，并且它实现了与 Rows 相同的扫描扩展，如上文及 高级扫描部分 所述：

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

Get and Select

Get and Select are time saving extensions to the handle types. They combine the execution of a query with flexible scanning semantics. To explain them clearly, we have to talk about what it means to be scannable:

Get 和 Select 是针对句柄类型的省时扩展。它们将查询的执行与灵活的扫描语义结合起来。为了清晰地解释它们，我们必须谈谈可 scannable 意味着什么：

Get and Select use rows.Scan on scannable types and rows.StructScan on non-scannable types. They are roughly analagous to QueryRow and Query, where Get is useful for fetching a single result and scanning it, and Select is useful for fetching a slice of results:

Get 和 Select 对可扫描类型使用 rows.Scan，对不可扫描类型使用 rows.StructScan。它们大致类似于 QueryRow 和 Query，其中 Get 用于获取单个结果并对其进行扫描，而 Select 用于获取结果切片：

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

// this will pull the first place directly into p
err = db.Get(&p, "SELECT * FROM place LIMIT 1")

// this will pull places with telcode > 50 into the slice pp
err = db.Select(&pp, "SELECT * FROM place WHERE telcode > ?", 50)

// they work with regular types as well
var id int
err = db.Get(&id, "SELECT count(*) FROM place")

// fetch at most 10 place names
var names []string
err = db.Select(&names, "SELECT name FROM place LIMIT 10")

Get and Select both will close the Rows they create during query execution, and will return any error encountered at any step of the process. Since they use StructScan internally, the details in the advanced scanning section also apply to Get and Select.

Get 和 Select 都会在查询执行期间关闭它们创建的行，并返回在该过程任何步骤中遇到的错误。由于它们在内部使用 StructScan，高级扫描部分 中的详细信息也适用于 Get 和 Select。

Select can save you a lot of typing, but beware! It's semantically different from Queryx, since it will load the entire result set into memory at once. If that set is not bounded by your query to some reasonable size, it might be best to use the classic Queryx/StructScan iteration instead.

Select 可以为你节省大量的输入，但要注意！它在语义上与 Queryx 不同，因为它会一次性将整个结果集加载到内存中。如果该结果集没有通过你的查询限制在合理大小内，那么最好还是使用经典的 Queryx/StructScan 迭代方式。

Transactions

To use transactions, you must create a transaction handle with DB.Begin(). Code like this will not work:

要使用事务，必须使用 DB.Begin() 创建事务处理器。像这样的代码 将不起作用：

// this will not work if connection pool > 1
db.MustExec("BEGIN;")
db.MustExec(...)
db.MustExec("COMMIT;")

Remember, Exec and all other query verbs will ask the DB for a connection and then return it to the pool each time. There's no guarantee that you will receive the same connection that the BEGIN statement was executed on. To use transactions, you must therefore use DB.Begin()

请记住，Exec 和所有其他查询动词每次都会向数据库请求一个连接，然后将其返回给连接池。无法保证你会得到与执行 BEGIN 语句时相同的连接。因此，要使用事务，你必须使用DB.Begin() 。

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

The DB handle also has the extensions Beginx() and MustBegin(), which return an sqlx.Tx instead of an sql.Tx:

数据库句柄还有扩展方法 Beginx() 和 MustBegin()，它们返回 sqlx.Tx 而非 sql.Tx：

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

sqlx.Tx has all of the handle extensions that sqlx.DB has.

sqlx.Tx 拥有 sqlx.DB 具备的所有句柄扩展。

Since transactions are connection state, the Tx object must bind and control a single connection from the pool. A Tx will maintain that single connection for its entire life cycle, releasing it only when Commit() or Rollback() is called. You should take care to call at least one of these, or else the connection will be held until garbage collection.

由于事务是连接状态，Tx 对象必须绑定并控制连接池中的单个连接。Tx 将在其整个生命周期内维护该单个连接，仅在调用 Commit() 或 Rollback() 时才释放它。你应注意至少调用其中一个，否则连接将一直保持到垃圾回收。

Because you only have one connection to use in a transaction, you can only execute one statement at a time; the cursor types Row and Rows must be Scanned or Closed, respectively, before executing another query. If you attempt to send the server data while it is sending you a result, it can potentially corrupt the connection.

因为在一个事务中你只有一个连接可用，所以一次只能执行一条语句；在执行另一个查询之前，游标类型 Row 和 Rows 必须分别进行扫描或关闭。如果你在服务器向你发送结果时尝试向服务器发送数据，这有可能会破坏连接。

Finally, Tx objects do not actually imply any behavior on the server; they merely execute a BEGIN statement and bind a single connection. The actual behavior of the transaction, including things like locking and isolation, is completely unspecified and database dependent.

最后，事务对象实际上并不意味着服务器上的任何行为；它们仅执行一条 BEGIN 语句并绑定单个连接。事务的实际行为，包括诸如锁定和隔离之类的内容，完全未指定，且依赖于数据库。

Prepared Statements

预编译语句

On most databases, statements will actually be prepared behind the scenes whenever a query is executed. However, you may also explicitly prepare statements for reuse elsewhere with sqlx.DB.Prepare():

在大多数数据库中，每当执行查询时，语句实际上都会在幕后进行预处理。不过，你也可以使用 sqlx.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 actually runs the preparation on the database, so it requires a connection and its connection state. database/sql abstracts this from you, allowing you to execute from a single Stmt object concurrently on many connections by creating the statements on new connections automatically. Preparex(), which returns an sqlx.Stmt which has all of the handle extensions that sqlx.DB and sqlx.Tx do:

Prepare 实际上是在数据库上运行准备操作，因此它需要一个连接及其连接状态。database/sql 为你抽象了这一点，允许你通过自动在新连接上创建语句，从单个 Stmt 对象在多个连接上并发执行。Preparex()，它返回一个sqlx.Stmt，该对象具有 sqlx.DB 和 sqlx.Tx 所有句柄扩展：

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

The standard sql.Tx object also has a Stmt() method which returns a transaction-specific statement from a pre-existing one. sqlx.Tx has a Stmtx version which will create a new transaction specific sqlx.Stmt from an existing sql.Stmt or sqlx.Stmt.

标准的 sql.Tx 对象也有一个 Stmt() 方法，该方法从一个已有的语句返回一个特定于事务的语句。sqlx.Tx 有一个 Stmtx 版本，它将从现有的 sql.Stmt 或 sqlx.Stmt 创建一个新的特定于事务的 sqlx.Stmt。

Query Helpers

查询辅助工具

The database/sql package does not do anything with your actual query text. This makes it trivial to use backend-specific features in your code; you can write queries just as you would write them in your database prompt. While this is very flexible, it makes writing certain kinds of queries difficult.

database/sql 包不会处理实际的查询文本。这使得在代码中使用特定于后端的特性变得轻而易举；你可以像在数据库提示符中编写查询一样编写查询语句。虽然这种方式非常灵活，但它也使得编写某些类型的查询变得困难。

"In" Queries

Because database/sql does not inspect your query and it passes your arguments directly to the driver, it makes dealing with queries with IN clauses difficult:

因为 database/sql 不会检查你的查询，而是将你的参数直接传递给驱动程序，这使得处理带有 IN 子句的查询变得困难：

SELECT * FROM users WHERE level IN (?);

When this gets prepared as a statement on the backend, the bindvar ? will only correspond to a single argument, but what is often desired is for that to be a variable number of arguments depending on the length of some slice, eg:

当在后端将其准备为一条语句时，绑定变量 ? 将仅对应一个 单个 参数，但通常期望的是，根据某个切片的长度，它可以是数量可变的参数，例如：

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

This pattern is possible by first processing the query with sqlx.In:

通过首先使用sqlx.In处理查询，这种模式是可行的：

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

// sqlx.In returns queries with the `?` bindvar, we can rebind it for our backend
query = db.Rebind(query)
rows, err := db.Query(query, args...)

What sqlx.In does is expand any bindvars in the query passed to it that correspond to a slice in the arguments to the length of that slice, and then append those slice elements to a new arglist. It does this with the ? bindvar only; you can use db.Rebind to get a query suitable for your backend.

sqlx.In 的作用是将传递给它的查询中的任何绑定变量扩展为与参数中的切片长度相对应的长度，然后将这些切片元素追加到一个新的参数列表中。它仅对 ? 绑定变量执行此操作；你可以使用 db.Rebind 来获取适合你后端的查询。

Named Queries

命名查询

Named queries are common to many other database packages. They allow you to use a bindvar syntax which refers to the names of struct fields or map keys to bind variables a query, rather than having to refer to everything positionally. The struct field naming conventions follow that of StructScan, using the NameMapper and the db struct tag. There are two extra query verbs related to named queries:

命名查询在许多其他数据库包中很常见。它们允许你使用绑定变量语法，该语法通过引用结构体字段名称或映射键来将变量绑定到查询中，而不必按位置引用所有内容。结构体字段命名约定遵循 StructScan 的约定，使用 NameMapper 和 db 结构体标签。有两个与命名查询相关的额外查询动词：

And one extra handle type: 还有一种额外的句柄类型

// named query with a struct
p := Place{Country: "South Africa"}
rows, err := db.NamedQuery(`SELECT * FROM place WHERE country=:country`, p)

// named query with a map
m := map[string]interface{}{"city": "Johannesburg"}
result, err := db.NamedExec(`SELECT * FROM place WHERE city=:city`, m)

Named query execution and preparation works off both structs and maps. If you desire the full set of query verbs, prepare a named statement and use that instead:

命名查询的执行和准备工作既适用于结构体，也适用于映射。如果你需要完整的查询动词集，请准备一条命名语句并使用它：

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

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

Named query support is implemented by parsing the query for the :param syntax and replacing it with the bindvar supported by the underlying database, then performing the mapping at execution, so it is usable on any database that sqlx supports. You can also use sqlx.Named, which uses the ? bindvar, and can be composed with sqlx.In:

命名查询支持是通过解析查询中的 :param 语法并将其替换为基础数据库支持的绑定变量来实现的，然后在执行时进行映射，因此它可用于 sqlx 支持的任何数据库。你还可以使用 sqlx.Named，它使用 ? 绑定变量，并且可以与 sqlx.In 组合使用：

arg := map[string]interface{}{
    "published": true,
    "authors": []{8, 19, 32, 44},
}
query, args, err := sqlx.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...)

Advanced Scanning

高级扫描

StructScan is deceptively sophisticated. It supports embedded structs, and assigns to fields using the same precedence rules that Go uses for embedded attribute and method access. A common use of this is sharing common parts of a table model among many tables, eg:

StructScan 的复杂程度超乎想象。它支持嵌套结构体，并使用与 Go 语言访问嵌套属性和方法相同的优先级规则为字段赋值。常见的用途是在多个表之间共享表模型的公共部分，例如：

type AutoIncr struct {
    ID       uint64
    Created  time.Time
}

type Place struct {
    Address string
    AutoIncr
}

type Person struct {
    Name string
    AutoIncr
}

With the structs above, Person and Place will both be able to receive id and created columns from a StructScan, because they embed the AutoIncr struct which defines them. This feature can enable you to quickly create an ad-hoc table for joins. It works recursively as well; the following will have the Person's Name and its AutoIncr ID and Created fields accessible, both via the Go dot operator and via StructScan:

有了上述结构体，Person 和 Place 都能够从 StructScan 接收 id 和 created 列，因为它们嵌入了定义了这些列的 AutoIncr 结构体。此特性使您能够快速创建一个临时表用于连接操作。它也能递归工作；以下代码中，Person 的 Name 以及其 AutoIncr 的 ID 和 Created 字段都可通过 Go 语言的点运算符和 StructScan 访问：

type Employee struct {
    BossID uint64
    EmployeeID uint64
    Person
}

Note that sqlx historically supported this feature for non-embedded structs, this ended up being confusing because users were using this feature to define relationships and embedding the same structs twice:

请注意，sqlx 过去曾对非嵌入结构体支持此功能，但最终这造成了混淆，因为用户使用此功能来定义关系，并两次嵌入相同的结构体：

type Child struct {
    Father Person
    Mother Person
}

This causes some problems. In Go, it's legal to shadow descendent fields; if Employee from the embedded example defined a Name, it would take precedence over the Person's Name. But ambiguous selectors are illegal and cause a runtime error. If we wanted to create a quick JOIN type for Person and Place, where would we put the id column, which is defined in both via their embedded AutoIncr? Would there be an error?

这会引发一些问题。在 Go 语言中，遮蔽派生字段是合法的；如果嵌入示例中的 Employee 定义了一个 Name，它将优先于 Person 的 Name。但是，模糊的 选择器是非法的，会导致 运行时错误。如果我们想为 Person 和 Place 创建一个快速的 JOIN类型，那么我们应该把通过它们嵌入的 AutoIncr 在两者中都定义的 id 列放在哪里呢？会出现错误吗？

Because of the way that sqlx builds the mapping of field name to field address, by the time you Scan into a struct, it no longer knows whether or not a name was encountered twice during its traversal of the struct tree. So unlike Go, StructScan will choose the "first" field encountered which has that name. Since Go struct fields are ordered from top to bottom, and sqlx does a breadth-first traversal in order to maintain precedence rules, it would happen in the shallowest, top-most definition. For example, in the type:

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

type PersonPlace struct {
    Person
    Place
}

A StructScan will set an id column result in Person.AutoIncr.ID, also accessible as Person.ID. To avoid confusion, it's suggested that you use AS to create column aliases in your SQL instead.

StructScan 会在 Person.AutoIncr.ID 中设置一个 id 列结果，也可以通过 Person.ID 访问。为避免混淆，建议在 SQL 中使用 AS 创建列别名

Scan Destination Safety

扫描目标安全性

By default, StructScan will return an error if a column does not map to a field in the destination. This mimics the treatment for things like unused variables in Go, but does not match the way that standard library marshallers like encoding/json behave. Because SQL is generally executed in a more controlled fashion than parsing JSON, and these errors are generally coding errors, a decision was made to return errors by default.

默认情况下，如果某列无法映射到目标中的字段，StructScan 将会返回错误。这类似于 Go 语言中对未使用变量的处理方式，但与 encoding/json 等标准库的编组器行为并不一致。由于 SQL 的执行方式通常比解析 JSON 更为可控，且这些错误通常属于编码错误，因此决定默认返回错误。

Like unused variables, columns which you ignore are a waste of network and database resources, and detecting things like an incompatible mapping or a typo in a struct tag early can be difficult without the mapper letting you know something wasn't found.

与未使用的变量类似，被你忽略的列会浪费网络和数据库资源，而且如果映射器没有告知你某些内容未找到，那么早期很难发现诸如不兼容的映射或结构体标签中的拼写错误等问题。

Despite this, there are some cases where ignoring columns with no destination might be desired. For this, there is the Unsafe method on each Handle type which returns a new copy of that handle with this safety turned off:

尽管如此，在某些情况下，可能希望忽略没有目标的列。为此，每个句柄类型 都有一个 Unsafe 方法，该方法会返回该句柄的一个新副本，其中此安全性已关闭：

var p Person
// err here is not nil because there are no field destinations for columns in `place`
err = db.Get(&p, "SELECT * FROM person, place LIMIT 1;")

// this will NOT return an error, even though place columns have no destination
udb := db.Unsafe()
err = udb.Get(&p, "SELECT * FROM person, place LIMIT 1;")

Controlling Name Mapping

控制名称映射

Struct fields used as targets for StructScans must be capitalized in order to be accessible by sqlx. Because of this, sqlx uses a NameMapper which applies strings.ToLower to field names to map them to columns in your rows result. This isn't always desirable, depending on your schema, so sqlx allows the mapping to be customized a number of ways.

用作 StructScans 目标的结构体字段 必须 大写，以便 sqlx 能够访问。因此，sqlx 使用 名称映射器，它对字段名称应用 strings.ToLower，将它们映射到行结果中的列。根据你的模式，这并不总是理想的，因此 sqlx 允许通过多种方式自定义映射。

The simplest of these ways is to set it for a db handle by using sqlx.DB.MapperFunc, which receives an argument of type func(string) string. If your library requires a particular mapper, and you don't want to poison the sqlx.DB you receive, you can create a copy for use in the library to ensure a particular default mapping:

其中最简单的方法是通过使用 sqlx.DB.MapperFunc 为数据库句柄设置它，该方法接收一个类型为 func(string) string 的参数。如果你的库需要特定的映射器，并且你不想影响你所接收的 sqlx.DB，则可以创建一个副本供库中使用，以确保特定的默认映射：

// if our db schema uses ALLCAPS columns, we can use normal fields
db.MapperFunc(strings.ToUpper)

// suppose a library uses lowercase columns, we can create a copy
copy := sqlx.NewDb(db.DB, db.DriverName())
copy.MapperFunc(strings.ToLower)

Each sqlx.DB uses the sqlx/reflectx package's Mapper to achieve this mapping underneath, and exposes the active mapper as sqlx.DB.Mapper. You can further customize the mapping on a DB by setting it directly:

每个sqlx.DB在底层使用sqlx/reflectx包的Mapper来实现这种映射，并将活动映射器公开为sqlx.DB.Mapper。你可以通过直接设置来进一步自定义数据库上的映射：

import "github.com/jmoiron/sqlx/reflectx"

// Create a new mapper which will use the struct field tag "json" instead of "db"
db.Mapper = reflectx.NewMapperFunc("json", strings.ToLower)

Alternate Scan Types

备用扫描类型

In addition to using Scan and StructScan, an sqlx Row or Rows can be used to automatically return a slice or a map of results:

除了使用 Scan 和 StructScan 之外，sqlx的Row或Rows可以用于自动返回结果的切片或映射：

rows, err := db.Queryx("SELECT * FROM place")
for rows.Next() {
    // cols is an []interface{} of all of the column results
    cols, err := rows.SliceScan()
}

rows, err := db.Queryx("SELECT * FROM place")
for rows.Next() {
    results := make(map[string]interface{})
    err = rows.MapScan(results)
}

SliceScan returns an []interface{} of all columns, which can be useful in situations where you are executing queries on behalf of a third party and have no way of knowing what columns may be returned. MapScan behaves the same way, but maps the column names to interface{} values. An important caveat here is that the results returned by rows.Columns() does not include fully qualified names, such that SELECT a.id, b.id FROM a NATURAL JOIN b will result in a Columns result of []string{"id", "id"}, clobbering one of the results in your map.

SliceScan 返回所有列的一个 []interface{}，在你代表第三方执行查询且无法得知可能返回哪些列的 情况 下，这可能很有用。MapScan 的行为类似，但会将列名映射到 interface{} 值。这里有一个重要的注意事项，rows.Columns() 返回的结果不包含完全限定名，因此 SELECT a.id, b.id FROM a NATURAL JOIN b 将导致 Columns 结果为 []string{"id", "id"}，这会覆盖你映射中的其中一个结果。

Custom Types

自定义类型

The examples above all used the built-in types to both scan and query with, but database/sql provides interfaces to allow you to use any custom types:

上述示例均使用内置类型进行扫描和查询，但 database/sql 提供了一些接口，允许你使用任何自定义类型：

These are the standard interfaces, and using them will ensure portability to any library that might be providing services on top of database/sql. For a detailed look at how to use them, read this blog post or check out the sqlx/types package, which implements a few standard useful types.

这些是标准接口，使用它们可确保能够移植到任何可能在 database/sql 之上提供服务的库。若要详细了解如何使用这些接口，请阅读此博客文章，或查看 sqlx/types包，该包实现了一些标准的实用类型。

The Connection Pool

连接池

Statement preparation and query execution require a connection, and the DB object will manage a pool of them so that it can be safely used for concurrent querying. There are two ways to control the size of the connection pool as of Go 1.2: 语句准备和查询执行需要一个连接，而DB对象将管理一个连接池，以便可以安全地用于并发查询。截至Go 1.2，有两种方法可以控制连接池的大小：

By default, the pool grows unbounded, and connections will be created whenever there isn't a free connection available in the pool. You can use DB.SetMaxOpenConns to set the maximum size of the pool. Connections that are not being used are marked idle and then closed if they aren't required. To avoid making and closing lots of connections, set the maximum idle size with DB.SetMaxIdleConns to a size that is sensible for your query loads.

默认情况下，连接池会无限制增长，每当连接池中没有可用的空闲连接时，就会创建新的连接。你可以使用 DB.SetMaxOpenConns 设置连接池的最大大小。未使用的连接会被标记为空闲，若不再需要则会被关闭。为避免频繁创建和关闭连接，可使用 DB.SetMaxIdleConns 将最大空闲连接数设置为适合你查询负载的合理大小。

It is easy to get into trouble by accidentally holding on to connections. To prevent this: 意外地持有连接很容易陷入麻烦。为防止这种情况发生：

If you neglect to do one of these things, the connections they use may be held until garbage collection, and your db will end up creating far more connections at once in order to compensate for the ones its using. Note that Rows.Close() can be called multiple times safely, so do not fear calling it where it might not be necessary.

如果你忽略了做这些事情中的某一件，它们所使用的连接可能会一直保持，直到垃圾回收，而你的数据库最终可能会一次性创建更多的连接，以弥补正在使用的连接。请注意，Rows.Close() 可以安全地多次调用，所以不用担心在可能不必要的地方调用它。