18 . Go之操作Mysql和sqlx使用

iginkgo18

发布于 2022-05-09 15:46:31

1.6K0

发布于 2022-05-09 15:46:31

文章被收录于专栏：devops_k8s devops_k8sdevops_k8s

安装mysql

wget http://dev.mysql.com/get/mysql57-community-release-el7-8.noarch.rpm
yum -y localinstall mysql57-community-release-el7-8.noarch.rpm
yum install mysql-community-server -y
systemctl start mysqld
grep 'temporary password' /var/log/mysqld.log
mysql -uroot -p
ALTER USER 'root'@'localhost' IDENTIFIED BY 'sdafdsf.22';

grant all privileges on *.* to admin@"%" identified by '123sdafd.21' withth grant option;

# 授权test用户有testdb数据库某一部分权限
grant select,update on testDB.* to test@'%' identified by 'sfsdaf.22';

# 授权test用户拥有所有数据库所有权限
grant all on *.* to 'test'@'%' identified by 'asdf.22';
flush privileges;

Mysql常用库操作

# 创建数据库
mysql> create database myblog1 charset=utf8;

# 查询数据库
mysql> show databases;

# 查看数据库
mysql> show create database myblog;
+----------+-----------------------------------------------------------------+
| Database | Create Database                                                 |
+----------+-----------------------------------------------------------------+
| myblog   | CREATE DATABASE `myblog` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+-----------------------------------------------------------------+

# 删除数据库
mysql> drop database myblog1;

Mysql常用表操作

# 进入Myblog数据库
mysql> use myblog;

# 创建表
mysql> create table name (
    -> colname coltype 修饰,
    -> ) engine=inndb default charset=utf8mb4;

colname: 列名, 大小写英文字母,数字,_组成
coltype: 数值类型(int bigint float double decimal(m,n))
				 字符串类型(varchar(n), char(n))
			   时间类型(data,datetime,time)
				 文本类型(text 64k,mediumtext 16M,longtext 4G)
				 二进制类型(blob,longblob)
				 json/array()

# 修饰:
		# 主键: primary key
		# 唯一: unique
		# 自动增长: auto_increment
		# 默认值: default 0,default ''
		# 是否允许为Null, 不允许为NULL ,NOT NULL
		# 注释: COMMENT ''

# 索引
	index

创建表Example1

create table task(
id bigint primary key auto_increment,
name varchar(64) not null default '' comment '任务名称',
status int not null default 0 comment '状态,0: 新建, 1: 正在执行, 2: 停止, 3:  完成',
start_time datetime comment '开始时间',
complete_time datetime comment '完成时间',
deadline_time datetime not null comment '截止时间',
content text comment '任务详情',
index idx_name (name),
index deadline_time(deadline_time)
)engine=innodb default charset utf8mb4 auto_increment 1000;

# 查看表结构
mysql> desc task;

# 删除表
mysql> drop table name;

# 修改表
mysql> alter table name 动作;

# 只允许添加列
alter table name add column colname coltype 修饰;

# 删除列
alter table name drop column colname;

# 修改列
alter table name modify column colname coltype 修饰;

# 索引
create index name on table (column,column2,...);
drop index name on table (column,column2);
create unique index name on table (column,column2,...);

表数据操作

# 增 insert into table(c1,c2,c3,c4) values(v1,v2,v3,v4);
mysql> insert into task(name,content,deadline_time) values('写作业','完成todulist web', '2020-06-27 15:20:00');

# 删


# 改

# 查
mysql> select * from task;
+------+-----------+--------+------------+---------------+---------------------+--------------------+
| id   | name      | status | start_time | complete_time | deadline_time       | content            |
+------+-----------+--------+------------+---------------+---------------------+--------------------+
| 1000 | 写作业    |      0 | NULL       | NULL          | 2020-06-27 15:20:00 | 完成todulist web   |
+------+-----------+--------+------------+---------------+---------------------+--------------------+

# 指定列查询
mysql> select name,status,start_time from task;
+-----------+--------+------------+
| name      | status | start_time |
+-----------+--------+------------+
| 写作业    |      0 | NULL       |
+-----------+--------+------------+

# 条件查询
mysql> select name,status,start_time from task where name='写作业';
+-----------+--------+------------+
| name      | status | start_time |
+-----------+--------+------------+
| 写作业    |      0 | NULL       |
| 写作业    |      0 | NULL       |
+-----------+--------+------------+



# 逻辑关系
		与  and
		或	 or colname=v1 or colname = v2
		非	 not
				c1 = v1 and (c2 = v2 or c3 = v3)
		删	 delete from table;
				delete from table where 条件;
				
# 改
		update table
		set colname = v1, col2 = v2 ,col3 = v3;
		mysql> update task set status=1;

内置函数

# 获取当前时间
select now();

# 获取md5('1');
select md5('1')

# date_format(time,layout)

查询

insert into task(name,status,content,deadline_time) values('1','1','2','2020-05-20'),
('1','1','3','2020-05-21'),
('2','2','3','2020-05-22'),
('3','2','2','2020-05-23'),
('4','3','2','2020-05-24'),
('5','3','2','2020-05-25'),
('6','3','2','2020-05-26');
# 数量
count(*)
count(id)

mysql> select count(*) from task where status=1;

# as 别名

# 

# 排序
   order by colname [asc | desc],col2 [asc|desc]
mysql> select * from task order by deadline_time;



# 分页
# 展示多少条数据 每页的数据量
# 展示第几页 页面
# limit 限制查询数量
# offset 设置便宜
# 每页展示5条 limit 5
# 第一页offset 0
# 第二页offset 5
mysql> select * from task order by deadline_time limit 5 offset 0;

# 分组
# IP time url status_code
# ip 出现的次数
# status_code出现的次数
# url,status_code出现的次数
# ip url status_code出现次数
# group by
select [] from table group by colname,colname2 [having 过滤条件;]
#  select 元素必须是指定分组的列名或聚合类
mysql> select status,count(*) from task group by status;
+--------+----------+
| status | count(*) |
+--------+----------+
|      0 |        7 |
|      1 |        2 |
+--------+----------+


create table accesslog(
	id bigint primary key auto_increment,
  logtime datetime not null comment '访问时间',
  ip varchar(128) not null default '' comment '访问来源',
  url varchar(4096) not null default '' comment '访问地址',
  status int not null default 0 comment '状态码'
) engine=innodb default charset utf8mb4;

insert into accesslog(logtime,ip,url,status) values
('2020-06-05 05:20:00','1.1.1.1','/index',200),
('2020-06-06 05:20:00','1.1.1.2','/home',302),
('2020-06-07 05:20:00','1.1.1.3','/test.txt',404),
('2020-06-08 05:20:00','1.1.1.4','/backup.sh',404),
('2020-06-09 05:20:00','1.1.1.5','/login',200),
('2020-06-10 05:20:00','1.1.1.6','/register',200),
('2020-06-15 05:20:00','1.1.1.7','/test',200),
('2020-06-13 05:20:00','1.1.1.8','/upload.php',200),
('2020-06-15 05:20:00','1.1.1.9','/index',200);

# ip出现次数
mysql> select ip,count(*) from accesslog group by ip;
+---------+----------+
| ip      | count(*) |
+---------+----------+
| 1.1.1.1 |        1 |
| 1.1.1.2 |        1 |
| 1.1.1.3 |        1 |
| 1.1.1.4 |        1 |
| 1.1.1.5 |        1 |
| 1.1.1.6 |        1 |
| 1.1.1.7 |        1 |
| 1.1.1.8 |        1 |
| 1.1.1.9 |        1 |
+---------+----------+


# status_code出现次数
mysql> select status,count(*) from accesslog group by status;
+--------+----------+
| status | count(*) |
+--------+----------+
|    200 |        6 |
|    302 |        1 |
|    404 |        2 |
+--------+----------+


# ip url status_code出现次数
mysql> select url,status,count(*) from accesslog group by url,status;
+-------------+--------+----------+
| url         | status | count(*) |
+-------------+--------+----------+
| /backup.sh  |    404 |        1 |
| /home       |    302 |        1 |
| /index      |    200 |        2 |
| /login      |    200 |        1 |
| /register   |    200 |        1 |
| /test       |    200 |        1 |
| /test.txt   |    404 |        1 |
| /upload.php |    200 |        1 |
+-------------+--------+----------+

# ip访问次数大于等于1
mysql> select ip,count(*) from accesslog group by ip having count(*)>=1;
+---------+----------+
| ip      | count(*) |
+---------+----------+
| 1.1.1.1 |        1 |
| 1.1.1.2 |        1 |
| 1.1.1.3 |        1 |
| 1.1.1.4 |        1 |
| 1.1.1.5 |        1 |
| 1.1.1.6 |        1 |
| 1.1.1.7 |        1 |
| 1.1.1.8 |        1 |
| 1.1.1.9 |        1 |
+---------+----------+

# 每一天的访问量
mysql> select date_format(logtime,'%Y-%m-%d') as log_day,count(*) as cnt from accesslog group by log_day;
+------------+-----+
| log_day    | cnt |
+------------+-----+
| 2020-06-05 |  18 |
+------------+-----+

mysql> select date_format(logtime,'%Y-%m-%d') as log_day,count(*) as cnt from accesslog group  by log_day order by cnt desc;


create table score(
	id bigint primary key auto_increment,
  day date not null comment '日期',
  name varchar(32) not null default '' comment '姓名',
  score float not null default 0 comment '分数'
)engine=innodb default charset utf8mb4;

insert into score(day,name,score) values
('2020-05-20','youmen',2),
('2020-05-21','xiaobai',3),
('2020-05-22','haba',4),
('2020-05-23','wunai',5),
('2020-05-24','Amaris',6),
('2020-05-25','Cassiel',6),
('2020-05-26','Gina',6),
('2020-05-27','Deirdre',6);

# 求和
mysql> select name,sum(score) from score group by name;
+---------+------------+
| name    | sum(score) |
+---------+------------+
| Amaris  |          6 |
| Cassiel |          6 |
| Deirdre |          6 |
| Gina    |          6 |
| haba    |          4 |
| wunai   |          5 |
| xiaobai |          3 |
| youmen  |          2 |
+---------+------------+

# 最小值,最大值,平均值
mysql> select name,sum(score),max(score),min(score),avg(score) from score group by name;
+---------+------------+------------+------------+------------+
| name    | sum(score) | max(score) | min(score) | avg(score) |
+---------+------------+------------+------------+------------+
| Amaris  |          6 |          6 |          6 |          6 |
| Cassiel |          6 |          6 |          6 |          6 |
| Deirdre |          6 |          6 |          6 |          6 |
| Gina    |          6 |          6 |          6 |          6 |
| haba    |          4 |          4 |          4 |          4 |
| wunai   |          5 |          5 |          5 |          5 |
| xiaobai |          3 |          3 |          3 |          3 |
| youmen  |          2 |          2 |          2 |          2 |
+---------+------------+------------+------------+------------+

# 每一天的总分



# 联查

# 多张表进行查询数据
join
left join on
inner join on
right join on

create table user(
		id bigint primary key auto_increment,
  	name varchar(32) not null default '',
  	status int not null default 0 comment '0:在职,1:离职'
)engine=innodb default charset utf8mb4;

alter table task add column user bigint;

insert into user(name,status) values
('youmen',0),
('幽梦',1),
('wunai',0);

insert into task(name,content,deadline_time,user) values
('完成web任务','',now(),1),
('打游戏','',now(),0),
('吃饭','',now(),1),
('睡觉','',now(),2);

# 每个人(名字)未完成的任务
mysql> select * from task where status != 3;

mysql> select user.name,task.name from task left join user on task.user=task.id where task.status!=3;

配置数据库驱动

// MySQL： github.com/go-sql-driver/mysql
// PostgreSQL：github.com/lib/pq
// Sqlite3：github.com/mattn/go-sqlite3
 go get  github.com/go-sql-driver/mysql

使用go-sql-driver

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

连接数据库代码

package main

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


func main() {
	dsn := "test:sdaf.22@tcp(121.36.43.223:3306)/myblog?charset=utf8"  // 字符串格式由对应驱动定义
	db, err := sql.Open("mysql", dsn)
	// if exists 没有表就创建
	db.Exec(`
	create table if not exists test2(
		id bigint primary key auto_increment,
		name varchar(32) not null default '' comment 'test_name'
	) engine=innodb default charset utf8mb4;`)
	fmt.Println(db, err)
	db.Exec(`update task set status=2;`)
}

增删改查

db包使用案例1

Example1

package main

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


func main() {
	dsn := "test:sdaf.22@tcp(121.36.43.223:3306)/myblog?charset=utf8"  // 字符串格式由对应驱动定义
	db, err := sql.Open("mysql", dsn)
	fmt.Println(db,err)
	result,_:= db.Exec(`update task set status=1;`)
	fmt.Println(result.RowsAffected())

	// 匹配修改
	result,_ = db.Exec(`update task set status = 2 where id%3=0`)
	fmt.Println(result.RowsAffected())

	// 匹配删除
	result,_ = db.Exec(`delete from task where id=1001`)
	fmt.Println(result.RowsAffected())

	// 插入
	db.Exec(`insert into task(name,content,deadline_time) values ('xxxx','xxxx','2020-05-20')`)
	fmt.Println(result.LastInsertId())
	fmt.Println(result.RowsAffected())

	// 查询
	rows,err := db.Query("select id,name from task")
	var(
		id int
		name string
	)

	for rows.Next() {
		rows.Scan(&id,&name)
		fmt.Println(id,name)
	}
}

Example2 效率提升版

package main

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


func main() {
	dsn := fmt.Sprintf("%s:%s@tcp(%s:%d)/%s?charset=utf8","test","sdaf.22","121.36.43.223",3306,"myblog")
	db, _ := sql.Open("mysql", dsn)
	start := time.Now()
	stmt,_:= db.Prepare(`insert into task(name,content,deadline_time) values ('xxxx','xxxx','2020-05-20'`)
	for i := 0; i<10000; i++{
		stmt.Exec(fmt.Sprintf("a_",i),1000)
	}
	fmt.Println(time.Now().Sub(start))
}

// 差不多能提升两秒

db包使用案例2

sql

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("youmen",18);
insert into user(name,age) values("wunai",19);

main.go

package main

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

type user struct {
	id   int
	name string
	age  int
}

var db *sql.DB // 是一个连接池对象

func initDB() (err error) {
	dsn := "test:sdaf.22@tcp(121.36.43.223:3306)/myblog?charset=utf8"
	// 连接数据库
	db, err = sql.Open("mysql", dsn)
	if err != nil {
		return
	}
	err = db.Ping() // 尝试连接数据库
	if err != nil {
		return
	}
	db.SetMaxOpenConns(10) // 设置数据库连接池的最大连接数
	db.SetMaxIdleConns(5)  // 设置最大空闲连接数
	return
}

func main() {
	err := initDB()
	if err != nil {
		fmt.Printf("init DB failed,err:%v\n", err)
	}
	fmt.Println("连接数据库成功")

	var u1 user
	sqlStr := `select id,name,age from user where id=?;`

	rowObj := db.QueryRow(sqlStr,1)
	rowObj.Scan(&u1.id,&u1.name,&u1.age)
	fmt.Printf("u1:%#v\n", u1)
}

多行查询

package main

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

type user struct {
	id   int
	name string
	age  int
}

var db *sql.DB // 是一个连接池对象

func initDB() (err error) {
	dsn := "test:sdafsdf.22@tcp(121.36.43.223:3306)/myblog?charset=utf8"
	// 连接数据库
	db, err = sql.Open("mysql", dsn)
	if err != nil {
		return
	}
	err = db.Ping() // 尝试连接数据库
	if err != nil {
		return
	}
	db.SetMaxOpenConns(10) // 设置数据库连接池的最大连接数
	db.SetMaxIdleConns(5)  // 设置最大空闲连接数
	return
}

func main() {
	err := initDB()
	if err != nil {
		fmt.Printf("init DB failed,err:%v\n", err)
	}
	fmt.Println("连接数据库成功")
	queryMore(0)
}

func queryMore(n int)  {
	sqlStr := `select id,name,age from user where id > ?;`

	rows,err:=db.Query(sqlStr,n)
	if err != nil {
		fmt.Printf("exec %s query failed, err:%v\n",sqlStr,err)
		return
	}

	defer rows.Close()

	var u1 user
	for rows.Next(){
		err := rows.Scan(&u1.id,&u1.name,&u1.age)
		if err != nil {
			fmt.Printf("scan failed,err:%v\n",err)
		}
		fmt.Printf("u1:%#v\n",u1)
	}
}

insert插入数据

package main

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

type user struct {
	id   int
	name string
	age  int
}

var db *sql.DB // 是一个连接池对象

func initDB() (err error) {
	dsn := "test:sadf.22@tcp(121.36.43.223:3306)/myblog?charset=utf8"
	// 连接数据库
	db, err = sql.Open("mysql", dsn)
	if err != nil {
		return
	}
	err = db.Ping() // 尝试连接数据库
	if err != nil {
		return
	}
	db.SetMaxOpenConns(10) // 设置数据库连接池的最大连接数
	db.SetMaxIdleConns(5)  // 设置最大空闲连接数
	return
}

func main() {
	err := initDB()
	if err != nil {
		fmt.Printf("init DB failed,err:%v\n", err)
	}
	fmt.Println("连接数据库成功")

	insert()
}

func insert()  {
	sqlStr := `insert into user(name,age) values("wunai",22);`

	ret,err := db.Exec(sqlStr)
	if err != nil {
		fmt.Printf("insert failed, err:%v\n",err)
		return
	}

	// 如果是插入数据的操作,能够拿到插入数据的id
	id,err := ret.LastInsertId()
	if err != nil {
		fmt.Printf("get id failed, err:%v\n",err)
		return
	}
	fmt.Println("id:",id)
}

update

package main

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

type user struct {
	id   int
	name string
	age  int
}

var db *sql.DB // 是一个连接池对象

func initDB() (err error) {
	dsn := "test:sdafsd.22@tcp(121.36.43.223:3306)/myblog?charset=utf8"
	// 连接数据库
	db, err = sql.Open("mysql", dsn)
	if err != nil {
		return
	}
	err = db.Ping() // 尝试连接数据库
	if err != nil {
		return
	}
	db.SetMaxOpenConns(10) // 设置数据库连接池的最大连接数
	db.SetMaxIdleConns(5)  // 设置最大空闲连接数
	return
}

func main() {
	err := initDB()
	if err != nil {
		fmt.Printf("init DB failed,err:%v\n", err)
	}
	fmt.Println("连接数据库成功")

	updateRow(21,1)
}

// 更新操作
func updateRow(newAge, id int)  {
	sqlStr := `update user set age=? where id > ?`
	ret,err := db.Exec(sqlStr,newAge,id)
	if err != nil {
		fmt.Printf("get id failed, err:%v\n")
	}
	fmt.Printf("更新了%n行数据\n",ret)
}

delete

package main

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

type user struct {
	id   int
	name string
	age  int
}

var db *sql.DB // 是一个连接池对象

func initDB() (err error) {
	dsn := "test:dsafdsa.22@tcp(121.36.43.223:3306)/myblog?charset=utf8"
	// 连接数据库
	db, err = sql.Open("mysql", dsn)
	if err != nil {
		return
	}
	err = db.Ping() // 尝试连接数据库
	if err != nil {
		return
	}
	db.SetMaxOpenConns(10) // 设置数据库连接池的最大连接数
	db.SetMaxIdleConns(5)  // 设置最大空闲连接数
	return
}

func main() {
	err := initDB()
	if err != nil {
		fmt.Printf("init DB failed,err:%v\n", err)
	}
	fmt.Println("连接数据库成功")

	deleteRow(5)
}

func deleteRow(id int)  {
	sqlStr := "delete from user where id = ?"
	ret,err := db.Exec(sqlStr,id)
	if err != nil {
		fmt.Printf("delete failed,err:%v\n",err)
		return
	}

	n,err := ret.RowsAffected()
	if err != nil {
		fmt.Printf("get id failed,err:%v\n",err)
		return
	}
	fmt.Printf("删除了%d行数据",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服务端，返回一个准备好的状态用于之后的查询和命令。
		返回值可以同时执行多个查询和命令。
*/

Example

package main

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

type user struct {
	id   int
	name string
	age  int
}

var db *sql.DB // 是一个连接池对象

func initDB() (err error) {
	dsn := "test:sadfsd.22@tcp(121.36.43.223:3306)/myblog?charset=utf8"
	// 连接数据库
	db, err = sql.Open("mysql", dsn)
	if err != nil {
		return
	}
	err = db.Ping() // 尝试连接数据库
	if err != nil {
		return
	}
	db.SetMaxOpenConns(10) // 设置数据库连接池的最大连接数
	db.SetMaxIdleConns(5)  // 设置最大空闲连接数
	return
}

func main() {
	err := initDB()
	if err != nil {
		fmt.Printf("init DB failed,err:%v\n", err)
	}
	fmt.Println("连接数据库成功")
	prepareInsert()
}

func prepareInsert()  {
	sqlStr := `insert into user(name,age) values(?,?)`
	stmt,err := db.Prepare(sqlStr)  // 把SQL语句先发给MySQL预处理一下
	if err != nil {
		fmt.Printf("prepare failed, err:%v\n",err)
	}

	// 后续只需要拿到stmt去执行一些操作
	defer stmt.Close()
	var m = map[string]int{
		"幽梦":22,
		"无奈":23,
		"哈巴":24,
	}
	for k,v := range m {
		stmt.Exec(k,v)   // 后续只需要循环
	}
}

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) Rollbock()  error

事务示例

下面的代码演示了一个简单的事务操作，该事物操作能够确保两次更新操作要么同时成功要么同时失败，不会存在中间状态。

package main

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

type user struct {
	id   int
	name string
	age  int
}

var db *sql.DB // 是一个连接池对象

func initDB() (err error) {
	dsn := "test:sadfds.22@tcp(121.36.43.223:3306)/myblog?charset=utf8"
	// 连接数据库
	db, err = sql.Open("mysql", dsn)
	if err != nil {
		return
	}
	err = db.Ping() // 尝试连接数据库
	if err != nil {
		return
	}
	db.SetMaxOpenConns(10) // 设置数据库连接池的最大连接数
	db.SetMaxIdleConns(5)  // 设置最大空闲连接数
	return
}

func main() {
	err := initDB()
	if err != nil {
		fmt.Printf("init DB failed,err:%v\n", err)
	}
	fmt.Println("连接数据库成功")

	transactionDemo()
}

func transactionDemo()  {
	// 1. 开启事务
	tx,err := db.Begin()
	if err != nil {
		fmt.Printf("begin failed, err:%v",err)
		return
	}

	// 执行多个SQL操作
	sqlStr := `update user set age = age-30 where id =1`
	sqlStr2 := `update xxx set age=age+20 where id=2`

	// 执行sql1
	_,err = tx.Exec(sqlStr)
	if err != nil {
		// 要回滚
		tx.Rollback()
		fmt.Println("执行SQL2出错了,要回滚了")
		return
	}


	// 执行sql2
	_,err = tx.Exec(sqlStr2)
	if err != nil {
		// 要回滚
		tx.Rollback()
		fmt.Println("执行SQL2出错了,要回滚了")
		return
	}

	// 上面两个sql都执行成功, 就提交本次事务
	err = tx.Commit()
	if err != nil {
		// 要回滚
		tx.Rollback()
		fmt.Println("提交出错了,要回滚!")
		return
	}
	fmt.Println("事务执行成功")
}

SQL注入问题

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

Example

package main

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

type user struct {
	id   int
	name string
	age  int
}

var db *sql.DB // 是一个连接池对象

func initDB() (err error) {
	dsn := "test:sadf.22@tcp(121.36.43.223:3306)/myblog?charset=utf8"
	// 连接数据库
	db, err = sql.Open("mysql", dsn)
	if err != nil {
		return
	}
	err = db.Ping() // 尝试连接数据库
	if err != nil {
		return
	}
	db.SetMaxOpenConns(10) // 设置数据库连接池的最大连接数
	db.SetMaxIdleConns(5)  // 设置最大空闲连接数
	return
}

func main() {
	err := initDB()
	if err != nil {
		fmt.Printf("init DB failed,err:%v\n", err)
	}
	fmt.Println("连接数据库成功")

	// sql注入示例
	//sqlInjectDemo("youmen")
	sqlInjectDemo("xxx' or 1=1#")
}

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

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

sqlx

简介

在项目中我们通常可能会使用database/sql连接MySQL数据库。sqlx可以认为是Go语言内置database/sql的超集，它在优秀的内置database/sql基础上提供了一组扩展。这些扩展中除了大家常用来查询的Get(dest interface{}, ...) error和Select(dest interface{}, ...) error外还有很多其他强大的功能。

安装SQLX

/*
	go get github.com/jmoiron/sqlx
*/

连接数据库

package main

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

var db *sqlx.DB

func main()  {
	dsn := "test:sdafdsafjian.22@tcp(121.36.43.223:3306)/myblog?charset=utf8"  // 字符串格式由对应驱动定义
	db,err := sqlx.Connect("mysql",dsn)
	if err != nil {
		fmt.Printf("connect DB failed, err:%v\n",err)
		return
	}
	db.SetMaxOpenConns(20)
	db.SetMaxIdleConns(10)
	return
}

查询

查询单行数据示例代码如下

// 查询单条数据示例
package main

import (
	"fmt"

	"github.com/jmoiron/sqlx"

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

// Go连接MySQL示例

var db *sqlx.DB // 是一个连接池对象

func initDB() (err error) {
	// 数据库信息
	// 用户名:密码@tcp(ip:端口)/数据库的名字
	dsn := "test:sdafdsf22@tcp(1.1.1.1:3306)/myblog?charset=utf8"

	// 连接数据库
	db, err = sqlx.Connect("mysql", dsn)
	if err != nil {
		return
	}
	db.SetMaxOpenConns(10) // 设置数据库连接池的最大连接数
	db.SetMaxIdleConns(5)  // 设置最大空闲连接数
	return
}

type user struct {
	ID   int
	Name string
	Age  int
}

func main() {
	err := initDB()
	if err != nil {
		fmt.Printf("init DB failed, err:%v\n", err)
		return
	}
	
	// 查询单行数据
	sqlStr1 := `select id, name, age from user where id=?`
	var u user
	db.Get(&u, sqlStr1,1)
	fmt.Printf("u:%#v\n", u)

	var userList []user
	sqlStr2 := `select id,name, age from user`
	err = db.Select(&userList, sqlStr2)
	if err != nil {
		fmt.Printf("select failed, err:%v\n", err)
		return
	}
	fmt.Printf("userList:%#v\n", userList)
}

多行查询

// 查询单条数据示例
package main

import (
	"fmt"

	"github.com/jmoiron/sqlx"

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

// Go连接MySQL示例

var db *sqlx.DB // 是一个连接池对象

func initDB() (err error) {
	// 数据库信息
	// 用户名:密码@tcp(ip:端口)/数据库的名字
	dsn := "test:sadf.22@tcp(1.2.3.223:3306)/myblog?charset=utf8"

	// 连接数据库
	db, err = sqlx.Connect("mysql", dsn)
	if err != nil {
		return
	}
	db.SetMaxOpenConns(10) // 设置数据库连接池的最大连接数
	db.SetMaxIdleConns(5)  // 设置最大空闲连接数
	return
}

type user struct {
	ID   int
	Name string
	Age  int
}

func main() {
	err := initDB()
	if err != nil {
		fmt.Printf("init DB failed, err:%v\n", err)
		return
	}
	queryMultiRowDemo()
}
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, "常见", 19)
	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, 88, 4)
	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, 4)
	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)
}

NamedExec

DB.NamedExec方法用来绑定SQL语句与结构体或map中的同名字段

// 插入数据
func insertUserDemo() (err error) {
	sqlStr := "insert into user(name,age) values (:name,:age)"
	_, err = db.NamedExec(sqlStr,
		map[string]interface{}{
			"name": "奋斗",
			"age":  24,
		})
	return
}

NamedQuery

与DB.NamedExec(),同理,这里是支持查询

// 删除数据
func namedQuery()  {
	sqlStr := "select * from user where name=:name"
	// 使用map做命名查询
	rows,err := db.NamedQuery(sqlStr,map[string]interface{}{"name":"无奈"})
	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)
			continue
		}
		fmt.Printf("user:%#v\n",u)
	}

	u := user {
		Name: "无奈",
	}
	// 使用结构体命名查询, 根据结构体字段的db tag进行映射
	rows,err = db.NamedQuery(sqlStr,u)
	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)
			continue
		}
		fmt.Printf("user:%#v\n",u)
	}
}

事务操作

对于事务操作,我们可以使用sqlx中提供的db.Beginx()和tx.Exec()方法

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

	defer func() {
		if p := recover(); p!= nil {
			tx.Rollback()
			panic(p)
		} else if err != nil {
			fmt.Println("rollback")
			tx.Rollback()
		} else {
			err = tx.Commit()
			fmt.Println("commit")
		}
	}()

	sqlStr1 := "update user set age = 20 where id = ?"
	rs,err := tx.Exec(sqlStr1,1)
	if err != nil {
		return err
	}

	n , err := rs.RowsAffected()
	if err != nil {
		return err
	}

	if n != 1 {
		return errors.New("exec sqlStr1 failed")
	}

	sqlStr2 := "Update user set age=50 where i=?"
	rs, err = tx.Exec(sqlStr2, 5)
	if err!=nil{
		return err
	}
	n, err = rs.RowsAffected()
	if err != nil {
		return err
	}
	if n != 1 {
		return errors.New("exec sqlStr1 failed")
	}
	return err
}

Sqlx.In

sqlx.In是sqlx提供的一个非常方便的函数

表结构

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;

结构体

type User struct {
	Name string `db:"name"`
	Age  int    `db:"age"`
}

bindvars(绑定变量)

查询占位符?在内部称为bindvars（查询占位符）,它非常重要。你应该始终使用它们向数据库发送值，因为它们可以防止SQL注入攻击。database/sql不尝试对查询文本进行任何验证；它与编码的参数一起按原样发送到服务器。除非驱动程序实现一个特殊的接口，否则在执行之前，查询是在服务器上准备的。因此bindvars是特定于数据库的:

/*
		Mysql中使用?
		PostgreSQL使用枚举的$1、$2等bindvar语法
		SQLite中?和$1的语法都支持
		Oracle中使用:name的语法
*/

bindvars的一个常见误解是，它们用来在sql语句中插入值。它们其实仅用于参数化，不允许更改SQL语句的结构。例如，使用bindvars尝试参数化列或表名将不起作用：

自己拼接语句实现批量插入

// BatchInsertUsers 自行构造批量插入的语句
// 多少个User就拼接多少个(?,?)
func BatchInsertUsers(users []*User) error {
	// 存放 (?, ?) 的slice
	valueStrings := make([]string, 0, len(users))
	// 存放values的slice
	valueArgs := make([]interface{}, 0, len(users) * 2)
	// 遍历users准备相关数据
	for _, u := range users {
		// 此处占位符要与插入值的个数对应
		valueStrings = append(valueStrings, "(?, ?)")
		valueArgs = append(valueArgs, u.Name)
		valueArgs = append(valueArgs, u.Age)
	}
	// 自行拼接要执行的具体语句
	stmt := fmt.Sprintf("INSERT INTO user (name, age) VALUES %s",
		strings.Join(valueStrings, ","))
	_, err := DB.Exec(stmt, valueArgs...)
	return err
}

使用sqlx.In实现批量插入

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

使用sqlx.In实现批量插入代码如下

// BatchInsertUsers2 使用sqlx.In帮我们拼接语句和参数, 注意传入的参数是[]interface{}
func BatchInsertUsers2(users []interface{}) error {
	query, args, _ := sqlx.In(
		"INSERT INTO user (name, age) VALUES (?), (?), (?)",
		users..., // 如果arg实现了 driver.Valuer, sqlx.In 会通过调用 Value()来展开它
	)
	fmt.Println(query) // 查看生成的querystring
	fmt.Println(args)  // 查看生成的args
	_, err := DB.Exec(query, args...)
	return err
}

查询示例

关于sqlx.In这里再补充一个用法，在sqlx查询语句中实现In查询和FIND_IN_SET函数。即实现SELECT * FROM user WHERE id in (3, 2, 1);和SELECT * FROM user WHERE id in (3, 2, 1) ORDER BY FIND_IN_SET(id, '3,2,1');。

in查询

查询id在给定id集合中的数据

// QueryByIDs 根据给定ID查询
func QueryByIDs(ids []int)(users []User, err error){
	// 动态填充id
	query, args, err := sqlx.In("SELECT name, age FROM user WHERE id IN (?)", ids)
	if err != nil {
		return
	}
	// sqlx.In 返回带 `?` bindvar的查询语句, 我们使用Rebind()重新绑定它
	query = DB.Rebind(query)

	err = DB.Select(&users, query, args...)
	return
}

in查询和FIND_IN_SET函数

查询id在给定id集合的数据并维持给定id集合的顺序`

// QueryAndOrderByIDs 按照指定id查询并维护顺序
func QueryAndOrderByIDs(ids []int)(users []User, err error){
	// 动态填充id
	strIDs := make([]string, 0, len(ids))
	for _, id := range ids {
		strIDs = append(strIDs, fmt.Sprintf("%d", id))
	}
	query, args, err := sqlx.In("SELECT name, age FROM user WHERE id IN (?) ORDER BY FIND_IN_SET(id, ?)", ids, strings.Join(strIDs, ","))
	if err != nil {
		return
	}

	// sqlx.In 返回带 `?` bindvar的查询语句, 我们使用Rebind()重新绑定它
	query = DB.Rebind(query)

	err = DB.Select(&users, query, args...)
	return
}

本文参与腾讯云自媒体分享计划，分享自作者个人站点/博客。

原始发表：2020-07-26，如有侵权请联系 cloudcommunity@tencent.com 删除

数据库

sql

云数据库 SQL Server

本文分享自作者个人站点/博客前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与腾讯云自媒体分享计划，欢迎热爱写作的你一起参与！

数据库

sql

云数据库 SQL Server