database/sql 使用注意事项

database/sql 是一个面向行的数据库的轻量级的接口,go会使用sql.DB这个结构体类型创建statements,transactions 执行查询操作并获取返回结果。

首先sql.DB不是一个数据库链接。它管理了一个链接池，是并发安全的。链接被使用的时候会被标记，等到使用完了之后重新放回freeConn链接池,如果没有成功释放链接会造成(太多的连接、太多的打开文件句柄、缺少可用的网络端口)等错误。

获取sql.DB 我们一般这样写:

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 与直觉相反，它不创建任何链接,也不校验链接参数,它与底层数据库链接的建立是惰性的，只有第一次使用链接的时候才会真正被创建，如果想立即知道数据库的可用性，可以使用Ping：

err = db.Ping()

if err != nil {

// do something here

}

sql.DB设计成一个长生命周期的对象，不要频繁的打开关闭数据库，可以针对不同的DB创建不用的sql.DB 全局的单例对象,否则可能会出现网络链接重用性差，网络资源耗尽,或者出现大量的TIME_WAIT的TCP网络链接。

获取执行结果几种方式:

1,直接执行一个查询返回1行或者多行数据

2,prepare a statement多次使用，最后销毁

3,不预先prepare 直接执行statement

使用示例:

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)}

常见的几种错误:

1,rows.Next()遍历完之后要用 rows.Err() 检查错误,确认正确遍历了所有行。

2,在迭代rows里面的结果集的时候底层链接是被占用的，该链接在链接池中还不可用,如果忘记调用 rows.Close() 底层链接仍然被占用,这样很容易耗尽资源。

3,如果rows已经被关闭可以多次调用不会有问题,调用rows.Close()之前需要检查error，防止panic

4,应该使用defer 调用rows.Close() 不要在循环结束的时候执行rows.Close

5,不要在循环语句中使用defer 防止内存泄漏

我们应该使用Prepare的方式而不是使用字符串拼接的方式执行sql，字符串拼接可能会造成sql注入问题。

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)

}

查询和更新比对:

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

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

Exec返回的是sql.Result,

Query返回的是sql.Rows，sql.Rows不会自动释放底层链接的,会很容易耗尽资源,如:too many connections,所以db.Query 返回的第一个参数不应该忽略。

一个事务本质上是一个保留有底层数据库链接的对象，它允许我们在相同的链接上执行一系列操作。

使用db.Begin()开启事务,使用或者关闭它,在事务中创建的PrepareStatement 只绑定到该事务。

使用Prepared Statements:

优势:安全(防止sql注入)，高效，方便

在数据库级别,prepared statement是绑定到单个数据库链接上的。

典型的执行过程:客户端发送带有占位符的sql语句到数据库进行prepare，服务器返回一个statement ID，然后客户端发送statement ID和参数执行它。

go prepare过程:

1,从链接池中拿链接做prepare

2,statement对象记住用的是哪个链接

3,执行Stmt的时候尝试使用该链接，如果链接不可用(已经关闭,或者繁忙) 则从链接池中重新取一个链接做prepare

prepare statement缺点:

当prepare的原始链接繁忙会re-prepared,高并发的时候可能会有很多链接繁忙,会创建大量的prepared statements可能会导致statements泄漏,prepared, re-prepared statements可能会比你想象的多即使服务端有限制statements数量

如何防止使用prepare statement:

如:db.Query(sql, param1, param2) 是先prepare，然后执行stmt，最后关闭stament

解决方法:

或者

事务中的prepare statement:

tx创建的prepared statement和 db创建的prepared statement不可以混合使用，因为是不同的底层链接,如果要在tx中使用外部db创建的stmt，可以通过tx.Stmt(stmt),将外部stmt的链接改成tx的链接 并且使用tx的链接重新prepare。

常见数据库的占位符语法:

1,MySQL

WHERE col = ? VALUES(?, ?, ?)

2,PostgreSQL

WHERE col = $1 VALUES($1, $2, $3)

3,Oracle

WHERE col = :col VALUES(:val1, :val2, :val3)

error处理:

正确姿势:

err = db.QueryRow("select name from users where id = ?", 1).Scan(&name)

if err != nil {

if err == sql.ErrNoRows {

//只是没有符合条件的数据,不是错误,只有QueryRow有这个错误

} else {log.Fatal(err)}

}

判断其他错误方式:

1,if strings.Contains(err.Error(), "Access denied") {}

2,if driverErr, ok := err.(*mysql.MySQLError); ok {

if driverErr.Number == 1045 {

}

}

3,也可以将错误码定义成常量:

if driverErr, ok := err.(*mysql.MySQLError); ok {

if driverErr.Number == mysqlerr.ER_ACCESS_DENIED_ERROR {

}

}

mysql错误常量:github.com/VividCortex/mysqlerr

当connection出现错误的时候，我们不需要实现重试机制,go底层将重新打开一个链接(或者从链接池)重试。

处理NULL值方法:

1,

for rows.Next() {

var s sql.NullString

err := rows.Scan(&s)

if s.Valid {

//非空

} else {

//null

}

}

//支持NullBool,NullFloat64,NullInt64,NullString

//也可以自定义Null类型，需要实现sql.Scanner接口

2,使用select COALESCE(name, '') as name from person

如果name是null 返回的是空字符串""

如果不知道sql返回的字段数量处理方式:

cols, err := rows.Columns()

vals := make([]interface{}, len(cols))

for i, _ := range cols {

vals[i] = new(sql.RawBytes)

}

for rows.Next() {

err = rows.Scan(vals...)

}

然后可以使用类型断言，将对应的字段转成不同的类型

链接池:

database/sql包有链接池,没有太多的方法取控制和检查

链接被回收的很快db.SetMaxIdleConns(N) 设置的大一些 有助于链接重用

db.SetMaxIdleConns(N)设置的太大也不可以，会有很多空闲链接长时间没有被使用而导致链接超时，需要根据实际情况合理设置

如果N==0 则表示不保留任何空闲链接

其他:

MySql数据库驱动暂时不支持存储过程:

err := db.QueryRow("CALL mydb.myprocedure").Scan(&result)

会报错:

Error 1312: PROCEDURE mydb.myprocedure can’t return a result set in the given context

因为Mysql要求链接设置成 multi-statement 模式,但是驱动暂时不支持

Mysql没有显式的支持多语句:

_, err := db.Exec("DELETE FROM tbl1; DELETE FROM tbl2")

//可能会报错，可能执行一条，可能执行两条，结果不可预料

事务也不可以批量执行多条语句，事务中的语句必须是串行执行

tx与db的区别:

rows, err := db.Query("select * from tbl1") // 链接1

for rows.Next() {

err = rows.Scan(&myvariable)

//下面语句使用新的链接

db.Query("select * from tbl2 where id = ?", myvariable)

}

tx, err := db.Begin()

rows, err := tx.Query("select * from tbl1") //使用事务的链接

for rows.Next() {

err = rows.Scan(&myvariable)//错误,事务链接繁忙,使用同一个链接

tx.Query("select * from tbl2 where id = ?", myvariable)

}