Go操作数据库(一)

发表于

MySQL是常用的关系数据库，本文主要介绍Go语言怎么操作MySQL数据库。

关键词：golang

连接

sql包提供了保证SQL或类SQL数据库的泛用接口。

使用sql包时必须注入（至少）一个数据库驱动。参见http://golang.org/s/sqldrivers 获取驱动列表。

更多用法示例，参见wiki页面：http://golang.org/s/sqlwiki。

这里使用github.com/go-sql-driver/mysql/作为MySQL的驱动。

下载依赖

1
go get -u github.com/go-sql-driver/mysql

初始化链接

1
func Open(driverName, dataSourceName string) (*DB, error)

  • 返回的DB类型可用被多个goroutine同时使用，只需要创建一次，很少需要关闭这个对象；

  • Open函数只验证参数格式是否正确，不创建数据库的连接，无法验证密码是否正确；

  • 一般设置成全局变量，一次初始化然后整个模块使用。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
import (
    "database/sql"
    _ "https://github.com/go-sql-driver/mysql" // init mysql驱动
)
var db *sql.DB
// Data Source Name
const dsn = "root:xxx@tcp(127.0.0.1)/dbname"
func main() {
    var err error
	// Open不会校验账号密码是否正确
    // 这里不要使用:=, 我们是给全局变量赋值，然后在main函数中示意使用全局变量
    db, err = sql.Open("mysql", dsn)
    if err != nil {
        panic(err)
    }
    defer db.Close()
}

验证用户名和密码是否正确

1
func (db *DB) Ping() error
  • 验证数据库是否可用
  • 如果必要的话建立一个连接

设置最大连接数

1
func (db *DB) SetMaxOpenConns(n int)
  • 设置数据库最大连接数
  • 如果n > 0且小于最大闲置连接数，则会将最大闲置连接数减少到匹配开启最大连接数的限制
  • 如果n <= 0（默认值），不会限制最大开启连接数限制

设置最大闲置连接数

1
func (db *DB) SetMaxIdleConns(n int)
  • 设置数据库最大闲置连接数
  • 如果n 大于最大连接数，则新的最大闲置连接数会减少到最大连接数
  • 如果n <= 0，则不会保持闲置连接，默认值为2

增删改查

建库建表

先在MySQL中创建一个名为movies的数据库

1
CREATE DATABASE movies;

进入该数据库：

1
USE movies;

创建一张用于测试的数据表：

1
2
3
4
5
6
CREATE TABLE `mt_subtitle_table`  (
  `id` text NOT NULL,
  `actress` text NOT NULL,
  `title` text NOT NULL,
  PRIMARY KEY (`id`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

为了方便查询，我们实现定义一个结构体来存储mt_subtitle_table表的数据。

1
2
3
4
5
type Movie struct {
    id string
    actress string
    title string
}

单行查询

单行查询执行一次查询，并期望返回最多一行结果（即Row）。QueryRow总是返回非nil的值，直到返回值的Scan方法被调用时，才会返回被延迟的错误，如未找到结果等。

1
func (db *DB) QueryRow(query string, args ...interface{}) *Row

代码实例：

1
2
3
4
5
6
7
8
9
10
11
12
// 查询单条数据
func queryRowDemo() {
    sqlStr := "select id, actress, title from user where id=?"
    var m Movie
    // 要确保QueryRow之后调用Scan方法，否则持有的数据库连接不会被释放。
    err := db.QueryRow(sqlStr, "APAA-401").Scan(&m.id, &m.actress, &m.title)
    if err != nil {
        fmt.Printf("scan failed, err: %v", err)
        return
    }
    fmt.Printf("id: %s, actress: %s, title: %s", m.id, m.actress, m.title)
}

多行查询

多行查询db.Query()执行一次查询，返回多行结果（Rows），一般用于执行select命令，参数args表示query中的占位符参数。

1
func (db *DB) Query(query string, args ...interface{})(*Rows, error)

代码示例：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
func queryMultRowDemo() {
    sqlStr := "select id, actress, title for user where actress = ?"
    rows, err := db.Query(sqlStr, "樱空桃")
    if err != nil {
        fmt.Printlf("query failed, err: %v", err)
        return
    }
    // 非常重要：释放持有的数据库连接
    for rows.Next() {
        var m Moive
        err := rows.Scan(&m.id, &m.actress, &m.title)
        if err != nil {
            fmt.Printf("scan failed, err: %v", err)
            return 
        }
        fmt.Printf("id: %s, actress: %s, title: %s", m.id, m.name, m.age)
    }
}

插入数据

插入、更新和删除都使用Exec方法。

1
func (db *DB) Exec(query string, args ...interface{}) (Result, error)

Exec执行一次命令（包括查询、删除、更新、插入等），返回的Result是对已执行的SQL命令的总结，参数args表示query中的占位参数。

插入数据代码示例：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
// 插入数据
func insertRowDemo() {
    sqlStr := "insert into mt_subtitle_table(id, actress, title) values(?, ?, ?)"
    ret, err := db.Exec(sqlStr, "APAA-401", "樱空桃", "先輩方の気遣いが嬉しい")
    if err != nil {
        fmt.Printf("insert failed, err :%v", err)
        return
    }
    theID, err := ret.LastInsertId() // 返回插入新数据的ID
    if err != nil {
        fmt.Printf("get lastinsert ID failed, err: %v", err)
        return
    }
    fmt.Printf("insert success, affected id: %v", theID)
}

更新数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
// 更新数据
func updateRowDemo() {
    sqlStr := "update mt_subtitle_table set title=? where id=?"
    ret, err := db.Exec(sqlStr, "先輩方の気遣いが嬉しい", "APAA-401")
    if err != nil {
        fmt.Printf("update failed, err: %v\n", err)
        return
    }
    n, err := ret.RowsAffected() // 返回操作影响的行数
    if err != nil {
        fmt.Printf("get RowsAffected failed, err:%v\n", err)
        return
    }
    fmt.Printf("update success, affected rows: %d\n", n)
}

删除数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
// 删除数据
func deleteRowDemo() {
    sqlStr := "delete from mt_subtitle_table where id = ?"
    ret, err := db.Exec(sqlStr, "APAA-401")
    if err != nil {
        fmt.Printf("delete failed, err: %v", err)
        return 
    }
    n, err := ret.RowAffected() // 操作影响的行数
    if err != nil {
        fmt.Printf("get RowsAffected failed, err: %v", err)
        return
    }
    fmt.Printf("delete success, affected rows: %d", n)
}

MySQL预处理

什么是预处理

普通SQL语句执行过程：

  1. 客户端对SQL语句进行占位符替换得到完整的SQL语句；
  2. 客户端发送完整的SQL语句到MySQL服务端；
  3. MySQL服务端执行完整的SQL语句并将结果返回给客户端。

预处理执行过程：

  1. 把SQL语句分成两部分，命令部分与数据部分。
  2. 先把命令部分发送到MySQL服务端，MySQL服务端进行SQL预处理。
  3. 然后把数据部分发送到MySQL服务端，MySQL服务端使用占位符进行替换。
  4. MySQL服务端执行完整的SQL语句并将结果返回给客户端。

为何要预处理

  1. 优化MySQL服务器重复执行SQL的方法，可以提升服务器性能，提前让服务器编译，一次编译多次执行，节省后续编译的成本。
  2. 避免SQL注入问题。

Go实现MySQL预处理

database/sql使用下面的Prepare方法来实现预处理操作。

1
func (db *DB) Prepare(query string)(*Stmt, error)

Prepare方法会先将sql语句发送给MySQL服务端，返回一个准备好的状态用于之后的查询和命令。返回值可以同时执行多个查询和命令。

查询操作的预处理实例代码如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
// 预处理查询实例
func prepareQueryDemo() {
    sqlStr := "select id, actress, title from mt_subtitle_table where actress = ?"
    stmt, err := db.Prepare(sqlStr)
    if err != nil {
        fmt.Printf("prepare failed, err: %v", err)
        return 
    }
    defer stmt.Close()
    rows, err := stmt.Query("白桃花")
    if err != nil {
        fmt.Printf("query failed, err: %v\n", err)
        return 
    }
    defer rows.Close()
    // 循环读取结果中的数据
    for rows.Next() {
        var m Movie
        err := rows.Scan(&m.id, &m.actress, &m.title)
        if err != nil {
            fmt.Printf("scan failed, err: %v", err)
            return
        }
        fmt.Printf("id: %v, actress: %v, title: %v", m.id, m.actress, m.title)
    }
}

插入、更新和删除的预处理操作十分类似，这里以插入数据的预处理为例：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
// 预处理插入实例
func prepareInsertDemo() {
    sqlStr := "insert into mt_subtitle_table(id, actress, title) values(?,?)"
    stmt, err := db.Prepare(sqlStr)
    if err != nil {
        fmt.Printf("prepare failed, err:%v", err)
        return
    }
    defer stmt.Close()
    _, err = stmt.Exec("APAA-401", "白桃花", "先輩方の気遣いが嬉しい")
    if err != nil {
        fmt.Printf("insert failed, err:%v", err)
        return
    }
    _, err = stmt.Exec("APAA-402", "樱空桃", "先輩方の気遣いが嬉しい")
    if err != nil {
        fmt.Printf("insert failed, err:%v", err)
        return
    }
    fmt.Println("insert success.")
}

SQL注入问题

任何时候都不应该自己拼接SQL语句！

这里我们编写一个根据name字段查询user表的函数：

1
2
3
4
5
6
7
8
9
10
11
// sql注入实例
func sqlInjectDemo(name string) {
    sqlStr := fmt.Sprinf("select id, actress, title from mt_subtitle_table where actress = '%s'", name)
    var m Movie
    err := db.QueryRow(sqlStr).Scan(&m.id, &m.name, &m.title)
    if err != nil {
        fmt.Printf("exec failed, err: %v\n", err)
        return
    }
    fmt.Printf("movie: #%v\n", m)
}

此时输入以下字符串都可以引发SQL注入问题：

1
2
3
sqlInjectDemo("xxx' or 1=1#")
sqlInjectDemo("xxx' union select * from user #")
sqlInjectDemo("xxx' and (select count(*) from user) > 10")

补充：不同的数据库中，SQL语句中使用的占位符语法不尽相同。

数据库 占位符
MySQL ?
PostgreSQL $1,$2等
SQlite ?和$1
Oracle :name

Go实现MySQL事务

什么是事务

事务：一个最小的不可再分的工作单元；

通常一个事务对应一个完整的业务（如银行转账业务，该业务就是一个最小的工作单元）同时这个完整的操作需要执行多次的DML（插入、更新、删除）语句共同联合完成，A转账给B，这里就需要两次update操作。

在MySQL中只有使用了Innodb数据库引擎的数据库才支持表或事物。事务处理可以用来维护数据的完整性，保证成批的SQL语句要么全部执行，要么全部不执行。

事务的ACID

通常事务必须满足四个条件（ACID）：原子性（Atomicity，或称不可分割性）、一致性（Consistency）、隔离性（Isolation又称独立性）、持久性（Durability）。

条件 解释
原子性 一个事务中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发送错误，会被回滚（Rollback）到事务前开始的状态，就像这个事务从来没有执行过一样。
一致性 在事务开始之前和事务结束以后，数据库的完整性没有被破坏，这表示写入的资料必须完全符合所有的预设规则，这包含资料的准确度、串联性以及后续数据库可以自发性的完成预定的工作。
隔离性 数据库允许多个并发事务同时对其数据进行读写和修改的能力。隔离性可以防止多个事务并发执行时由于交叉执行而导致的数据行不一致的情况。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（Read committed)、可重复读（Repeatable read）和串行化（Serializable）。
持久性 事务处理结束以后，对数据的修改就是永久的，即便系统故障也不会丢失。

事务相关方法

Go语言使用以下三个方法实现MySQL中的事务操作。

开始事务

1
func (db *DB) Begin() (*Tx, error)

提交事务

1
func (tx *Tx) Commit() error

回滚事务

1
func (tx *Tx) Rollback() error

事务实例

下面的代码演示了一个简单的事务操作，该事务操作能够保证两次更新操作要么同时成功要么同时失败，不会出现中间状态。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
// 事务操作实例
func transationDemo() {
    tx, err := db.Begin() // 开启事务
    if err != nil {
        if tx != nil {
        	tx.Rollback() // 回滚
        }
        fmt.Printf("begin trans fail, err: %v", err)
        return
    }
    sqlStr1 := "UPDATE mt_subtitle_table set id = AAA501 where actress=?"
    ret1, err := tx.Exec(sqlStr1, "若狭留美")
    if err != nil {
        tx.Rollback() // 回滚
        fmt.Prinf("exec sql1 failed, err: %v", err)
        return
    }
    affRow1, err := ret1.RowAffected()
    if err != nil {
        tx.Rollback() // 回滚
        fmt.Printf("exec ret1.RowAffected() failed, err:%v", err)
        return
    }
    sqlStr2 := "UPDATE mt_subtitle_table set id = AAA4501 where actress = ?"
    ret2, err := tx.Exec(sqlStr2, "泷泽萝拉")
    if err := nil {
        tx.Rollback() // 回滚
        fmt.Println("exec sql2 failed, err: %v", err)
        return
    }
    affRow2, err := ret2.RowsAffected()
    if err != nil {
        tx.Rollback() // 回滚
        fmt.Printf("exec ret2.RosAffected() failed, err:%v", err)
        return 
    }
    fmt.Println(affRow1, affRow2)
    if affRow1 == 1 && affRow2 == 1 {
        fmt.Println("很好，事务提交了")
        tx.Commit() // 提交事务
    }else {
        tx.Rollback()
        fmt.Println("不好意思，有点问题，事务回滚了")
    }
    fmt.Println("事务执行成功！")
}