Go语言操作MySQL数据库

7,Go操作MySQl

1.依赖下载

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

• 通过函数,驱动mysql

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

• 示例，打开一个SQL连接

package main

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

func main() {
  // user 用户名
  // password 密码
  // host IP
  // port 端口
  // dbName 库名
	dsn := "user:password@tcp(host:port)/dbName"
	db, err := sql.Open("mysql",dsn)
	if err != nil{
		panic(err)
	}
	defer db.Close()// 这里写在err下面。
}

2.连接的初始化

• Open函数可能只是验证其参互格式是否正确，实际上并不创建与数据库的连接，如果要检查数据源的名称是否真实有效应该调用ping

• 返回DB对象可以被多个goroutine并发使用，并且维护其自己的空闲连接池。因此Open函数应该仅被调用一次，很少需要关闭这个DB对象。

  package main
  
  import (
  	"database/sql"
  	"fmt"
  	_ "github.com/go-sql-driver/mysql"
  )
  // 全局定义db对象
  var db *sql.DB
  
  func initDB() (err error) {
  	dsn := "root:123@tcp(IP:HOST)/user_list?charset=utf8mb4&parseTime=True"
  	// 注意！！！这里不要使用:=，我们是给全局变量赋值，然后在main函数中使用全局变量db
  	db, err = sql.Open("mysql",dsn)
  	if err != nil{
  		return err
  	}
  	// 尝试与数据库建立连接（校验dsn是否正确）
  	err = db.Ping()
  	if err != nil{
  		return err
  	}
  	return nil
  }
  
  func main() {
  	err := initDB()
  	if err != nil {
  		fmt.Printf("init db failed, err:%v
  ", err)
  		return
  	}
  }
  

• Sql.DB 表示连接数据库对象（结构体实例）。它内部维护着一个具有零到多个底层连接的连接池，它可以安全地被多个goroutine同时使用。

3.SetMaxOpenConns

• SetMaxOpenConns设置与数据库建立连接的最大数目。如果n大雨0且小于最大闲置连接数，会将最大闲置连接数减小到匹配最大开启连接数的限制。 如果n<=0，不会限制最大开启连接数，默认为0（无限制）

  func (db *DB) SetMaxOpenConns(n int)
  

4.SetMaxIdleConns

• SetMaxIdleConns设置连接池中的最大闲置连接数。 如果n大于最大开启连接数，则新的最大闲置连接数会减小到匹配最大开启连接数的限制。 如果n<=0，不会保留闲置连接。

  func (db *DB) SetMaxIdleConns(n int)
  

5.CURD boy

5.1 插入数据

• 插入，更新和删除操作使用Exec方法。

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

• 操作：

  var db *sql.DB
  
  func inserRowDemo() {
  	sqlStr := "insert into user(name,age) values (?,?)"
  	ret, err := db.Exec(sqlStr,"小明",25)
  	if err != nil {
  		fmt.Printf("insert failed, err:%v
  ", err)
  	}
  	// 获取新数据插入id
  	theID, err := ret.LastInsertId()
  	if err != nil {
  		fmt.Printf("get last insert IO failed, err:%v
  ", err)
  		return
  	}
  	fmt.Printf("insert success, the id is %d.
  ",theID)
  }
  // insert success, the id is 1.
  

5.2 更新操作

func updateRowDemo() {
	sqlStr := "update user set age=? where id=?"
	ret,err := db.Exec(sqlStr,35,3)
	if err != nil{
		fmt.Printf("update failed, err:%v
",err)
		return
	}
	n, err := ret.RowsAffected() //获取操作影响的行数
	if err!= nil {
		fmt.Printf("get RowsAffected failed err:%v
",err)
		return
	}
	fmt.Printf("update success affected rows:%d
",n)
}
// update success affected rows:1

5.3 删除操作

func deleteRowDemo() {
	sqlStr := "delete from user where id=?"
	ret, err := db.Exec(sqlStr,3)
	if err != nil {
		fmt.Printf("delete failed, err:%v
", err)
		return
	}
	n, err := ret.RowsAffected() //操作影响的行数
	if err!=nil{
		fmt.Printf("get RowAffected failed, err:%v
",err)
		return
	}
	fmt.Printf("delete success, affected rows:%d
", n)
}

5.4单行数据查询

• 单行查询通过:db.QueryRow().

func (db *DB) QueryRow(query string, args ...interface{}) *Row

• 示例代码

type user struct {
	id   int
	age  int
	name string
}

func queryRowDemo(){
	sqlStr := "select id,name,age from user where id=?"
	var u user
	err := db.QueryRow(sqlStr, 1).Scan(&u.id,&u.name,&u.age)
	if err != nil{
		fmt.Printf("scan failed ,err:%v
",err)
		return
	}
	fmt.Printf("id:%d name:%s age:%d",u.id,u.name,u.age)//id:1 name:小明 age:25
}

5.5多行查询

• 多行查询db.Query() 执行一次查询。返回多行结果。一般用于执行select命令，参数 args表示query中的占位参数。

func (db *DB) Query(query string, args ...interface{}) (*Rows, error)

• 示例代码

type user struct {
	id   int
	age  int
	name string
}

func queryMultiRowDemo(){
	sqlStr := "select id,name,age from user where id > ?"
	rows, err := db.Query(sqlStr,0)
	if err!=nil{
		fmt.Printf("query failed err:%v
",err)
		return
	}
	// 关闭rows 释放持有数据库链接
	defer rows.Close()
	// 循环读取结果集数据
	for rows.Next() {
		var u user
		err := rows.Scan(&u.id,&u.name,&u.age)
		if err != nil{
			fmt.Printf("scan failed err:%v
", err)
			return
		}
		fmt.Printf("id:%d name:%s age:%d
", u.id, u.name, u.age)
	}
}

6.MySQL预处理

6.1什么是预处理？

• 普通SQL执行：

  • 客户端对SQL语句进行占位符替换得到完整的SQL语句。
  • 客户端发送完整SQL语句到MySQL服务端。
  • MySQL服务端执行完整的SQL语句并将结果返回给客户端。

• 预处理执行过程：

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

6.2预处理好处

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

6.3预处理示例

• 查询操作的预处理示例代码如下：

  func prepareQueryDemo() {
  	sqlStr := "select id,name,age from user where id > ?"
  	// 预处理一条SQL语句
  	stmt, err := db.Prepare(sqlStr)
  	if err != nil {
  		fmt.Printf("prepare failed,err:%v
  ", err)
  		return
  	}
  	defer stmt.Close()
  	// 数据部分发送MySQL服务端。
  	rows, err := stmt.Query(0)
  	if err != nil {
  		fmt.Printf("query failed err:%v
  ",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("scan failed,err:%v
  ", err)
  			return
  		}
  		fmt.Printf("id:%d name:%s age:%d
  ", u.id, u.name, u.age)
  	}
  }
  

• 插入消息预处理示例

  func preporeInsertDemo() {
  	sqlStr := "insert into user(name,age) values(?,?)"
  	// 预处理一条SQL语句
  	stmt, err := db.Prepare(sqlStr)
  	if err != nil {
  		fmt.Printf("prepare failed, err:%v
  ", err)
  	}
  	defer stmt.Close()
  	// 添加数据
  	_, err = stmt.Exec("小红", 18)
  	if err != nil {
  		fmt.Printf("insert failed, err:%v
  ", err)
  		return
  	}
  	_, err = stmt.Exec("李二",25)
  	if err != nil {
  		fmt.Printf("insert failed err:%v
  ", err)
  	}
  	fmt.Println("insert success!")
  }
  

6.4数据库占位符

数据库	占位符语法
MySQL	?
PostgreSQL	$1, $2等
SQLite	?和$1
Oracle	:name

7.Go语言实现MySQL事务

• 事务具有四大特性：原子性，一致性，隔离性，持久性。

• 事务相关语法：

  func (db *DB) Begin() (*Tx, error) // 开启事务
  func (tx *Tx) Commit() error	//提交事务
  func (tx *Tx) Rollback() error //事务回滚
  

• 事务示例

  // 事务示例
  func transactionDemo() {
  	tx, err := db.Begin() //开启事务
  	if err != nil{
  		if tx != nil{
  			tx.Rollback()//回滚
  		}
  		fmt.Printf("begin trans failed, err:%v
  ",err)
  		return
  	}
  	sqlStrl := "Update user set age=18 where id=?"
  	ret1, err := tx.Exec(sqlStrl,1)
  	if err != nil {
  		tx.Rollback()
  		fmt.Printf("exec sql1 failed, err:%v
  ",err)
  		return
  	}
  	// 获取更改行数
  	affRow1, err := ret1.RowsAffected()
  	if err != nil {
  		fmt.Printf("exec ret1.RowsAffected() failed, err:%v
  ",err)
  		return
  	}
  	sqlStr2 := "Update user set age=22 where id=?"
  	ret2, err := tx.Exec(sqlStr2, 4)
  	if err != nil{
  		tx.Rollback()//回滚
  		fmt.Printf("exec sql2 failed, err:%v
  ", err)
  		return
  	}
  	affRow2, err := ret2.RowsAffected()
  	if err != nil {
  		tx.Rollback()
  		fmt.Printf("exec ret2.RowsAffected() failed,Err:%v
  ", err)
  		return
  	}
  	fmt.Println(affRow1,affRow2)
  	if affRow1 == 1 && affRow2 == 1 {
  		fmt.Println("事务提交了...")
  		tx.Commit()//提交事务
  	} else {
  		tx.Rollback()
  		fmt.Println("事务回滚了...")
  	}
  	fmt.Println("exec trans success!")
  }
  

• 参考博客：https://www.liwenzhou.com/posts/Go/go_mysql/