golang操作mysql在我看来还是挺舒服的,但是其中的细枝末节还是需要摸摸清楚,正好看到了这个tutorial就仔细的看一遍,顺便做个记录。
GO DATABASE/SQL Tutorial
Overview
要在Go中访问数据库,请使用sql.DB. 您可以使用此类型来创建语句和事务,执行查询以及获取结果。
sql.DB不是数据库连接。,它也没有映射到任何特定的数据库软件的“数据库”或“模式”的概念。它是接口和数据库实体的抽象,它像文件一样可以通过网络连接访问 或者在内存中和进程中访问。
sql.DB在后台执行了一些重要的任务:
- 它通过驱动程序打开和关闭与实际底层数据库的连接。
- 它根据需要管理连接池,这可能是前面提到的各种事情。
sql.DB抽象旨在避免担心如何管理对底层数据存储的并发访问。 当我们使用连接执行任务时,连接将被标记为正在使用,然后在不再使用时返回到可用池。如果我们无法将连接释放回池,则可能导致sql.DB打开大量连接,可能会耗尽资源(建立很多的链接、打开很多的文件句柄,缺少可用的网络及端口资源等等)。
创建sql.DB后,可以用它来访问它所代表的数据库,以及建表和处理事务等等。
Importing a Database Driver
要使用database/sql,我们不光需要安装它自己,还需要安装我们要使用的数据库的驱动。比如mysql的驱动。
尽管一些驱动程序鼓励我们直接使用它们,但一般来说不需要。最好只引用database/sql中的数据类型就可以了。有助于避免代码依赖,并使用最少的代码修改来更改底层驱动程序(以及访问的数据库)。它也会让你使用特定的GO语言程序而不是特定的驱动程序。
使用数据库的话要在文件中导入包:
import (
"database/sql"
_ "github.com/go-sql-driver/mysql"
)
使用_来加载驱动的init函数就可以了。这样我们就可以访问数据库了。
Access the Database
现在我们已经加载了驱动程序包,要创建sql.DB数据库对象,使用sql.Open()。 这将返回* sql.DB:
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的第一个参数是驱动程序名称。 这是驱动程序用于向database / sql注册自身的字符串,并且通常与包名称相同以避免混淆。 例如,它是github.com/go-sql-driver/mysql的mysql。 某些驱动程序不遵循约定并使用数据库名称,例如 sqlite3用于github.com/mattn/go-sqlite3和postgres用于github.com/lib/pq。
- 第二个参数是用于特定数据库驱动的语法,告诉程序如何访问数据库。上面的例子是说连接本地mysql服务器实例中的hello数据库。
- sql.Open会返回error,一定要检查一下它。
- 如果这个sql.DB对象只在这个作用域存在,比如上面的main函数中,就在检查错误后(别放到检查错误前)延迟(defer)释放它。
也许与直觉相反,sql.Open() 不会与数据库建立任何连接,也不会验证驱动程序连接参数。 相反,它只是准备数据库抽象以供以后使用。 当第一次连接数据库时,才建立与基础数据存储区的第一个实际连接。 如果要立即检查数据库是否可用且可访问(例如,检查是否可以建立网络连接并登录),使用db.Ping() 执行此操作,并记住检查错误:
err = db.Ping()
if err != nil {
// do something here
}
尽管当我们完成对数据库操作时一般要Close(),但是sql.DB时长期存在的(long-lived)。不要频繁的打开/关闭数据库。相反,为需要访问的每个不同的数据存储创建一个SQL.DB对象,并将其保留到程序访问完该数据存储为止。根据需要传递它,或者以某种方式在全局范围内提供,但保持打开状态。也不要在一个短生命周期的function中使用Open()和Close(),而是把sql.DB传给它。
如果不把sql.DB当成一个长期存在的对象,可能会遇到低重用性和连接共享的问题,因为每一次连接都算是一次tcp的调用,也可能导致网络资源不足或者出现大量处于TIME_WAIT状态的tcp连接没有释放。这些问题表明我们没有按照设计来使用数据库。
之后我们就可以通过sql.DB来操作数据库了。
Retrieving Result Sets
有几种常用的操作可以从数据存储中检索结果。
- 执行查询后返回多行结果
- 准备一个重复使用的声明,在多次使用后销毁掉。
- 以一次性的方式执行声明。
- 执行查询只返回一行。这种情况有一种便捷的方式。
Go的deatabase/sql的函数名很重要,如果函数名包含query的时候,是指查询数据库的相关问题,并返回一些行,即使它是空的。不做查询返回的话就不要用query函数了,应该用Exec()。
Fetching Data from the Database
我们来看个例子,它说明了如何查询数据库。我们会查询用户表找到id为1的用户,并打印出用户的id和名字。我们使用rows.Scan() 将结果分配给变量,一次取一行。
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()把查询请求发给数据库,检查错误。
- 要关闭连接
- 通过rows.Next迭代执行
- 用rows.Scan()将每行中的列读入变量
- 迭代过程中检查错误
这差不多时GO中完成这件事的唯一方式。比如不能映射到row,因为一切都是强类型的。需要创建正确类型变量并将指针传递给它们。
有些地方容易出错:
- 要在每次迭代过程中检查错误
- 只要有一个结果集合rows,底层连接就不能做其他的查询任务。这意味着不能在连接池中这么用。如果用Next迭代所有的行,最后得到的时最后一行,Next遇到EOF错误会自动调用Close。如果过程中由于某些原因提前结束,那么不会自动关闭,连接还是打开的。这时耗尽资源的简易实现方式(-_-)
- rows.Close()是无害的,可以多次调用,不像close channel那样。但是一定要先检查错误,为避免运行时错误,如果没有错误只调用close()。
- 最好defer close,即使最后你还是写了另一个close()。
- 不要在循环中defer,函数退出前 defer不会执行的,所以长时间运行的函数不应该这么用,如果要再循环中反复的查询和使用结果集,就显示的用Close吧。
How Scan() Works
在我们迭代行并将它们扫描到目标变量时,Go会在后台执行数据类型转换。它基于目标变量的类型。意识到这一点可以清理代码并帮助避免重复性工作。
例如,假设从用字符串列定义的表中选择一些行,例如varchar(45)或类似的行。然而,您碰巧知道表中总是包含数字。如果向字符串传递指针,Go将把字节复制到字符串中。现在可以使用strconv.parseint()或类似的方法将值转换为数字。您必须检查SQL操作中的错误,以及分析整数时的错误。这是一件杂乱而没意思的事。
或者,只需将scan()指针传递给整数即可。Go将检测到这一点并调用strconv.parseInt()。如果转换中有错误,则对scan()的调用将返回该错误。你的代码现在越来越少了,这是使用数据库/SQL的推荐方法。
Preparing Queries
通常,我们应该始终准备多次使用的查询。 准备查询的结果是一个预准备语句,它可以在执行语句时提供的参数提供占位符(绑定值)。 由于所有常见原因(例如,避免SQL注入攻击),这比串联字符串要好得多。
在MySQL中,参数占位符是?。举个栗子。
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)
}
其实db.Query()在底层干了三件事 prepare statement ,执行,close prepare statement。这是与数据库的三次往返。更多信息来看prepared statement
Single-Row Queries
如果只返回一行,我们可以这样写:
var name string
err = db.QueryRow("select name from users where id = ?", 1).Scan(&name)
if err != nil {
log.Fatal(err)
}
fmt.Println(name)
查询中的错误将被推迟,直到调用Scan(),然后从中返回。 还可以在预准备语句上调用QueryRow():
stmt, err := db.Prepare("select name from users where id = ?")
if err != nil {
log.Fatal(err)
}
defer stmt.Close()
var name string
err = stmt.QueryRow(1).Scan(&name)
if err != nil {
log.Fatal(err)
}
fmt.Println(name)
Modifying Data and Using Transactions
现在我们来看如何修改数据和处理事务。如果你熟悉程序语言使用语句对象来获取rows和更新数据,那么这种区别是假的,但是在GO中,有一个重要的原因导致了这种区别。
Statements that Modify Data
最好使用预准备语句来执行插入、修改、删除或其他不返回行数据的行为。下面演示如何插入并检查相关操作的元数据:
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)
执行这个语句会生成一个sql.Result,它提供对语句元数据的访问:最后插入的id和受影响的行数。
如果我们不关心这个结果,只想执行一个语句并检查是否出错了,不关心返回的东西。下面两个函数是不是在做同样的事情。
_, err := db.Exec("DELETE FROM users") // OK
_, err := db.Query("DELETE FROM users") // BAD
答案是否,上面两句话不一样,我们不能这样用quety。quety会返回一个rows,并保留数据库连接,直到rows关闭。由于rows包含很多行,因此不能直接关闭连接。上面的query永远不会关闭连接。最终的最终GC会把底层的tcp连接关掉,这可能得要会儿时间。另外database/sql包会一直跟踪连接池中的连接,需要释放连接后再重用。所以这样会导致资源浪费。
Working with Transactions
在Go中,事务本质上是一个保留与数据存储区连接的对象。 它允许执行我们看到的要执行的所有操作,但保证它们将在同一连接上执行。
我们使用db.Begin()来开始一个事务,并在生成的事务结果变量上使用Commit或Rollback来提交或回滚。往底层看,事务从连接池中获取一个连接,保留它仅用于该事务。事务中的方法一对一的映射到数据库本身的各种方法上,比如query。
事务中生成的预准备语句专门绑定带该失误,具体请看prepared statements。
不应该在SQL代码中使用与事务相关的函数(如Begin()和Commit())与SQL语句(如BEGIN和COMMIT)混合使用。可能导致不好的事情:
- 事务会保持打开,保留连接而不返回该链接。
- 数据库的状态可能与表示它的GO变量的状态不一致。
- 您可能会相信,在事务内部,您正在对单个连接执行查询,而实际上,Go已经为您无形地创建了多个连接,并且某些语句不是事务的一部分。
当您在事务内部工作时,应该注意不要调用db变量。 所有的执行操作都要作用到使用db.Begin()创建的事务变量上。db不是一个事务,只是一个事务对象。如果进一步调用db.Exec或类似函数,这些调用会发生在其他的连接上。
如果您需要使用修改连接状态的多个语句,那么即使您本身不需要事务,也需要一个TX。例如:
- 创建仅对当前连接可见的临时表
- 设置变量。比如mysql的set @var:=somevalues 语法
- 更改连接选项,比如字符集和超时时间
如果需要执行上述任何操作,则需要将语句绑定到单个连接,而在Go中执行此操作的唯一方法是使用事务。
Using Prepared Statements
预准备语句在GO中有大量的好处:安全,效率,方便。但是实现方式可能与我们所想的不同,特别时它们如何与database/sql内部进行交互。
Prepared Statements And Connections
在数据库角度看,一个预准备语句就绑定了一个数据库连接。典型的流程是客户端将带有占位符的SQL语句发送给数据库进行准备,服务器用语句id进行响应,客户端通过发送id和参数来执行语句。
但是在Go中,连接不会直接暴露给database/sql的用户。我们不能在连接上预准备语句。我们要在db和事务中预准备。并且database/sql 包含了一些方便的操作比如自动重试。由于这些原因,预准备语句与连接的底层关联是隐藏在代码中的,它们是驱动级别的。
以下是它的工作原理:
- 当我们预准备一个语句,它是准备在连接池的一个连接上的。
- Stmt对象会记住时哪个连接。
- 当我们执行Stmt时,它会尝试使用这个连接。如果已经关闭了或者忙于正在执行的操作无法调用时,会从连接池再获得一个连接,执行这个过程。
因为在原始连接繁忙时将根据需要重新准备语句,所以数据库的高并发使用可能会使很多连接繁忙,从而创建大量预准备语句。 这可能导致语句的明显泄漏,正在准备和重新准备的语句比我们想象的更频繁,甚至在语句数量上遇到服务器端限制。
Avoiding Prepared Statements
GO在底层准备好了预准备语句。比如,一个简单的db.Query(sql, p1,p2)通过准备sql,然后使用参数执行它并最终关闭语句来工作。
但是有时候预准备的语句不是我们想要的,可能有以下几种原因:
- 数据库不支持预准备语句。比如,使用mysql驱动的时候,可以连接到MemSql和Sphinx,因为它们支持mysql协议。但是它们不支持包含预准备语句的二进制协议。可能导致一些混乱行为。
- 这些语句的重用不足以使它们有价值。并且安全问题使用其他的方式处理掉了,因此不希望出现性能开销。
如果不想使用预准备语句,就需要用fmt.Sprintf()或类似的语法来自己组装SQL,将其作为db.Query的唯一参数来传递。并且数据库驱动要支持明文查询,这在GO1.1版本通过Execr和Queryer接口添加。
Prepared Statements in Transactions
在事务中创建预准备语句只与该事物绑定,所以之前关于预准备的caution不适用。当我们对事务对象执行操作时,操作会直接映射到它所在的连接。
这也意味着在Tx中创建的预处理语句不能与它分开使用。同样,在DB上创建的预准备语句不能在事务中使用,因为它们将绑定到不同的连接。
要在事务中使用在事务外准备的预准备语句,可以用Tx.Stmt(),它会将从事务外准备的语句创建新的特定于事务的语句。它通过获取现有的预准备语句,将连接设置为事务的连接并在每次执行时重新表示所有语句来完成此操作。 这种行为及其实现是不可取的,在数据库/ sql源代码中甚至还有一个TODO来改进它; 我们建议不要使用它。
在事务中处理预准备语句时必须谨慎行事。 请考虑以下示例:
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!
在Go 1.4之前,关闭一个* sql.Tx释放了与之关联的连接放回连接池中,但是在已经发生的情况下执行了对预准备语句的延迟调用,这可能导致并发访问底层连接,导致连接状态不一致。 如果使用Go 1.4或更早版本,则应确保在提交或回滚事务之前始终关闭该语句。
Parameter Placeholder Syntax
预准备语句中占位符参数的语法是特定于数据库的。MYSQL中就是?
参考文献:
记录每天解决的小问题,积累起来去解决大问题。