Go database/sql 教程

原文地址：Go database/sql tutorial

目录

1、概述

2、导入数据库驱动

3、访问数据库

4、取结果

4.1 从数据库中取结果

4.2 Scan()是如何工作的

4.3 准备查询

4.4 单行查询

5、更新数据和事务

5.1 更新数据的语句

5.2 使用事务

6、 使用预处理语句

6.1 预处理语句和连接

6.2 避免预处理语句

6.3 事务中的预处理语句

7、错误处理

7.1 遍历结果集时发生的错误

7.2 关闭结果集时发生的错误

7.3 QueryRow()发生的错误

7.4 识别特定的数据库错误

7.5 处理数据库连接错误

8、处理NULL值

9、处理未知列

9.1 列数量未知

9.2 列类型未知

10、连接池

11、其他

---

1、概述

在Go中使用SQL或类SQL数据库的惯用方法是通过database/sql包。它为面向行的数据库提供了一个轻量级接口。本文将会介绍如何使用database/sql包。

2、导入数据库驱动

为了使用database/sql包，你还需要确定你想要使用的数据库驱动。

通常情况下，我们不直接使用database/sql/driver包，我们应尽可能使用database/sql包中定义的各种类型；这可以避免我们写的代码依赖具体的驱动，以便于我们可以通过最少的代码更改来改变底层驱动；这也是Go提倡的使用方式。

在本文中，我们将会使用 MySQL drivers 作为例子。

在你的Go文件中添加以下代码：

import (
	"database/sql"
	_ "github.com/go-sql-driver/mysql"
)

上面的代码我们是匿名加载驱动的，将它的包限定符别名为_，所以它导出的名称对我们的代码是不可见的。在底层，驱动程序将自己注册为database/sql包可用，但通常除了运行init函数外，不会发生其他任何事情。

3、访问数据库

加载驱动程序包后，你就可以创建数据库对象sql.DB了。

可以使用sql.Open()来创建sql.DB，这个方法会返回一个*sql.DB类型：

func main() {
	db, err := sql.Open("mysql",
		"user:password@tcp(127.0.0.1:3306)/hello")
	if err != nil {
		log.Fatal(err)
	}
	defer db.Close()
}

在这个例子中，我们展示了以下几点内容：

1. sql.Open的第一个参数是驱动程序的名称，它和在database/sql注册驱动程序时使用的名字一样，通常情况下和驱动程序的包名是一致的；例如：mysql对应github.com/go-sql-driver/mysql. 当然也有一些驱动没有遵循该规则，如sqlite3、postgres.
2. 第二个参数是驱动指定的用来访问底层数据库的特定语法；上面的例子表示我们要连接到本地的hello数据库；
3. 我们通常需要检查并处理database/sql操作所返回的错误，当然也有一些例外情况，我们将在后面讨论（见7.2 关闭结果集时发生的错误）；
4. 如果sql.DB的作用域仅在该函数中，惯用的做法是通过defer db.Close()语句；

也许与我们的直觉相反，sql.Open()既不会创建到数据库的连接也不会校验连接参数，这仅仅会返回一个供后续使用的数据库抽象；数据库连接在首次使用时通过懒加载的方式创建。可以使用db.Ping()来检查数据库的可用性，同时记得判断错误信息：

err = db.Ping()
if err != nil {
	// do something here
}

虽然习惯上在数据库结束时调用Close()来关闭数据库，但sql.DB对象被设计为长连接。应尽量避免频繁的Open()和Close()数据库连接，相反，在程序生命周期内，为每一个数据存储创建一个sql.DB对象，作为全局变量供各个函数使用；

如果不将sql.DB作为长连接对象使用，你会遇到一些问题，如：连接复用率低、网络资源被耗尽或由于大量的TCP连接处于TIME_WAIT导致的偶尔失败，这些问题都表明你没有按照database/sql的设计去使用它。

4、取结果

Go中database/sql包的函数名称的设计十分重要：

1. 包含Query的函数名，被设计为需要从数据库中查询一组数据；
2. 不返回数据的语句使用Exec()函数；

4.1 从数据库中取结果

下面一个例子展示了如何在Go中查询数据库并处理结果：

var (
	id int
	name string
)
rows, err := db.Query("select id, name from users where id = ?", 1)
if err != nil {
	log.Fatal(err)
}
defer rows.Close()
for rows.Next() {
	err := rows.Scan(&id, &name)
	if err != nil {
		log.Fatal(err)
	}
	log.Println(id, name)
}
err = rows.Err()
if err != nil {
	log.Fatal(err)
}

上面的例子展示的几乎是在Go中实现它的唯一方法。Go中并不能直接使用map接收查询到的数据，因为GO是强类型语音，你需要创建对应类型的变量并将指针传递给方法。

• 需要通过rows.Err()检查rows.Next()是否发生了错误；
• 结果集rows未关闭前，底层数据库连接会处于被占用状态且不会被另一个查询所使用；通过调用rows.Next() 将数据全部读出后，当rows.Next()因为错误返回false时会自动关闭；如果提前退出了循环，此时rows并不会被关闭，这种情况下会导致资源耗尽；
• rows.Close()允许被多次调用，通常需要先检查rows.Err()是否发生了错误，再调用rows.Close()，避免发生运行时异常；
• 通常使用defer rows.Close() ，也可以显示的调用rows.Close();

4.2 Scan()是如何工作的

在遍历结果并将结果赋值到目标变量时，Go会根据目标变量的类型帮我们做类型转换；了解这一点你可以有助于你写出更加简洁的代码及避免一些重复的工作；

例如：查询结果在数据库中的定义为VARCHAR(45)，同时在业务中该字段存储的内容通常为数字，这种情况下你可以使用字符串去接收结果，然后使用strconv.ParseInt()将其转换为数字类型，这个过程挺麻烦的；

4.3 准备查询

当某个查询被多次使用时你可以使用Prepare()，该方法支持参数占位符，在真正执行时传递具体参数，与拼接SQL相比，这可以避免SQL注入；

MySQL驱动中，占位符是？，PostgreSQL和SQLite是$N，Oracle是:param1的形式；

stmt, err := db.Prepare("select id, name from users where id = ?")
if err != nil {
	log.Fatal(err)
}
defer stmt.Close()
rows, err := stmt.Query(1)
if err != nil {
	log.Fatal(err)
}
defer rows.Close()
for rows.Next() {
	// ...
}
if err = rows.Err(); err != nil {
	log.Fatal(err)
}

在底层，db.Query()实际上执行了准备、执行、关闭准备语句三件事，与数据库有三次交互；使用Prepare语句执行多次查询可以减少与数据库的交互（？）

4.4 单行查询

当查询最多返回一条数据时，可以使用如下方式：

var name string
err = db.QueryRow("select name from users where id = ?", 1).Scan(&name)
if err != nil {
	log.Fatal(err)
}
fmt.Println(name)

在上面的查询中，错误会被推迟到Scan()被调用时；也可以使用以下方式：

stmt, err := db.Prepare("select name from users where id = ?")
if err != nil {
	log.Fatal(err)
}
defer stmt.Close()
var name string
err = stmt.QueryRow(1).Scan(&name)
if err != nil {
	log.Fatal(err)
}
fmt.Println(name)

5、更新数据和事务

5.1 更新数据的语句

增、删、改使用Exec()函数，通常与Prepare()搭配使用；

stmt, err := db.Prepare("INSERT INTO users(name) VALUES(?)")
if err != nil {
	log.Fatal(err)
}
res, err := stmt.Exec("Dolly")
if err != nil {
	log.Fatal(err)
}
lastId, err := res.LastInsertId()
if err != nil {
	log.Fatal(err)
}
rowCnt, err := res.RowsAffected()
if err != nil {
	log.Fatal(err)
}
log.Printf("ID = %d, affected = %d\n", lastId, rowCnt)

执行结果返回sql.Result，可以获取到LastInsertId()和RowsAffected()；

如果你不关注执行结果只关注是否执行成功，下面两个语句做的事情是否一样呢？

_, err := db.Exec("DELETE FROM users")  // OK
_, err := db.Query("DELETE FROM users") // BAD

答案是否定的，他们做的事情不一样，你也不应该像上面一样使用Query()；

Query()返回sql.Rows，它会持有一个数据库连接直到sql.Rows被关闭；数据未读取完成前，连接不会被其他查询复用；上面的例子中，连接将不会被释放；GC将会在一段时间（也许很久）后关闭底层net.Conn；此外，database/sql包会继续跟踪该连接，希望您在某个时刻释放它，以便可以再次使用该连接。因此，此反模式是耗尽资源(例如，太多连接)的好方法。

5.2 使用事务

Go中的事务对象会保持一个到数据源的连接，并且保证在事务中的所有语句均在一个相同的连接上执行；

使用tx := db.Begin()开启一个事务，tx.Commit() 或 tx.Rollback()结束事务；你可以像使用db一样使用Tx来执行SQL语句，即Tx具有的方法和db一一对应；

准备语句tx.Prepare会绑定查询到当前事务；

是否可以使用db.Exec("BEGIN")/db.Exec("COMMIT")代替db.Begin()/tx.Commit()呢？答案是不可以，这么做也许会导致一些问题：

• 连接无法释放：Tx会一直持有着数据库连接；
• 状态管理混乱：数据库的状态可能与表示它的Go变量的状态不同步。
• 实际与预期不符：你觉得所有查询都是在同一个连接上进行，实际上GO可能创建了好几个连接，这些查询语句并不在同一个事务中；

在一个事务中，应避免使用db进行一些操作，相应的，使用Tx去执行这些操作；开启一个事务Tx后，如果你使用了db执行一些操作，如db.Exec，这些操作将不会在事务Tx的作用域中；

如果你想执行多个修改连接状态的操作，此时你也可以使用Tx，即使你不需要事务本身，例如：

• 创建临时表；
• 设置变量；
• 修改连接选项，如字符集或超时时间；

6、 使用预处理语句

预处理语句具有Go中所有常见的优点:安全、高效、方便。但是它们的实现方式与您所熟悉的略有不同，特别是在它们如何与database/sql的一些内部构件交互方面。

6.1 预处理语句和连接

在数据库层面，预处理语句会绑定到一个数据库连接上，典型的处理流程是：

1. 客户端发送带有占位符的SQL语句到服务器；
2. 服务器响应一个语句ID；
3. 客户端发送语句ID和参数去执行该语句；

在GO中，数据库连接不直接暴露给database/sql包的用户；你不是在一个连接上准备语句，你在DB或Tx上准备语句；database/sql会提供一些便利的行为，如自动重试；由于这种原因，预处理语句和连接在底层的关联存在于驱动层，该行为对用户隐藏；

工作原理如下：

• 当你准备语句时，准备语句和连接池中的一个连接相关联；
• Stmt对象记录了准备语句所使用的连接；
• Stmt执行时会尝试使用已记录的连接，如果该连接不可用（已被关闭或处于繁忙状态），Stmt会从连接池中获取一个新的连接使用；

因为当原始连接不可用时语句会被重新准备，所以在高并发的应用场景下且大量连接处于繁忙状态时，会造成准备语句被大量创建；这可能会导致明显的语句泄漏，语句准备和重新准备的频率比您想象的要高，甚至会遇到服务器端对语句数量的限制。

6.2 避免预处理语句

Go在后台为你创建预处理语句，如 db.Query(sql, param1, param2), 将会准备SQL、执行、关闭语句；

有时准备语句会和你的预期不符，有以下几点原因：

1. 数据库不支持预处理语句。例如你可以使用MySQL驱动连接MemSQL和Sphinx，因为它们都支持MySQL连接协议，但是他们不支持包含与处理语句的二进制协议，因此它们会以一种令人困惑的方式失败；
2. 语句的重用度不够使得不划算，当安全存在问题时，性能则是其次的；

6.3 事务中的预处理语句

在事务中创建的预处理语句会被唯一绑定到该事务，因此前面关于预处理语句的说法都变的不再适用；当你在事务中进行操作时，你的任何动作都会直接映射到底层的唯一一个连接上；

这就意味着在事务中创建的预处理语句不能和事务分开使用；同样的，通过DB创建的预处理语句也不能在事务中使用，因为它们绑定了不同的连接；

为了在事务中创建一个独立于事务的预处理语句，你可以使用Tx.Stmt()；不赞成这么使用；

在事务中使用准备好的语句时必须谨慎。考虑下面的例子:

tx, err := db.Begin()
if err != nil {
	log.Fatal(err)
}
defer tx.Rollback()
stmt, err := tx.Prepare("INSERT INTO foo VALUES (?)")
if err != nil {
	log.Fatal(err)
}
defer stmt.Close() // danger!
for i := 0; i < 10; i++ {
	_, err = stmt.Exec(i)
	if err != nil {
		log.Fatal(err)
	}
}
err = tx.Commit()
if err != nil {
	log.Fatal(err)
}
// stmt.Close() runs here!

Go <= 1.4，释放数据库连接后，关闭准备语句stmt.Close()，会造成兵法访问底层连接；如果你使用的Go版本 <= 1.4，你应该保证准备语句先于事务提交被关闭，即先stmt.Close()后tx.Commit()。

7、错误处理

几乎所有使用database/sql类型的操作均会通过最后一个返回值返回操作的错误信息，我们应该判断操作返回的错误信息；有一些地方的错误行为是特殊情况，也有一些额外的信息你需要去了解。

7.1 遍历结果集时发生的错误

观察以下代码：

for rows.Next() {
	// ...
}
if err = rows.Err(); err != nil {
	// handle the error here
}

rows.Err()用来获取在rows.Next()时候发生的错误，rows.Next()会因为一些情况非正常退出，因此你需要去判断循环是否正常结束。非正常退出的时候会自动调用rows.Close()，允许多次调用。

7.2 关闭结果集时发生的错误

我们需要显示调用rows.Close()来关闭结果集，有时候你可能会错误使用该方法，如下：

for rows.Next() {
	// ...
	break; // whoops, rows is not closed! memory leak...
}
// do the usual "if err = rows.Err()" [omitted here]...
// it's always safe to [re?]close here:
if err = rows.Close(); err != nil {
	// but what should we do if there's an error?
	log.Println(err)
}

之前我们讲“最好对数据库操作返回的所有异常进行捕捉并判断”，但对于rows.Close()返回的错误则例外；因为当rows.Close()返回错误时，我们并不清楚需要做什么，通常我们可以记录错误日志或抛出异常，如果对此类错误不敏感，可以选择忽略该错误。

7.3 QueryRow()发生的错误

通过以下代码查询一行结果：

var name string
err = db.QueryRow("select name from users where id = ?", 1).Scan(&name)
if err != nil {
	log.Fatal(err)
}
fmt.Println(name)

当没有id=1的记录时会怎样？此时将没有查询结果，且.Scan()不会给name赋值。

在GO中定义了一个特殊的错误sql.ErrNoRows，当结果为空时QueryRow()将返回sql.ErrNoRows。在很多时候需要对这种特殊情况进行处理；在应用层代码中，空结果通常不作为一个错误进行处理，但是如果你不检查这种情况的话可以会引起非预期的应用层代码异常；

QueryRow()时发生的错误只有在调用Scan()时才会被返回；下面的代码是一种更好的写法：

var name string
err = db.QueryRow("select name from users where id = ?", 1).Scan(&name)
if err != nil {
	if err == sql.ErrNoRows {
		// there were no rows, but otherwise no error occurred
	} else {
		log.Fatal(err)
	}
}
fmt.Println(name)

也许会有人有疑问：为什么空结果被设计为错误进行处理；空集合本身没有任何错误，原因是QueryRow()方法需要使用这种特殊的方式让调用者知道QueryRow()是否查询到了结果，没有这种设计的话，你也许不会意识到查询并没有将任何结果赋值给你提供的变量；

sql.ErrNoRows只会在使用QueryRow()时才会遇到。如果你在其他地方碰到了该错误，说明你做错了。

7.4 识别特定的数据库错误

你可能会编写以下代码：

rows, err := db.Query("SELECT someval FROM sometable")
// err contains:
// ERROR 1045 (28000): Access denied for user 'foo'@'::1' (using password: NO)
if strings.Contains(err.Error(), "Access denied") {
	// Handle the permission-denied error
}

上面的写法并不友好，因为上面的错误信息可能会，最好的方式是通过错误码来区分；

if driverErr, ok := err.(*mysql.MySQLError); ok { // Now the error number is accessible directly
	if driverErr.Number == 1045 {
		// Handle the permission-denied error
	}
}

上面的例子中使用了魔法值1045，更好的方式如下：

GitHub - VividCortex/mysqlerr: MySQL Server Error Constants for Golang 列举了MySQL中出现的错误码；

if driverErr, ok := err.(*mysql.MySQLError); ok {
	if driverErr.Number == mysqlerr.ER_ACCESS_DENIED_ERROR {
		// Handle the permission-denied error
	}
}

7.5 处理数据库连接错误

如果数据库连接被断开、终止或出现错误该怎么办？

你不需要实现任何失败重试逻辑去处理这种情况。因为失败重试逻辑被作为连接池的一部分在database/sql包中有相关实现；如果你执行查询或其他语句时发生了错误，Go将会重新打开一个新的连接（或从连接池中复用）进行重试，最多重试10次；

然而，可能会造成一些意想不到的后果。当发生其他错误条件时，可能会重试某些类型的错误。这也可能是驱动程序特有的。MySQL驱动程序的一个例子是，使用KILL取消不需要的语句(例如长时间运行的查询)会导致该语句被重试多达10次。

8、处理NULL值

NULL列的处理比较棘手且需要许多不必要的代码来兼容这种情况，应尽量避免NULL值的情况，如果避免不了可以使用database/sql包中的特殊类型去处理他们，当然你也可以定义自己的特殊类型。

对于NULL值，你可以按照下面的方式处理：

for rows.Next() {
	var s sql.NullString
	err := rows.Scan(&s)
	// check err
	if s.Valid {
	   // use s.String
	} else {
	   // NULL value
	}
}

限制可空类型以及避免可空列的原因如下：

1. 没有你想要的Null类型时你需要自己定义，如：sql.NullUint64；
2. 如果你认为某些不该为空的情况出现了NULL值，这种情况上线前就应该被发现；
3. Go的每个类型都有默认的零值，这不能作为NULL值去对待；

如果你想定义自己的NULL值，你可以参考sql.NullString的实现。

如果你无法在你的数据库中避免NULL值的出现，也可以使用COALESCE()来处理这种情况；

rows, err := db.Query(`
	SELECT
		name,
		COALESCE(other_field, '') as otherField
	WHERE id = ?
`, 42)

for rows.Next() {
	err := rows.Scan(&name, &otherField)
	// ..
	// If `other_field` was NULL, `otherField` is now an empty string. This works with other data types as well.
}

9、处理未知列

9.1 列数量未知

Scan()需要传递准确的参数数量，如果不知道查询返回的列数量时可以使用rows.Columns()；

cols, err := rows.Columns()

9.2 列类型未知

列类型未知时，可以使用sql.RawBytes。

cols, err := rows.Columns() // Remember to check err afterwards
vals := make([]interface{}, len(cols))
for i, _ := range cols {
	vals[i] = new(sql.RawBytes)
}
for rows.Next() {
	err = rows.Scan(vals...)
	// Now you can check each element of vals for nil-ness,
	// and you can use type introspection and type assertions
	// to fetch the column into a typed variable.
}

10、连接池

database/sql包实现了基本的连接池功能，但是并没有提供太多控制及检查连接池的能力；下面是一些有用的点：

• 连接池意味着一个数据库上两条连续的查询也许会打开两个分别执行他们的连接（前一个连接尚未释放且连接池中无空闲连接）；程序员通常会对他们程序执行不符合预期感到困惑，例如：LOCK TABLES后面跟着一个INSERT会造成INSERT阻塞，因为INSERT位于一个不持有表锁的连接上；
• 当需要一个连接且连接池里没有空闲连接时，新的连接将会被创建；
• 默认情况下，连接池的大小没有限制，如果你在同一时刻有大量并发调用将会创建大量数据库连接，这可能会耗尽数据库连接进而引起数据库错误，如：“too many connections.”；
• Go版本 >= 1.1，可以通过 db.SetMaxIdleConns(N) 去限制连接池中空闲连接的数量；
• Go版本 >= 1.2.1，可以通过db.SetMaxOpenConns(N)限制打开连接的总数量；
• 在连接回收比较快的情况下，可以通过设置较高的db.SetMaxIdleConns(N)来提高连接的重用；
• 连接空闲时间过长会引发一些问题，如果由于连接空闲时间过长而导致连接超时，请尝试db.SetMaxIdleConns(0)。
• 可以通过db.SetConnMaxLifetime(duration)设置连接最大时间来解决长连接存在的网络问题，惰性关闭未使用的连接。

11、其他