go 操作数据库

go 操作数据库

1.1 SQL语法简介

SQL(i/?si?kw?l/; Structured Query Language)是一套语法标准，不区分大小写。MySQL、sql-server和Oracle都是关系型数据库，在一些高级语法上跟标准SQL略有出入。
Linux用户安装MySQL服务端

yum install mysql-server

安装MySQL客户端

yum install mysql

启动MySQL服务端

service mysqld start

以管理员登录

mysql -h localhost -P 3306 -u root -p'123456'
    -h：mysql server host，不写时默认是localhost。
    -P：mysql server port，不写时默认是3306。
    -u：user name，-u后可以加空格也可以不加。
    -p：password，密码中可能包含空格，所以要加引号。高版本的mysql不允许在命令行中直接输入密码，此时只输入-p后面不要写密码即可。 
创建账号
create user 'tester' identified by '123456'

创建database

create database test

把特定database的操作权限授予一个普通用户

grant all on test.* to tester;

以普通用户登录

mysql -utester -p'123456'

使用database

use test

创建表

#primary key (id) 设置id为主键
#unique key idx_name (name)表示为name创建唯一索引
//索引是什么？从数据库中每次查询数据，都会遍历表。我们创建索引后，就相当于构建一个快速查找树，所以我们要预估那个列会频繁进行where查询，我们对这些列建索引。
create table if not exists student(
    id int not null auto_increment comment '主键自增id',
    name char(10) not null comment '姓名',
    province char(6) not null comment '省',
    city char(10) not null comment '城市',
    addr varchar(100) default '' comment '地址',
    score float not null default 0 comment '考试成绩',
    enrollment date not null comment '入学时间',
    primary key (id),  unique key idx_name (name),  
    key idx_location (province,city)
)default charset=utf8 comment '学员基本信息';

我们执行下面命令，可以看到创建这个表的时候的语句。

show create table student \G; 

新增记录，必须给not null且无default值的列赋值

insert into student (name,province,city,enrollment) values
    ('张三','北京','北京','2021-03-05'),
    ('李四','河南','郑州','2021-04-25'),
    ('小丽','四川','成都','2021-03-10');

查询

select id,name from student where id>0;

select province,avg(score) as avg_score from student 
    where score>0 
    group by province having avg_score>50 
    order by avg_score desc;

修改

update student set score=score+10,addr='海淀' where province='北京';

update student set
    score=case province
        when '北京' then score+10     
        when '四川' then score+5 
        else score+7
    end,
    addr=case province
        when '北京' then '东城区'        
        when '四川' then '幸福里'        
        else '朝阳区'    
    end
where id>0;

删除

delete from student where city= '郑州';
delete from student;	--删除表里的所有行
drop table student;	    --删除表

2.1 MySQL最佳实践

• 写sql时一律使用小写。
• 建表时先判断表是否已存在if not exists。
• 所有的列和表都加comment。
• 字符串长度比较短时尽量使用char，定长有利于内存对齐，读写性能更好，而varchar字段频繁修改时容易产生内存碎片。
• 满足需求的前提下尽量使用短的数据类型，如tinyint vs int, float vs double, date vs datetime。

null

• default null有别于default ''和default 0
• is null, is not null有别于!= '', !=0
• 尽量设为not null
• 有些DB索引列不允许包含null
• 对含有null的列进行统计，结果可能不符合预期
• null值有时候会严重拖慢系统性能

3.1 索引

索引是什么？
索引是为了实现快速查找而设计的一种数据结构，快速查找的数据结构有map，b+tree

3.1.1 b+tree

b+tree，b即Balance，如图所示，这是一个三叉树，比2大比28小的在最左，比28大的在右

• Mysql索引默认使用B+tree

• 主键默认会加索引，按主键构建的B+树里面包含所有列的数据，而普通索引的B+树里只存储了主键，还需要再查一次主键对应的B+数（回表），简单来说就是where后面是主键索引，主键索引后面跟着一串信息，包含学生的分数，省份等，查一遍就都查到了，而如果是普通索引，后面没有这些，需要再查一遍mysql，称为回表

• 联合索引的前缀同样具有索引的效果。

• 如果mysql没有选择最优的索引方案，可以在where前force index（index_name）
  

• sql语句前加explain可以查看索引使用情况
  

从上面可以通过explain看到，有没有使用索引，会不会走索引

4.1 go连接mysql

Go官方没有提供数据库驱动，而是为开发数据库驱动定义了一些标准接口（即database/sql ），开发者可以根据定义的接口来开发相应的数据库驱动。Go中支持MySQL的驱动比较多，如

• github.com/go-sql-driver/mysql 支持 database/sql
• github.com/ziutek/mymysql 支持 database/sql，也支持自定义的接口
• github.com/Philio/GoMySQL 不支持 database/sql，自定义接口

4.1.1 Driver

type Driver interface { 
    Open(name string) (Conn, error) 
}
var d = Driver{proto: "tcp", raddr: "127.0.0.1:3306"}
sql.Register("mysql", &d)    //注册数据库驱动

4.1.2 Conn

type Conn interface {
    Prepare(query string) (Stmt, error)  //把一个查询query传给Prepare，返回Stmt(statement)
    Close() error  //关闭数据库连接
    Begin() (Tx, error)  //返回一个事务Tx(transaction)
}

4.1.3 Stmt

type Stmt interface {
    Close() error  //关闭当前的链接状态
    NumInput() int  //返回当前预留参数的个数
    Exec(args []Value) (Result, error)  //执行Prepare准备好的 sql，传入参数执行 update/insert 等操作，返回 Result 数据
    Query(args []Value) (Rows, error)  //执行Prepare准备好的 sql，传入需要的参数执行 select 操作，返回 Rows 结果集
}

4.1.4 Tx

type Tx interface {
    Commit() error  //提交事务
    Rollback() error  //回滚事务
}

4.1.5 Result

type Result interface {
    LastInsertId() (int64, error)  //返回由数据库执行插入操作得到的自增ID号。如果使用单个INSERT将多行插入到表中，则LastInsertId是第一条数据使用的id
    RowsAffected() (int64, error)  //返回操作影响的数据条目数
}

4.1.6 RowsAffected

RowsAffected是int64的别名，它实现了Result接口。
type RowsAffected int64
func (RowsAffected) LastInsertId() (int64, error)
func (v RowsAffected) RowsAffected() (int64, error)

4.1.7 Rows

type Rows interface {
    Columns() []string  //查询所需要的表字段
    Close() error  //关闭迭代器
    Next(dest []Value) error  //返回下一条数据，把数据赋值给dest，dest里面的元素必须是 driver.Value的值。如果最后没数据了，Next 函数返回 io.EOF
}

4.1.8 Value

type Value interface{}
Value 要么是 nil，要么是下面的任意一种
int64
float64
bool
[]byte
string
time.Time ValueConverter

type ValueConverter interface {
    //把数据库里的数据类型转换成Value允许的数据类型
    ConvertValue(v interface{}) (Value, error)
}

5.1 数据库增删改查

下载第三方库

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

连接数据库

db, err := sql.Open("mysql", "root:@tcp(localhost:3306)/test?charset=utf8")

DSN(data source name)格式：
[username[:password]@][protocol[(address)]]/dbname[?param1=value1&...¶mN=valueN]
??例如user:password@tcp(localhost:5555)/dbname?charset=utf8mb4&parseTime=True
??如果是本地MySQl，且采用默认的3306端口，可简写为：user:password@/dbname
连接参数
??要支持完整的UTF-8编码，您需要将charset=utf8更改为charset=utf8mb4 ??想要正确的处理time.Time ，您需要带上parseTime参数

增删改

func (*sql.DB).Exec(sql string) (sql.Result, error)

func (*sql.DB).Query(sql string) (*sql.Rows, error)

例子

import (
	"database/sql"
	"fmt"
	"go-course/database"
	"time"

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

const TIME_LAYOUT = "2006-01-02"

var (
	loc *time.Location
)

func init() {
	loc, _ = time.LoadLocation("Asia/Shanghai")
}

func main() {
	//想要正确的处理time.Time ，您需要带上parseTime参数
	//要支持完整的UTF-8编码，您需要将charset=utf8更改为charset=utf8mb4
	dsn := "tester:123456@tcp(127.0.0.1:3306)/test?charset=utf8mb4&parseTime=True"
	db, err := gorm.Open(mysql.Open(dsn), &gorm.Config{})
	database.CheckError(err)
	db.AutoMigrate(&Student{}) //让数据库之前存储的记录的表格字段和程序中最新使用的表格字段保持一致（只增不减）
	// query(db)
	// update(db)
	// create(db)
	// delete(db)
	// transaction(db)
	transaction2(db)
	// db.Where("name like ?", "学生%").Delete(&Student{}) //删除name以"学生"为前缀的记录
}

//insert 插入数据
func insert(db *sql.DB) {
	//一条sql，插入2行记录
	res, err := db.Exec("insert into student (name,province,city,enrollment) values ('小明', '深圳', '深圳', '2021-04-18'), ('小红', '上海', '上海', '2021-04-26')")
	database.CheckError(err)
	lastId, err := res.LastInsertId() //ID自增，用过的id（即使对应的行已delete）不会重复使用。如果使用单个INSERT语句将多行插入到表中，则LastInsertId是第一条数据使用的id
	database.CheckError(err)
	fmt.Printf("after insert last id %d\n", lastId)
	rows, err := res.RowsAffected() //插入2行，所以影响了2行
	database.CheckError(err)
	fmt.Printf("insert affect %d row\n", rows)
}

//replace 插入(覆盖)数据
func replace(db *sql.DB) {
	//由于name字段上有唯一索引，insert重复的name会报错。而使用replace会先删除，再插入
	res, err := db.Exec("replace into student (name,province,city,enrollment) values ('小明', '深圳', '深圳', '2021-04-18'), ('小红', '上海', '上海', '2021-04-26')")
	database.CheckError(err)
	lastId, err := res.LastInsertId() //ID自增，用过的id（即使对应的行已delete）不会重复使用
	database.CheckError(err)
	fmt.Printf("after insert last id %d\n", lastId)
	rows, err := res.RowsAffected() //先删除，后插入，影响了4行
	database.CheckError(err)
	fmt.Printf("insert affect %d row\n", rows)
}

//update 修改数据
func update(db *sql.DB) {
	//不同的city加不同的分数
	res, err := db.Exec("update student set score=score+10 where city='上海'") //上海加10分
	database.CheckError(err)
	lastId, err := res.LastInsertId() //0, 仅插入操作才会给LastInsertId赋值
	database.CheckError(err)
	fmt.Printf("after update last id %d\n", lastId)
	rows, err := res.RowsAffected() //where city=?命中了几行，就会影响几行
	database.CheckError(err)
	fmt.Printf("update affect %d row\n", rows)
}

//query 查询数据
func query(db *sql.DB) {
	rows, err := db.Query("select id,name,city,score from student where id>2") //查询得分大于2的记录
	database.CheckError(err)
	for rows.Next() { //没有数据或发生error时返回false
		var id int
		var score float32
		var name, city string
		err = rows.Scan(&id, &name, &city, &score) //通过scan把db里的数据赋给go变量
		database.CheckError(err)
		fmt.Printf("id=%d, score=%.2f, name=%s, city=%s \n", id, score, name, city)
	}
}

//delete 删除数据
func delete(db *sql.DB) {
	res, err := db.Exec("delete from student where id>13") //删除得分大于13的记录
	database.CheckError(err)
	rows, err := res.RowsAffected() //where id>13命中了几行，就会影响几行
	database.CheckError(err)
	fmt.Printf("delete affect %d row\n", rows)
}

6.1 stmt

首先看两个sql注入攻击的例子。stmt可以很好地避免sql注入，他可以让传参进来的username就是一个string，而不会识别成sql的一部分

sql = "select username,password from user where username='" + username + "' and password='" + password + "'"; 

变量username和password从前端输入框获取，如果用户输入的username为lily， password为aaa' or '1'='1，则完整的sql为select username,password from user where username='lily' and password='aaa' or '1'='1'。会返回表里的所有记录，如果记录数大于0就允许登录，则lily的账号被盗。

sql="insert into student (name) values ('"+username+" ') ";

变量username从前端输入框获取，如果用户输入的username为lily'); drop table student;--。完整sql为insert into student (name) values ('lily'); drop table student;--')。通过注释符--屏蔽掉了末尾的')，删除了整个表。
防止sql注入的方法：

• 前端输入要加正则校验、长度限制。
• 对特殊符号(<>&*; '"等)进行转义或编码转换，Go的text/template 包里面的HTMLEscapeString函数可以对字符串进行转义处理。
• 不要将用户输入直接嵌入到sql语句中，而应该使用参数化查询接口，如Prepare、Query、Exec(query string, args ...interface{})。
• 使用专业的SQL注入检测工具进行检测，如sqlmap、SQLninja。
• 避免网站打印出SQL错误信息，以防止攻击者利用这些错误信息进行SQL注入。

参数化查询

db.Where("merchant_id = ?", merchantId)

拼接sql

db.Where(fmt.Sprintf("merchant_id = %s", merchantId))

定义一个sql模板

stmt, err := db.Prepare("update student set score=score+? where city=?")

多次使用模板

res, err := stmt.Exec(10, "上海")
res, err = stmt.Exec(9, "深圳") 

7.1 SQL预编译

DB执行sql分为3步：

• 词法和语义解析。
• 优化SQL语句，制定执行计划。
• 执行并返回结果。
  ??SQL预编译技术是指将用户输入用占位符?代替，先对这个模板化的sql进行预编译，实际运行时再将用户输入代入。除了可以防止SQL注入，还可以对预编译的SQL语句进行缓存，之后的运行就省去了解析优化SQL语句的过程。

stmt_demo.go

//update 通过stmt修改数据
func update(db *sql.DB) {
	//不同的city加不同的分数
	stmt, err := db.Prepare("update student set score=score+? where city=?")
	database.CheckError(err)
	//执行修改操作通过stmt.Exec，执行查询操作通过stmt.Query
	res, err := stmt.Exec(10, "上海") //上海加10分
	database.CheckError(err)
	res, err = stmt.Exec(9, "深圳") //深圳加9分
	database.CheckError(err)
	lastId, err := res.LastInsertId() //0, 仅插入操作才会给LastInsertId赋值
	database.CheckError(err)
	fmt.Printf("after update last id %d\n", lastId)
	rows, err := res.RowsAffected() //where city=?命中了几行，就会影响几行
	database.CheckError(err)
	fmt.Printf("update affect %d row\n", rows)
}

//query 通过stmt查询数据
func query(db *sql.DB) {
	stmt, err := db.Prepare("select id,name,city,score from student where id>?")
	database.CheckError(err)
	//执行修改操作通过stmt.Exec，执行查询操作通过stmt.Query
	rows, err := stmt.Query(2) //查询得分大于2的记录
	database.CheckError(err)
	for rows.Next() { //没有数据或发生error时返回false
		var id int
		var score float32
		var name, city string
		err = rows.Scan(&id, &name, &city, &score) //通过scan把db里的数据赋给go变量
		database.CheckError(err)
		fmt.Printf("id=%d, score=%.2f, name=%s, city=%s \n", id, score, name, city)
	}
}

遍历一张表的正确姿势：

//traverse 借助于主健自增ID，通过where id>maxid遍历表
func traverse(db *sql.DB) {
	var maxid int
	begin := time.Now()
	stmt, _ := db.Prepare("select id,name,province from student where id>? limit 100") //limit m,n  limit 0,n
	for i := 0; i < 100; i++ {
		t0 := time.Now()
		rows, _ := stmt.Query(maxid)
		fmt.Println(i, time.Since(t0))

		for rows.Next() {
			var id int
			var name string
			var province string
			rows.Scan(&id, &name, &province)
			if id > maxid {
				maxid = id
			}
		}
	}
	fmt.Println("total", time.Since(begin))
}