Go操作mysql

2020-12-18 约 7173 字预计阅读 15 分钟次阅读

SQL科普

bindvars绑定变量

在MYSQL语句中经常会出现占位符 ? ，在SQL内部称为bindvars查询占位符
日常的SQL语句中，任何时候都不应该自己拼接SQL语句，应该始终使用bindvars占位符来对数据发送值，防止SQL注入攻击
在GO语言中的database/sql中，不会尝试对查询文本进行任何校验，它与编码参数一起按照原样发送到服务器。
不同的数据库有不同的占位符
- Mysql中使用 ?
- PostgreSQL中使用$1、$2等枚举的bindvars语法
- SQLite中?与$1、$2等枚举的语法都支持
- Oracle中使用的:name的语法
注意
- bindvars仅用于参数化，不允许更改SQL语句的结构
- bindvars占位符不能用于插入表名，如：select * from ?这种是不被允许的
- bindvars占位符不能用于插入列名，如：select ?,? from user这也是不被允许的

SQL标准库

GO语言中的database/sql包提供了保证SQL或类SQL数据库的范接口，但是并不提供具体的数据库驱动。在使用database/sql包时，必须注入至少一个数据库驱动

下载Mysql驱动

1

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

连接数据库的例子

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26


package main

import (
	"database/sql"
	//引入mysql驱动，只需用到init()方法
	_ "github.com/go-sql-driver/mysql"
)

func main(){
	//连接mysql字符串  格式：  用户名:密码@tcp(主机地址:端口)/库名
	dsn := "root:root@tcp(192.168.116.90:3306)/sql_test"

	db,err := sql.Open("mysql",dsn)
	if err != nil{
		panic(err)
	}

	//关闭数据库连接
	defer db.Close()

	//Ping函数 尝试校验dsn,判断账号密码地址是否正确，也就是看是不是能连接上
	err = db.Ping()
	if err != nil{
		panic(err)
	}
}

sql.Open函数

1
2
3
4


func Open(driverName, dataSourceName string) (*DB, error)

driverName - 指具体的数据库类型, 如: mysql
dataSourceName - 指具体的数据信息, 如: 账号、密码、协议、IP、端口、数据库名 等。

初始化连接的例子

sql.Open返回的 DB 对象可以安全的被多个goroutine并发使用, 并维护自己的空闲连接池, 因此很少需要关闭 DB 这个对象。
SetMaxOpenConns设置与数据库建立连接的最大连接数，如果n大于0且小于最大闲置连接数，会将最大闲置连接数减小到匹配最大开启连接数的限制。如果n<=0，不会限制最大开启连接数，默认为0（无限制）。
SetMaxIdleConns设置连接池中最大空闲连接数，如果n大于最大开启连接数，则新的最大闲置连接数会减小到匹配最大开启连接数的限制。如果n<=0，不会保留闲置连接。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45


package main

import (
	"database/sql"
	//引入mysql驱动，只需用到init()方法
	_ "github.com/go-sql-driver/mysql"
)

//定义全局的 数据库对象DB
var DB *sql.DB

//初始化DB函数
func initDB()(err error){
	//连接mysql字符串  格式：  用户名:密码@tcp(主机地址:端口)/库名
	dsn := "root:root@tcp(192.168.116.90:3306)/sql_test"

	DB,err = sql.Open("mysql",dsn)
	if err != nil{
		return err
	}

	//关闭数据库连接
	//defer DB.Close()

	//Ping函数 尝试校验dsn,判断账号密码地址是否正确，也就是看是不是能连接上
	err = DB.Ping()
	if err != nil{
		return err
	}

	////最大连接数
	DB.SetMaxOpenConns(60)
	// 连接池中空闲连接数
	DB.SetMaxIdleConns(50)
	return err
}

func main(){
	err := initDB()
	if err != nil{
		panic(err)
	}
	defer DB.Close()
}

增删改查操作

环境准备

建库建表

 1
 2
 3
 4
 5
 6
 7
 8
 9
10


CREATE DATABASE sql_test;
use sql_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;

insert into user(name,age) values('xx',20),('yy',20),('zz',18);

查询操作

单行查询

单行查询使用DB.QueryRow()方法执行一次查询，并返回最多一行结果（Row）

QueryRow方法，总是返回非nil值，知道返回值的Scan方法被调用时才会返回被延迟的错误。如：未查询到结果

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25


//根据主键id查询
func queryRowToId(id int)  {
	sqlStr := "select id, name, age from user where id=?"
	var u user
	// 非常重要：确保QueryRow之后调用Scan方法，否则持有的数据库链接不会被释放
	err := DB.QueryRow(sqlStr, id).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(){
	err := initDB()
	if err != nil{
		panic(err)
	}
	defer DB.Close()
	queryRowToId(2)
}

输出结果：
id:2 name:yy age:20

多行查询

多行查询使用 DB.Query() 方法执行一次查询, 返回多行结果(Row)。
func (db *DB) Query(query string, args ...interface{}) (*Rows, error) 方法

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36


func queryMultiRowToId(id int){
	var u user
	sqlStr := "select id,name,age from user where id > ?"
	rows,err := DB.Query(sqlStr,id)
	if err != nil{
		fmt.Printf("QueryMulti failed ,Error %v\n",err)
		return
	}

	// 关闭数据库连接
	defer  rows.Close()
	//遍历读取多条数据
	for rows.Next(){
		err := rows.Scan(&u.id,&u.name,&u.age)
		if err != nil{
			fmt.Printf("QueryMulti failed ,Error %v\n",err)
			return
		}
		fmt.Printf("ID: %d Name: %s  Age: %d \n", u.id, u.name, u.age)
	}

}

func main(){
	err := initDB()
	if err != nil{
		panic(err)
	}
	defer DB.Close()
	queryMultiRowToId(1)
}

输出：
ID: 2 Name: yy  Age: 20
ID: 3 Name: zz  Age: 18

插入数据操作

插入、更新、删除操作都可以使用Exec方法
- Exec执行一次命令（包含查询、删除、更新、插入等），返回的Result是对已执行的SQL命令的总结。
- func (db *DB) Exec(query string, args ...interface{}) (Result, error) 方法

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30


func insertRow(name string ,age  int){
	sqlStr := "insert into user(name,age) values(?,?)"
	ret ,err := DB.Exec(sqlStr,name,age)
	if err != nil{
		fmt.Printf("Insert row error %v\n",err)
		return
	}
	var inId int64
	//获取最后一次插入数据的ID，也就是当前新插入数据的ID
	inId,err = ret.LastInsertId()
	if err != nil{
		fmt.Printf("Get LastinsertId Error %v\n",err)
		return
	}
	fmt.Printf("Insert success ID %d\n",inId)
}

func main(){
	err := initDB()
	if err != nil{
		panic(err)
	}
	defer DB.Close()
	//queryMultiRowToId(1)
	insertRow("赶路人",9)
}

输出结果：
Insert success ID 4

更新数据集操作

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35


func updateRowToAge(age,id int){
	sqlStr := "update user set age = ? where id = ?"
	ret,err := DB.Exec(sqlStr,age,id)
	if err != nil{
		fmt.Printf("update failed error %v\n",err)
		return
	}
	var n int64
	//RowAffected 返回更新影响的行数
	n,err = ret.RowsAffected()
	if err != nil{
		fmt.Printf("Get RowAffected failed error %v\n",err)
		return
	}
	fmt.Printf("update Success Affected Row: %d \n",n)
}

func main(){
	err := initDB()
	if err != nil{
		panic(err)
	}
	defer DB.Close()
	//queryMultiRowToId(1)
	//insertRow("赶路人",9)
	queryRowToId(4)
	updateRowToAge(16,4)
	queryRowToId(4)
}

输出：
id:4 name:赶路人 age:9
update Success Affected Row: 1
id:4 name:赶路人 age:16

删除数据

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40


func deleteRowToId(id int)  {
	sqlStr := "delete from user where id = ?"
	ret ,err := DB.Exec(sqlStr,id)
	if err != nil{
		fmt.Printf("delete faild error %v\n",err)
		return
	}
	var n int64
	// RowsAffected 返回更新影响的行数
	n , err = ret.RowsAffected()
	if err != nil{
		fmt.Printf("Get RowAffected failed error %v\n",err)
		return
	}
	fmt.Printf("delete success affected row: %d \n",n)
}

func main(){
	err := initDB()
	if err != nil{
		panic(err)
	}
	defer DB.Close()
	//queryMultiRowToId(1)
	//insertRow("赶路人",9)
	queryMultiRowToId(0)
	deleteRowToId(3)
	queryMultiRowToId(0)
}

输出：
ID: 1 Name: xx  Age: 20
ID: 2 Name: yy  Age: 20
ID: 3 Name: zz  Age: 18
ID: 4 Name: 赶路人  Age: 16
delete success affected row: 1
ID: 1 Name: xx  Age: 20
ID: 2 Name: yy  Age: 20
ID: 4 Name: 赶路人  Age: 16

mysql预处理

什么是预处理

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

为什么要预处理

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

GO实现Mysql预处理

database/sql中使用下面的Prepare方法来实现预处理操作。
func (db *DB) Prepare(query string) (*Stmt, error)
Prepare方法会先将sql语句发送给MySQL服务端，返回一个准备好的状态用于之后的查询和命令。返回值可以同时执行多个查询和命令。

预处理查询例子

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39


func perPareQuery(id int){
	sqlStr := "select id,name,age from user where id > ?"
	stmt , err := DB.Prepare(sqlStr)
	if err != nil{
		fmt.Printf("Prepare Failed Error %v\n",err)
		return
	}
	defer stmt.Close()

	rows,err := stmt.Query(id)
	if err != nil{
		fmt.Printf("Prepare Query Error %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("Perpare Scan Failed Error %v\n",err)
			return
		}
		fmt.Printf("iD: %d name: %s age: %d\n",u.id,u.name,u.age)
	}
}

func main(){
	err := initDB()
	if err != nil{
		panic(err)
	}
	defer DB.Close()
	perPareQuery(0)
	//queryMultiRowToId(1)
	//insertRow("赶路人",9)
	//queryMultiRowToId(0)
	//deleteRowToId(3)
	//queryMultiRowToId(0)
}

预处理插入处理

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42


func prepareInsertDemo(name string,age int)  {
	sqlStr := "insert into user(name,age) values (?,?)"
	stmt ,err := DB.Prepare(sqlStr)
	if err != nil{
		fmt.Printf("Prepare Failed error %v\n",err)
		return
	}
	defer stmt.Close()
	result , err := stmt.Exec(name,age)
	if err != nil{
		fmt.Printf("Prepare Exec Error %v\n",err)
		return
	}
	inId, err := result.LastInsertId()
	if err != nil{
		fmt.Printf("Get LastInsertID Failed error %v\n",err)
		return
	}
	fmt.Printf("insert Id: %d success\n",inId)
}

func main(){
	err := initDB()
	if err != nil{
		panic(err)
	}
	defer DB.Close()
	queryMultiRowToId(0)
	prepareInsertDemo("xieys",20)
	queryMultiRowToId(0)
}

输出结果
ID: 1 Name: xx  Age: 20
ID: 2 Name: yy  Age: 20
ID: 4 Name: 赶路人  Age: 16
insert Id: 5 success
ID: 1 Name: xx  Age: 20
ID: 2 Name: yy  Age: 20
ID: 4 Name: 赶路人  Age: 16
ID: 5 Name: xieys  Age: 20

SQL注入问题

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

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

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28


func sqlInject(name string)  {
	sqlStr := fmt.Sprintf("select id,name,age from user where name='%s'",name)
	fmt.Println("SQL:",sqlStr)
	var u user
	err := DB.QueryRow(sqlStr).Scan(&u.id,&u.name,&u.age)
	if err != nil{
		fmt.Printf("QueryRow failed error %v\n",err)
		return
	}
	fmt.Printf("ID:%d name:%s age:%d \n",u.id,u.name,u.age)
}

func main(){
	err := initDB()
	if err != nil{
		panic(err)
	}
	defer DB.Close()
	sqlInject("xxxx' or 1=1 #")
	sqlInject("xxx' union select * from user #")
}

输出：
SQL: select id,name,age from user where name='xxxx' or 1=1 #'
ID:1 name:xx age:20
SQL: select id,name,age from user where name='xxx' union select * from user #'
ID:1 name:xx age:20

Go实现Mysql事务

什么是事务

事务：一个最小的不可再分的工作单元；通常一个事务对应一个完整的业务(例如银行账户转账业务，该业务就是一个最小的工作单元)，同时这个完整的业务需要执行多次的DML(insert、update、delete)语句共同联合完成。A转账给B，这里面就需要执行两次update操作。

在MySQL中只有使用了Innodb数据库引擎的数据库或表才支持事务。事务处理可以用来维护数据库的完整性，保证成批的SQL语句要么全部执行，要么全部不执行。

事务的ACID

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

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

Go操作事务的方法

开始事务 func (db *DB) Begin() (*Tx, error)

提交事务 func (db *DB) Commit() error

回滚事务 func (tx *Tx) Rollback() error

事务操作例子

1
2
3
4
5


#当前表中所有数据
ID: 1 Name: xx  Age: 20
ID: 2 Name: yy  Age: 20
ID: 4 Name: 赶路人  Age: 16
ID: 5 Name: xieys  Age: 20

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72


func transaction()  {
	tx,err := DB.Begin()  //开启事务
	if err != nil{
		if tx != nil{
 			err = tx.Rollback() //事务回滚
			if err != nil{
				fmt.Printf("Rollback Failed Error %v\n",err)
				return
			}
		}
		fmt.Printf("Transaction Begin Error %v\n",err)
		return
	}
	sqlStr := "update user set age=18 where id=?"
	rs1,err := tx.Exec(sqlStr,2)
	if err != nil{
		err = tx.Rollback()
		fmt.Printf("rs1 Exec Failed Error %v\n",err)
		if err != nil{
			fmt.Printf("Rollback Failed Error %v\n",err)
			return
		}
	}
	rowID1,_ := rs1.RowsAffected()

	sqlStr = "update user set age=40 where id = ?"
	rs2,err := tx.Exec(sqlStr,3)
	if err != nil{
		err = tx.Rollback()
		fmt.Printf("rs2 exec failed error:%v\n",err)
		if err != nil{
			fmt.Printf("rollback failed error %v\n",err)
			return
		}
	}

	rowID2,_ := rs2.RowsAffected()

	if rowID1 == 1 && rowID2 == 1 {
		fmt.Println("事务提交....")
		err = tx.Commit()
		if err != nil{
			err = tx.Rollback()
			fmt.Printf("exec failed error %v\n",err)
			if err != nil{
				fmt.Printf("rollout failed error %v\n",err)
				return
			}
		}
	}else {
		fmt.Println("事务回滚....")
		err = tx.Rollback()
		if err != nil{
			fmt.Printf("rollout failed error %v\n",err)
			return
		}
	}
}

func main(){
	err := initDB()
	if err != nil{
		panic(err)
	}
	defer DB.Close()
	//queryMultiRowToId(0)
	transaction()
}

输出结果： 由于没有id为3的记录，所以在执行第二条语句的时候，影响的行数为0，会触发回滚操作
事务回滚....

sqlx模块

官方地址 https://github.com/jmoiron/sqlx

sqlx 是 go 标准库 database/sql 的一个扩展库, 在保持 database/sql 标准接口不变的情况下增加了一些扩展。

sqlx 的扩展

可将行记录映射给struct（内嵌struct也支持）,map与slices
支持在preprared statement中使用命名参数
将Get和select的查询结果保存到struct和slice中

sqlx模块使用

安装sqlx

go get github.com/jmoiron/sqlx

基本使用

初始化数据库

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40


package main

import (
	"fmt"
	//引入mysql驱动，只需要用到init()方法
	_ "github.com/go-sql-driver/mysql"
	"github.com/jmoiron/sqlx"
	"time"
)


//定义全局的，数据库对象
var DB *sqlx.DB

//数据库初始化
func initDB()(err error) {
	dsn :="root:root@tcp(192.168.116.90:3306)/sql_test?charset=utf8mb4&parseTime=True"

	// 这里DB赋值是给上面定义的全局变量赋值，不要使用 :=
	// 也可以使用MustConnect连接不成功就panic
	DB,err = sqlx.Connect("mysql",dsn)
	if err != nil{
		return err
	}

	//连接存活时间，超时会被自动关闭
	DB.SetConnMaxLifetime(time.Second * 300)
	//最大连接数
	DB.SetMaxOpenConns(100)
	//连接池中空闲连接数
	DB.SetMaxIdleConns(50)
	return err
}

func main()  {
	if err := initDB();err != nil{
		fmt.Printf("InitDB failed error %v\n",err)
		return
	}
}

查询数据

单行数据查询

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30


//创建一个结构体，并未db打上标签
type user struct{
	Id int `db:"id"`
	Name string `db:"name"`
	Age int `db:"age"`
}

//查询单行数据示例代码如下：
func  queryRowDemo(id int)  {
	sqlStr := "select id,name,age from user where id=?"
	var u user
	err := DB.Get(&u,sqlStr,id)
	if err != nil{
		fmt.Printf("get failed ,err: %v\n",err)
		return
	}
	fmt.Printf("id: %d name: %v age: %d\n",u.Id,u.Name,u.Age)
}

func main()  {
	if err := initDB();err != nil{
		fmt.Printf("InitDB failed error %v\n",err)
		return
	}
	queryRowDemo(1)
}

输出结果：
id: 1 name: xx age: 20

多行数据查询

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33


//创建一个结构体，并未db打上标签
type user struct{
	Id int `db:"id"`
	Name string `db:"name"`
	Age int `db:"age"`
}

func queryMultiRows(id int64)  {
	sqlStr := "select id,name,age from user where id > ?"
	var users []user
	err := DB.Select(&users,sqlStr,id)
	if err != nil{
		fmt.Printf("query failed ,err: %v\n",err)
		return
	}
	for _,v := range users{
		fmt.Printf("id: %d name: %v age: %d\n",v.Id,v.Name,v.Age)
	}
}

func main()  {
	if err := initDB();err != nil{
		fmt.Printf("InitDB failed error %v\n",err)
		return
	}
	queryMultiRows(0)
}
输出结果
id: 1 name: xx age: 20
id: 2 name: yy age: 20
id: 4 name: 赶路人 age: 16
id: 5 name: xieys age: 20

查询指定的多条数据

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44


//创建一个结构体，并未db打上标签
type user struct{
	Id int `db:"id"`
	Name string `db:"name"`
	Age int `db:"age"`
}

//批量查询
func queryRowByIDs(ids []int)(users []user,err error){
	query ,args ,err := sqlx.In("select id,name,age from user where id in (?)",ids)
	if err != nil{
		fmt.Printf("queryrowByIds In Failed error %v\n",err)
		return nil, err
	}
	//使用Rebind()重新绑定 组合后的SQL语句
	query = DB.Rebind(query)
	err = DB.Select(&users,query,args...)
	if err != nil{
		fmt.Printf("query Failed error %v\n",err)
		return nil, err
	}
	return
}

func main()  {
	if err := initDB();err != nil{
		fmt.Printf("InitDB failed error %v\n",err)
		return
	}
	//这里返回一个[]user的切片和一个error
	users,err := queryRowByIDs([]int{1,2,4})
	if err != nil{
		fmt.Printf("queryRowByIDs Failed Error %v\n",err)
		return
	}
	for _,user := range users{
		fmt.Printf("id: %d name: %v age:%d\n",user.Id,user.Name,user.Age)
	}
}

输出结果：
id: 1 name: xx age:20
id: 2 name: yy age:20
id: 4 name: 赶路人 age:16

NamedQuery

func (db *DB) NamedQuery(query string, arg interface{}) (*Rows, error)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48


//创建一个结构体，并未db打上标签
type user struct{
	Id int `db:"id"`
	Name string `db:"name"`
	Age int `db:"age"`
}

//where查询条件结构体
type query struct {
	Name string `db:"name"`
	Age int	`db:"age"`
}

func queryNamedQuery(q query)  {
	sqlStr := "select id,name,age from user where name= :name  and age= :age"
	rows ,err := DB.NamedQuery(sqlStr,q)
	if err != nil{
		fmt.Printf("db.namedQuery failed ,err :%v\n",err)
		return
	}
	defer rows.Close()
	for rows.Next(){
		var u user
		err := rows.StructScan(&u)
		if err != nil{
			fmt.Printf("scan failed ,err :%v\n",err)
			return
		}
		fmt.Printf("user:%#v\n",u)
	}
}


func main()  {
	if err := initDB();err != nil{
		fmt.Printf("InitDB failed error %v\n",err)
		return
	}

	queryNamedQuery(query{
		Name: "上海小王子",
		Age: 19,
	})

}
输出结果：
user:main.user{Id:7, Name:"上海小王子", Age:19}

插入数据

Exec

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

sqlx中的exec方法与原生sql中的exec使用基本一致

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27


func InsertSqlXExec(name string,id int){
	sqlStr := "insert into user(name,age) values (?,?)"
	ret , err := DB.Exec(sqlStr,name,id)
	if err != nil{
		fmt.Printf("insert failed,err: %v\n",err)
		return
	}
	tid,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",tid)
}



func main()  {
	if err := initDB();err != nil{
		fmt.Printf("InitDB failed error %v\n",err)
		return
	}
	InsertSqlXExec("深圳小王子",20)
}

输出结果：
insert success ,the id is 6

NamedExec

func (db *DB) NamedExec(query string, arg interface{}) (sql.Result, error)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39


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

func InsertSqlXNamedExec(u user){
	// (:后面是数据库字段), 最后传入整个 结构体
	sqlStr := "insert into user(name,age) values (:name,:age)"
	ret , err := DB.NamedExec(sqlStr,u)
	if err != nil{
		fmt.Printf("insert namedexec failed,err: %v\n",err)
		return
	}
	tid,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",tid)
}



func main()  {
	if err := initDB();err != nil{
		fmt.Printf("InitDB failed error %v\n",err)
		return
	}
	s1 := user{
		Name: "上海小王子",
		Age: 19,
	}
	InsertSqlXNamedExec(s1)
}

输出结果：
insert success ,the id is 7

更新数据

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24


func updateRow(id,age int64)  {
	sqlStr := "update user set age = ? where id = ?"
	result , err := DB.Exec(sqlStr,age,id)
	if err != nil{
		fmt.Printf("UpdateRow Exec Failed Error %v\n",err)
		return
	}
	n,err := result.RowsAffected()
	if err != nil{
		fmt.Printf("updateRow Get RowAffected Failed Error %v\n",err)
		return
	}
	fmt.Printf("update id:%d to age = %d sucess rows: %d\n",id,age,n)
}

func main()  {
	if err := initDB();err != nil{
		fmt.Printf("InitDB failed error %v\n",err)
		return
	}
	updateRow(1,22)
}
输出：
update id:1 to age = 22 sucess rows: 1

删除数据

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24


func deleteRowToId(id int64){
	sqlStr := "delete from user where id = ?"
	result,err := DB.Exec(sqlStr,id)
	if err != nil{
		fmt.Printf("deleteRowToId Exec Failed Error %v\n",err)
		return
	}
	n,err := result.RowsAffected()
	if err != nil{
		fmt.Printf("DeleteRow Get RowsAffected Failed Error %v\n",err)
		return
	}
	fmt.Printf("delete id: %id Success Rows: %d\n",id,n)
}

func main()  {
	if err := initDB();err != nil{
		fmt.Printf("InitDB failed error %v\n",err)
		return
	}
	deleteRowToId(2)
}
输出：
delete id: 2 Success Rows: 1

事务的操作

事务操作的例子

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63


func transaction()(err error){
	tx,err := DB.Begin()
	if err != nil{
		fmt.Printf("Transaction Begin Failed Error %v\n",err)
		return err
	}
	//这里使用defer 调用一个匿名函数，用户捕获panic执行Rollback操作
	defer func() {
		if p := recover();p!= nil{
			tx.Rollback()
			panic(p)
		}else if err != nil{
			fmt.Printf("RollBack...\n")
			tx.Rollback()
		}else {
			err = tx.Commit()
			fmt.Println("commit....")
		}
	}()

	sqlStr1 := "update user set age=25 where id =?"
	result , err := tx.Exec(sqlStr1,1)
	if err != nil{
		return err
	}
	n,err := result.RowsAffected()
	if err != nil{
		return err
	}
	if n != 1{
		return errors.New("exec sqlstr1 Failed!")
	}

	sqlStr2 := "update user set age=25 where id = ?"
	result,err = tx.Exec(sqlStr2,2)
	if err != nil{
		return err
	}
	n,err = result.RowsAffected()
	if err != nil{
		return err
	}
	if n != 1{
		return errors.New("exec sqlStr2 Failed!")
	}
	return err

}

func main()  {
	if err := initDB();err != nil{
		fmt.Printf("InitDB failed error %v\n",err)
		return
	}
	if err := transaction();err != nil{
		fmt.Println("Transaction Failed ,error: ",err)
	}
}

输出：
RollBack...
Transaction Failed ,error:  exec sqlStr2 Failed!

批量插入数据

sqlx.In函数

sqlx.In是sqlx提供的一个函数
使用sqlx.In需要对数据映射的结构体实现一个 driver.Valuer 的接口返回值的方法

func (u User) Value() (driver.Value, error) { return []interface{}{u.Name, u.Age}, nil }

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50


//创建一个结构体，并未db打上标签
type User struct{
	Name string `db:"name"`
	Age int `db:"age"`
}

func (u User)Value()(driver.Value ,error)  {
	return []interface{}{u.Name,u.Age},nil
}

func BatchInertUser1(users []interface{})error{
	//users 有几条数据，这里就得有多少个(?) 占位符
	query ,args, _ :=  sqlx.In("INSERT INTO user (name, age) VALUES (?),(?),(?)",users...)
	// 如果arg实现了 driver.Valuer, sqlx.In 会通过调用 Value()来展开它
	fmt.Println(query)
	fmt.Println(args)
	_, err := DB.Exec(query,args...)
	return err
}

func main()  {
	if err := initDB();err != nil{
		fmt.Printf("InitDB failed error %v\n",err)
		return
	}
	u1 := User{
		Name: "小肉肉",
		Age:  28,
	}
	u2 := User{
		Name: "大肉肉",
		Age:  48,
	}
	u3 := User{
		Name: "中肉肉",
		Age:  38,
	}

	// 需要先创建interface 的切片
	users := []interface{}{u1,u2,u3}
	if err := BatchInertUser1(users);err!= nil{
		fmt.Printf("Batch Insert Failed Error %v \n", err)
		return
	}
}

输出：
INSERT INTO user (name, age) VALUES (?, ?),(?, ?),(?, ?)
[小肉肉 28 大肉肉 48 中肉肉 38]

NamedExec函数

注意：sqlx版本必须 >=1.3.1

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35


//创建一个结构体，并未db打上标签
type User struct{
	Name string `db:"name"`
	Age int `db:"age"`
}

func BatchInsertUsers(users []*User)error  {
	_,err := DB.NamedExec("INSERT INTO user(name,age) VALUES (:name,:age)",users)
	return err
}
func main()  {
	if err := initDB();err != nil{
		fmt.Printf("InitDB failed error %v\n",err)
		return
	}
	u1 := User{
		Name: "小肉肉",
		Age:  28,
	}
	u2 := User{
		Name: "大肉肉",
		Age:  48,
	}
	u3 := User{
		Name: "中肉肉",
		Age:  38,
	}

	users := []*User{&u1,&u2,&u3}
	if err := BatchInsertUsers(users);err!= nil{
		fmt.Printf("Batch Insert Failed Error %v \n", err)
		return
	}
}

目录

Go操作mysql

SQL科普

bindvars绑定变量

SQL标准库

下载Mysql驱动

连接数据库的例子

sql.Open函数

初始化连接的例子

增删改查操作

环境准备

建库建表

查询操作

单行查询

多行查询

插入数据操作

更新数据集操作

删除数据

mysql预处理

什么是预处理

为什么要预处理

GO实现Mysql预处理

SQL注入问题

Go实现Mysql事务

什么是事务

事务的ACID

Go操作事务的方法

事务操作例子

sqlx模块

sqlx 的扩展

sqlx模块使用

安装sqlx

基本使用

初始化数据库

查询数据

插入数据

更新数据

删除数据

事务的操作

批量插入数据

sqlx.In函数

NamedExec函数