1操作MySQL之mysql库

一 快速使用

Go语言中的database/sql包提供了保证SQL或类SQL数据库的接口，并没有具体的实现。使用database/sql包时必须使用其它数据库驱动，如第三方实现：https://github.com/go-sql-driver/mysql

1.1 下载

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

1.2 快速链接

package main
import (
	"database/sql"
	"fmt"
	_ "github.com/go-sql-driver/mysql"
)
func main() {
	//conn_url := "用户名:密码@tcp(地址:端口)/数据库名字"
	connUrl := "root:123@tcp(127.0.0.1:3306)/lqz"
	db, err := sql.Open("mysql", connUrl)
	if err != nil {
		fmt.Println("链接出错：",err)
	}
	defer db.Close()
	fmt.Println(db)
}

1.3 最佳使用方案

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

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

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

package main

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

var DB *sql.DB

func createDB() (err error) {
	connUrl := "root:123@tcp(127.0.0.1:3306)/lqz?charset=utf8mb4"
	// 注意此处：不要用  DB, err := ; err作为返回值，已经定定于了，DB是全局变量
	DB, err = sql.Open("mysql", connUrl)
	if err != nil {
		return
	}
	// 尝试与数据库建立连接
	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) 间隔时间

	connUrl := "root:123@tcp(127.0.0.1:3306)/lqz?charset=utf8mb4"
	DB, _ = sql.Open("mysql", connUrl)
	DB.SetMaxOpenConns(30)
	DB.SetMaxIdleConns(15)

二 查询数据

type Music struct {
	Id int
	Name string
	Year string
	SignId int
}

2.1 单行查询db.QueryRow()

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

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

2.2 多行查询db.Query()

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

func queryMulti() {
	sqlStr := "select * from music 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(&m.Id, &m.Name, &m.Year,&m.SignId)
		if err != nil {
			fmt.Println("遍历出错",err)
			return
		}
		fmt.Println(m)
	}
}

三 插入数据

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

func insertMusic() {
	sqlStr := "insert into music(name, year,sign_id) values (?,?,?)"
	ret, err := DB.Exec(sqlStr, "听妈妈的话", 2010,1)
	if err != nil {
		fmt.Println("插入出错",err)
		return
	}
	theID, err := ret.LastInsertId() // 新插入数据的id
	if err != nil {
		fmt.Println("获取插入的id错误：",err)
		return
	}
	fmt.Println("插入成功，id为：", theID)
}

三 删除数据

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

四 更新数据

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

五 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方法来实现预处理操作。

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

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

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

// 预处理查询示例
func prepareQueryDemo() {
	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
	}
	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 u user
		err := rows.Scan(&u.id, &u.name, &u.age)
		if err != nil {
			fmt.Printf("scan failed, err:%v\n", err)
			return
		}
		fmt.Printf("id:%d name:%s age:%d\n", u.id, u.name, u.age)
	}
}

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

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

SQL注入问题

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

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

// 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 u user
	err := db.QueryRow(sqlStr).Scan(&u.id, &u.name, &u.age)
	if err != nil {
		fmt.Printf("exec failed, err:%v\n", err)
		return
	}
	fmt.Printf("user:%#v\n", u)
}

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

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

Go实现MySQL事务

事务的ACID

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

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

事务相关方法

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

案例

func transactionDemo()  {
	conn,err:=DB.Begin()
	if err != nil {
		fmt.Println("开启事务出错误：",err)
		return
	}
	_,err=conn.Exec("insert into music(name,year,sign_id) values (?,?,?)","动物世界","2022",2)
	if err!=nil{
		fmt.Println("插入出错：",err)
		conn.Rollback()
	}
	_,err=conn.Exec("insert into music (name,year,sign_id) values (?,?,?)","世上只有妈妈好","2022",3)
	if err!=nil{
		fmt.Println("插入出错：",err)
		conn.Rollback()
	}
	conn.Commit()

}