Go语言对于MySQL的基本操作

一.下载依赖

终端中输入：

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

导入包

import (
	"database/sql"

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

二.案例

package main

//go get-u github.com/go-sql-driver/mysql  获取驱动
import (
	"database/sql"
	"fmt"
	_ "github.com/go-sql-driver/mysql"
)

var db *sql.DB
//根据数据库表中，有属性id,name,age，且id为插入时，自增1
var usergroup map[int]user = make(map[int]user, 100)

type user struct {
	id   int
	name string
	age  int
}

func initDB() (err error) {
	//连接数据库  user:password@tcp(ip:port)/databasename
	dsn := "root:123456@tcp(127.0.0.1:3306)/sql_test"
	db, err = sql.Open("mysql", dsn) //dsn格式不对这里会报错
	if err != nil {
		fmt.Printf("dsn: %s invaid! err:%v\n", dsn, err)
		return err
	}

	//判断一下是否连接成功
	err = db.Ping()
	if err != nil {
		fmt.Printf("open %s failed! err:%v\n", dsn, err)
		return err
	} else {
		fmt.Printf("open %s success!\n", dsn)
	}
	//设置数据库连接池的最大连接数，根据业务调整
	db.SetMaxOpenConns(10)
	db.SetMaxIdleConns(5) //最大闲置连接数
	return nil
}

// 输入id，返回对应的user信息  查询单条记录
func queryOne(id int) (u user, err error) {
	//查询单条语句
	sqlstr := "select * from user where id=?" //?为占位符，在Query的时候可以用后续的参数进行填充
	rowObj := db.QueryRow(sqlstr, id)         //从数据库连接池中拿去一个连接去进行查询
	//得到了rowObj必须调用Scan方法，因为该方法会释放数据库连接，把连接放回连接池，否则连接池最大连接用完，则会影响后续连接查询
	rowObj.Scan(&u.id, &u.name, &u.age)
	if rowObj == nil {
		fmt.Printf("query failed,err:%v\n", err)
		return u, err
	}
	return u, nil
}

// 查询多行  读取到map中
func query() (err error) {
	sqlstr := "select * from user"
	rows, err := db.Query(sqlstr)
	if err != nil {
		fmt.Printf("query failed,err:%v\n", err)
		return
	}
	//记得关闭，放回连接池
	defer rows.Close()
	var u user
    //用一个for循环，把每次读到的行信息，存放到全局变量usergroup中，达到程序启动初始化的效果
	for rows.Next() {
		err = rows.Scan(&u.id, &u.name, &u.age)
		if err != nil {
			fmt.Printf("scan failed,err:%v\n", err)
			return
		}
		usergroup[u.id] = u
	}
	return nil
}

// 插入一条学生数据，并且更新map  id是主码，
//实际插入学生数据的时候，并不知道数据库中学号应该分配多少，所以在插入后得到返回的id，再更新map
func insert(u user) (id int64, err error) {
	sqlstr := "insert into user(name,age) values(?,?)"
	var res sql.Result
    //执行该语句
	res, err = db.Exec(sqlstr, u.name, u.age)
	if err != nil {
		fmt.Printf("insert failed,err:%v\n", err)
		return
	}
	//拿到插入的id，返回
	id, err = res.LastInsertId()
	if err != nil {
		fmt.Printf("insert failed,err:%v\n", err)
		return
	}
	return id, nil
}

//删除指定id的user，实际上最好判断一下是否存在map中，这里就先不写了
func deleteUser(id int) (err error) {
	sqlstr := "delete from user where id=?"
	_, err = db.Exec(sqlstr, id)
	if err != nil {
		fmt.Printf("delete failed,err:%v\n", err)
		return err
	}
	delete(usergroup, id)
	return nil
}

// 查询单条语句
func test01() {
	//查询学号为1的学生信息
	u, err := queryOne(1)
	if err != nil {
		fmt.Printf("query failed,err:%v\n", err)
		return
	}
	println(u.id, u.name, u.age)

}

// 程序初始化时，把user表中信息全部读取到map中
func readToMap() {
	//从数据库中读取user放入map中
	err := query()
	if err != nil {
		fmt.Printf("query failed,err:%v\n", err)
		return
	}
}

// 测试插入
func myInsertTest() {
	var u user
	u.age = 23
	u.name = "周杰伦"
	id, err1 := insert(u)
	if err1 != nil {
		fmt.Printf("insert failed,err:%v\n", err1)
		return
	}
	//说明插入成功
	u.id = int(id)
	usergroup[u.id] = u
}

//遍历map
func printMap() {
	//遍历
	for _, u := range usergroup {
		println(u.id, u.name, u.age)
	}
	println("***************************************")
}

func main() {
	err := initDB()
	defer db.Close()
	if err != nil {
		fmt.Printf("init db failed,err:%v\n", err)
		return
	}
	readToMap()
	printMap()

	myInsertTest()
	printMap()

}

三.预处理

预处理执行过程：

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

优点：

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

func prepareInsert() {
	sqlstr := "insert into user(name,age) values(?,?)"
	//先将sql语句发送给数据库，为后续执行做准备，后续只需要传递参数即可
	stmt, err := db.Prepare(sqlstr)
	if err != nil {
		fmt.Printf("prepare failed,err:%v\n", err)
		return
	}
	defer stmt.Close()
	//后续只需要用stmt执行操作，传递参数
	res, err1 := stmt.Exec("陶喆", 18)
	if err1 != nil {
		fmt.Printf("insert failed,err:%v\n", err1)
		return
	}
	id, err2 := res.LastInsertId()
	if err2 != nil {
		fmt.Printf("insert failed,err:%v\n", err2)
		return
	}
	usergroup[int(id)] = user{int(id), "陶喆", 18}

}

四.事务操作

//事务操作
func transactionDemo() {
	//开启事务
	tx,err:=db.Begin()
	if err!=nil{
		fmt.Printf("begin failed,err:%v\n", err)
		return
	}
	//执行多个SQL操作
	sqlstr1:="update user set age=? where id=1"
	sqlstr2:="update user set age=? where id=2"

	_,err=tx.Exec(sqlstr1, 18)
	if err!=nil{
		//回滚
		fmt.Println("sqlstr1 failed,err:%v\n", err)
		tx.Rollback()
	}
	_,err=tx.Exec(sqlstr2, 18)
	if err!=nil{
		fmt.Println("sqlstr2 failed,err:%v\n", err)
		//回滚
		tx.Rollback()
	}
	//提交
	tx.Commit()
}

五.对于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

//和上一个例子不一样，这个要字段要供其它包方法使用，比如Get方法需要对结构体进行反射，所以要大写
type user struct {
	Id   int
	Name string
	Age  int
}

func initDB() (err error) {
	dsn := "root:9826942694yzy@tcp(127.0.0.1:3306)/sql_test"
	// 也可以使用MustConnect连接不成功就panic
	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
}

func main() {
	err := initDB()
	if err != nil {
		fmt.Printf("init db failed,err:%v\n", err)
		return
	}
	defer db.Close()

	sqlStr := "select *from user where id=?"
	var u user
	err = db.Get(&u, sqlStr, 1)
	if err != nil {
		fmt.Printf("get failed, err:%v\n", err)
		return
	}
	fmt.Println(u.Id, u.Name, u.Age)

	var userlist = make([]user, 100)
	sqlStr1 := "select * from user"
	err = db.Select(&userlist, sqlStr1)
	if err != nil {
		fmt.Printf("select failed,err:%v\n", err)
		return
	}
	fmt.Println(userlist)
}

六.sql注入问题

func main() {
	err := initDB()
	if err != nil {
		fmt.Printf("init db failed,err:%v\n", err)
		return
	}
	defer db.Close()
	//这样符合要求
	inject_demo("周杰伦")
	//但是由于用户可以自行输出，如果输入以下，会输出所有
	//"select * from user where name='xxx' or 1=1#'"  == select * from user where name='xxx' or 1=1  这样会输出所有信息 #在mysql中是注释
	inject_demo("xxx' or 1=1#")
	//select *from user where name='xxx' union select * from user  输出所有
	inject_demo("xxx' union select * from user#")
	
}