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

携手创作，一起生长！这是我参与「日新方案 8 月更文挑战」的第2天。

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句子履行过程：

客户端对SQL句子进行占位符替换得到完好的SQL句子。
客户端发送完好SQL句子到MySQL服务端
MySQL服务端履行完好的SQL句子并将成果回来给客户端。

预处理履行过程：

把SQL句子分成两部分，指令部分与数据部分。
先把指令部分发送给MySQL服务端，MySQL服务端进行SQL预处理。
然后把数据部分发送给MySQL服务端，MySQL服务端对SQL句子进行占位符替换。
MySQL服务端履行完好的SQL句子并将成果回来给客户端。

为什么要预处理？

优化MySQL服务器重复履行SQL的办法，能够提升服务器功能，提前让服务器编译，一次编译屡次履行，节约后续编译的本钱。
避免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`

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

下载驱动

运用驱动

初始化衔接

CRUD操作

建库建表

初始化数据库衔接

刺进数据

查询数据

单行查询

多行查询

更新数据

删去数据

完好代码

MySQL预处理

什么是预处理？

为什么要预处理？

运用预处理

MySQL业务

什么是业务？

业务的ACID

业务相关办法

示例代码

SQL注入

SQL中的占位符

相关文章

OceanBase 如何通过日志观测冻结转储流程？

暗光图像处理

iOS核心基础与面试题之block

基于GPT3打造你的专属的个人知识库（一）

作者信息