go 操作MySQL之mysql包

1 快速使用

go 语言中的database/sql包提供了保证SQL或类SQL数据库的接口，并没有具体的实现。使用database/sql包时必须使用其它数据库驱动，类似于java的JDBC，如第三方实现：https://github.com/go-sql-driver/mysql 和 github.com/jmoiron/sqlx 。 相比而言，sqlx 包功能更强大，但这里只介绍mysql包。

1.1 下载

go get -u github.com/go-sql-driver/mysql  // -u 表示下最新版

1.2 快速链接

package main
import (
	"database/sql"
	"fmt"
	_ "github.com/go-sql-driver/mysql"
)
// mysql包操作mysql
func main() {
    
	//conn_url := "用户名:密码@tcp(地址:端口)/数据库名字"
	connUrl := "root:1[email protected](127.0.0.1:3306)/study"  // 连接字符串
	db, err := sql.Open("mysql", connUrl)
	if err != nil {
    
		fmt.Println("打开数据库失败，",err)
	}
	defer db.Close()  // 关闭连接
  err=db.Ping()
	if err != nil {
    
		fmt.Println("ping 失败",err)
	}
	fmt.Println(db) // go中mysql连接，并没有真正连，只有在使用的时候，才创建连接并执行
}

1.3 最佳使用方案

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

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

接下来，我们定义一个全局变量DB，用来保存数据库连接对象。将上面的示例代码拆分出一个独立的init DB函数，只需要在程序启动时调用一次该函数完成全局变量DB的初始化，其他函数中就可以直接使用全局变量DB了。

package main

import (
	"database/sql"
	"fmt"
	_ "github.com/go-sql-driver/mysql"
)

var DB *sql.DB

func createDB() (err error) {
    
	connUrl := "root:[email protected](127.0.0.1:3306)/study?charset=utf8mb4"
	// 注意此处：不要用 DB, err := ; err作为返回值，已经定义了，DB是全局变量
	DB, err = sql.Open("mysql", connUrl)
	if err != nil {
    
		return
	}
	// 尝试与数据库建立连接
    // defer DB.Close() // 全局使用时，不能加上这句,会提前关闭报错
	err = DB.Ping()
	if err != nil {
    
		return
	}
	return nil
}
func main() {
    
	err:=createDB()
	if err != nil {
    
		fmt.Println("获取链接出错：",err)
	}
	fmt.Println(DB)
}

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

1.4 设置连接池

默认情况下，连接池增长无限制，并且只要连接池中没有可用的空闲连接，就会创建连接。我们可以使用DB.SetMaxOpenConns设置池的最大大小。未使用的连接标记为空闲，如果不需要则关闭。要避免建立和关闭大量连接，可以使用DB.SetMaxIdleConns设置最大空闲连接。

注意：该设置方法golang版本至少为1.2

• DB.SetMaxIdleConns(n int) 设置最大空闲连接数

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

• DB.SetMaxOpenConns(n int) :设置最大打开的连接数

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

• DB.SetConnMaxIdleTime(time.Second*10) 间隔时间

// 放在 CerateDB 内部
DB.SetMaxOpenConns(30) //连接池大小是30
DB.SetMaxIdleConns(15) //设置最大空闲连接数15 
DB.SetConnMaxIdleTime(time.Second*10) // 间隔时间

2 查询数据

type People struct {
    
	Id int
	Name string
	Age int
	Sex string
}

2.1 单行查询db.QueryRow()

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

func queryOne()  {
    
	var people People
	// QueryRow后一定要调用Scan方法，否则持有的数据库链接不会被释放
	err:=DB.QueryRow("select * from people where id=?",1).Scan(&people.Id,&people.Name,&people.Age,&people.Sex)
	if err != nil {
    
		fmt.Println("查询出错：",err)
	}
	fmt.Println(music)
}

2.2 多行查询db.Query()

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

func queryMulti() {
    
	sqlStr := "select * from people where id > ?"
	rows, err := DB.Query(sqlStr, 1)
	if err != nil {
    
		fmt.Println("查询出错：",err)
		return
	}
	// 关闭rows释放持有的数据库链接
	defer rows.Close()
	// 循环读取结果集中的数据
	for rows.Next() {
    
		var m Music
		err := rows.Scan(&people.Id,&people.Name,&people.Age,&people.Sex)
		if err != nil {
    
			fmt.Println("遍历出错",err)
			return
		}
		fmt.Println(m)
	}
}

3 插入数据

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

func insertPeople() {
    
	sqlStr := "insert into people(name,age,sex) values (?,?,?)"
	ret, err := DB.Exec(sqlStr, "tony", 23, "男")
	if err != nil {
    
		fmt.Println("插入出错,", err)
		return
	}
	theID, err := ret.LastInsertId() // 新插入数据的id
	if err != nil {
    
		fmt.Println("获取插入的id出错", err)
		return
	}
	fmt.Println("插入成功，id为:", theID)
}

4 删除数据

func deletePeople() {
    
	sqlStr := "delete from people where id = ?"
	ret, err := DB.Exec(sqlStr, 5)
	if err != nil {
    
		fmt.Println("删除出错：",err)
		return
	}
	n, err := ret.RowsAffected() // 操作影响的行数
	if err != nil {
    
		fmt.Println("获取操作影响的行数出错：",err)
		return
	}
	fmt.Println("删除成功，影响的行数为：",n)
}

5 更新数据

func updateMusic() {
    
	sqlStr := "update music set name= ? where id = ?"
	ret, err := DB.Exec(sqlStr, "南拳妈妈", 4)
	if err != nil {
    
		fmt.Println("更新失败",err)
		return
	}
	n, err := ret.RowsAffected()
	if err != nil {
    
		fmt.Println("获取影响的行数失败：",err)
		return
	}
	fmt.Println("更新成功，影响行数为：",n)
}

6 MySQL预处理

6.1 什么是预处理？

普通SQL语句执行过程：

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

预处理执行过程：

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

6.2 为什么要预处理？

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

6.3 Go实现MySQL预处理

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

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

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

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

// 预处理查询示例
func prepareQueryDemo() {
    
	sqlStr := "select id, name, age,sex from people where id > ?"
	stmt, err := DB.Prepare(sqlStr)
	if err != nil {
    
		fmt.Printf("prepare failed, err:%v\n", err)
		return
	}
	defer stmt.Close()
	rows, err := stmt.Query(0)
	if err != nil {
    
		fmt.Printf("query failed, err:%v\n", err)
		return
	}
	defer rows.Close()
	// 循环读取结果集中的数据
	for rows.Next() {
    
		var p People
		err = rows.Scan(&p.Id, &p.Name, &p.Age, &p.Sex)
		if err != nil {
    
			fmt.Printf("scan failed, err:%v\n", err)
			return
		}
		fmt.Printf("id:%d name:%s age:%d sex:%s\n", p.Id, p.Name, p.Age, p.Sex)
	}
}

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

// 预处理插入示例
func prepareInsertDemo() {
    
	sqlStr := "insert into people(name, age, sex) values (?,?,?)"
	stmt, err := DB.Prepare(sqlStr)
	if err != nil {
    
		fmt.Printf("prepare failed, err:%v\n", err)
		return
	}
	defer stmt.Close()
	r, err := stmt.Exec("小王子", 18, "男")
	if err != nil {
    
		fmt.Printf("insert failed, err:%v\n", err)
		return
	}
	fmt.Println(r.LastInsertId())
	r, err = stmt.Exec("沙河娜扎", 18, "女")
	if err != nil {
    
		fmt.Printf("insert failed, err:%v\n", err)
		return
	}
	fmt.Println(r.RowsAffected())
	fmt.Println("insert success.")
}

6.4 SQL注入问题

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

这里我们演示一个自行拼接SQL语句的示例，编写一个根据name字段查询user表的函数如下：

// sql注入示例
func sqlInjectDemo(name string) {
    
	sqlStr := fmt.Sprintf("select id, name, age from people where name='%s'", name)
	fmt.Printf("SQL:%s\n", sqlStr)
	var p people
	err := db.QueryRow(sqlStr).Scan(&p.Id, &p.Name, &p.Age, &p.Sex)
	if err != nil {
    
		fmt.Printf("exec failed, err:%v\n", err)
		return
	}
	fmt.Printf("user:%#v\n", p)
}

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

sqlInjectDemo("xxx' or 1=1#")
sqlInjectDemo("xxx' union select * from people #")
sqlInjectDemo("xxx' and (select count(*) from people) <10 #")

7 Go实现MySQL事务

7.1 事务的ACID

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

7.2 事务相关方法

Db.Begin()        开始事务
Db.Commit()        提交事务
Db.Rollback()     回滚事务

案例

func transactionDemo()  {
    
    conn,err:=DB.Begin()
    if err != nil {
    
        fmt.Println("开启事务出错误：",err)
        return
    }
    _,err=conn.Exec("insert into people(name,age,sex) values (?,?,?)","judy",16,"女")
    if err!=nil{
    
        fmt.Println("插入出错：",err)
        conn.Rollback()
        return
    }
    _,err=conn.Exec("insert into people(name,age,sex) values (?,?,?)","kong",19,"男")
    if err!=nil{
    
        fmt.Println("插入出错：",err)
        conn.Rollback()
        return
    }
    conn.Commit()

}