Go 的 MySQL 预处理、MySQL 事务

vlambda
2022-02-23

Go 的 MySQL 预处理、MySQL 事务

预处理是什么

在普通 SQL 语句执行过程中，客户端会对 SQL 语句进行占位符替换，从而得到要执行的完整 SQL 语句，客户端再将此 SQL 语句发送到服务端执行，服务端最后把结果返回给客户端。

而预处理，则是将 SQL 语句分为命令部分以及数据部分，客户端先把命令部分发送给服务器，服务器先进行预处理，而后客户端才把数据部分发送给服务器，由服务器对 SQL 语句进行占位符替换并执行，最后将结果返回给客户端。

预处理可以提高服务器的性能，提前让服务器编译，一次编译多次执行，甚至可以避免 SQL 注入问题。

Go 实现 MySQL 预处理

在上一期《Go 操作 MySQL 数据库》中，很多例子都使用了预处理。Go 中的 Prepare() 方法会将 SQL 语句发送给服务器，返回一个准备好的状态用于之后的查询和命令，返回值可以同时执行多个查询和命令：

func (db *DB) Prepare(query string) (*Stmt, error) {
 return db.PrepareContext(context.Background(), query)
}

下面是一个查询操作预处理的例子：

package main

import (
 "database/sql"
 "fmt"

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

type User struct {
 id int
 phone string
 nickName string
 age int
}

func main() {
 // 连接数据库
 db, _ := sql.Open("mysql", "root:root@(127.0.0.1:3306)/godb_test?charset=utf8mb4")

 // Ping() 验证与数据库的连接是否仍处于活动状态，并在必要时建立连接
 err := db.Ping()

 if err != nil {
  fmt.Println("Database connection failed")
  return
 }

 // 延迟调用关闭数据库 阻止新的查询
 defer db.Close()

 // 准备 SQL 语句
 sqlStr := "select id, nick_name, phone, age from acl_user where id > ? and is_deleted = 0"

 stmt, err := db.Prepare(sqlStr)
 if err != nil {
  fmt.Println("Prepare failed")
  return
 }

 defer stmt.Close()
 rows, err := stmt.Query("100")

 if err != nil {
  fmt.Println("Query failed")
  return
 }

 defer rows.Close()

 // 读取结果集中的数据
 for rows.Next() {
  var user User
  err := rows.Scan(&user.id, &user.nickName, &user.phone, &user.age)
  if err != nil {
   fmt.Println("Scan failed")
   return
  }
  fmt.Println(user)
 }

}

Go 实现 MySQL 事务

Go 语言中使用下面的方法实现 MySQL 事务操作：

// 开启事务方法
func (db *DB) Begin() (*Tx, error) {
 return db.BeginTx(context.Background(), nil)
}

// 回滚事务方法
func (tx *Tx) Rollback() error {
 return tx.rollback(false)
}

// 提交事务方法
func (tx *Tx) Commit() error {
 select {
 default:
 case <-tx.ctx.Done():
  if atomic.LoadInt32(&tx.done) == 1 {
   return ErrTxDone
  }
  return tx.ctx.Err()
 }
 if !atomic.CompareAndSwapInt32(&tx.done, 0, 1) {
  return ErrTxDone
 }

 tx.cancel()
 tx.closemu.Lock()
 tx.closemu.Unlock()

 var err error
 withLock(tx.dc, func() {
  err = tx.txi.Commit()
 })
 if err != driver.ErrBadConn {
  tx.closePrepared()
 }
 tx.close(err)
 return err
}

下面是一个事务操作的例子，该事务操作确保两次更新操作要么同时成功要么同时失败：

package main

import (
 "database/sql"
 "fmt"

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

func main() {
 // 连接数据库
 db, _ := sql.Open("mysql", "root:root@(127.0.0.1:3306)/godb_test?charset=utf8mb4")

 // Ping() 验证与数据库的连接是否仍处于活动状态，并在必要时建立连接
 err := db.Ping()

 if err != nil {
  fmt.Println("Database connection failed")
  return
 }

 // 延迟调用关闭数据库 阻止新的查询
 defer db.Close()

 // 开启事务
 t, err := db.Begin()
 if err != nil {
  if t != nil {
   // 回滚
   t.Rollback()
  }
  fmt.Println("Begin transaction failed")
  return
 }

 // 准备 SQL 语句
 sqlStr := "update acl_user set nick_name = ?, age = ? where id = ? and is_deleted = 0"
 // 执行 SQL 语句
 _, err = t.Exec(sqlStr, "AAA", 60, 102)

 if err != nil {
  // 回滚
  t.Rollback()
  fmt.Println("Exec failed")
  return
 }

 // 准备 SQL 语句
 sqlStr2 := "update acl_user set nick_name = ?, age = ? where id = ? and is_deleted = 0"
 // 执行 SQL 语句
 _, err = t.Exec(sqlStr2, "BBB", 50, 103)

 if err != nil {
  // 回滚
  t.Rollback()
  fmt.Println("Exec failed")
  return
 }

 // 提交事务
 err = t.Commit()

 if err != nil {
  // 回滚
  t.Rollback()
  fmt.Println("Commit failed")
  return
 }

 fmt.Println("Exec transaction Success")

}