Go语言中常见100问题-#78 SQL common mistakes

SQL操作常见的错误

Go标准库database/sql提供一系列操作数据库的通用接口，开发者在使用这个包时通常存在不当的模式使用，本文将深入研究五个常见的错误。

忘了sql.Open不一定会建立与数据库的连接

使用sql.Open时，一个常见的误解是期望通过调用这个函数建立到数据库的连接。

db, err := sql.Open("mysql", dsn)
if err != nil {
        return err
}

然而，情况不一定是这样的，根据sql.Open documentation官方文档介绍, Open可能只是验证其参数而不创建与数据库的连接。实际中，Open行为取决于所使用的SQL驱动程序。对于某些驱动程序，sql.Open不建立任何连接，它只是为后续使用做准备，例如后续使用db.Query. 因此，与DB的第一个连接可能会延迟建立。

❝Open may just validate its arguments without creating a connection to the database. ❞

了解上面sql.Open这种行为非常重要，因为在某些情况下，我们希望只有在知道所有依赖项都已正确设置且可以访问后才能准备好服务。否则的话，尽管配置有错误，服务可能会接收一些流量，这不是我们期望的。因此，如果我们在使用sql.Open时也想保证底层DB是可达的，应该使用Ping方法。示例代码如下：

db, err := sql.Open("mysql", dsn)
if err != nil {
        return err
}
if err := db.Ping(); err != nil {
        return err
}

通过Ping强制建立连接，可以确保数据源名称有效且数据库是可以访问的。有一点需要了解，就是在Ping的时候希望可以取消或超时终止，可以使用PingContext取代Ping,该方法支持传入一个上下文参数，示例代码如下：

db, err = sql.Open("mysql", dsn)
if err != nil {
   log.Fatal(err)
}

ctx, cancel := context.WithTimeout(context.Background(), 10*time.Second)
defer cancel()

if err = db.PingContext(ctx); err != nil {
    log.Fatal(err)
}

sql.Open使用可能有点违反直觉，调用它不一定建立任何连接，使得第一个连接可能被延迟建立，我们应该牢记这一点。如果想测试配置是否正确并且DB是可以访问的，应该在sql.Open之后调用Ping或PingContext方法。

忘了DB连接池

了解Go中如何处理DB连接非常重要。sql.Open返回一个*sql.DB结构对象，该结构不表示单个数据库连接，它代表一个连接池。值得注意的是，我们不用自己再手动实现这种连接池功能。连接池中的一个连接可以有如下两种状态：

• 已经被使用状态，例如，连接被另一个查询的goroutine使用
• 空闲状态，创建的连接暂时未被任何人使用

此外，需要记住创建连接池有四个配置参数。每个参数的设置都提供了对外的方法。具体方法及含义如下：

• SetMaxOpenConns：设置池中与数据打开的最大连接数，默认不限制连接数量。一般来说，该值设置的越大，可以并发执行的数据库查询就越多。
• SetMaxIdleConns：设置池中最大空闲连接数，默认值是2. 理论上有更多的空闲连接可以减少从头建立新连接的概率，建立连接的过程比较耗时。但是过多的空闲连接会浪费内存占用。如果一个连接空闲时间过长，它也可能变得不可用。MySQL默认会自动关闭8小时未使用的连接。
• SetConnMaxIdleTime：设置池中连接在关闭之前可用空闲的最长时间，默认是不限制时间。如果设置为2小时，表示池中自上次使用以后在池中空闲了2小时的连接将标为过期被清理。
• SetConnMaxLifetime：设置池中连接关闭前可以保持打开的最长时间，默认是不限制时间。

下图是一个最多有五个连接的示例，其中有四个正在进行的连接：三个空闲和一个已在使用中。剩余一个占位插槽（empty connection slot)可用于创建额外连接。如果有一个新的查询请求，将从空闲连接中选一个连接（如果仍然可用）。如果没有空闲连接但有占位插槽，连接池将创建一个新连接，分配给新的查询请求使用。如果也没有占位插槽，新的查询请求将等待连接可用。

上面四个配置参数已有默认配置，为什么还需要调整这些配置参数呢？

设置SetMaxOpenConns对于生产环境中的程序非常重要。由于该参数的默认值为不限制连接，在生产环境中我们应将该值设置一个合理的值，以确保适合数据库可以处理的能力。

如果我们的应用程序要处理大量并发请求，则应增加SetMaxIdleConns的值（它的默认值为2），否则可能会导致频繁的创建新的连接。

如果我们的应用程序可能面临大量的请求，设置SetConnMaxIdleTime就很关键。当这些大量请求处理完成之后，希望创建的连接能够确保最终被释放掉。

合理的设置SetConnMaxLifetime值也是很重要的，例如，如果我们的连接到的是有负载均衡的DB服务器，希望应用程序永远不会使用连接太长时间，在这种情况下，设置合适的SetConnMaxLifetime值很有用。

总结，对于生产级应用，需要考虑对上面的四个配置参数设置合适的值。另外，如果一个应用程序面临多种不同的情况，可以考虑创建多个连接池。

不使用prepared statement功能

prepared statement是很多数据库都支持的功能，特别是在执行重复SQL语句的时候。它可以将SQL语句预编译并与提供的数据分离，主要有两个好处：

• 提高效率：语句不用重新编译，比如用于执行带有WHERE条件的SELECT时，只需要每次修改变量值即可。
• 提供安全性：降低了SQL注入攻击的风险。

因此，如果一个语句被重复执行，我们应该使用prepared statement，另外在不受信任的上下文中，也应该使用它，例如在直接将互联网上的请求映射到SQL语句进行操作的时候。要使用prepared statement，我们必须先调用Prepare方法，而不是直接调用*sql.DB的Query方法。

stmt, err := db.Prepare("SELECT * FROM ORDER WHERE ID = ?")
if err != nil {
        return err
}
rows, err := stmt.Query(id)
// ...

示例代码如上，首先执行prepared statement，即执行db.Prepare，然后将要查询的参数id传给它执行。Prepare方法的第一个返回值是一个*sql.Stmt类型的对象，它可以重复使用并能够在并发环境中使用。有一点需要注意，当不再需要执行该语句时，必须使用Close()方法将其关闭。

「NOTE: Prepare和Query方法还提供有一个带有上下文的版本，它们是PrepareContext和QueryContext方法。」

为了提供效率和安全性，在必要的时候我们应该使用prepared statement。

错误处理数据表中含有NULL值问题

本小节将讨论查询操作返回 converting NULL问题。下面通过一个具体的例子进行说明，该程序会查询员工部门和年龄。

rows, err := db.Query("SELECT DEP, AGE FROM EMP WHERE ID = ?", id)
if err != nil {
        return err
}
// Defer closing rows

var (
        department string
        age int
)
for rows.Next() {
        err := rows.Scan(&department, &age)
        if err != nil {
                return err
        }
        // ...
}

我们使用Query语句进行查询，然后遍历查询的每行用Scan方法将每列的内容赋值到department和age中。如果运行这段程序，可能会在调用Scan时接收到如下错误。

2021/10/29 17:58:05 sql: Scan error on column index 0, name "DEPARTMENT":
converting NULL to string is unsupported

是SQL驱动程序引发错误，因为部门值为NULL. 如果数据库中某列的值可以为NULL, 有两种方法可以防止Scan返回错误。

第一种方法是将部门申明为字符串指针,代码如下。传递给Scan的第一个参数是指针变量的地址，而不是字符串类型变量的地址。如果部门值为NULL, 则department的值为nil, 不会返回错误信息。

var (
        department *string
        age        int
)
for rows.Next() {
        err := rows.Scan(&department, &age)
        // ...
}

第二种处理方法是使用sql.NullXXX类型，例如部门是一个字符串，则将department定义为sql.NullString类型。

var (
        department sql.NullString
        age        int
)
for rows.Next() {
        err := rows.Scan(&department, &age)
        // ...
}

sql.NullString类型是对string类型的包装，包含两个可导出字段：存储字符串值的字符串字段和表示字符串是否不为NULL的Valid字段。具体的sql.NullXXX类型有如下几种。

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

上述两种处理方法都是有效的。采用sql.NullXXX方法更清楚地表达了程序处理意图。正如Go核心维护者Russ Cox提到的, 两种处理方式没有实质区别，认为开发者可能想要使用NullString, 因为它可能比*string更有表达能力。但是，使用任何一种都是可以的。

❝There’s no effective difference. We thought people might want to use NullString because it is so common and perhaps expresses the intent more clearly than *string. But either will work. ❞

总结，我们应该牢记，在处理数据库中含有NULL值列的时候最佳实践是使用sql.NullXXX类型或者使用指针。

忽视rows迭代时错误

另一个常见的错误是在遍历行时遗漏了可能的错误。下面通过一个程序进行说明，代码如下。

func get(ctx context.Context, db *sql.DB, id string) (string, int, error) {
        rows, err := db.QueryContext(ctx,
                "SELECT DEP, AGE FROM EMP WHERE ID = ?", id)
        if err != nil {
                return "", 0, err
        }
        defer func() {
                err := rows.Close()
                if err != nil {
                        log.Printf("failed to close rows: %v\n", err)
                }
        }()

        var (
                department string
                age        int
        )
        for rows.Next() {
                err := rows.Scan(&department, &age)
                if err != nil {
                        return "", 0, err
                }
        }

        return department, age, nil
}

在上述函数中，我们处理了三个地方的错误：执行查询时、关闭查询时和扫描行遇到错误时。然而，只有这三个地方的处理是还不够的。因为for rows.Next()循环可能会在没有更多行时或在准备下一行时发生错误而终止循环，所以在循环语句之后，应该调用rows.Err方法来判断这两种情况。正确示例代码如下:

func get(ctx context.Context, db *sql.DB, id string) (string, int, error) {
        // ...
        for rows.Next() {
                // ...
        }

        if err := rows.Err(); err != nil {
                return "", 0, err
        }

        return department, age, nil
}

总结，因为rows.Next会在迭代完所有行或在准备下一行时发生错误而停止，我们应该牢记处理rows.Next的最佳实践：在循环之后通过rows.Err()检查是否存在错误。