Go 访问数据库

开启掘金成长之旅！这是我参与「掘金日新计划 · 12 月更文挑战」的第4天，点击查看活动详情

本文主要介绍使用标准库database/sql包访问数据库的相关知识。

使用database/sql包访问数据库的方法：

• 打开一个实际代表着连接池的database handle；
• 使用handle执行数据库访问操作；
• 只需要在释放资源的时候调用Close方法；

导入数据库驱动

除了database/sql包之外，还需要导入使用的数据库对应的驱动，例如使用MySQL：

import "github.com/go-sql-driver/mysql"

注意，如果代码中没有直接调用驱动包中的任何函数，那么需要使用blank import，也就是：

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

实际上也不推荐使用驱动包中的API来进行数据库操作，相反，应该使用database/sql包来操作，实现代码与DBMS的解耦，方便在需要的时候替换成其它DBMS。

打开database handle

方法一：使用sql.Open函数和Connection string

• 一种做法是直接使用 connection string

db, err = sql.Open("mysql", "username:password@tcp(127.0.0.1:3306)/jazzrecords")
if err != nil {
    log.Fatal(err)
}

• 另一种做法使用结构体（数据库驱动中的Config），更具可读性

cfg := mysql.Config{
    User:   username,
    Passwd: password,
    Net:    "tcp",
    Addr:   "127.0.0.1:3306",
    DBName: "jazzrecords",
}

// Get a database handle.
db, err = sql.Open("mysql", cfg.FormatDSN())
if err != nil {
    log.Fatal(err)
}

方法二：使用sql.OpenDB和Connector

• 这种方法可以实现connection string无法实现的功能，例如驱动支持一些额外的连接属性，通过这种方法可以进行配置。

cfg := mysql.Config{
    User:   username,
    Passwd: password,
    Net:    "tcp",
    Addr:   "127.0.0.1:3306",
    DBName: "jazzrecords",
}

// Get a driver-specific connector.
connector, err := mysql.NewConnector(&cfg)
if err != nil {
    log.Fatal(err)
}

// Get a database handle.
db = sql.OpenDB(connector)

确认连接

当使用前面的方法打开一个database handle时，sql包可能不会立刻创建一个新的数据库连接，而是在代码真正需要的时候再创建。如果想确认一下连接建立，使用：

db, err = sql.Open("mysql", connString)

// Confirm a successful connection.
if err := db.Ping(); err != nil {
    log.Fatal(err)
}

此外，应当避免在代码中保存数据库的凭证，有泄露给他人的风险。一种流行的做法是在程序运行前保存在环境变量，然后用代码读取环境变量。

username := os.Getenv("DB_USER")
password := os.Getenv("DB_PASS")

释放资源

使用database/sql包进行编程时不需要显式的管理和关闭连接，但是需要你手动释放那些获得的资源，例如查询返回的sql.Rows或者sql.Stmt。 方法是使用defer

rows, err := db.Query("SELECT * FROM album WHERE artist = ?", artist)
if err != nil {
    log.Fatal(err)
}
defer rows.Close()

执行不返回数据的SQL语句

使用Exec或ExecContex执行INSERT, DELETE, UPDATE语句。 其它函数：

函数	描述
DB.Exec
DB.ExecContext	执行单个SQL语句
Tx.Exec
Tx.ExecContext	在更大的事务中执行 SQL 语句
Stmt.Exec
Stmt.ExecContext	执行一个 prepared SQL语句
Conn.ExecContext	用于保留的连接

查询数据

总体方法：使用Query方法得到返回的Row或者Rows，然后使用Scan方法拷贝到变量中。

• 使用QueryRow查询一行数据
• 使用Query查询多行数据，可以通过Rows.Next 迭代获取每一行

处理可为空的列值

database/sql包提供了一些特殊的类型，用于Scan函数遇到空列值的情况，这些类型有Valid字段来判断是否真的为空。

• sql.NullBool/sql.NullFloat64/sql.NullInt32/sql.NullInt64/sql.NullString/sql.NullTime

var s sql.NullString
err := db.QueryRow("SELECT name FROM customer WHERE id = ?", id).Scan(&s)
if err != nil {
    log.Fatal(err)
}

// Find customer name, using placeholder if not present.
name := "Valued Customer"
if s.Valid {
    name = s.String
}

处理多个结果集合

使用Rows.NextResultSet会准备下一个结果集合，因此Rows.Next会从下个集合的第一行开始。

rows, err := db.Query("SELECT * from album; SELECT * from song;")
if err != nil {
    log.Fatal(err)
}
defer rows.Close()

// Loop through the first result set.
for rows.Next() {
    // Handle result set.
}

// Advance to next result set.
rows.NextResultSet()

// Loop through the second result set.
for rows.Next() {
    // Handle second set.
}

// Check for any error in either result set.
if err := rows.Err(); err != nil {
    log.Fatal(err)
}

使用prepared statements

Prepared statements是一种由DBMS解析并保存的SQL语句，通常包含没有实际参数值的占位符。这些语句可以在之后使用对应的参数执行。 在需要重复使用某些语句的情况下使用 prepared statements，避免重复创建语句的开销，加快代码的运行速度。

func AlbumByID(id int) (Album, error) {
    // Define a prepared statement. You'd typically define the statement
    // elsewhere and save it for use in functions such as this one.
    stmt, err := db.Prepare("SELECT * FROM album WHERE id = ?")
    if err != nil {
        log.Fatal(err)
    }

    var album Album

    // Execute the prepared statement, passing in an id value for the
    // parameter whose placeholder is ?
    err := stmt.QueryRow(id).Scan(&album.ID, &album.Title, &album.Artist, &album.Price, &album.Quantity)
    if err != nil {
        if err == sql.ErrNoRows {
            // Handle the case of no rows returned.
        }
        return album, err
    }
    return album, nil
}

• 创建一个prepared statement：通过对一个给定的SQL文本调用DB.Prepare，返回一个sql.Stmt
• 运行：向Stmt.Exec,Stmt.QueryRow,Stmt.Query传递参数来执行
• 确保代码结束时调用stmt.Close

执行事务

数据库的事务（transaction）将多个操作组合在一起，所有操作要么全部执行，要么全不执行。

• 开始一个事务。使用DB.Begin或DB.BeginTx开始，返回sql.Tx
• 执行一系列数据库操作。可以使用sql.Tx的Exec,ExecContext,Query,QueryContext,QueryRow,QueryRowContext,Prepare,PrepareContext等执行查询或更新数据库。
• 最后以下面两种方式之一结束事务：
  • 提交事务Tx.Commit。如果提交成功，所有查询结果都是有效的，所有更新操作将以原子操作持久化到数据库；如果提交失败，所有查询结果都应该丢弃。
  • 回滚事务Tx.Rollback。
• 注意：使用事务的时候不要使用sql.DB调用非事务的方法！

// CreateOrder creates an order for an album and returns the new order ID.
func CreateOrder(ctx context.Context, albumID, quantity, custID int) (orderID int64, err error) {

    // Create a helper function for preparing failure results.
    fail := func(err error) (int64, error) {
        return fmt.Errorf("CreateOrder: %v", err)
    }

    // Get a Tx for making transaction requests.
    tx, err := db.BeginTx(ctx, nil)
    if err != nil {
        return fail(err)
    }
    // Defer a rollback in case anything fails.
    defer tx.Rollback()

    // Confirm that album inventory is enough for the order.
    var enough bool
    if err = tx.QueryRowContext(ctx, "SELECT (quantity >= ?) from album where id = ?",
        quantity, albumID).Scan(&enough); err != nil {
        if err == sql.ErrNoRows {
            return fail(fmt.Errorf("no such album"))
        }
        return fail(err)
    }
    if !enough {
        return fail(fmt.Errorf("not enough inventory"))
    }

    // Update the album inventory to remove the quantity in the order.
    _, err = tx.ExecContext(ctx, "UPDATE album SET quantity = quantity - ? WHERE id = ?",
        quantity, albumID)
    if err != nil {
        return fail(err)
    }

    // Create a new row in the album_order table.
    result, err := tx.ExecContext(ctx, "INSERT INTO album_order (album_id, cust_id, quantity, date) VALUES (?, ?, ?, ?)",
        albumID, custID, quantity, time.Now())
    if err != nil {
        return fail(err)
    }
    // Get the ID of the order item just created.
    orderID, err := result.LastInsertId()
    if err != nil {
        return fail(err)
    }

    // Commit the transaction.
    if err = tx.Commit(); err != nil {
        return fail(err)
    }

    // Return the order ID.
    return orderID, nil
}

• Defer了事务的回滚
• 如果事务成功，会在函数退出前提交，导致回滚操作调用一个no-op