Go中使用database/sql操作MySQL数据库

Go语言中的database/sql包提供了保证SQL或类SQL数据库的泛用接口，并不提供具体的数据库驱动。使用database/sql包时必须注入（至少）一个数据库驱动。 我们常用的数据库基本上都有完整的第三方实现。例如：MySQL驱动

下载驱动

在Go中使用go get命令来下载一些依赖，会将这些依赖保存在$GOPATH/src/xxx.com下，如果是在github.com上下载的依赖就会保存在$GOPATH/src/github.com下。

使用如下命令下载MySQL驱动：

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

其中-u表示下载最新的。

使用驱动

用法如下：

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

db, err := sql.Open("mysql", "user:password@/dbname")

其中：

• 参数1表示驱动的名字
• 参数2表示连接数据库的一些信息

如下：

package main

// 导入包
import (
	"database/sql"

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

func main() {
	// 配置数据库的一些源信息
	dsn := "root:root@tcp(127.0.0.1:3306)/dbName"
	// 打开连接
	db, err := sql.Open("mysql", dsn)
	if err != nil {
		panic(err)
	}
	defer db.Close()
}

初始化连接

Open函数可能只是验证其参数格式是否正确，实际上并不创建与数据库的连接。如果要检查数据源的名称是否真实有效，应该调用Ping方法。 返回的DB对象可以安全地被多个goroutine并发使用，并且维护其自己的空闲连接池。因此，Open函数应该仅被调用一次，很少需要关闭这个DB对象。

package main

// 导入包
import (
	"database/sql"

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

func main() {
	// 配置数据库的一些源信息
	dsn := "root:root@tcp(127.0.0.1:3306)/dbName"
	// 打开连接
	db, err := sql.Open("mysql", dsn)
	if err != nil {
		panic(err)
	}
    // 初始化连接，就是检查用户名密码等等是否正确
	err = db.Ping()
	if err != nil {
		panic(err)
	}
}

其中sql.DB是一个数据库（操作）句柄，代表一个具有零到多个底层连接的连接池。它可以安全地被多个goroutine同时使用。database/sql包会自动创建和释放连接；它也会维护一个闲置连接的连接池。

sql.DB还有两个方法：

• SetMaxOpenConns
• SetMaxIdleConns

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

比如：

	db.SetMaxOpenConns(10)
	db.SetMaxIdleConns(10)

CRUD操作

建库建表

前提：安装好MySQL数据库，通过mysql -u xxx -p xxx进入数据库

在数据库中建一个库，命令如下：

 create database coolops;

创建表，命令如下：

use coolops;
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;

查看创建的表：

> show tables;
+-------------------+
| Tables_in_coolops |
+-------------------+
| user              |
+-------------------+

初始化数据库连接

package main

import (
	"database/sql"
	"fmt"

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

// 声明一个全局变量db
var db *sql.DB

// 创建一个初始化数据库的函数
func initDB(dsn string) (err error) {
	db, err = sql.Open("mysql", dsn)
	if err != nil {
		return
	}
	err = db.Ping()
	if err != nil {
		return
	}
	db.SetMaxIdleConns(10)
	db.SetMaxOpenConns(10)
	fmt.Println("数据库连接成功")
	return
}

func main() {
	dsn := "coolops:coolopsP@ssW0rd@tcp(127.0.0.1:3306)/coolops"
	err := initDB(dsn)
	if err != nil {
		fmt.Println("数据库初始化失败. err:", err)
		return
	}
}

插入数据

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

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

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

具体插入数据示例代码如下：

// 插入数据
func insertData() {
	sqlStr := "insert into user(name, age) values (?,?)"
	ret, err := db.Exec(sqlStr, "王五", 38)
	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 insertManyData() {
	sqlStr := "insert into user(name, age) values (?,?)"
	insertInfo := map[string]int{
		"张三":  20,
		"李四":  30,
		"李世民": 9000,
		"孙悟空": 100000,
	}
	for k, v := range insertInfo {
		ret, err := db.Exec(sqlStr, k, v)
		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)
	}
}

进数据库查看：

> select * from user;
+----+-----------+--------+
| id | name      | age    |
+----+-----------+--------+
|  1 | 王五      |     38 |
|  2 | 张三      |     20 |
|  3 | 李四      |     30 |
|  4 | 李世民    |   9000 |
|  5 | 孙悟空    | 100000 |
+----+-----------+--------+

查询数据

单行查询

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

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

具体示例代码：

// 查询数据
func selectData(id int) {
	var u user
	sqlStr := "select id,name,age from user where id=?"
	row := db.QueryRow(sqlStr, id)
	err := row.Scan(&u.id, &u.name, &u.age)
	if err != nil {
		fmt.Println("数据读取失败. err:", err)
		return
	}
	// 输出数据
	fmt.Printf("id:%d name:%s age:%d\n", u.id, u.name, u.age)
}

注意：在QueryRow后要调用Scan方法，不然数据库连接不会被释放

多行查询

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

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

示例代码如下：

// 查询多行数据
func selectManyData(id int) {
	sqlStr := "select id,name,age from user where id>?"
	rows, err := db.Query(sqlStr, id)
	if err != nil {
		fmt.Println("数据查询失败. err:", err)
	}
	defer rows.Close()
	// 循环读数据
	for rows.Next() {
		var u user
		err := rows.Scan(&u.id, &u.name, &u.age)
		if err != nil {
			fmt.Println("数据读取失败. err:", err)
			return
		}
		// 输出数据
		fmt.Printf("id:%d name:%s age:%d\n", u.id, u.name, u.age)
	}
}

查询多行数据必须调用Close()方法来释放数据库连接

更新数据

更新数据也是用上面介绍的Exec()方法。

示例代码如下：

// 更新数据
func updateData(age, id int) {
	sqlStr := "update user set age=? where id = ?"
	ret, err := db.Exec(sqlStr, age, id)
	if err != nil {
		fmt.Println("数据更新失败. err:", err)
		return
	}
	// 操作影响的行数
	rowNum, err := ret.RowsAffected()
	if err != nil {
		fmt.Println("获取影响的行数失败. err:", err)
		return
	}
	// 打印影响的行数
	fmt.Printf("修改成功，影响行数:%d\n", rowNum)
}

删除数据

删除数据也是用Exec()方法。

示例代码如下:

// 删除数据
func deleteData(id int) {
	sqlStr := "delete from user where id = ?"
	ret, err := db.Exec(sqlStr, id)
	if err != nil {
		fmt.Printf("数据删除失败, err:%v\n", err)
		return
	}
	n, err := ret.RowsAffected() // 操作影响的行数
	if err != nil {
		fmt.Printf("获取受影响行数失败, err:%v\n", err)
		return
	}
	fmt.Printf("删除成功，影响行数:%d\n", n)
}

完整代码

package main

import (
	"database/sql"
	"fmt"

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

// 声明一个全局变量db
var db *sql.DB

// 声明一个user结构体，用来保存查询数据
type user struct {
	id   int
	name string
	age  int
}

// 创建一个初始化数据库的函数
func initDB(dsn string) (err error) {
	db, err = sql.Open("mysql", dsn)
	if err != nil {
		return
	}
	err = db.Ping()
	if err != nil {
		return
	}
	db.SetMaxIdleConns(10)
	db.SetMaxOpenConns(10)
	fmt.Println("数据库连接成功")
	return
}

// 插入单条数据
func insertData() {
	sqlStr := "insert into user(name, age) values (?,?)"
	ret, err := db.Exec(sqlStr, "王五", 38)
	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 insertManyData() {
	sqlStr := "insert into user(name, age) values (?,?)"
	insertInfo := map[string]int{
		"张三":  20,
		"李四":  30,
		"李世民": 9000,
		"孙悟空": 100000,
	}
	for k, v := range insertInfo {
		ret, err := db.Exec(sqlStr, k, v)
		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 selectData(id int) {
	var u user
	sqlStr := "select id,name,age from user where id=?"
	row := db.QueryRow(sqlStr, id)
	err := row.Scan(&u.id, &u.name, &u.age)
	if err != nil {
		fmt.Println("数据读取失败. err:", err)
		return
	}
	// 输出数据
	fmt.Printf("id:%d name:%s age:%d\n", u.id, u.name, u.age)
}

// 查询多行数据
func selectManyData(id int) {
	sqlStr := "select id,name,age from user where id>?"
	rows, err := db.Query(sqlStr, id)
	if err != nil {
		fmt.Println("数据查询失败. err:", err)
	}
	defer rows.Close()
	// 循环读数据
	for rows.Next() {
		var u user
		err := rows.Scan(&u.id, &u.name, &u.age)
		if err != nil {
			fmt.Println("数据读取失败. err:", err)
			return
		}
		// 输出数据
		fmt.Printf("id:%d name:%s age:%d\n", u.id, u.name, u.age)
	}
}

// 更新数据
func updateData(age, id int) {
	sqlStr := "update user set age=? where id = ?"
	ret, err := db.Exec(sqlStr, age, id)
	if err != nil {
		fmt.Println("数据更新失败. err:", err)
		return
	}
	// 操作影响的行数
	rowNum, err := ret.RowsAffected()
	if err != nil {
		fmt.Println("获取影响的行数失败. err:", err)
		return
	}
	// 打印影响的行数
	fmt.Printf("修改成功，影响行数:%d\n", rowNum)
}

// 删除数据
func deleteData(id int) {
	sqlStr := "delete from user where id = ?"
	ret, err := db.Exec(sqlStr, id)
	if err != nil {
		fmt.Printf("数据删除失败, err:%v\n", err)
		return
	}
	n, err := ret.RowsAffected() // 操作影响的行数
	if err != nil {
		fmt.Printf("获取受影响行数失败, err:%v\n", err)
		return
	}
	fmt.Printf("删除成功，影响行数:%d\n", n)
}

func main() {
	dsn := "coolops:coolopsP@ssW0rd@tcp(127.0.0.1:3306)/coolops"
	err := initDB(dsn)
	if err != nil {
		fmt.Println("数据库初始化失败. err:", err)
		return
	}
	// 插入数据操作
	// insertData()
	// insertManyData()
	// selectData(2)
	// selectManyData(0)
	// updateData(40, 3)
	deleteData(3)
}

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中使用*sql.DB.prepare()来处理预处理问题。 方法如下：

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

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

示例代码如下：

package main

import (
	"database/sql"
	"fmt"

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

// 声明一个全局变量db
var db *sql.DB

// 声明一个user结构体，用来保存查询数据
type user struct {
	id   int
	name string
	age  int
}

// 创建一个初始化数据库的函数
func initDB(dsn string) (err error) {
	db, err = sql.Open("mysql", dsn)
	if err != nil {
		return
	}
	err = db.Ping()
	if err != nil {
		return
	}
	db.SetMaxIdleConns(10)
	db.SetMaxOpenConns(10)
	fmt.Println("数据库连接成功")
	return
}

// 插入单条数据
func prepareInsertData() {
	sqlStr := "insert into user(name, age) values (?,?)"
	stmt, err := db.Prepare(sqlStr)
	if err != nil {
		fmt.Println("预处理失败。err:", err)
		return
	}
	_, err = stmt.Exec("唐僧", 4000)
	if err != nil {
		fmt.Printf("insert failed, err:%v\n", err)
		return
	}
	fmt.Printf("数据插入成功\n")
}

// 查询数据
func prepareSelectData(id int) {
	var u user
	sqlStr := "select id,name,age from user where id=?"
	stmt, err := db.Prepare(sqlStr)
	if err != nil {
		fmt.Println("预处理失败。err:", err)
		return
	}
	defer stmt.Close()
	rows, err := stmt.Query(id)
	if err != nil {
		fmt.Println("预处理查询失败. err:", err)
		return
	}
	for rows.Next() {
		err := rows.Scan(&u.id, &u.name, &u.age)
		if err != nil {
			fmt.Println("数据读取失败. err:", err)
			return
		}
		// 输出数据
		fmt.Printf("id:%d name:%s age:%d\n", u.id, u.name, u.age)
	}
}

// 更新数据
func prepareUpdateData(age, id int) {
	sqlStr := "update user set age=? where id = ?"
	stmt, err := db.Prepare(sqlStr)
	if err != nil {
		fmt.Println("预处理失败. err:", err)
		return
	}
	_, err = stmt.Exec(age, id)
	if err != nil {
		fmt.Println("数据更新失败. err:", err)
		return
	}

	// 打印影响的行数
	fmt.Printf("修改成功\n")
}

// 删除数据
func prepareDeleteData(id int) {
	sqlStr := "delete from user where id = ?"
	stmt, err := db.Prepare(sqlStr)
	if err != nil {
		fmt.Println("预处理失败. err:", err)
		return
	}
	_, err = stmt.Exec(id)
	if err != nil {
		fmt.Printf("数据删除失败, err:%v\n", err)
		return
	}
	fmt.Printf("删除成功\n")
}

func main() {
	dsn := "coolops:coolopsP@ssW0rd@tcp(122.51.79.172:3306)/coolops"
	err := initDB(dsn)
	if err != nil {
		fmt.Println("数据库初始化失败. err:", err)
		return
	}
	// prepareSelectData(1)
	// prepareUpdateData(80, 1)
	// prepareDeleteData(5)
	prepareInsertData()
}

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中的事务操作。 开始事务

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

提交事务

func (tx *Tx) Commit() error

回滚事务

func (tx *Tx) Rollback() error

示例代码

// 事务操作
func transaction() {
	// 开启事务
	tx, err := db.Begin()
	if err != nil {
		fmt.Println("开启事务失败。err:", err)
		return
	}
	sqlStr1 := "update user set age=age-? where id =?"
	_, err = tx.Exec(sqlStr1, 3, 4)
	if err != nil {
		fmt.Println("SQL 1 执行失败，准备回滚")
		tx.Rollback()
		return
	}
	sqlStr2 := "update user set age=age+? where id =?"
	_, err = tx.Exec(sqlStr2, 3, 6)
	if err != nil {
		fmt.Println("SQL 2 执行失败，准备回滚")
		tx.Rollback()
		return
	}
	// 如果上面都没错。则提交事务
	err = tx.Commit()
	if err != nil {
		fmt.Println("事务提交失败，准备回滚")
		tx.Rollback()
		return
	}
	// 如果代码走到了这里，则代表事务处理成功
	fmt.Println("执行事务成功")
}

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 users []user
	err := db.Select(&users, sqlStr)
	if err != nil {
		fmt.Printf("exec failed, err:%v\n", err)
		return
	}
	for _, u := range users {
		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 #")

SQL中的占位符

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

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