概述

sql.DB 不是一个连接，它是数据库的抽象接口。它可以根据 driver 打开关闭数据库连接，管理连接池。正在使用的连接被标记为繁忙，用完后回到连接池等待下次使用。所以，如果你没有把连接释放回连接池，会导致过多连接使系统资源耗尽。

使用 DB

导入 driver

这里使用的是 MySQL drivers

1
2
3
4
import (
    "database/sql"
    _ "github.com/go-sql-driver/mysql"
)

连接 DB

1
2
3
4
5
6
7
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()
}

sql.Open 的第一个参数是 driver 名称，第二个参数是 driver 连接数据库的信息，各个 driver 可能不同。DB 不是连接，并且只有当需要使用时才会创建连接，如果想立即验证连接，需要用 Ping() 方法，如下：

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

sql.DB 的设计就是用来作为长连接使用的。不要频繁 Open，Close。

比较好的做法是，为每个不同的 datastore 建一个 DB 对象，保持这些对象 Open。如果需要短连接，那么把 DB 作为参数传入 function，而不要在 function 中 Open, Close。

读取 DB

如果方法包含 Query，那么这个方法是用于查询并返回 rows 的。其他情况应该用 Exec()。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
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)
}

上面代码的过程为：db.Query() 表示向数据库发送一个 query，defer rows.Close() 非常重要，遍历 rows 使用 rows.Next()， 把遍历到的数据存入变量使用 rows.Scan(), 遍历完成后检查 error。有几点需要注意：

  1. 检查遍历是否有 error
  2. 结果集 (rows) 未关闭前，底层的连接处于繁忙状态。当遍历读到最后一条记录时，会发生一个内部 EOF 错误，自动调用 rows.Close()，但是如果提前退出循环，rows 不会关闭，连接不会回到连接池中，连接也不会关闭。所以手动关闭非常重要。rows.Close() 可以多次调用，是无害操作。

单行 Query

err 在 Scan 后才产生，所以可以如下写：

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

修改数据，事务

一般用 Prepared Statements 和 Exec() 完成 INSERT, UPDATE, DELETE 操作。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
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)

事务

db.Begin() 开始事务，Commit() 或 Rollback() 关闭事务。Tx 从连接池中取出一个连接，在关闭之前都是使用这个连接。Tx 不能和 DB 层的 BEGIN, COMMIT 混合使用。

如果你需要通过多条语句修改连接状态，你必须使用 Tx，例如：

  • 创建仅对单个连接可见的临时表
  • 设置变量，例如 SET @var := somevalue
  • 改变连接选项，例如字符集，超时

Prepared Statements

Prepared Statements and Connection

在数据库层面，Prepared Statements 是和单个数据库连接绑定的。客户端发送一个有占位符的 statement 到服务端，服务器返回一个 statement ID，然后客户端发送 ID 和参数来执行 statement。

在 GO 中，连接不直接暴露，你不能为连接绑定 statement，而是只能为 DB 或 Tx 绑定。database/sql 包有自动重试等功能。当你生成一个 Prepared Statement

  1. 自动在连接池中绑定到一个空闲连接
  2. Stmt 对象记住绑定了哪个连接
  3. 执行 Stmt 时，尝试使用该连接。如果不可用，例如连接被关闭或繁忙中，会自动 re-prepare，绑定到另一个连接。

这就导致在高并发的场景，过度使用 statement 可能导致 statement 泄漏，statement 持续重复 prepare 和 re-prepare 的过程，甚至会达到服务器端 statement 数量上限。

某些操作使用了 PS，例如 db.Query(sql, param1, param2), 并在最后自动关闭 statement。

有些场景不适合用 statement：

  1. 数据库不支持。例如 Sphinx，MemSQL。他们支持 MySQL wire protocol, 但不支持 “binary” protocol。
  2. statement 不需要重用很多次，并且有其他方法保证安全。例子

在 Transaction 中使用 PS

PS 在 Tx 中唯一绑定一个连接，不会 re-prepare。

Tx 和 statement 不能分离，在 DB 中创建的 statement 也不能在 Tx 中使用，因为他们必定不是使用同一个连接使用 Tx 必须十分小心，例如下面的代码：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
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!

*sql.Tx 一旦释放，连接就回到连接池中，这里 stmt 在关闭时就无法找到连接。所以必须在 Tx commit 或 rollback 之前关闭 statement。

处理 Error

循环 Rows 的 Error

如果循环中发生错误会自动运行 rows.Close()，用 rows.Err() 接收这个错误，Close 方法可以多次调用。循环之后判断 error 是非常必要的。

1
2
3
4
5
6
for rows.Next() {
    // ...
}
if err = rows.Err(); err != nil {
    // handle the error here
}

关闭 Resultsets 时的 error

如果你在 rows 遍历结束之前退出循环，必须手动关闭 Resultset，并且接收 error。

1
2
3
4
5
6
7
8
9
10
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)
}

QueryRow () 的 error

1
2
3
4
5
6
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 的不存在，err 为 sql.ErrNoRows，一般应用中不存在的情况都需要单独处理。此外，Query 返回的错误都会延迟到 Scan 被调用，所以应该写成如下代码：

1
2
3
4
5
6
7
8
9
10
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)

把空结果当做 Error 处理是为了强行让程序员处理结果为空的情况

分析数据库 Error

各个数据库处理方式不太一样，mysql 为例：

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

MySQLError, Number 都是 DB 特异的，别的数据库可能是别的类型或字段。这里的数字可以替换为常量，例如这个包 MySQL error numbers maintained by VividCortex

连接错误

NULL 值处理

简单说就是设计数据库的时候不要出现 null，处理起来非常费力。Null 的 type 很有限，例如没有 sql.NullUint64; null 值没有默认零值。

1
2
3
4
5
6
7
8
9
10
for rows.Next() {
    var s sql.NullString
    err := rows.Scan(&s)
    // check err
    if s.Valid {
       // use s.String
    } else {
       // NULL value
    }
}

未知 Column

rows.Columns() 的使用，用于处理不能得知结果字段个数或类型的情况，例如：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
cols, err := rows.Columns()
if err != nil {
    // handle the error
} else {
    dest := []interface{}{ // Standard MySQL columns
        new(uint64), // id
        new(string), // host
        new(string), // user
        new(string), // db
        new(string), // command
        new(uint32), // time
        new(string), // state
        new(string), // info
    }
    if len(cols) == 11 {
        // Percona Server
    } else if len(cols) > 8 {
        // Handle this case
    }
    err = rows.Scan(dest...)
    // Work with the values in dest
}
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.
}

关于连接池

  1. 避免错误操作，例如 LOCK TABLE 后用 INSERT 会死锁，因为两个操作不是同一个连接，insert 的连接没有 table lock。
  2. 当需要连接，且连接池中没有可用连接时，新的连接就会被创建。
  3. 默认没有连接上限，你可以设置一个，但这可能会导致数据库产生错误 “too many connections”
  4. db.SetMaxIdleConns(N) 设置最大空闲连接数
  5. db.SetMaxOpenConns(N) 设置最大打开连接数
  6. 长时间保持空闲连接可能会导致 db timeout