第15章 数据库

大多数应用程序都至少需要与一种类型的数据库打交道。SQL数据库非常常见，以至于Go标准库提供了一种统一的方式来连接和使用它们。本章将展示一些使用标准库中SQL包实现的模式。

许多数据库在功能和查询语言方面都提供了非标准的扩展。即使你使用标准库与数据库进行交互，也应该始终查看特定于数据库厂商的驱动程序，以了解潜在的限制、实现差异以及支持的SQL方言。

这里，提到非关系型数据库（NoSQL数据库）可能会有所帮助。Go标准库没有提供NoSQL数据库包。这是因为，与SQL不同，大多数NoSQL数据库都有专为特定数据库构建的非标准查询语言。为特定工作负载构建的NoSQL数据库比通用的SQL数据库性能要好得多。如果你正在使用这样的数据库，请参考其文档。不过，本章介绍的许多概念在一定程度上也适用于NoSQL数据库。

本章包含以下方法：

• 连接数据库
• 运行SQL语句
• 不使用显式事务运行SQL语句
• 使用事务运行SQL语句
• 在事务中运行预编译语句
• 从查询中获取值
• 动态构建SQL语句
• 构建UPDATE语句
• 构建WHERE子句

连接数据库

将数据库集成到应用程序中有两种方式：可以使用数据库服务器，也可以使用嵌入式数据库。我们先定义一下它们是什么。 数据库服务器作为一个独立进程，在同一主机或不同主机上运行，但与你的应用程序相互独立。通常，你的应用程序通过网络连接到这个数据库服务器，所以你必须知道它的网络地址和端口。通常，你需要将一个库导入到程序中，这是一个特定于你所使用数据库服务器的 “数据库驱动程序”。这个驱动程序通过管理连接、查询、事务等来提供应用程序与数据库之间的接口。

嵌入式数据库不是一个独立的进程。它作为一个库包含在你的应用程序中，并在相同的地址空间中运行。数据库驱动程序充当适配器，为应用程序提供标准接口（即使用database/sql包）。在使用嵌入式数据库时，你必须注意与其他进程共享的资源。许多嵌入式数据库不允许多个程序访问相同的底层数据。

在执行任何操作之前，你必须连接到数据库服务器（如MySQL或PostgreSQL服务器）或嵌入式数据库引擎（如SQLite）。

提示 此页面包含SQL驱动程序列表：https://go.dev/wiki/SQLDrivers (opens new window)。

操作方法……

找到你需要的特定于数据库的驱动程序。这个驱动程序可能由数据库厂商提供，也可能作为开源项目发布。你可以查看 https://go.dev/wiki/SQLDrivers (opens new window) 获取SQL驱动程序列表。在主包中导入这个数据库驱动程序。

连接数据库服务器或嵌入式数据库引擎时，你需要特定于驱动程序的驱动名称和连接字符串。如果你连接的是数据库服务器，这个连接字符串通常包括主机/端口信息、认证信息和连接选项。如果是嵌入式数据库引擎，连接字符串可能包含文件名/目录信息。然后，你可以调用sql.Open，或者使用特定于驱动程序的连接函数，该函数会返回一个*sql.DB。

数据库驱动程序可能会将实际连接推迟到第一次数据库操作时。也就是说，使用sql.Open连接数据库可能并不会立即建立实际连接。为确保已连接到数据库，可以使用DB.Ping。嵌入式数据库驱动程序通常不需要执行Ping操作。

以下是连接MySQL数据库的示例：

package main

import (
    "fmt"
    "database/sql"
    "context"
    // 导入mysql驱动
    _ "github.com/go-sql-driver/mysql"
)

func main() {
    // 使用mysql驱动名称和特定于驱动程序的连接字符串
    db, err := sql.Open("mysql", "username:password>@tcp(host:port)/databaseName")
    if err != nil {
        panic(err.Error())
    }
    
    defer db.Close()
    // 检查数据库连接是否成功，设置5秒超时
    ctx, cancel := context.WithTimeout(context.Background(), 5*time.Second)
    defer cancel()
    if err := db.PingContext(ctx); err != nil {
        panic(err)
    }
    
    fmt.Println("Success!")
}

以下是使用本地文件连接内存中SQLite数据库的示例：

package main

import (
    "database/sql"
    "fmt"
    "os"
    // 导入数据库驱动
    _ "github.com/mattn/go-sqlite3"
)

func main() {
    // 使用给定的本地文件./database.db打开sqlite数据库
    db, err := sql.Open("sqlite3", "./database.db")
    if err != nil {
        log.Fatal(err)
    }
    
    defer db.Close()
    // 嵌入式数据库不需要ping操作
}

提示 注意在导入数据库驱动时使用了空白标识符_。这意味着导入该包仅为了其副作用，在这种情况下，副作用是用于注册数据库驱动的init()函数。例如，在main函数中导入go-sqlite3包会使go-sqlite3中声明的init()函数以名称sqlite3将自身注册到SQL驱动程序映射中。

运行SQL语句

获取*sql.DB实例后，你可以运行SQL语句来修改或查询数据。这些查询只是SQL字符串，但不同数据库厂商的SQL风格有所不同。

不使用显式事务运行SQL语句

与数据库交互时，一个重要的考虑因素是确定事务边界。如果你需要执行单个操作，例如插入一行数据或运行一个查询，通常不需要显式创建事务。你可以执行单个SQL语句，该语句会启动并结束事务。然而，如果你有多个SQL语句，这些语句要么作为一个原子单元运行，要么都不运行，这时就必须使用事务。

操作方法……

1. 要运行SQL语句来更新数据，可以使用DB.Exec或DB.ExecContext：

result, err := db.ExecContext(ctx, `UPDATE users SET user.last_login=? WHERE user_id=?`, time.Now(), userId)
if err != nil {
    // 处理错误
}

n, err := result.RowsAffected()
if err != nil {
    // 处理错误
}

if n != 1 {
    return errors.New("Cannot update last login time")
}

要使用不同的值多次运行同一条语句，可以使用预编译语句。预编译语句通常会将语句发送到数据库服务器，在服务器上进行解析和准备。然后，你可以使用不同的参数简单地运行这条已解析的语句，绕过数据库引擎的解析和优化阶段。

使用完预编译语句后，应该关闭它：

func AddUsers(db *sql.DB, users []User) error {
    stmt, err := db.Prepare(`INSERT INTO users (user_name,email) VALUES (?,?)`)
    if err != nil {
        return err
    }
    
    // 使用完后关闭预编译语句
    defer stmt.Close()
    for _, user := range users {
        // 使用不同的参数运行预编译语句
        _, err := stmt.Exec(user.Name, user.Email)
        if err != nil {
            return err
        }
    }
}

提示 你可以在连接数据库后创建预编译语句，并在程序结束前一直使用它们。预编译语句可以由多个goroutine并发执行。

要运行返回结果的查询，可以使用DB.Query或DB.QueryContext。要运行预期最多返回一行数据的查询，可以使用DB.QueryRow或DB.QueryRowContext便捷函数。

DB.Query和DB.QueryContext方法返回一个*sql.Rows对象，它本质上是一个查询结果的单向游标。这提供了一个接口，允许你处理大型结果集，而无需将所有结果加载到内存中。数据库引擎通常会分批返回结果，*sql.Rows对象允许你逐行遍历结果，根据需要分批获取结果。

另一件需要记住的事情是，许多数据库引擎会将查询的实际执行推迟到你开始获取结果时。换句话说，仅仅因为你运行了一个查询，并不意味着服务器实际上对该查询进行了求值。查询求值可能在你获取第一行结果时发生：

func GetUserNamesLoggedInAfter(db *sql.DB, after time.Time) ([]string, error) {
    rows, err := db.Query(`SELECT users.user_name FROM users WHERE last_login > ?`, after)
    if err != nil {
        return nil, err
    }
    
    defer rows.Close()
    names := make([]string, 0)
    for rows.Next() {
        var name string
        if err := rows.Scan(&name); err != nil {
            return err
        }
        names = append(names, name)
    }
    
    // 检查迭代过程中是否产生错误
    if err := rows.Err(); err != nil {
        return nil, err
    }
    
    return names
}

如果预期的结果集最多只有一行（换句话说，你在查找一个可能存在也可能不存在的特定对象），你可以使用DB.QueryRow或DB.QueryRowContext简化上述模式。你可以通过检查返回的错误是否为sql.ErrNoRows来确定操作是否找到了该行数据：

func GetUserByID(db *sql.DB, id string) (*User, error) {
    var user User
    err := db.QueryRow(`SELECT user_id, user_name, last_login FROM users WHERE user_id=?`, id).
        Scan(&user.Id, &user.Name, &user.LastLogin)
    if errors.Is(err, sql.ErrNoRows) {
        return nil, nil
    }
    
    if err != nil {
        return nil, err
    }
    
    return &user, nil
}

在构建SQL语句时，切勿在未先进行验证的情况下，使用从用户处获取的值、从配置文件中读取的值或从API请求中接收的值。应使用查询参数来避免SQL注入攻击。

使用事务运行SQL语句

如果你需要以原子方式执行多个更新操作，则必须在事务中执行这些更新。在这种情况下，原子方式意味着要么所有更新都成功完成，要么都不完成。

事务隔离级别决定了其他并发事务如何看待在一个事务中执行的更新。你可以找到许多描述事务隔离级别的资料。这里，我将提供一个总结，帮助你确定哪种隔离级别最适合你的用例：

• sql.LevelReadUncommitted：这是最低的事务隔离级别。一个事务可能会看到另一个事务执行的未提交更改。另一个事务可能会更新已读取的数据、提交事务或回滚事务，因此无法保证读取的数据在读取后仍然正确。
• sql.ReadCommitted：一个事务仅读取另一个事务已提交的更改。这意味着如果一个事务试图读取/写入正被另一个事务修改的数据，第一个事务必须等待第二个事务完成。然而，在ReadCommitted隔离级别的事务读取数据后，另一个事务可能会更改该数据。
• sql.RepeatableRead：一个事务仅读取另一个事务已提交的更改。此外，在RepeatableRead隔离级别的事务中读取的值，在事务提交或回滚之前保证不会改变。任何其他试图修改可重复读事务读取的数据的事务，都将等待可重复读事务结束。不过，这种隔离级别并不能防止另一个事务向表中插入满足可重复读事务查询条件的行，因此使用范围查询查询同一表可能会产生不同的结果。
• sql.Serializable：这是最高的事务隔离级别。可串行化事务仅读取已提交的更改，防止其他事务修改它读取的数据，并防止其他事务插入/更新/删除与事务内执行的任何查询条件匹配的行。

随着事务隔离级别的提高，并发数据库操作会减少，这也会影响性能：较低的事务隔离级别速度更快。你必须仔细选择隔离级别：选择对操作安全的较低隔离级别。通常，如果未明确指定级别，会使用特定于驱动程序的默认隔离级别。

操作方法……

使用所需的隔离级别启动一个事务：

ctx, cancel := context.WithCancel(context.Background())
defer cancel()

// 1. 启动事务
tx, err := db.BeginTx(ctx, &TxOptions{
    Isolation: sql.LevelReadCommited,
})
if err != nil {
    // 处理错误
}

// 2. 使用defer调用回滚，以便在发生错误时回滚事务
defer tx.Rollback(context.Background())

确保事务要么提交，要么回滚。你可以通过延迟调用tx.Rollback来实现这一点。如果函数在未提交事务的情况下返回，这将导致事务回滚。如果事务成功，则提交事务。一旦事务提交，延迟的回滚操作将不会产生任何效果。

使用事务执行数据库操作。使用*sql.Tx的方法执行的所有数据库操作都将在事务内完成：

_, err := tx.Exec(`UPDATE users SET user.last_login=? WHERE user_id=?`, time.Now(), userId)
if err != nil {
    // 不提交，处理错误
}

如果没有错误，则提交事务：

tx.Commit(ctx)

提示 当查询由于约束冲突（例如唯一索引上的重复值）而无法完成时，一些数据库驱动程序可能会回滚并取消事务。查看你的驱动程序文档，了解它是否会执行自动回滚。

在事务中运行预编译语句

可以通过调用事务结构体（transaction struct）的sql.Tx.Prepare或sql.Tx.PrepareContext方法来准备一条语句。这两个方法返回的预编译语句将仅与该事务相关联。也就是说，不能使用一个事务准备一条语句，然后将该语句用于另一个事务。

操作方法……

在事务中使用预编译语句有两种方式。第一种是使用由*DB准备的语句：

1. 使用DB.Prepare或DB.PrepareContext准备语句。
2. 获取事务特定的事务副本：

txStmt := tx.Stmt(stmt)

3. 使用新语句运行操作。

第二种是使用由*Tx准备的语句：

1. 使用Tx.Prepare或Tx.PrepareContext准备语句。
2. 使用该语句运行操作。

从查询中获取值

SQL查询返回*sql.Rows，如果使用QueryRow方法，则返回*sql.Row。接下来要做的是遍历这些行，并将值扫描到Go变量中。

操作方法……

运行Query或QueryContext意味着期望从查询中得到零行或多行数据。因此，它返回*sql.Rows。

在本节的代码片段中，我们使用以下User结构体：

type User struct {
    ID        uint64
    Name      string
    LastLogin time.Time
    AvatarURL string
}

它与以下表定义一起使用：

CREATE TABLE users (
    user_id    int         not null,
    user_name  varchar(32) not null,
    last_login timestamp null,
    avatar_url varchar(128) null
);

遍历这些行并处理每一个结果行。在下面的示例中，查询返回零行或多行数据。对rows.Next的第一次调用会移动到结果集的第一行，随后对rows.Next的每次调用都会移动到下一行。这使得可以使用for语句，如下例所示：

rows, err := db.Query(`SELECT user_id, user_name, last_login, avatar_url FROM users WHERE last_login >?`, after)
if err!= nil {
    return err
}

// 完成后关闭rows对象
defer rows.Close()
for rows.Next() {
    // 从此行检索数据
}

对于每一行，使用Scan将数据复制到Go变量中：

users := make([]User, 0)
for rows.Next() {
    // 从此行检索数据
    var user User
    // avatar列可为空，因此我们传递*string而不是string
    var avatarURL *string
    if err := rows.Scan(
        &user.ID,
        &user.Name,
        &user.LastLogin,
        &avatarURL); err!= nil {
        return err
    }
    // 数据库中的avatar URL可以为nil
    if avatarURL!= nil {
        user.AvatarURL = *avatarURL
    }
   
    users = append(users, user)
}

Scan的参数顺序必须与从SELECT语句中检索到的列顺序相匹配。也就是说，第一个参数&user.ID对应user_id列；下一个参数&user.Name对应user_name列，依此类推。因此，Scan的参数数量必须等于获取的列数。

SQL驱动程序会将数据库原生类型转换为Go数据类型。如果转换导致数据或精度丢失，驱动程序通常会返回一个错误。例如，如果试图将一个大整数的值扫描到一个int16变量中，而转换无法表示该值，Scan就会返回一个错误。如果数据库列被定义为可为空（在这个例子中，avatar_url varchar(128) NULL），并且从数据库中检索到的数据值为空，那么Go值必须能够容纳空值。例如，如果在Scan中使用&user.AvatarURL，而数据库中的值为空，那么Scan会返回一个错误，抱怨空值不能扫描到字符串中。为了避免这类错误，我们使用*string而不是string。一般来说，如果底层数据库列可为空，在Scan中对该列应该使用指针。在获取所有行之后检查错误：

// 检查迭代过程中是否有错误
if err := rows.Err(); err!= nil {
    return err
}

关闭*sql.Rows。通常像前面那样使用defer rows.Close()语句来完成。运行QueryRow或QueryRowContext意味着期望从查询中得到零行或一行数据。然后，返回一个*sql.Row对象，可用于扫描值并检查错误。

运行QueryRow或QueryRowContext，并像前面描述的那样扫描值：

var user User
row := db.QueryRow(`SELECT user_id, user_name, last_login, avatar_url FROM users WHERE user_id =?`, id)
if err := row.Scan(
    &user.ID,
    &user.Name,
    &user.LastLogin,
    &avatarURL); err!= nil {
    return err
}

return user

如果在查询执行过程中出现错误，它将由row返回。

动态构建SQL语句

在任何使用SQL数据库的重要应用程序中，都必须动态构建SQL语句。在以下情况下，这变得很有必要：

• 使用灵活的搜索条件，这些条件可能会根据用户输入或请求而变化。
• 根据请求的字段选择性地连接多个表。
• 选择性地更新部分列。
• 插入数量可变的列。

本节展示了针对不同用例构建SQL语句的几种常见方法。

提示 有许多开源的查询构建器包。在编写自己的代码之前，你可能想探索一下这些包。

构建UPDATE语句

如果需要更新表中的某些列，同时不修改其他列，可以遵循本节给出的模式。

操作方法……

1. 运行UPDATE语句需要两部分信息：

   • 更新的数据：描述此类信息的一种常见方式是使用指针来表示更新的值。考虑以下示例：

   type UpdateUserRequest struct {
       Name      *string
       LastLogin *time.Time
       AvatarURL *string
   }
   

   在这里，只有当相应字段不为空时，列才会被更新。例如，对于以下UpdateUserRequest实例，只有LastLogin和AvatarURL字段会被更新：

   now := time.Now()
   urlString := "https://example.org/avatar.jpg"
   update := UpdateUserRequest{
       LastLogin: &now,
       AvatarURL: &urlString,
   }
   

   • 记录定位器：这通常是需要更新的行的唯一标识符。不过，使用一个可以定位多条记录的查询也是很常见的。

   有了这些信息，编写更新函数的一种常见方式如下：

   func UpdateUser(ctx context.Context, userId uint64, req *UpdateUserRequest) error {
   	...
   }
   

   在前面的代码中，记录定位器是userId。

   • 使用strings.Builder构建语句，同时在一个切片中跟踪查询参数：

   query := strings.Builder{}
   args := make([]interface{}, 0)
   // 开始构建查询。注意用空格分隔查询子句
   query.WriteString("UPDATE users SET ")
   

2. 为每个需要更新的列创建一个SET子句：

if req.Name != nil {
    args = append(args, *req.Name)
    query.WriteString("user_name=?")
}

if req.LastLogin != nil {
    if len(args) > 0 {
        query.WriteString(",")
    }
    args = append(args, *req.LastLogin)
    query.WriteString("last_login=?")
}

if req.AvatarURL != nil {
    if len(args) > 0 {
        query.WriteString(",")
    }
    args = append(args, *req.AvatarURL)
    query.WriteString("avatar_url=?")
}

3. 添加WHERE子句：

query.WriteString(" WHERE user_id=?")
args = append(args, userId)

4. 运行语句：

_, err := db.ExecContext(ctx, query.String(), args...)

并非所有数据库驱动程序都使用?作为查询参数。例如，其中一个Postgres驱动程序使用$n，其中n是从1开始的数字，表示参数的顺序。对于这类驱动程序，算法略有不同：

if req.Name != nil {
    args = append(args, *req.Name)
    fmt.Fprintf(&query, "user_name=$%d", len(args))
}

if req.LastLogin != nil {
    if len(args) > 0 {
        query.WriteString(",")
    }
    args = append(args, *req.LastLogin)
    fmt.Fprintf(&query, "last_login=$%d", len(args))
}

if req.AvatarURL != nil {
    if len(args) > 0 {
        query.WriteString(",")
    }
    args = append(args, *req.AvatarURL)
    fmt.Fprintf(&query, "avatar_url=$%d", len(args))
}

构建WHERE子句

WHERE子句可以是SELECT、UPDATE或DELETE语句的一部分。这里，我将展示一个SELECT的示例，你可以将其扩展应用于UPDATE和DELETE语句。要注意参数，因为UPDATE语句还会包含用于更新列值的参数。

操作方法……

这个示例展示了在搜索条件中使用AND的情况：

1. 需要一个数据结构来指定WHERE子句中包含哪些列。以下面的示例为例：

type UserSearchRequest struct {
    Ids            []uint64
    Name           *string
    LoggedInBefore *time.Time
    LoggedInAfter  *time.Time
    AvatarURL      *string
}

有了这个结构，搜索函数如下所示：

func SearchUsers(ctx context.Context, req *UserSearchRequest) ([]User, error) {
   ...
}

2. 使用strings.Builder构建语句部分，同时在一个切片中跟踪查询参数：

query := strings.Builder{}
where := strings.Builder{}
args := make([]interface{}, 0)
// 开始构建查询。注意用空格分隔查询子句
query.WriteString("SELECT user_id, user_name, last_login, avatar_url FROM users ")

3. 为每个搜索项构建一个谓词：

if len(req.Ids) > 0 {
    // 使用AND将其添加到WHERE子句中
    if where.Len() > 0 {
        where.WriteString(" AND ")
    }
    
    // 构建一个IN子句。
    // 我们必须为每个id添加一个参数
    where.WriteString("user_id IN (")
    for i, id := range req.Ids {
        if i > 0 {
            where.WriteString(",")
        }
        args = append(args, id)
        where.WriteString("?")
    }
    
    where.WriteString(")")
}

if req.Name != nil {
    if where.Len() > 0 {
        where.WriteString(" AND ")
    }
    args = append(args, *req.Name)
    where.WriteString("name=?")
}

if req.LoggedInBefore != nil {
    if where.Len() > 0 {
        where.WriteString(" AND ")
    }
    args = append(args, *req.LoggedInBefore)
    where.WriteString("last_login<?")
}

if req.LoggedInAfter != nil {
    if where.Len() > 0 {
        where.WriteString(" AND ")
    }
    
    args = append(args, *req.LoggedInAfter)
    where.WriteString("last_login>?")
}

if req.AvatarURL != nil {
    if where.Len() > 0 {
        where.WriteString(" AND ")
    }
    args = append(args, *req.AvatarURL)
    where.WriteString("avatar_url=?")
}

4. 构建并运行查询：

if where.Len() > 0 {
    query.WriteString(" WHERE ")
    query.WriteString(where.String())
}
rows, err := db.QueryContext(ctx, query.String(), args...)

同样，并非所有数据库驱动程序都使用?标记。如果你的数据库驱动程序属于此类，请参阅上一节了解替代方法。