[大数据]Go操作MySQL

Go操作MySql

连接

go语言中的database/sql包提供了Sql数据库的泛用接口，但是不提供具体的驱动。使用这个包时必须注入数据库驱动。

下载依赖

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

使用MySql驱动

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

打开一个指定driverName的数据库，dataSourceName指定数据源

import (
	"database/sql"
	"fmt"

	_ "github.com/go-sql-driver/mysql"
)
func Open() {
	// DSN:data source name
	dsn := "root:123@tcp(127.0.0.1:3306)/t1231est"
	db, err := sql.Open("mysql", dsn)

	if err != nil {
		fmt.Printf("打开失败，db:%#v", db)
		panic(err)
	}
	defer db.Close() // 写在error判断的下面
	fmt.Printf("成功打开数据库，db:%#v", db)
}

初始化连接

Open函数只有检查参数格式的作用，并不创建与数据库的连接，如需检查数据源是否有效应该使用Ping方法。

Open得到的DB对象可以被多个goroutine并发使用，并且维护一个空闲的连接池。所以Open函数应该只被调用一次，很少关闭DB对象。

// 定义一个全局变量
var db *sql.DB // 表示连接的数据库对象， 内部维护着一个连接池，可以安全的被多个goroutine使用

func initDB() (err error) {
   dsn := "root:123@tcp(127.0.0.1:3306)/tes22t"
   db, err = sql.Open("mysql", dsn)
   if err != nil {
      fmt.Printf("打开失败，db:%#v", db)
      return err
   }
   // 尝试与数据库建立连接
   err = db.Ping()
   if err != nil {
      return err
   }
   return nil
}

SetMaxOpenConns

func (db *DB) SetMaxOpenConns(n int)

设置与数据库建立连接的最大数目。n≤0时连接数无限制，0<n<最大闲置连接数时，最大闲置连接数会减小到最大连接数。

SetMaxIdleConns

func (db *DB) SetMaxIdleConns(n int)

设置连接池中的最大闲置连接数。n ≤ 0时不会保留闲置连接，n > 最大开启连接数时，最大闲置连接数减小到最大开启连接数。

CRUD

创建表

选定或新建一个数据库，使用以下SQL创建一张表

CREATE TABLE `user` (
  `id` int(4) NOT NULL,
  `name` varchar(32) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

查询

首先封装结构体代表表中的每一行记录

// 创建结构体封装数据库查询对象
type user struct {
	id   int
	name string
}

单行查询

通过db.QueryRow()来查询单行数据，期望返回最多一行结果。

返回值的Scan方法被调用时才会返回被延时的错误。

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

示例：

func queryRowDemo() {
	sqlStr := "select id, name from user where id = ?"
	var u user
	// 确保QueryRow之后调用Scan方法，否则持有的数据库连接不会被释放
	err := db.QueryRow(sqlStr, 1).Scan(&u.id, &u.name)
	if err != nil {
		fmt.Printf("scan failed, err:%v\n", err)
		return
	}
	fmt.Printf("id:%d name:%s\n", u.id, u.name)
}

多行查询

db.Query()执行一次查询返回多行数据

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

示例：

func queryMultiRowDemo() {
	sqlStr := "select id, name from user where id > 0"
	rows, err := db.Query(sqlStr)
	if err != nil {
		fmt.Printf("scan failed, err:%v\n", err)
		return
	}
	// 非常重要，关闭rows释放所有连接数据
	defer rows.Close()
	// 循环读取结果集中的数据
	for rows.Next() {
		var u user
		err := rows.Scan(&u.id, &u.name)
		if err != nil {
			fmt.Printf("Scan failed ,err: %v\n", err)
			return
		}
		fmt.Printf("id:%d name:%s\n", u.id, u.name)
	}
}

插入

插入、更新和删除操作都使用Exec方法。

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

Result是对已执行的SQL命令的总结, 参数args表示query中的占位参数。

示例:

func insertRowDemo() {
	sqlstr := "insert into user (id, name) values (?, ?)"
	ret, err := db.Exec(sqlstr, 223, "王五")
	if err != nil {
		fmt.Printf("insert failed, err:%v\n", err)
		return
	}
	theID, err := ret.LastInsertId()
	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 name = ? where id = ?"
	ret, err := db.Exec(sqlStr, "张s", 222)
	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, 2)
	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)
}

预处理

概念

不使用预处理时完整的SQL语句会发送给客户端，会有SQL注入的风险。使用预处理可以将SQL与数据拆分，先将要执行的SQL“模板”发送给数据库预处理，之后再将占位符参数发送给数据库，这样就可以防止SQL注入。

普通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)

返回值可以同时执行多个查询和命令。

查询示例：

func prepareQueryDemo() {
	sqlstr := "select id, name from user where id > ?"
	s, err := db.Prepare(sqlstr)
	if err != nil {
		fmt.Printf("prepare failed, err:%v", err)
		return
	}
	defer s.Close()
	rows, err := s.Query(0)
	if err != nil {
		fmt.Printf("query failed, err:%v\n", err)
		return
	}
	defer rows.Close()
	for rows.Next() {
		var u user
		err := rows.Scan(&u.id, &u.name)
		if err != nil {
			fmt.Printf("scan failed, err:%v\n", err)
			return
		}
		fmt.Printf("id:%d name:%s\n", u.id, u.name)
	}
}

插入、删除、更新示例：

func prepareInsertDemo() {
	sqlStr := "insert into user (id, name) values (?, ?)"
	s, err := db.Prepare(sqlStr)
	if err != nil {
		fmt.Printf("prepare failed err:%v", err)
		return
	}
	defer s.Close()
	_, err = s.Exec(123, "大王子")
	if err != nil {
		fmt.Printf("insert failed err:%v", err)
		return
	}
	_, err = s.Exec(124, "小公主")
	if err != nil {
		fmt.Printf("insert failed err:%v", err)
		return
	}

	fmt.Printf("insert succeed")
}

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\n", err)
		return
	}
	sqlStr1 := "Update user set name = ? where id = ?"
	ret1, err := tx.Exec(sqlStr1, "大公主", 124)
	if err != nil {
		tx.Rollback() // 回滚
		fmt.Printf("exec sql1 failed, err:%v\n", err)
		return
	}
	affRow1, err := ret1.RowsAffected()
	if err != nil {
		tx.Rollback() // 回滚
		fmt.Printf("exec ret1.RowsAffected() failed, err:%v\n", err)
		return
	}

	sqlStr2 := "Update user set name='王大锤' where id=?"
	ret2, err := tx.Exec(sqlStr2, 3)
	if err != nil {
		tx.Rollback() // 回滚
		fmt.Printf("exec sql2 failed, err:%v\n", err)
		return
	}
	affRow2, err := ret2.RowsAffected()
	if err != nil {
		tx.Rollback() // 回滚
		fmt.Printf("exec ret1.RowsAffected() failed, err:%v\n", 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!")

}