连接

Go语言中的database/sql包提供了保证SQL或类SQL数据库的泛用接口，并不提供具体的数据库驱动。使用database/sql包时必须注入(至少)一个数据库驱动。

下载依赖

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

使用MySql驱动

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

Open打开一个driverName指定的数据库，database指定的数据库，dataSourceName指定数据源，一般至少包括数据库文件名和其它连接必要的信息。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
import (
	"database/sql"
	"fmt"
	//连接数据库的驱动
	_ "github.com/go-sql-driver/mysql" //init()初始化驱动
)

//Go连接MYSQL数据库示例
func main() {
	//数据库连接信息
	dataBasesName := "root:Lys52014.@tcp(127.0.0.1:3306)/goday10"
	//不会校验数据库用户名密码
	//只会校验数据库连接信息的格式是否正确
	//mysql为不同驱动注册到驱动管理的key
	db, err := sql.Open("mysql", dataBasesName)
	if err != nil {
		fmt.Printf("dataBasesName: %s invalid failed, err: %v\n", dataBasesName, err)
		return
	}
	//这行代码要写在err判断的下面
    defer db.Close()
}

初始化连接

Open函数只是验证其参数格式是否正确，实际上并不创建与数据库的连接。如果要检查数据源的名称是否真实有效，应该调用Ping()方法。

返回的DB对象可以安全地被多个goroutine并发使用，并维护其自己的空闲连接池。因此，Open函数应该仅被调用一次，很少关闭这个DB对象。

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
import (
   "database/sql"
   "fmt"
   _ "github.com/go-sql-driver/mysql"
)

//定义一个初始化数据库的函数
//定义一个全局对象db
var db *sql.DB

//定义一个初始化数据库的函数
func initDb() (err error) {
   dataSourceName := "root:Lys52014.@tcp(127.0.0.1:3306)/goday10"
   //不会校验账号密码是否正确
   //注意!!! 这里不要使用:=，给全局变量赋值，然后在main函数中使用全局变量db
   db, err = sql.Open("mysql", dataSourceName)
   if err != nil {
      return err
   }
   //尝试与数据库建立连接
   err = db.Ping()
   if err != nil {
      return err
   }
   return nil
}

func main() {
   err := initDb()
   if err != nil {
      fmt.Printf("init db failed, err:%v\n", err)
      return
   }
}

其中sql.DB表示连接数据库的对象(结构体实例)，它保存了连接数据库相关的信息。它内部维护着一个具有零到多个底层的连接池，它可以安全地被多个goroutine同时使用。

SetMaxOpenConns

1
func (db *DB) SetMaxOpenConns(n int)

SetMaxOpenConns设置与数据库建立连接的最大数目。如果n大于o且小于最大闲置连接数，会将最大空闲连接数减小到匹配最大最大开启的连接数的限制。如果n<=o，不会限制最大开启的连接数，默认为o(无限制)。

SetMaxIdleConns

1
func (db *DB) SetMaxIdleConns(n int)

SetMaxIdleConns设置连接池中的最大闲置连接数。如果n大于最大开启连接数，则新的最大闲置连接数会减小到匹配最大开启连接数的限制。如果n<=o，不会保留闲置连接。

CRUD

建库建表

创建数据库：

1
CREATE DATEBASE sql_test;

进入数据库：

1
use sql_test;

创建表：

1
2
3
4
5
6
CREATE TABLE `user` (
    `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(20) DEFAULT '',
    `age` INT(11) DEFAULT '0',
    PRIMARY KEY(`id`)
)ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

查询

定义一个结构体来储存user表的数据：

1
2
3
4
5
type User struct {
	id int
	name string
	age int
}

单行查询

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

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

Example：

1
2
3
4
5
6
7
8
9
10
//查询单条数据
func QueryById(id int){
   var user User
   //1.写查询单条记录的sql语句
   sqlStr := `select id, name, age from user where id = ?`
   //2.执行查询并使用Scan解析查询结果
   //必须对rowObj对象调用Scan方法，因为该方法会释放数据库连接
   db.QueryRow(sqlStr, id).Scan(&user.id, &user.name, &user.age)
   fmt.Printf("user:%#v\n", user)
}

多行查询

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

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

Example:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
//查询多条数据
func QueryMultiRowDemo(id int){
   sqlStr := `select id, name, age from user where id > ?`
   rows, err := db.Query(sqlStr, id)
   if err != nil {
      fmt.Printf("query failed, err:%v\n", err)
      return
   }
   //非常重要：关闭rows释放持有的数据库连接
   defer rows.Close()

   //循环读取结果集中的数据
   for rows.Next() {
      var user User
      err := rows.Scan(&user.id, &user.name, &user.age)
      if err != nil {
         fmt.Printf("scan failed, err:%v\n", err)
         return
      }
      fmt.Printf("id:%d name:%s age:%d\n", user.id, user.name, user.age)
   }
}

插入数据

插入、更新和删除都是用Exec()方法

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

Example:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
//插入数据
func Insert(){
   sqlStr := `insert into user(name, age) values('张飞', 45)`
   result, err := db.Exec(sqlStr)
   if err != nil {
      fmt.Printf("insert failed, err:%v\n", err)
      return
   }
   //如果是插入数据的操作，能够拿到插入的数据Id
   id, err := result.LastInsertId()
   if err != nil {
      fmt.Printf("get id failed, err:%v\n", err)
      return
   }
   fmt.Println("id: ", id)
}

更新数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
//更新数据
func UpdateRow(){
   sqlStr := `update user set age=? where id=?`
   result, err := db.Exec(sqlStr, 999, 1)
   if err != nil {
      fmt.Printf("update failed, err:%v\n", err)
      return
   }
   //获取受影响的行数
   affected, err := result.RowsAffected()
   if err != nil {
      fmt.Printf("get rowsAffected failed, err:%v\n", err)
      return
   }
   fmt.Printf("update success, Affected rows:%d\n", affected)
}

删除数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
//删除数据
func DeleteRow(n int){
   sqlStr := `delete from user where id=?`
   result, err := db.Exec(sqlStr, n)
   if err != nil {
      fmt.Printf("delete failed, err:%v\n", err)
      return
   }
   //获取受影响的行数
   affected, err := result.RowsAffected()
   if err != nil {
      fmt.Printf("get rowsAffected failed, err:%v\n", err)
      return
   }
   fmt.Printf("delete success, Affected rows:%d\n", affected)
}

MySql预处理

什么是预处理？

普通Sql语句执行过程：

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

预处理执行过程：

  • 1、把Sql语句分成两部分，命令部分和数据部分。
  • 2、先把命令部分发送给MySql服务端，MySql服务端进行Sql预处理。
  • 3、然后把数据部分发送给MySql服务端，MySql服务端对Sql语句进行占位符替换。
  • 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服务端，返回一个准备好的状态用于之后的查询和命令。返回值可以同时执行多个查询和命令。

Query Example:

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
//预处理方式查询多条数据
func PrepareQuery(n int){
   sqlStr := `select id, name, age from user where id > ?`
   stmt, err := db.Prepare(sqlStr)
   if err != nil {
      fmt.Printf("prepare failed, err:%v\n", err)
      return
   }
   //关闭Stmt
   defer stmt.Close()
   //后续直接使用stmt去执行一些操作
   rows, err := stmt.Query(n)
   if err != nil {
      fmt.Printf("query failed, err:%v\n", err)
      return
   }
   defer rows.Close()
   //循环读取结果集中的数据
   for rows.Next() {
      var user User
      err := rows.Scan(&user.id, &user.name, &user.age)
      if err != nil {
         fmt.Printf("scan failed, err:%v\n", err)
         return
      }
      fmt.Printf("id:%d name:%s age:%d\n", user.id, user.name, user.age)
   }
}

Insert Example：

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
//预处理方式插入多条数据
func PrepareInsert(){
   sqlStr := `insert into user(name, age) values (?, ?)`
   stmt, err := db.Prepare(sqlStr)
   if err != nil {
      fmt.Printf("prepare failed, err:%v\n", err)
      return
   }
   //关闭Stmt
   defer stmt.Close()
   //后续直接使用stmt去执行一些操作
   m := map[string]int{
      "唐僧": 18,
      "猪八戒": 35,
      "孙悟空": 24,
      "沙悟净": 34,
   }
   for key, value := range m {
      result, err := stmt.Exec(key, value)
      if err != nil {
         fmt.Printf("key:%v value:%v insert failed, err:%v\n", key, value, err)
         continue
      }
      id, err := result.LastInsertId()
      if err != nil {
         fmt.Printf("get id failed, err:%v\n", err)
         return
      }
      fmt.Println("insert data id: ", id)
   }
}

Sql注入问题：

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

1
2
3
4
5
6
7
8
9
10
11
12
//Sql注入错误实例
func SqlInjectDemo(name string){
   sqlStr := fmt.Sprintf(`select id, name, age from user where name='%s'`, name)
   fmt.Printf("SQL: %s\n", sqlStr)
   var user User
   err := db.QueryRow(sqlStr).Scan(&user.id, &user.name, &user.age)
   if err != nil {
      fmt.Printf("exec failed, err:%v\n", err)
      return
   }
   fmt.Printf("user:%#v\n", user)
}

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

1
2
3
SqlInjectDemo("李四' or 1=1#")
SqlInjectDemo("李四' union select * from user #")
SqlInjectDemo("李四' and (select count(*) from user) <10 #")

不同的数据库，Sql语句使用的占位符语法相似。

数据库 占位符语法
MySql ?
PostgreSQL $1,$2等
SQLite ?和$1
Orcale :name

Go实现MySQL事务

什么是事物？

事务：一个最小的不可再分割的工作单元；通常一个事物对应一个完整的业务(如银行的转账业务，该业务就是一个最小的工作单元)，同时这个完整的业务需要执行多次的DML(insert 、update、delete)语句共同联合完成。A转账给B，这里就需要执行两次update操作。

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

事务的ACID

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

条件 解释
原子性 一个事物(transaction)中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚(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

Example:

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
47
48
49
50
51
52
53
54
55
//事务
func Transaction(){
   //开启事务
   tx, err := db.Begin()
   if err != nil {
      if tx != nil{
         //回滚
         tx.Rollback()
      }
      fmt.Printf("begin trans failed, err:%v\n", err)
      return
   }
   sqlStr1 := `update user set age=30 where id=?`
   ret1, err := tx.Exec(sqlStr1, 2)
   if err != nil {
      //回滚
      tx.Rollback()
      fmt.Printf("exec ret1 failed, err:%v\n", err)
      return
   }
   affected1, err := ret1.RowsAffected()
   if err != nil {
      //回滚
      tx.Rollback()
      fmt.Printf("exec ret1.RowsAffected() failed, err:%v\n", err)
      return
   }

   sqlStr2 := `update user set age=40 where id=?`
   ret2, err := tx.Exec(sqlStr2, 3)
   if err != nil {
      //回滚
      tx.Rollback()
      fmt.Printf("exec ret2 failed, err:%v\n", err)
      return
   }
   affected2, err := ret2.RowsAffected()
   if err != nil {
      //回滚
      tx.Rollback()
      fmt.Printf("exec ret2.RowsAffected() failed, err:%v\n", err)
      return
   }

   fmt.Println(affected1, affected2)
   if affected1 == 1 && affected2 == 1{
      fmt.Printf("提交事务。。。")
      tx.Commit()
   }else {
      tx.Rollback()
      fmt.Printf("事务回滚。。。")
      return
   }
   fmt.Println("exec trans success...")
}