go语言实战-----27-----mysql增删改查、预处理、事务、第三方库sqlx

一 mysql增删改查

Mysql准备工作

首先我们建库建表，以方便进行测试。运行下面sql文件：

CREATE DATABASE IF NOT EXISTS go_test;
use go_test;

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;

1 连接mysql

go下面连接mysql我们使用Open函数。Open返回的是一个连接池对象，可以对其进行最大连接数，空闲连接数等参数，可以看下面事务的案例。 具体去看函数的注释即可。

Open函数：
db, err := sql.Open("mysql", "用户名:密码@tcp(IP:端口)/数据库?charset=utf8")

例如：
db, err := sql.Open("mysql", "root:123456@tcp(127.0.0.1:3306)/go_test?charset=utf8")

这里强调一点，Open函数的返回值是不能直接判断是否已经连接mysql成功，必须使用返回的db对象里面Ping方法去判断。
例如如果我们将密码改为错误，db不会返回nil，err会返回nil，所以无法通过Open的返回值去判断。

package main

import (
	"database/sql"
	"fmt"

	_ "github.com/go-sql-driver/mysql" // 注释掉_后会异常，因为添加 _ 后，它会帮我们自动调用初始化数据库的相关函数。
)

// https://github.com/go-sql-driver/mysql#usage
func main() {

	// 1. 连接数据库
	db, err := sql.Open("mysql", "root:123456@tcp(127.0.0.1:3306)/go_test?charset=utf8mb4")
	// 例如如果我们将密码改为错误，db不会返回nil，err会返回nil，所以无法通过Open的返回值去判断。
	//db, err := sql.Open("mysql", "root:qqqwww@tcp(127.0.0.1:3306)/go_test?charset=utf8mb4")// 错误密码
	fmt.Println("err:", err)
	if db == nil {
		fmt.Println("db open failed:", err)
	} else {
		defer db.Close()
	}

	// 2. 判断数据库是否连接成功。
	err = db.Ping() //Ping verifies a connection to the database is still alive, establishing a connection if necessary
	if err != nil {
		fmt.Println("数据库链接失败", err)
	}

	fmt.Println("db connect success")
}

密码正确时：

密码错误时：

2 mysql插入数据

package main

import (
	"database/sql"
	"fmt"

	_ "github.com/go-sql-driver/mysql"
)

// 插入数据
func insertRowDemo(db *sql.DB) {
	// 1. 编写sql语句。两个问号代表占位符，当我们执行Exec函数时，只需要输入数据即可。与Qt的mysql占位符是一样的。
	sqlStr := "insert into user(name, age) values (?,?)"

	// 2. 数据插入到占位符，然后执行sql语句。
	ret, err := db.Exec(sqlStr, "tyy", 18)
	if err != nil {
		fmt.Printf("insert failed, err:%v\n", err)
		return
	}
	theID, err := ret.LastInsertId() // 新插入数据的id
	if err != nil {
		fmt.Printf("get lastinsert ID failed, err:%v\n", err)
		return
	}
	fmt.Printf("insert success, the id is %d.\n", theID)
}

func main() {
	db, err := sql.Open("mysql", "root:123456@tcp(127.0.0.1:3306)/go_test?charset=utf8mb4")
	fmt.Println("err:", err)
	if db != nil {
		defer db.Close()
	}
	err = db.Ping()
	if err != nil {
		fmt.Println("数据库链接失败", err)
		return
	}

	insertRowDemo(db)
}

结果：

3 mysql查询

3.1 单行查询

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

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

package main

import (
	"database/sql"
	"fmt"

	_ "github.com/go-sql-driver/mysql"
)

type user struct {
	id   int
	name string
	age  int
}

// 查询单条数据示例
func queryRowDemo(db *sql.DB) {
	sqlStr := "select id, name, age from user where id=?"
	var u user

	// 非常重要：确保QueryRow之后调用Scan方法，否则持有的数据库链接不会被释放
	err := db.QueryRow(sqlStr, 1).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 main() {
	db, err := sql.Open("mysql", "root:123456@tcp(127.0.0.1:3306)/go_test?charset=utf8mb4")
	fmt.Println("err:", err)
	if db != nil {
		defer db.Close()
	}
	err = db.Ping()
	if err != nil {
		fmt.Println("数据库链接失败", err)
		return
	}

	queryRowDemo(db)
}

成功：

失败，例如输入一个不存在的id，由于找不到结果，所以返回失败：

3.2 多行查询

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

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

package main

import (
	"database/sql"
	"fmt"

	_ "github.com/go-sql-driver/mysql"
)

type user struct {
	id   int
	name string
	age  int
}

// 查询多条数据示例
func queryMultiRowDemo(db *sql.DB) {
	sqlStr := "select id, name, age from user where id > ?"
	rows, err := db.Query(sqlStr, 0)
	if err != nil {
		fmt.Printf("query failed, err:%v\n", err)
		return
	}
	// 非常重要：关闭rows释放持有的数据库链接
	defer rows.Close()

	// 循环读取结果集中的数据
	for rows.Next() {
		var u user
		err := rows.Scan(&u.id, &u.name, &u.age) // 通过SCAN读取出来
		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 main() {
	db, err := sql.Open("mysql", "root:123456@tcp(127.0.0.1:3306)/go_test?charset=utf8mb4")
	fmt.Println("err:", err)
	if db != nil {
		defer db.Close()
	}
	err = db.Ping()
	if err != nil {
		fmt.Println("数据库链接失败", err)
		return
	}
	queryMultiRowDemo(db)
}

多行查询时，会把数据库中满足条件的行数返回。

4 mysql更新

package main

import (
	"database/sql"
	"fmt"

	_ "github.com/go-sql-driver/mysql"
)

type user struct {
	id   int
	name string
	age  int
}

// 更新数据
func updateRowDemo(db *sql.DB) {
	sqlStr := "update user set age=? where id = ?"
	ret, err := db.Exec(sqlStr, 100, 2)
	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)
}

func main() {
	db, err := sql.Open("mysql", "root:123456@tcp(127.0.0.1:3306)/go_test?charset=utf8mb4")
	if db != nil {
		defer db.Close()
	}
	err = db.Ping()
	if err != nil {
		fmt.Println("数据库链接失败", err)
		return
	}
	updateRowDemo(db)
}

5 mysql删除

package main

import (
	"database/sql"
	"fmt"

	_ "github.com/go-sql-driver/mysql"
)

type user struct {
	id   int
	name string
	age  int
}

// 删除数据
func deleteRowDemo(db *sql.DB) {
	sqlStr := "delete from user where id = ?"
	ret, err := db.Exec(sqlStr, 1)
	if err != nil {
		fmt.Printf("delete 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("delete success, affected rows:%d\n", n)
}

func main() {
	db, err := sql.Open("mysql", "root:123456@tcp(127.0.0.1:3306)/go_test?charset=utf8mb4")
	if db != nil {
		defer db.Close()
	}
	err = db.Ping()
	if err != nil {
		fmt.Println("数据库链接失败", err)
		return
	}
	deleteRowDemo(db)
}

可以看到id=1行数据被删除了。

二 MySQL预处理

什么是预处理？
1. 普通SQL语句执行过程：

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

2. 预处理执行过程：

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

为什么要预处理？

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

例如，我们上面插入多条时不使用预处理，如下图一，那么我们就需要发送3次给mysql服务器，mysql服务器需要解析3次。
而我们使用预处理的话，如图二，虽然会多发送一次Prepare，但是由于mysql服务器提前编译解析了该sql命令，所以后面的数据只需要代入即可，也就说预处理总共只需要编译解析一次。而不用预处理的话，需要编译解析3次，效率难免降低。并且当插入的语句越多时，预处理的效率高性能就越突出。

图一：

图二：

1 Go实现MySQL预处理

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

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

案例，非常简单：

package main

import (
	"database/sql"
	"fmt"

	_ "github.com/go-sql-driver/mysql"
)

type user struct {
	id   int
	name string
	age  int
}

// 预处理查询示例
func prepareQueryDemo(db *sql.DB) {
	// 1. 先把sql命令提前发送给mysql服务器，让其编译解析。
	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()

	// 2. 此后只需要发送数据给mysql服务器即可，mysql不需要再进行sql命令解析的工作。
	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(db *sql.DB) {
	// 1. 先把sql命令提前发送给mysql服务器，让其编译解析。
	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()

	// 2. 此后只需要发送数据给mysql服务器即可，mysql不需要再进行sql命令解析的工作。
	_, err = stmt.Exec("tyy1", 18)
	if err != nil {
		fmt.Printf("insert failed, err:%v\n", err)
		return
	}
	_, err = stmt.Exec("tyy2", 18)
	if err != nil {
		fmt.Printf("insert failed, err:%v\n", err)
		return
	}
	_, err = stmt.Exec("tyy3", 18)
	if err != nil {
		fmt.Printf("insert failed, err:%v\n", err)
		return
	}
	fmt.Println("insert success.")
}

func main() {
	db, err := sql.Open("mysql", "root:123456@tcp(127.0.0.1:3306)/go_test?charset=utf8mb4")
	// fmt.Println("err:", err)
	if db != nil {
		defer db.Close()
	}
	err = db.Ping()
	if err != nil {
		fmt.Println("数据库链接失败", err)
		return
	}
	prepareInsertDemo(db)
	prepareQueryDemo(db)
}

三 Go实现MySQL事务

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

• 1）开始事务： func (db *DB) Begin() (*Tx, error)
• 2）提交事务： func (tx *Tx) Commit() error
• 3）回滚事务： func (tx *Tx) Rollback() error

案例：

package main

import (
	"database/sql"
	"fmt"

	_ "github.com/go-sql-driver/mysql"
)

type user struct {
	id   int
	name string
	age  int
}

// 预处理查询示例
func prepareQueryDemo(db *sql.DB) {
	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(db *sql.DB) {
	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("tyy", 24)
	if err != nil {
		fmt.Printf("insert failed, err:%v\n", err)
		return
	}
	_, err = stmt.Exec("hc", 24)
	if err != nil {
		fmt.Printf("insert failed, err:%v\n", err)
		return
	}
	fmt.Println("insert success.")
}

// 事务操作示例
func transactionDemo(db *sql.DB) {
	// 1. 开启事务
	tx, err := db.Begin()
	if err != nil {
		// 1.1 发生错误则回滚.
		if tx != nil {
			tx.Rollback()
		}
		fmt.Printf("begin trans failed, err:%v\n", err)
		return
	}

	// 2. 插入，开始事务后的命令操作。
	sqlStr1 := "Update user set age=30 where id=?"
	_, err = tx.Exec(sqlStr1, 2)
	if err != nil {
		// 2.1 如果插入失败，则回滚。
		tx.Rollback()
		fmt.Printf("exec sql1 failed, err:%v\n", err)
		return
	}

	fmt.Println("+++++++++ 2 insert ok")

	// 3. 插入，开始事务后的命令操作。
	sqlStr2 := "Update user set age=40 where id=?"
	_, err = tx.Exec(sqlStr2, 4)
	if err != nil {
		// 3.1 回滚
		tx.Rollback()
		fmt.Printf("exec sql2 failed, err:%v\n", err)
		return
	}

	fmt.Println("+++++++++ 3 insert ok")

	// 4. 提交事务
	err = tx.Commit()
	if err != nil {
		tx.Rollback() // 回滚
		fmt.Printf("commit failed, err:%v\n", err)
		return
	}

	fmt.Println("exec trans success!")
}

func main() {
	db, err := sql.Open("mysql", "root:123456@tcp(127.0.0.1:3306)/go_test?charset=utf8mb4")
	if db != nil {
		defer db.Close() // 健壮的写法
	}
	err = db.Ping()
	if err != nil {
		fmt.Println("数据库链接失败", err)
		return
	}

	db.SetMaxOpenConns(10)
	db.SetMaxIdleConns(5)
	stats := db.Stats()
	fmt.Println("stats1: ", stats) // 用于查看数据库连接池的状态，例如最大连接数，正在使用的连接数，空闲连接数等等。

	prepareInsertDemo(db) // 这里调用插入和查询主要是想查看连接池的状态。
	prepareQueryDemo(db)
	stats = db.Stats()
	fmt.Println("stats2: ", stats)

	// 事务操作。
	transactionDemo(db)
}

结果：

例如，我们想验证开启事务后的操作失败时，能否正常回滚，我们只需要在if判断时将 != 改成 == 即可：

if err != nil {
	// 2.1 如果插入失败，则回滚。
	tx.Rollback()
	fmt.Printf("exec sql1 failed, err:%v\n", err)
	return
}
// 改成
if err == nil {
	// 2.1 如果插入失败，则回滚。
	tx.Rollback()
	fmt.Printf("exec sql1 failed, err:%v\n", err)
	return
}

将第3步发if条件改成 == ，然后将第2步的age=30，改成age=50，以方便我们对比是否进行了回滚。如果id=2的数据还是原始数据age=30，说明回滚成功，如果是age=50，说明回滚失败。

截图验证，可以看到，虽然第2步插入成功，但是id=2的age仍然是30，说明回滚了。注意，打印的id的顺序不需要理会，这是我为了测试从id=10后，删除了几条数据而已。

同理，我们可以模拟第4步发送错误，即提交事务时出错了。

// 提交事务时将!=改成==即可模拟错误。
if err == nil {
	tx.Rollback() // 回滚
	fmt.Printf("commit failed, err:%v\n", err)
	return
}

然后将第2、3步插入的语句的age都改成1000。如果数据没变成1000，说明回滚成功，否则回滚失败。

验证截图，可以看到，虽然两条插入语句都成功了，但是提交事务失败，回滚成功，所以age都保持为原来的数据。

四 第三方库sqlx

sqlx是一个第三方库，看源码看出，它是对上面的"github.com/go-sql-driver/mysql"库的接口进行封装而已，最终sqlx.go都是调用到sql.go，操作是一样的，不作过多解释。

package main

import (
	"fmt"

	_ "github.com/go-sql-driver/mysql"
	"github.com/jmoiron/sqlx"
)

type user struct {
	ID   int    `json:"id" db:"id"`
	Name string `json:"name" db:"name"`
	Age  int    `json:"age" db:"age"`
}

// 一个全局的sqlx.DB数据库连接池对象。
var db *sqlx.DB

// 连接数据库
func initDB() (err error) {
	// 1. 连接数据库。
	// 跳转源码看到，sqlx是对原有的"github.com/go-sql-driver/mysql"接口进行封装，方便我们使用，大家可以根据实际需要进行选择。
	dsn := "root:123456@tcp(127.0.0.1:3306)/go_test?charset=utf8mb4"
	// 也可以使用MustConnect连接不成功就panic
	db, err = sqlx.Connect("mysql", dsn)
	if err != nil {
		fmt.Printf("connect DB failed, err:%v\n", err)
		return
	}

	// 2. 设置连接池的属性。
	db.SetMaxOpenConns(20)
	db.SetMaxIdleConns(10)
	return
}

// 查询单条数据
func queryRowDemo() {
	sqlStr := "select id, name, age from user where id=?"
	var u user
	err := db.Get(&u, sqlStr, 2) // 单条查询
	if err != nil {
		fmt.Printf("get failed, err:%v\n", err)
		return
	}
	fmt.Printf("id:%d name:%s age:%d\n", u.ID, u.Name, u.Age)
}

// 查询多行数据
func queryMultiRowDemo() {
	sqlStr := "select id, name, age from user where id > ?"
	var users []user
	err := db.Select(&users, sqlStr, 0) // 主要是查询
	if err != nil {
		fmt.Printf("query failed, err:%v\n", err)
		return
	}
	fmt.Printf("users:%#v\n", users)
}

// 插入数据
func insertRowDemo() {
	sqlStr := "insert into user(name, age) values (?,?)"
	ret, err := db.Exec(sqlStr, "蔡文姬", 18)
	if err != nil {
		fmt.Printf("insert failed, err:%v\n", err)
		return
	}
	theID, err := ret.LastInsertId() // 新插入数据的id
	if err != nil {
		fmt.Printf("get lastinsert ID failed, err:%v\n", err)
		return
	}
	fmt.Printf("insert success, the id is %d.\n", theID)
}

// 更新数据
func updateRowDemo() {
	sqlStr := "update user set age=? where id = ?"
	ret, err := db.Exec(sqlStr, 39, 6)
	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)
}

// 删除数据
func deleteRowDemo() {
	sqlStr := "delete from user where id = ?"
	ret, err := db.Exec(sqlStr, 6)
	if err != nil {
		fmt.Printf("delete 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("delete success, affected rows:%d\n", n)
}

// 事务操作
func transactionDemo() {
	tx, err := db.Beginx() // 开启事务
	if err != nil {
		if tx != nil {
			tx.Rollback()
		}
		fmt.Printf("begin trans failed, err:%v\n", err)
		return
	}

	sqlStr1 := "Update user set age=40 where id=?"
	tx.MustExec(sqlStr1, 2) // 等价于Tx.Exec，因为最终还是调用到sql.go的func (tx *Tx) Exec(query string, args ...interface{}) (Result, error)
	sqlStr2 := "Update user set age=50 where id=?"
	tx.MustExec(sqlStr2, 4)

	err = tx.Commit() // 提交事务
	if err != nil {
		tx.Rollback() // 回滚
		fmt.Printf("commit failed, err:%v\n", err)
		return
	}
	fmt.Println("exec trans success!")
}

func getNum() {
	var num int
	_ = db.Get(&num, "select count(*) from user")
	fmt.Printf("数据库一共有：%d 个用户\n", num)
	var u user
	_ = db.Get(&u, "select name, id, age from user where id = ?", 1)
	fmt.Printf("查找用户id==1的用户:%v \n", u)
}

func main() {
	// 1. 连接数据库。
	err := initDB()
	if err != nil {
		fmt.Println("initDB: ", err)
		return
	}

	insertRowDemo()
	queryRowDemo()
	getNum()
	queryMultiRowDemo()

	if db != nil {
		defer db.Close()
	}
}

结果：