32

Go语言开发（十八）、Go语言MySQL数据库操作

 5 years ago
source link: https://studygolang.com/articles/17825?amp%3Butm_medium=referral
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.

Go语言开发（十八）、Go语言MySQL数据库操作

一、MySQL数据库驱动

1、MySQL数据库驱动简介

Go语言官方没有实现MySQL数据库驱动，常用的开源MySQL数据库驱动实现如下：

（1）Go MySQL Driver

Go MySQL Driver支持database/sql接口，全部采用Go语言实现。

官方网站：

https://github.com/go-sql-driver/mysql/

（2）MyMySQL

MyMySQL支持database/sql接口，也支持自定义的接口，全部采用Go语言实现。

官方网站：

https://github.com/ziutek/mymysql

（3）GoMySQL

GoMySQL不支持database/sql接口，采用自定义接口，全部采用Go语言实现。

官方网站：

https://github.com/Philio/GoMySQL

2、Go-MySQL-Driver简介

Go-MySQL-Driver优点：

（1）维护比较好。

（2）完全支持database/sql接口。

（3）支持keepalive，保持长连接。

Go-MySQL-Driver安装如下：

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

导入包：

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

二、MySQL基本操作

1、MySQL数据库创建

登录MySQL数据库，创建数据库

create database student default character set utf8;

2、sql常用方法

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

driverName参数为数据库驱动名称。

dataSourceName是连接参数，参数格式如下：

user:password@tcp(host:port)/dbname?charset=utf8

func (db *DB) Prepare(query string) (*Stmt, error)

Prepare为后续查询或执行操作创建一个准备SQL

func (s *Stmt) Exec(args ...interface{}) (Result, error)

使用给定参数执行准备的SQL语句

func (s *Stmt) Query(args ...interface{}) (*Rows, error)

使用给定参数执行准备的SQL查询语句

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

执行SQL操作，query为SQL语句，可以接收可变参数，用于填充SQL语句的某些字段值。

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

执行SQL查询操作，可以接收多个参数

3、MySQL常用操作

package main

import (
   "database/sql"
   "fmt"

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

func errorHandler(err error) {
   if err != nil {
      fmt.Println(err.Error())
   }
}

var (
   CREATE_TABLE = "CREATE TABLE student(" +
      "sid INT(10) NOT NULL AUTO_INCREMENT," +
      "sname VARCHAR(64) NULL DEFAULT NULL," +
      "age INT(10) DEFAULT NULL,PRIMARY KEY (sid))" +
      "ENGINE=InnoDB DEFAULT CHARSET=utf8;"
)

// 建立数据库连接
func setupConnect() *sql.DB {
   db, err := sql.Open("mysql", "root:xxxxxx@tcp(118.24.159.133:3306)/student?charset=utf8")
   errorHandler(err)
   return db
}

// 创建表
func CreateTable(db *sql.DB, sql string) {
   _, err := db.Exec(sql)
   errorHandler(err)
}

var INSERT_DATA = `INSERT INTO student(sid,sname,age) VALUES(?,?,?);`

// 插入数据
func Insert(db *sql.DB) {
   db.Exec(INSERT_DATA, 1, "唐僧", 30)
}

var UPDATE_DATA = `UPDATE student SET age=28 WHERE sname="唐僧";`

// 修改数据
func Update(db *sql.DB) {
   db.Exec(UPDATE_DATA)

}

var DELETE_DATA = `DELETE FROM student WHERE age>=30`

// 删除记录
func Delete(db *sql.DB) {
   db.Exec(DELETE_DATA)
}

var DELETE_TABLE = `DROP TABLE student;`

// 删除表
func DeleteTable(db *sql.DB) {
   db.Exec(DELETE_TABLE)
}

var QUERY_DATA = `SELECT * FROM student;`

// 查询数据
func Query(db *sql.DB) {
   rows, err := db.Query(QUERY_DATA)
   if err != nil {
      fmt.Println(err)
   }
   for rows.Next() {
      var name string
      var id int
      var age int
      if err := rows.Scan(&id, &name, &age); err != nil {
         fmt.Println(err)
      }
      fmt.Printf("%s is %d\n", name, age)
   }
}

func main() {
   // 建立数据连接
   db := setupConnect()
   // 创建数据库表
   CreateTable(db, CREATE_TABLE)
   // 插入数据
   Insert(db)
   // 查询数据
   Query(db)
   // 删除数据
   Delete(db)
   // 插入数据
   Insert(db)
   // 修改数据
   Update(db)
   // 查询数据
   Query(db)
   // 删除表
   DeleteTable(db)
   // 关闭数据库连接
   db.Close()
}

三、MySQL事务操作

1、事务常用方法

func (db *DB) Begin() (*Tx, error)

开启事务，从连接池中取出一个 *TX 类型连接。使用TX类型连接可以进行回滚事务和提交事务。

func (tx *Tx) Commit() error

提交事务

func (tx *Tx) Rollback() error

回滚

func (tx *Tx) Exec(query string, args ...interface{}) (Result, error)

执行SQL操作

func (tx *Tx) Query(query string, args ...interface{}) (*Rows, error)

执行SQL查询操作

2、事务示例

// 支持事务回滚机制的批量数据插入
func MultiInsert(db *sql.DB) {
   // 批量数据插入
   tx, err := db.Begin()
   if err != nil {
      fmt.Println(err)
   }
   values := [][]interface{}{{2, "孙悟空", 500}, {3, "猪八戒", 200}, {4, "沙悟净", 100}}
   stmt, err := tx.Prepare("INSERT INTO student(sid,sname,age) VALUES(?,?,?);")
   for _, val := range values {
      _, err := stmt.Exec(val...)
      if err != nil {
         fmt.Printf("INSERT failed：%v", err)
         tx.Rollback()
      }
   }
   tx.Commit()
}

四、MySQL操作的效率分析

1、sql接口效率分析

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

sql.Open返回一个DB对象，DB对象对于多个goroutines并发使用是安全的，DB对象内部封装了连接池。Open函数并没有创建连接，只是验证参数是否合法，然后开启一个单独goroutine去监听是否需要建立新的连接，当有请求建立新连接时就创建新连接。

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

执行不返回行（row）的查询，比如INSERT，UPDATE，DELETE

DB交给内部的exec方法负责查询。exec会首先调用DB内部的conn方法从连接池里面获得一个连接。然后检查内部的driver.Conn是否实现了Execer接口，如果实现了Execer接口，会调用Execer接口的Exec方法执行查询；否则调用Conn接口的Prepare方法负责查询。

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

用于查询，DB交给内部的query方法负责查询。query首先调用DB内部的conn方法从连接池里面获得一个连接，然后调用内部的queryConn方法负责查询。

func (db *DB) Prepare(query string) (*Stmt, error)

返回一个Stmt。Stmt对象可以执行Exec,Query,QueryRow等操作。DB交给内部的prepare方法负责查询。prepare首先调用DB内部的conn方法从连接池里面获得一个连接，然后调用driverConn的prepareLocked方法负责查询。

func (db *DB) Begin() (*Tx, error)

开启事务，返回Tx对象。调用Begin方法后，TX会与指定的连接绑定，一旦事务提交或者回滚，事务绑定的连接就还给DB的连接池。DB交给内部的begin方法负责处理。begin首先调用DB内部的conn方法从连接池里面获得一个连接，然后调用Conn接口的Begin方法获得一个TX。

进行MySQL数据库操作时，如果每次SQL操作都从DB对象的连接池中获取连接，则会在很大程度上损耗效率。因此，必须尽量在一个连接上执行SQL操作。

2、效率分析示例

package main

import (
   "database/sql"
   "fmt"
   "strconv"
   "time"

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

var db = &sql.DB{}

func init() {
   db, _ = sql.Open("mysql", "root:123456@tcp(118.24.159.133:3306)/student?charset=utf8")
   CREATE_TABLE := "CREATE TABLE student(" +
      "sid INT(10) NOT NULL AUTO_INCREMENT," +
      "sname VARCHAR(64) NULL DEFAULT NULL," +
      "age INT(10) DEFAULT NULL,PRIMARY KEY (sid))" +
      "ENGINE=InnoDB DEFAULT CHARSET=utf8;"
   db.Exec(CREATE_TABLE)
}

func update() {
   //方式1 update
   start := time.Now()
   for i := 1001; i <= 1100; i++ {
      db.Exec("UPDATE student set age=? where sid=? ", i, i)
   }
   end := time.Now()
   fmt.Println("db.Exec update total time:", end.Sub(start).Seconds())

   //方式2 update
   start = time.Now()
   for i := 1101; i <= 1200; i++ {
      stm, _ := db.Prepare("UPDATE student set age=? where sid=? ")
      stm.Exec(i, i)
      stm.Close()
   }
   end = time.Now()
   fmt.Println("db.Prepare 释放连接 update total time:", end.Sub(start).Seconds())

   //方式3 update
   start = time.Now()
   stm, _ := db.Prepare("UPDATE student set age=? where sid=?")
   for i := 1201; i <= 1300; i++ {
      stm.Exec(i, i)
   }
   stm.Close()
   end = time.Now()
   fmt.Println("db.Prepare 不释放连接 update total time:", end.Sub(start).Seconds())

   //方式4 update
   start = time.Now()
   tx, _ := db.Begin()
   for i := 1301; i <= 1400; i++ {
      tx.Exec("UPDATE student set age=? where sid=?", i, i)
   }
   tx.Commit()

   end = time.Now()
   fmt.Println("tx.Exec 不释放连接 update total time:", end.Sub(start).Seconds())

   //方式5 update
   start = time.Now()
   for i := 1401; i <= 1500; i++ {
      tx, _ := db.Begin()
      tx.Exec("UPDATE student set age=? where sid=?", i, i)
      tx.Commit()
   }
   end = time.Now()
   fmt.Println("tx.Exec 释放连接 update total time:", end.Sub(start).Seconds())
}

func delete() {
   //方式1 delete
   start := time.Now()
   for i := 1001; i <= 1100; i++ {
      db.Exec("DELETE FROM student WHERE sid=?", i)
   }
   end := time.Now()
   fmt.Println("db.Exec delete total time:", end.Sub(start).Seconds())

   //方式2 delete
   start = time.Now()
   for i := 1101; i <= 1200; i++ {
      stm, _ := db.Prepare("DELETE FROM student WHERE sid=?")
      stm.Exec(i)
      stm.Close()
   }
   end = time.Now()
   fmt.Println("db.Prepare 释放连接 delete total time:", end.Sub(start).Seconds())

   //方式3 delete
   start = time.Now()
   stm, _ := db.Prepare("DELETE FROM student WHERE sid=?")
   for i := 1201; i <= 1300; i++ {
      stm.Exec(i)
   }
   stm.Close()
   end = time.Now()
   fmt.Println("db.Prepare 不释放连接 delete total time:", end.Sub(start).Seconds())

   //方式4 delete
   start = time.Now()
   tx, _ := db.Begin()
   for i := 1301; i <= 1400; i++ {
      tx.Exec("DELETE FROM student WHERE sid=?", i)
   }
   tx.Commit()

   end = time.Now()
   fmt.Println("tx.Exec 不释放连接 delete total time:", end.Sub(start).Seconds())

   //方式5 delete
   start = time.Now()
   for i := 1401; i <= 1500; i++ {
      tx, _ := db.Begin()
      tx.Exec("DELETE FROM student WHERE sid=?", i)
      tx.Commit()
   }
   end = time.Now()
   fmt.Println("tx.Exec 释放连接 delete total time:", end.Sub(start).Seconds())

}

func query() {

   //方式1 query
   start := time.Now()
   rows, _ := db.Query("SELECT sid,sname FROM student")
   defer rows.Close()
   for rows.Next() {
      var name string
      var id int
      if err := rows.Scan(&id, &name); err != nil {
         fmt.Println(err)
      }
   }
   end := time.Now()
   fmt.Println("db.Query query total time:", end.Sub(start).Seconds())

   //方式2 query
   start = time.Now()
   stm, _ := db.Prepare("SELECT sid,sname FROM student")
   defer stm.Close()
   rows, _ = stm.Query()
   defer rows.Close()
   for rows.Next() {
      var name string
      var id int
      if err := rows.Scan(&id, &name); err != nil {
         fmt.Println(err)
      }
   }
   end = time.Now()
   fmt.Println("db.Prepare query total time:", end.Sub(start).Seconds())

   //方式3 query
   start = time.Now()
   tx, _ := db.Begin()
   defer tx.Commit()
   rows, _ = tx.Query("SELECT sid,sname FROM student")
   defer rows.Close()
   for rows.Next() {
      var name string
      var id int
      if err := rows.Scan(&id, &name); err != nil {
         fmt.Println(err)
      }
   }
   end = time.Now()
   fmt.Println("tx.Query query total time:", end.Sub(start).Seconds())
}

func insert() {

   //方式1 insert
   start := time.Now()
   for i := 1001; i <= 1100; i++ {
      //每次循环内部都会去连接池获取一个新的连接，效率低下
      db.Exec("INSERT INTO student(sid,sname,age) values(?,?,?)", i, "student"+strconv.Itoa(i), i-1000)
   }
   end := time.Now()
   fmt.Println("db.Exec insert total time:", end.Sub(start).Seconds())

   //方式2 insert
   start = time.Now()
   for i := 1101; i <= 1200; i++ {
      //Prepare函数每次循环内部都会去连接池获取一个新的连接，效率低下
      stm, _ := db.Prepare("INSERT INTO student(sid,sname,age) values(?,?,?)")
      stm.Exec(i, "student"+strconv.Itoa(i), i-1000)
      stm.Close()
   }
   end = time.Now()
   fmt.Println("db.Prepare 释放连接 insert total time:", end.Sub(start).Seconds())

   //方式3 insert
   start = time.Now()
   stm, _ := db.Prepare("INSERT INTO student(sid,sname,age) values(?,?,?)")
   for i := 1201; i <= 1300; i++ {
      //Exec内部并没有去获取连接，为什么效率还是低呢？
      stm.Exec(i, "user"+strconv.Itoa(i), i-1000)
   }
   stm.Close()
   end = time.Now()
   fmt.Println("db.Prepare 不释放连接 insert total time:", end.Sub(start).Seconds())

   //方式4 insert
   start = time.Now()
   //Begin函数内部会去获取连接
   tx, _ := db.Begin()
   for i := 1301; i <= 1400; i++ {
      //每次循环用的都是tx内部的连接，没有新建连接，效率高
      tx.Exec("INSERT INTO student(sid,sname,age) values(?,?,?)", i, "user"+strconv.Itoa(i), i-1000)
   }
   //最后释放tx内部的连接
   tx.Commit()

   end = time.Now()
   fmt.Println("tx.Exec 不释放连接 insert total time:", end.Sub(start).Seconds())

   //方式5 insert
   start = time.Now()
   for i := 1401; i <= 1500; i++ {
      //Begin函数每次循环内部都会去连接池获取一个新的连接，效率低下
      tx, _ := db.Begin()
      tx.Exec("INSERT INTO student(sid,sname,age) values(?,?,?)", i, "user"+strconv.Itoa(i), i-1000)
      //Commit执行后释放连接
      tx.Commit()
   }
   end = time.Now()
   fmt.Println("tx.Exec 释放连接 insert total time:", end.Sub(start).Seconds())
}

func main() {
   insert()
   query()
   update()
   query()
   delete()
}

// output:
// db.Exec insert total time: 2.069104068
// db.Prepare 释放连接 insert total time: 1.869348813
// db.Prepare 不释放连接 insert total time: 1.447833105
// tx.Exec 不释放连接 insert total time: 1.098540307
// tx.Exec 释放连接 insert total time: 3.465670469
// db.Query query total time: 0.005803479
// db.Prepare query total time: 0.010966584
// tx.Query query total time: 0.011800843
// db.Exec update total time: 2.117122871
// db.Prepare 释放连接 update total time: 2.132430998
// db.Prepare 不释放连接 update total time: 1.523685366
// tx.Exec 不释放连接 update total time: 1.346163272
// tx.Exec 释放连接 update total time: 3.129312377
// db.Query query total time: 0.00848425
// db.Prepare query total time: 0.013472261
// tx.Query query total time: 0.012418198
// db.Exec delete total time: 2.100008271
// db.Prepare 释放连接 delete total time: 1.9821439490000001
// db.Prepare 不释放连接 delete total time: 1.429259466
// tx.Exec 不释放连接 delete total time: 1.103143464
// tx.Exec 释放连接 delete total time: 2.863670582

从示例结果看，执行SQL操作时如果不释放连接，则效率比释放连接要高。


report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK