2.Go语言项目操作MySQL数据库实践

全栈工程师修炼指南

发布于 2022-09-29 16:54:59

6.4K0

发布于 2022-09-29 16:54:59

文章被收录于专栏：全栈工程师修炼之路全栈工程师修炼之路

[TOC]

Go语言编程快速入门之go-sql-driver操作MySQL数据库

快速了解 MySQL 数据库 MySQL 是目前主流关系型的数据库，它的胞胎兄弟 MariaDB (MySQL 的一个分支)，除此之外使用最多的就是 Oracle 和 PostgreSQL 数据库。

SQL 语言类型:

DDL : 主要是操作数据库
DML : 主要进行表的增删改查
DCL : 主要进行用户和权限操作

MySQL 至此插件式的存储引擎，其常见存储引擎MyISAM 和 InnoDB： MyISAM 特点:

查询速度快
只支持表锁
不支持事务

InnoDB 特点:

整体操作速度快
支持表锁和行锁
支持事务

事务的特点即我们常说的ACID:

A(Atomicity)- 原子性 (多个语句要么全成功，要么即失败，将不会更改数据库的数据)
C(Consistence) - 一致性 (在每次提交或回滚之后以及正在进行的事务处理期间,数据库始终保持一致状态,要么全部旧值要么全部新值)
I(Isolation) - 隔离性 (事务之间的相互隔离的)
D(Durability) - 持久性 (事务操作的结果是不会丢失的)

1.MySQL驱动下载

描述: Go语言中的database/sql包提供了保证SQL或类SQL数据库的泛用接口, 并不提供具体的数据库驱动, 所以使用database/sql包时必须注入（至少）一个数据库驱动。

Go语言中我们常用的数据库操作, 基本上都有完整的第三方实现，例如本节的MySQL驱动(https://github.com/go-sql-driver/mysql)

# 下载mysql驱动依赖, 第三方的依赖默认保存在 `$GOPATH/src` (注意是在项目目录里)
➜ go get -u github.com/go-sql-driver/mysql
go: downloading github.com/go-sql-driver/mysql v1.6.0
➜ weiyigeek.top go get github.com/go-sql-driver/mysql

# 项目地址
➜ weiyigeek.top pwd
/home/weiyigeek/app/program/project/go/src/weiyigeek.top

# 第三方包地址
➜ go-sql-driv pwd
/home/weiyigeek/app/program/project/go/pkg/mod/github.com/go-sql-driv

2.MySQL驱动格式

描述: 使用MySQL驱动格式函数原型如下所示：

func Open(driverName, dataSourceName string) (*DB, error) : Open方法是打开一个dirverName指定的数据库，dataSourceName指定数据源，一般至少包括数据库文件名和其它连接必要的信息。
func (db *DB) SetMaxOpenConns(n int) : SetMaxOpenConns方法是设置与数据库建立连接的最大数目。
- 如果n大于0且小于最大闲置连接数，会将最大闲置连接数减小到匹配最大开启连接数的限制。
- 如果n<=0，不会限制最大开启连接数，默认为0（无限制）。
func (db *DB) SetMaxIdleConns(n int) : SetMaxIdleConns方法是设置连接池中的最大闲置连接数。
- 如果n大于最大开启连接数，则新的最大闲置连接数会减小到匹配最大开启连接数的限制。
- 如果n<=0，不会保留闲置连接。

基础示例

import (
  "database/sql"
  "fmt"

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

func main() {
  // 数据库DSN(Data Source Name)连接数据源
  dsn := "root:WwW.weiyigeek.top@tcp(10.20.172.248:3306)/test?charset=utf8&parseTime=True"

  // 连接数据库
  db, err := sql.Open("mysql", dsn)
  if err != nil {
    fmt.Printf("DSN : %s Format failed, Error: %v \n", dsn, err)
    panic(err)
  }
  // 此行代码要写在上面err判断的下面（注意点）。
  defer db.Close()

  // 判断连接的数据库
  err = db.Ping()
  if err != nil {
    fmt.Printf("Connection %s Failed, Error: %v \n", dsn, err)
    return
  }

  fmt.Println("数据库连接成功！")
}

Tips: 为什么上面代码中的defer db.Close()语句不应该写在if err != nil的前面呢？

3.MySQL初始化连接

描述: 上面的例子可以看到Open函数可能只是验证其参数格式是否正确，实际上并不创建与数据库的连接，此时我们如果要检查数据源的名称是否真实有效，应该调用Ping方法。

下述代码中sql.DB是表示连接的数据库对象（结构体实例），它保存了连接数据库相关的所有信息。它内部维护着一个具有零到多个底层连接的连接池，它可以安全地被多个goroutine同时使用。

MySQL 用户密码更改:

-- MySQL 5.7.x & MySQL 8.x
ALTER USER `root`@`%` IDENTIFIED BY 'weiyigeek.top';

初始化示例:

// Go 语言利用 MySQL Driver 连接 MySQL 示例
package main
import (
  "database/sql"
  "fmt"

  _ "github.com/go-sql-driver/mysql"
)
// 定义一个全局对象db
var db *sql.DB
// 定义一个初始化数据库的函数
func initDB() (err error) {
  // DSN(Data Source Name) - 数据库连接数据源
  // MySQL 5.7.X 与 MySQL 8.x 都是支持的
  dsn := "root:weiyigeek.top@tcp(10.20.172.248:3306)/test?charset=utf8&parseTime=True"
  // 注册第三方mysql驱动到sql中，此处并不会校验账号密码是否正确，此处赋值给全局变量db。
  db, err = sql.Open("mysql", dsn)
  if err != nil {
    fmt.Printf("DSN : %s Format failed\n %v \n", dsn, err)
    return err
  }
  // 尝试与数据库建立连接（校验DSN是否正确）
  err = db.Ping()
  if err != nil {
    fmt.Printf("Connection %s Failed,\n%v \n", dsn, err)
    return err
  }
  // 设置与数据库建立连接的最大数目
  db.SetMaxOpenConns(1024)
  // 设置连接池中的最大闲置连接数，0 表示不会保留闲置。
  db.SetMaxIdleConns(0) 
  fmt.Println("数据库初始化连接成功!")
  return nil
}

func main() {
  // 调用输出化数据库的函数
  err := initDB()
  defer db.Close()

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

执行结果:

# 连接成功时
数据库初始化连接成功!

# 连接失败时
Connection root:www.weiyigeek.top@tcp(10.20.172.248:3306)/test?charset=utf8&parseTime=True Failed,
Error 1045: Access denied for user 'root'@'10.20.172.108' (using password: YES) 
Database Init failed!

4.MySQL的CRUD操作

库表准备

我们首先需要在MySQL(8.x)数据库中创建一个名为test数据库和一个user表,SQL语句如下所示:

-- 建库建表
CREATE DATABASE test;
USE test;
CREATE TABLE `user` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(20) DEFAULT '',
  `age` INT(11) DEFAULT '0',
  PRIMARY KEY(`id`)
)ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

-- 测试数据插入
INSERT INTO `test`.`user`(`uid`, `name`, `age`) VALUES (1, 'WeiyiGeek', 20);
INSERT INTO `test`.`user`(`uid`, `name`, `age`) VALUES (2, 'Elastic', 18);
INSERT INTO `test`.`user`(`uid`, `name`, `age`) VALUES (3, 'Logstash', 20);
INSERT INTO `test`.`user`(`uid`, `name`, `age`) VALUES (4, 'Beats', 10);
INSERT INTO `test`.`user`(`uid`, `name`, `age`) VALUES (5, 'Kibana', 19);
INSERT INTO `test`.`user`(`uid`, `name`, `age`) VALUES (6, 'C', 25);
INSERT INTO `test`.`user`(`uid`, `name`, `age`) VALUES (7, 'C++', 25);
INSERT INTO `test`.`user`(`uid`, `name`, `age`) VALUES (8, 'Python', 26);

示例结构体声明:

type user struct {
  id   int
  age  int
  name string
}

单行查询

函数原型: func (db *DB) QueryRow(query string, args ...interface{}) *Row 函数说明: 单行查询db.QueryRow()执行一次查询，并期望返回最多一行结果（即Row）。 Tips: QueryRow总是返回非nil的值，直到返回值的Scan方法被调用时，才会返回被延迟的错误。（如：未找到结果）

简单示例:

// 查询单条数据示例
func queryRowDemo() {
  var u user
  sqlStr := "select id, name, age from user where id=?"
  // 非常重要：确保QueryRow之后调用Scan方法，否则持有的数据库链接不会被释放 [注意点]
  err := db.QueryRow(sqlStr, 1).Scan(&u.id, &u.name, &u.age)
  if err != nil {
    fmt.Printf("scan failed, err:%v\n", err)
    return
  }
  fmt.Printf("id:%d name:%s age:%d\n", u.id, u.name, u.age)
}

多行查询

函数原型: func (db *DB) Query(query string, args ...interface{}) (*Rows, error)

函数说明: 多行查询db.Query()执行一次查询，返回多行结果(即 Rows), 一般用于执行select命令, 参数args表示 query中的占位参数(空接口)。

简单示例:

// 查询多条数据示例
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\n", 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\n", err)
      return
    }
    fmt.Printf("id:%d name:%s age:%d\n", u.id, u.name, u.age)
  }
}

插入/更新/删除数据

函数原型: func (db *DB) Exec(query string, args ...interface{}) (Result, error) 函数说明: Exec执行一次命令（包括查询、删除、更新、插入等），返回的Result是对已执行的SQL命令的总结。参数args表示query中的占位参数。

具体插入数据示例代码如下：

// 插入数据
func insertRowDemo() {
  sqlStr := "insert into user(name, age) values (?,?)"
  ret, err := db.Exec(sqlStr, "王五", 38)
  if err != nil {
    fmt.Printf("insert failed, err:%v\n", err)
    return
  }
  // 新插入数据的id
  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 age=? where id = ?"
  ret, err := db.Exec(sqlStr, 39, 3)
  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, 3)
  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)
}

综合实践

下述代码简单实现利用Go语言操作MySQL数据库的增、删、改、查等。

数据库连接封装：weiyigeek.top/studygo/Day09/MySQL/mypkg

// 自定义mypkg包 initdb.go
package mypkg
import (
  "database/sql"
  "fmt"

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

// 定义一个mysqlObj结构体
type MysqlObj struct {
  Mysql_host             string
  Mysql_port             uint16
  Mysql_user, Mysql_pass string
  Database               string
  Db                     *sql.DB
}

// 定一个Person结构体
type Person struct {
  Uid  int
  Name string
  Age  int
}

// 定义一个初始化数据库的函数
func (conn *MysqlObj) InitDB() (err error) {

  // DSN(Data Source Name) 数据库连接字符串
  // MySQL 5.7.X 与 MySQL 8.x 都是支持的
  dsn := fmt.Sprintf("%s:%s@tcp(%s:%d)/%s?charset=utf8mb4&parseTime=True", conn.Mysql_user, conn.Mysql_pass, conn.Mysql_host, conn.Mysql_port, conn.Database)

  // 注册第三方mysql驱动到sql中，此处并不会校验账号密码是否正确，此处赋值给全局变量db。
  conn.Db, err = sql.Open("mysql", dsn)
  if err != nil {
    fmt.Printf("DSN : %s Format failed\n%v \n", dsn, err)
    return err
  }

  // 尝试与数据库建立连接（校验DSN是否正确）
  err = conn.Db.Ping()
  if err != nil {
    fmt.Printf("Connection %s Failed,\n%v \n", dsn, err)
    return err
  }

  // 设置与数据库建立连接的最大数目
  conn.Db.SetMaxOpenConns(1024)

  // 设置连接池中的最大闲置连接数
  conn.Db.SetMaxIdleConns(0) // 不会保留闲置

  return nil
}

实践 main 入口函数:

package main
import (
  "database/sql"
  "fmt"
  db "weiyigeek.top/studygo/Day09/MySQL/mypkg"
)

// 单结果语句查询函数示例
func queryPersonOne(conn *sql.DB, Uid int) (res db.Person) {
  // 1.单条SQL语句
  sqlStr := `select Uid,name,age from test.user where Uid=?;`
  // 2.执行SQL语句并返回一条结果
  rowObj := conn.QueryRow(sqlStr, Uid)
  // 3.必须对rowObj调用Scan方法，因为查询后我们需要释放数据库连接对象，而它调用后会自动释放。
  rowObj.Scan(&res.Uid, &res.Name, &res.Age)
  // 4.返回一个person对象
  return res
}

// 多结果语句查询函数示例
func queryPersonMore(conn *sql.DB, id int) {
  // 1.SQL 语句
  sqlStr := `select Uid,name,age from test.user where Uid > ?;`
  // 2.执行 SQL
  rows, err := conn.Query(sqlStr, id)
  if err != nil {
    fmt.Printf("Exec %s query failed！,err : %v \n", sqlStr, err)
    return
  }
  // 3.调用结束后关闭rows，释放数据库连接资源
  defer rows.Close()
  // 4.循环读取结果集中的数据
  for rows.Next() {
    var u db.Person
    err := rows.Scan(&u.Uid, &u.Name, &u.Age)
    if err != nil {
      fmt.Printf("scan failed, err:%v\n", err)
      return
    }
    fmt.Printf("Uid:%d name:%s age:%d\n", u.Uid, u.Name, u.Age)
  }
}

// 执行插入操作的函数示例
func insertPerson(conn *sql.DB) {
  // 1.SQL 语句
  sqlStr := `insert into user(name,age) values("Go语言",15)`
  // 2.执行插入语句
  ret, err := conn.Exec(sqlStr)
  if err != nil {
    fmt.Printf("Insert Failed, err : %v \n", err)
    return
  }
  // 3.插入数据操作，拿到插入数据库的id值
  Uid, err := ret.LastInsertId()
  if err != nil {
    fmt.Printf("Get Id Failed, err : %v \n", err)
    return
  }
  // 4.打印插入数据的id值
  fmt.Println("插入语句Uid值: ", Uid)
}

// 执行更新操作的函数示例
func updatePerson(conn *sql.DB, age, Uid int) {
  // 1.SQL 语句
  sqlStr := `update user set age=? where Uid = ?`
  // 2.执行插入语句
  ret, err := conn.Exec(sqlStr, age, Uid)
  if err != nil {
    fmt.Printf("Update Failed, err : %v \n", err)
    return
  }
  // 3.更新数据操作，获取到受影响的行数
  count, err := ret.RowsAffected()
  if err != nil {
    fmt.Printf("Get Id Failed, err : %v \n", err)
    return
  }
  // 4.打印数据影响的行数
  fmt.Println("更新数据影响的行数: ", count)
}

// 执行删除数据的操作函数示例
func deletePerson(conn *sql.DB, Uid int) {
  // 1.SQL 语句
  sqlStr := `delete from user where Uid > ?`
  // 2.执行删除的语句
  ret, err := conn.Exec(sqlStr, Uid)
  if err != nil {
    fmt.Printf("Delete Failed, err : %v \n", err)
    return
  }
  // 3.删除数据操作，获取到受影响的行数
  count, err := ret.RowsAffected()
  if err != nil {
    fmt.Printf("Get Id Failed, err : %v \n", err)
    return
  }
  // 4.打印删除数据的影响的行数:
  fmt.Println("删除数据影响的行数: ", count)
}

func main() {
  // 1.mysqlObj 结构体实例化
  conn := &db.MysqlObj{
    Mysql_host: "10.20.172.248",
    Mysql_port: 3306,
    Mysql_user: "root",
    Mysql_pass: "weiyigeek.top",
    Database:   "test",
  }
  // 2.初始化数据库
  err := conn.InitDB()
  if err != nil {
    panic(err)
  } else {
    fmt.Println("数据库初始化连接成功!")
  }

  // 3.程序结束时关闭数据库连接
  defer conn.Db.Close()

  // 4.单行查询
  res := queryPersonOne(conn.Db, 1)
  fmt.Printf("单行查询: %#v\n", res)

  // 5.多行查询
  fmt.Println("多行查询")
  queryPersonMore(conn.Db, 6)

  // 6.插入数据
  fmt.Println("插入数据")
  insertPerson(conn.Db)

  // 7.更新数据
  fmt.Println("更新数据")
  updatePerson(conn.Db, 16, 9)

  // 8.删除数据
  fmt.Println("删除数据")
  deletePerson(conn.Db, 10)
}

执行结果&数据库查询结果:

数据库初始化连接成功!
单行查询: main.person{uid:1, name:"WeiyiGeek", age:20}
多行查询
uid:7 name:C++ age:25
uid:8 name:Python age:26
uid:9 name:Golang age:15
插入数据
插入语句uid值:  10
更新数据
更新数据影响的行数:  1
删除数据
删除数据影响的行数:  1

WeiyiGeek.Go语言针对于MySQL数据库的CRUD操作

5.MySQL预处理

基础介绍

什么是预处理？

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

为什么要预处理？

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

SQL注入

描述: 非常注意, 我们任何时候都不应该自己拼接SQL语句, 可能会导致SQL注入的问题。

此处演示一个自行拼接SQL语句的示例，编写一个根据name字段查询user表的函数如下：

// 可被 sql 注入示例
func sqlInjectDemo(name string) {
  var u user
  sqlStr := fmt.Sprintf("select id, name, age from user where name='%s'", name)  // 关键点
  fmt.Printf("SQL:%s\n", sqlStr)
  err := db.QueryRow(sqlStr).Scan(&u.id, &u.name, &u.age)
  if err != nil {
    fmt.Printf("exec failed, err:%v\n", err)
    return
  }
  fmt.Printf("user:%#v\n", u)
}

当name变量输入以下字符串时便会引发SQL注入问题:

sqlInjectDemo("xxx' or 1=1#")
sqlInjectDemo("xxx' union select * from user #")
sqlInjectDemo("xxx' and (select count(*) from user) <10 #")

示例演示

Go是如何实现MySQL预处理 描述: database/sql 中使用下面的Prepare方法来实现预处理操作。函数原型: func (db *DB) Prepare(query string) (*Stmt, error) 函数说明: Prepare方法会先将sql语句发送给MySQL服务端，返回一个准备好的状态用于之后的查询和命令。返回值可以同时执行多个查询和命令。

示例演示: 描述: 此处引用上面封装的结构体成员以及方法,进行数据库的初始化操作。

package main

import (
  "database/sql"
  "fmt"

  db "weiyigeek.top/studygo/Day09/MySQL/mypkg"
)

// ## 预处理查询示例函数
func prepareQuery(conn *sql.DB, id int) {
  // SQL语句
  sqlStr := "select uid,name,age from user where uid > ?;"
  // 预处理
  stmt, err := conn.Prepare(sqlStr)
  if err != nil {
    fmt.Printf("prepare failed, err:%v\n", err)
    return
  }
  // 释放预处理
  defer stmt.Close()

  // 查询 uid 为 id 以上的数据
  rows, err := stmt.Query(id)
  if err != nil {
    fmt.Printf("query failed, err:%v\n", err)
    return
  }
  // 释放 rows
  defer rows.Close()

  // 循环读取结果集中的数据，此处利用map来装我们遍历获取到的数据，注意内存申请。
  res := make(map[int]db.Person, 5)
  for rows.Next() {
    var u db.Person
    err := rows.Scan(&u.Uid, &u.Name, &u.Age)
    if err != nil {
      fmt.Printf("scan failed, err:%v\n", err)
      return
    }
    _, ok := res[u.Uid]
    if !ok {
      res[u.Uid] = u
    }
    fmt.Printf("id:%d name:%s age:%d\n", u.Uid, u.Name, u.Age)
  }
  fmt.Printf("%#v\n", res)
}


// ## 插入、更新和删除操作的预处理十分类似，这里以插入操作的预处理为例：
func prepareInsert(conn *sql.DB) {
  // 插入的SQL语句
  sqlStr := "insert into user(name,age) values (?,?)"
  // 进行SQL语句的预处理
  stmt, err := conn.Prepare(sqlStr)
  if err != nil {
    fmt.Printf("prepare failed, err:%v\n", err)
    return
  }
  // 释放 stmt 资源
  defer stmt.Close()

  // 执行预处理后的SQL (可以多次执行)
  _, err = stmt.Exec("WeiyiGeek", 18)
  if err != nil {
    fmt.Printf("insert failed, err:%v\n", err)
    return
  }
  // 执行预处理后的SQL
  _, err = stmt.Exec("插入示例", 82)
  if err != nil {
    fmt.Printf("insert failed, err:%v\n", err)
    return
  }
  // 插入成功会显示如下
  fmt.Println("insert success.")
}

// 入口函数
func main() {
  // MysqlObj 结构体初始化
  conn := &db.MysqlObj{
    Mysql_host: "10.20.172.248",
    Mysql_port: 3306,
    Mysql_user: "root",
    Mysql_pass: "weiyigeek.top",
    Database:   "test",
  }
  // 数据库初始化
  err := conn.InitDB()
  if err != nil {
    panic(err)
  } else {
    fmt.Println("[INFO] - 已成功连接到数据库!")
  }
  // 关闭数据库对象
  defer conn.Db.Close()

  // 预处理查询
  fmt.Println("预处理查询示例函数 prepareQuery:")
  prepareQuery(conn.Db, 5)

  // 预处理插入
  fmt.Println("预处理插入示例函数 prepareInsert:")
  prepareInsert(conn.Db)
}

执行结果:

[INFO] - 已成功连接到数据库!

-- 预处理查询示例函数 prepareQuery:
id:6 name:C age:25
id:7 name:C++ age:25
id:8 name:Python age:26
id:9 name:Golang age:16
id:12 name:WeiyiGeek age:18
id:13 name:插入示例 age:82
map[int]mypkg.Person{6:mypkg.Person{Uid:6, Name:"C", Age:25}, 7:mypkg.Person{Uid:7, Name:"C++", Age:25}, 8:mypkg.Person{Uid:8, Name:"Python", Age:26}, 9:mypkg.Person{Uid:9, Name:"Golang", Age:16}, 12:mypkg.Person{Uid:12, Name:"WeiyiGeek", Age:18}, 13:mypkg.Person{Uid:13, Name:"插入示例", Age:18}}

-- 预处理插入示例函数 prepareInsert:
insert success.

Tips：不同的数据库中，SQL语句使用的占位符语法不尽相同，例如下表所示。

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

6.MySQL事务处理

什么是事务？

事务：一个最小的不可再分的工作单元；通常一个事务对应一个完整的业务(例如银行账户转账业务，该业务就是一个最小的工作单元)，同时这个完整的业务需要执行多次的DML(insert、update、delete)语句共同联合完成。A转账给B，这里面就需要执行两次update操作。

在MySQL中只有使用了Innodb数据库引擎的数据库或表才支持事务, 事务处理可以用来维护数据库的完整性，保证成批的SQL语句要么全部执行，要么全部不执行。

事务特性复习 ACID

描述: 通常事务必须满足4个条件（ACID）：原子性（Atomicity，或称不可分割性）、一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）。

原子性: 一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
一致性: 在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
隔离性: 数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
持久性: 事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

事务方法原型 描述：Go语言中使用以下三个方法实现MySQL中的事务操作。

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

实践示例 描述: 下面的代码演示了一个简单的事务操作，该事物操作能够确保两次更新操作要么同时成功要么同时失败，不会存在中间状态。例如: A 转账给 B 50 RMB,即从A账号余额-50,B账号余额+50。

数据库表创建:

-- 测试表
create table `money` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(20) DEFAULT '',
  `balance` INT(16) DEFAULT '0',
  PRIMARY KEY(`id`)
)ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

-- 测试数据
insert into `test`.`money`(`name`,`balance`) values("WeiyiGeek",1200);
insert into `test`.`money`(`name`,`balance`) values("辛勤的小蜜蜂",3650);

-- 查看插入的测试数据
SELECT * from money;
1	WeiyiGeek	1200
2	辛勤的小蜜蜂	3650

示例代码:

package main
import (
  "database/sql"
  "fmt"

  "weiyigeek.top/studygo/Day09/MySQL/mypkg"
)

// ## 事务操作示例
func transactionDemo(conn *sql.DB, money int) {
  // 开启事务
  tx, err := conn.Begin()
  if err != nil {
    if tx != nil {
      tx.Rollback() // 回滚
    }
    fmt.Printf("begin trans failed, err:%v\n", err)
    return
  }

  // (1) A 用户转账 50 给 B 则 - 50
  sqlStr1 := "UPDATE `money` SET balance=balance-? WHERE id=?;"
  ret1, err := tx.Exec(sqlStr1, money, 1)
  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
  }

  // B 用户接收到 A 转账的 50 给 则 + 50
  sqlStr2 := "UPDATE `money` SET balance=balance+? WHERE id=?;"
  ret2, err := tx.Exec(sqlStr2, money, 2)
  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("[INFO] - 事务完成了 ，exec trans success!")
}

func main() {
  // (1) MysqlObj 结构体初始化
  conn := &mypkg.MysqlObj{
    Mysql_host: "10.20.172.248",
    Mysql_port: 3306,
    Mysql_user: "root",
    Mysql_pass: "weiyigeek.top",
    Database:   "test",
  }

  // (2) 数据库初始化
  err := conn.InitDB()
  if err != nil {
    panic(err)
  } else {
    fmt.Println("[INFO] - 已成功连接到数据库!")
  }
  // 关闭数据库对象
  defer conn.Db.Close()

  // (3) 简单的事务操作示例
  transactionDemo(conn.Db, 50)
}

执行结果:

[INFO] - 已成功连接到数据库!
事务处理影响行数判断是否修改成功:  1 1
事务正在提交啦...
[INFO] - 事务完成了 ，exec trans success!

# 可以看到用户的在数据库中金额变化
1	WeiyiGeek	1150
2	辛勤的小蜜蜂	3700

0x01 Go语言编程快速入门之第三方sqlx库操作MySQL数据库

描述: 在项目中我们通常可能会使用database/sql(原生库)连接MySQL数据库,而我们可以采用sqlx来替代它, 它可以简化代码量、从而使查询更加方便。

你可以认为sqlx是Go语言内置database/sql的超集，它在优秀的内置database/sql基础上提供了一组扩展。例如: 常用来查询的 Get(dest interface{}, ...) error 和 Select(dest interface{}, ...) error 外还有很多其他强大的功能。

本文借助使用sqlx实现批量插入数据的例子，介绍了sqlx中可能被你忽视了的sqlx.In和DB.NamedExec方法。

第三方sqlx库主页: http://jmoiron.github.io/sqlx/

sqlx 安装&语法

描述: 在shell或者cmd终端中执行如下命令进行sqlx的安装:

go get github.com/jmoiron/sqlx

语法原型:

func (db *DB) Get(dest interface{}, query string, args ...interface{}) error: 执行SQL并绑定单行结果查询到指定类型变量中, 占位符参数都将替换为提供的参数，如果结果集为空则返回错误。
func (db *DB) Select(dest interface{}, query string, args ...interface{}) error : 执行SQL并绑定多行结果查询到指定类型变量中。
func (db *DB) Exec(query string, args ...interface{}) (Result, error): Exec 执行查询时不返回任何行但可以获取影响的行数, 支持插入、更新、删除等SQL语句

sqlx 数据库初始化

描述: 我们可以利用下述示例看到sqlx与sql之间的小小区别.

// weiyigeek.top/studygo/Day09/MySQL/mypkg/initsqlx.go
package mypkg
import (
  "fmt"

  "github.com/jmoiron/sqlx"
)

// 定义一个MysqlObj结构体
type SqlObj struct {
  Mysql_host             string
  Mysql_port             uint16
  Mysql_user, Mysql_pass string
  Database               string
  DB                     *sqlx.DB
}

// 定一个Person结构体
type User struct {
  Uid  int
  Name string
  Age  int
}

func (conn *SqlObj) InitDB() (err error) {
  // DSN(Data Source Name) 数据库连接字符串
  dsn := fmt.Sprintf("%s:%s@tcp(%s:%d)/%s?charset=utf8mb4&parseTime=True", conn.Mysql_user, conn.Mysql_pass, conn.Mysql_host, conn.Mysql_port, conn.Database)
  // 注册第三方mysql驱动到sqlx中并连接到dsn数据源设定的数据库中(与database/sql不同点，代码更加精简)
  conn.DB, err = sqlx.Connect("mysql", dsn)
  if err != nil {
    fmt.Printf("Connect %s DB Failed\n%v \n", dsn, err)
    return err
  }
  // 设置与数据库建立连接的最大数目
  conn.DB.SetMaxOpenConns(1024)
  // 设置连接池中的最大闲置连接数
  conn.DB.SetMaxIdleConns(10)
  return nil
}

sqlx CRUD操作

描述: 在测试使用sqlx针对MySQL数据库进行CRUD操作时,我们需要准备 库表准备

-- 建库建表
CREATE DATABASE test;
USE test;
CREATE TABLE `user` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(20) DEFAULT '',
  `age` INT(11) DEFAULT '0',
  PRIMARY KEY(`id`)
)ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

-- 测试数据插入
INSERT INTO `test`.`user`(`uid`, `name`, `age`) VALUES (1, 'WeiyiGeek', 20);
INSERT INTO `test`.`user`(`uid`, `name`, `age`) VALUES (2, 'Elastic', 18);
INSERT INTO `test`.`user`(`uid`, `name`, `age`) VALUES (3, 'Logstash', 20);
INSERT INTO `test`.`user`(`uid`, `name`, `age`) VALUES (4, 'Beats', 10);
INSERT INTO `test`.`user`(`uid`, `name`, `age`) VALUES (5, 'Kibana', 19);
INSERT INTO `test`.`user`(`uid`, `name`, `age`) VALUES (6, 'C', 25);
INSERT INTO `test`.`user`(`uid`, `name`, `age`) VALUES (7, 'C++', 25);
INSERT INTO `test`.`user`(`uid`, `name`, `age`) VALUES (8, 'Python', 26);

单行查询结果

// ## 查询显示单行数据示例代码
func queryRow(db *sqlx.DB) {
  // User 结构体类型声明
  var u mypkg.User
  sqlStr := "SELECT uid,name,age FROM user WHERE uid=?"
  // 执行查询语句并通过反射reflect将查询结果进行一一绑定,返回单行数据
  err := db.Get(&u, sqlStr, 1)
  if err != nil {
    fmt.Printf("get failed, err:%v\n", err)
    return
  }
  fmt.Printf("id:%d name:%s age:%d\n", u.Uid, u.Name, u.Age)
}

多行查询结果

// ## 查询显示多行数据示例代码
func queryMultiRow(db *sqlx.DB) {
  // User 结构体类型数组声明
  var u []mypkg.User
  sqlStr := "select uid, name, age from user where uid > ?"

  // 执行多行数据结果查询
  err := db.Select(&u, sqlStr, 8)
  if err != nil {
    fmt.Printf("query failed, err:%v\n", err)
    return
  }
  fmt.Printf("users:%#v\n", u)
}

插入数据示例

// 插入数据方法示例
func insertRow(db *sqlx.DB) {
  sqlStr := "insert into user(name, age) values (?,?)"
  // EXEC 方法执行的SQL语句包括 插入/更新和删除
  ret, err := db.Exec(sqlStr, "我爱学Go", 19)
  if err != nil {
    fmt.Printf("insert failed, err:%v\n", err)
    return
  }
  theID, err := ret.LastInsertId() // 新插入数据的id
  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 updateRow(db *sqlx.DB) {
  sqlStr := "update user set age=? where uid = ?"
  ret, err := db.Exec(sqlStr, 39, 8)
  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 deleteRow(db *sqlx.DB) {
  sqlStr := "delete from user where uid = ?"
  ret, err := db.Exec(sqlStr, 16)
  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)
}

主函数数据库初始化和调用示例

func main() {
  // 1.sqlx结构体初始化
  conn := &mypkg.SqlObj{
    Mysql_host: "10.20.172.248",
    Mysql_port: 3306,
    Mysql_user: "root",
    Mysql_pass: "weiyigeek.top",
    Database:   "test",
  }

  // 2.连接数据库初始化操作
  err := conn.InitDB()
  if err != nil {
    panic(err)
  } else {
    fmt.Println("[INFO] - 数据库已连接成功！")
  }

  // 3.关闭sqlx.DB数据连接对象(资源释放)
  defer conn.DB.Close()

  // 4.单行数据查询
  fmt.Println("单行数据查询结果：")
  queryRow(conn.DB)

  // 5.多行数据查询
  fmt.Println("多行数据查询结果：")
  queryMultiRow(conn.DB)

  // 6.插入数据
  fmt.Println("输入数据操作：")
  insertRow(conn.DB)

  // 7.更新数据
  fmt.Println("更新数据操作: ")
  updateRow(conn.DB)

  // 8.删除数据
  fmt.Println("删除数据操作: ")
  deleteRow(conn.DB)
}

执行结果:

[INFO] - 数据库已连接成功！
单行数据查询结果：
id:1 name:WeiyiGeek age:20
多行数据查询结果：
users:[]mypkg.User{mypkg.User{Uid:16, Name:"我爱学Go", Age:19}}
输入数据操作：
insert success, the id is 17.
更新数据操作: 
update success, affected rows:1
删除数据操作: 
delete success, affected rows:1

# 查看数据库中存储的数据结果
select uid,name,age from `test`.`user` 
1	WeiyiGeek	20
2	Elastic	18
3	Logstash	20
4	Beats	10
5	Kibana	19
6	C	25
7	C++	25
8	Python	39
17	我爱学Go	19

sqlx 绑定SQL语句到同名字段

我们可以使用 DB.NamedExec 和 DB.NamedQuery 方法用来绑定SQL语句与结构体或map中的同名字段，来分别进行操作字段里面的值或者将查询的结果赋予这些字段。

函数原型:

func (db *DB) NamedQuery(query string, arg interface{}) (*Rows, error) - 执行查询语句返回*rows类型的数据。
func (db *DB) NamedExec(query string, arg interface{}) (sql.Result, error)  - 执行操作语句单行sql.Result结果集。

示例演示:

// # NamedQuery
func namedQuery(){
  sqlStr := "SELECT * FROM user WHERE name=:name"
  // 1.使用 map 做命名查询
  rows, err := db.NamedQuery(sqlStr, map[string]interface{}{"name": "WeiyiGeek"})
  if err != nil {
    fmt.Printf("db.NamedQuery failed, err:%v\n", err)
    return
  }
  // 2.程序结束后释放资源给连接池
  defer rows.Close()

  // 3.遍历查询结果
  for rows.Next(){
    var u user
    err := rows.StructScan(&u)
    if err != nil {
      fmt.Printf("scan failed, err:%v\n", err)
      continue
    }
    fmt.Printf("user:%#v\n", u)
  }

  
  // 4.使用结构体命名查询，根据结构体字段的 db tag进行映射
  u := user{
    Name: "WeiyiGeek",
  }
  rows, err = db.NamedQuery(sqlStr, u)
  if err != nil {
    fmt.Printf("db.NamedQuery failed, err:%v\n", err)
    return
  }
  defer rows.Close()
  for rows.Next(){
    var u user
    err := rows.StructScan(&u)
    if err != nil {
      fmt.Printf("scan failed, err:%v\n", err)
      continue
    }
    fmt.Printf("user:%#v\n", u)
  }
}

// # NamedExec
func insertUserDemo()(err error){
  sqlStr := "INSERT INTO user (name,age) VALUES (:name,:age)"
  // 执行后不返回结果，但可以通过调用.RowsAffected()了解到影响的行数。
  _, err = db.NamedExec(sqlStr,
    map[string]interface{}{
      "name": "WeiyiGeek",
      "age": 28,
    })
  return
}

sqlx 事务处理

描述: 对于事务操作sqlx中为我们提供了db.Beginx()和tx.Exec()等方法。

函数原型:

// Beginx开始一个事务并返回一个*sqlx.Tx而不是*sql.Tx。
func (db *DB) Beginx() (*Tx, error)

测试库表:

// # 插入测试数据
INSERT INTO `test`.`money`(`id`, `name`, `balance`) VALUES (1, 'WeiyiGeek', 1100);
INSERT INTO `test`.`money`(`id`, `name`, `balance`) VALUES (2, '辛勤的小蜜蜂', 3800);

实际案例:

// 事务处理
func transactionSqlx(db *sqlx.DB) (err error) {
  // 开启事务
  tx, err := db.Beginx()
  if err != nil {
    fmt.Printf("begin trans failed, err:%v\n", err)
    return err
  }
  // 任务执行完毕后判断是否进行rollback
  defer func() {
    if p := recover(); p != nil {
      // 回滚操作并抛出异常
      tx.Rollback()
      panic(p)
    } else if err != nil {
      fmt.Println("rollback")
      // 当错误不为nil则进行回滚操作
      tx.Rollback() 
    } else {
      // 提交操作
      err = tx.Commit() 
      fmt.Println("commit")
    }
  }()

  // A 用户向 B用户转账 50 rmb
  sqlStr1 := "UPDATE `money` SET balance=balance-50 WHERE id=?"
  rs, err := tx.Exec(sqlStr1, 1) // 执行更新语句
  if err != nil {
    return err
  }
  n, err := rs.RowsAffected()   // 获得影响行数
  if err != nil {
    return err
  }
  if n != 1 {
    return errors.New("exec sqlStr1 failed")
  }

  // B 接收到 A用户的转账 50 rmb
  sqlStr2 := "UPDATE `money` SET balance=balance+50 WHERE id=?;"
  rs, err = tx.Exec(sqlStr2, 2)  // 执行更新语句
  if err != nil {
    return err
  }
  n, err = rs.RowsAffected()     // 获得影响行数
  if err != nil {
    return err
  }
  if n != 1 {
    return errors.New("exec sqlStr1 failed")
  }
  return err
}

执行后结果:

# 表示已提交事务
commit

# 数据库中可以看到A和B的balance都发送了变化。
1	WeiyiGeek	1050
2	辛勤的小蜜蜂	3850

sqlx 批量执行

描述: sqlx 为我们提供了一个非常方便的函数sqlx.In使得我们可以批量插入,使用的函数原型格式如下:

查询占位符-bindvars

描述: 例如此处查询占位符?在内部称为bindvars（查询占位符）它非常重要, 由于通过字符串格式 database/sql 不尝试对查询文本进行任何验证, 而利用查询占位符进行预处理，可以极大的防止SQL注入攻击。

MySQL 中使用?
PostgreSQL 中使用枚举的1、2
SQLite 中?和$1的语法都支持
Oracle 中使用:name的语法

Tips: 非常注意bindvars的一个常见误解是，它们用来在sql语句中插入值,它们其实仅用于参数化，不允许更改SQL语句的结构。

例如，使用bindvars尝试参数化列名或表名将不起作用：

// ？不能用来插入表名（做SQL语句中表名的占位符）
db.Query("SELECT * FROM ?", "mytable")

// ？也不能用来插入列名（做SQL语句中列名的占位符）
db.Query("SELECT ?, ? FROM people", "name", "location")

测试表库

-- 为了方便演示插入数据操作，这里创建一个user表，表结构如下：
CREATE TABLE `user` (
    `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(20) DEFAULT '',
    `age` INT(11) DEFAULT '0',
    PRIMARY KEY(`id`)
)ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

-- 结构体：定义一个user结构体，字段通过tag与数据库中user表的列一致。
type User struct {
  Name string `db:"name"`
  Age  int    `db:"age"`
}

自定义批量插入

描述: 通常拼接语句实现批量插入, 方法可能比较笨但是很好理解，就是有多少个User就拼接多少个(?, ?)。

// BatchInsertUsers 自行构造批量插入的语句
func BatchInsertUsers1(users []*User, db *sqlx.DB) error {
  // 1.存放 (?, ?) 的slice
  valueStrings := make([]string, 0, len(users))
  // 2.存放values的slice
  valueArgs := make([]interface{}, 0, len(users)*2)

  // 3.遍历users准备相关数据
  for _, u := range users {
    // 此处占位符要与插入值的个数对应
    valueStrings = append(valueStrings, "(?, ?)")
    valueArgs = append(valueArgs, u.Name)
    valueArgs = append(valueArgs, u.Age)
  }
  fmt.Printf("%#v\n%#v\n", valueStrings, valueArgs)

  // 4.自行拼接要执行的具体语句
  stmt := fmt.Sprintf("INSERT INTO user (name, age) VALUES %s",
    strings.Join(valueStrings, ","))
  fmt.Println(stmt)
  res, err := db.Exec(stmt, valueArgs...)
  if err != nil {
    fmt.Printf("Exec Batch Insert Users SQL Failed, %v\n", err)
    return err
  }

  // 5.输出插入成功的行函数(影响行)
  count, err := res.RowsAffected()
  if err != nil {
    fmt.Printf("Get Rows Affected Failed, %v\n", err)
    return err
  } else {
    fmt.Println("Insert Rows Affected ：", count)
    return nil
  }
}

func main() {
  // 1.sqlx结构体初始化
  conn := &mypkg.SqlObj{
    Mysql_host: "10.20.172.248",
    Mysql_port: 3306,
    Mysql_user: "root",
    Mysql_pass: "weiyigeek.top",
    Database:   "test",
  }

  // 2.连接数据库初始化操作
  err := conn.InitDB()
  if err != nil {
    panic(err)
  } else {
    fmt.Println("[INFO] - 数据库已连接成功！")
  }

  // 3.关闭sqlx.DB数据连接对象(资源释放)
  defer conn.DB.Close()

  // 4.自定义利用占位符进行批量插入
  userInsert := make([]*User, 0)
  userInsert = append(userInsert, &User{Name: "WeiyiGeek-20", Age: 20})
  userInsert = append(userInsert, &User{Name: "WeiyiGeek-21", Age: 21})
  userInsert = append(userInsert, &User{Name: "WeiyiGeek-22", Age: 22})
  err = BatchInsertUsers1(userInsert, conn.DB)
  if err != nil {
    panic(err)
  } else {
    fmt.Println("批量插入执行完毕!")
  }
}

执行结果:

[INFO] - 数据库已连接成功！
插入占位符: 
[]string{"(?, ?)", "(?, ?)", "(?, ?)"}
[]interface {}{"WeiyiGeek-20", 20, "WeiyiGeek-21", 21, "WeiyiGeek-22", 22}
INSERT INTO user (name, age) VALUES (?, ?),(?, ?),(?, ?)
Insert Rows Affected ： 3
批量插入执行完毕!

# 数据库中的结果
19	WeiyiGeek-20	20
20	WeiyiGeek-21	21
21	WeiyiGeek-22	22

使用 sqlx.In 实现批量插入

描述: 我们除了使用自定义的还可以使用sqlx.In方法与NamedExec方法实现批量插入，下面我们来实践sqlx.In的批量插入。

步骤01.插入实例前提是需要我们的结构体实现driver.Valuer接口(类似于Java中的重写), 此处将字段值包装为空接口进返回。

func (u User) Value() (driver.Value, error) {
  return []interface{}{u.Name, u.Age}, nil
}

步骤02.使用sqlx.In实现批量插入代码如下：

// BatchInsertUsers2 使用sqlx.In帮我们拼接语句和参数, 注意传入的参数是[]interface{}
func BatchInsertUsers2(users []interface{}, db *sqlx.DB) error {
  // 1.预处理SQL将参数与占位符绑定。
  query, args, _ := sqlx.In(
    "INSERT INTO user (name, age) VALUES (?), (?), (?)",
    users..., // 如果arg实现了 driver.Valuer, sqlx.In 会通过调用 Value()来展开它
  )
  fmt.Println(query) // 查看生成的querystring
  fmt.Println(args)  // 查看生成的args

  // 2.执行批量插入。
  res, err := db.Exec(query, args...)
  if err != nil {
    fmt.Printf("Exec Batch Insert Users SQL Failed, %v\n", err)
    return err
  }

  // 3.输出插入成功的行函数(影响行)。
  count, err := res.RowsAffected()
  if err != nil {
    fmt.Printf("Get Rows Affected Failed, %v\n", err)
    return err
  } else {
    fmt.Println("Insert Rows Affected ：", count)
    return nil
  }
}

// 在前面的Main 函数中调用执行如下代码块，我就不再重新写了。
......
// 主要功能：使用sqlx.in进行批量插入
userInsert := make([]interface{}, 0) // 空接口数组内存申请
userInsert = append(userInsert, &User{Name: "Gooo-20", Age: 20})
userInsert = append(userInsert, &User{Name: "R-21", Age: 21})
userInsert = append(userInsert, &User{Name: "Javascript-22", Age: 22})
err = BatchInsertUsers2(userInsert, conn.DB)
if err != nil {
  panic(err) // 在进行开发测试代码时使用，正式环境中请勿使用。
} else {
  fmt.Println("sqlx.In - 批量插入执行完毕!")
}

执行结果:

[INFO] - 数据库已连接成功！
INSERT INTO user (name, age) VALUES (?, ?), (?, ?), (?, ?)
[Go-20 20 R-21 21 Javascript-22 22]
Insert Rows Affected ： 3
sqlx.In - 批量插入执行完毕!

# 数据库插入结果查询
25	Go-20	20
26	R-21	21
27	Javascript-22	22

扩展学习之 sqlx.In 的查询示例 在sqlx查询语句中实现In查询和 FIND_IN_SET函数, 即实现 SELECT * FROM user WHERE id in (3, 2, 1); 和 SELECT * FROM user WHERE id in (3, 2, 1) ORDER BY FIND_IN_SET(id, '3,2,1');.

In查询: IN 操作符允许我们在 WHERE 子句中规定多个值

// QueryByIDs 根据给定ID查询
func QueryByIDs(ids []int)(users []User, err error){
  // 动态填充id
  query, args, err := sqlx.In("SELECT name, age FROM user WHERE id IN (?)", ids)
  if err != nil {
    return
  }
  // sqlx.In 返回带 `?` bindvar的查询语句, 我们使用Rebind()重新绑定它
  query = DB.Rebind(query)
    err = DB.Select(&users, query, args...)
    return
}

in 查询 和 FIND_IN_SET 查询: 查询id在给定id集合的数据并维持给定id集合的顺序。

// QueryAndOrderByIDs 按照指定id查询并维护顺序
func QueryAndOrderByIDs(ids []int)(users []User, err error){
  // 动态填充id
  strIDs := make([]string, 0, len(ids))
  for _, id := range ids {
    strIDs = append(strIDs, fmt.Sprintf("%d", id))
  }
  query, args, err := sqlx.In("SELECT name, age FROM user WHERE id IN (?) ORDER BY FIND_IN_SET(id, ?)", ids, strings.Join(strIDs, ","))
  if err != nil {
    return
  }
  // sqlx.In 返回带 `?` bindvar的查询语句, 我们使用Rebind()重新绑定它
  query = DB.Rebind(query)

  err = DB.Select(&users, query, args...)
  return
}

当然，在这个例子里面你也可以先使用IN查询，然后通过代码按给定的ids对查询结果进行排序。

Tips: 上述SQL执行结果以及IN 关键字与 FIND_IN_SET 区别如下:

-- # IN 关键字段
SELECT uid, name, age FROM user WHERE uid IN (1,2)
-- uid	name	age
-- 1	WeiyiGeek	20
-- 2	Elastic	18
SELECT uid, name, age FROM user WHERE 8 IN (uid)
-- uid	name	age
-- 8	Python	39
SELECT uid, name, age FROM user WHERE 1 IN (2,3,4)
-- 字段返回为空


-- # FIND_IN_SET 函数使用
SELECT FIND_IN_SET (5, '1,5,6,18') as 'Index'; 
-- Index
-- 2
SELECT uid, name, age FROM user WHERE FIND_IN_SET (1,uid);
-- uid	name	age
-- 1	WeiyiGeek	20

-- # 组合使用 : 安装顺序数组但将设定的FIND_IN_SET的uid那一行值放在末尾。
SELECT uid, name, age FROM user WHERE uid IN (1,5,6,18) ORDER BY FIND_IN_SET (1,uid);
-- uid	name	age
-- 5	Kibana	19
-- 6	C	25
-- 18	我爱学Go	19
-- 1	WeiyiGeek	20

使用 NamedExec 实现批量插入

注意：该功能需1.3.1版本以上并在1.3.1版本目前还有点问题sql语句最后不能有空格和, 不过当前版本 v1.3.4 中已解决;

使用NamedExec实现批量插入示例如下:

// BatchInsertUsers3 使用NamedExec实现批量插入函数
func BatchInsertUsers3(users []*User, db *sqlx.DB) error {
  // 1.SQL预处理以及执行批量插入
  res, err := db.NamedExec("INSERT INTO user (name, age) VALUES (:name, :age)", users)
  if err != nil {
    fmt.Printf("Exec Batch Insert Users SQL Failed, %v\n", err)
    return err
  }

  // 2.输出插入成功的行函数(影响行)。
  count, err := res.RowsAffected()
  if err != nil {
    fmt.Printf("Get Rows Affected Failed, %v\n", err)
    return err
  } else {
    fmt.Println("Insert Rows Affected ：", count)
    return nil
  }
}

// 在 Main 函数中执行执行如下代码片段进行使用 NamedExec 实现批量插入
userInsert := make([]*User, 0)
userInsert = append(userInsert, &User{Name: "小红", Age: 20})
userInsert = append(userInsert, &User{Name: "小南", Age: 21})
userInsert = append(userInsert, &User{Name: "小白", Age: 22})
err = BatchInsertUsers3(userInsert, conn.DB)
if err != nil {
  fmt.Printf("[Error] - %v\n", err)
} else {
  fmt.Println("NamedExec - 批量插入执行完毕!")
}

执行结果:

[INFO] - 数据库已连接成功！
Insert Rows Affected ： 3
NamedExec - 批量插入执行完毕!

# 数据库中插入的数据查看
28	小红	20
29	小南	21
30	小白	22

此处将上面三种方法综合起来试一下：

func main() {
  err := initDB()
  if err != nil {
    panic(err)
  }
  defer DB.Close()
  u1 := User{Name: "WeiyiGeek", Age: 18}
  u2 := User{Name: "weiy_", Age: 28}
  u3 := User{Name: "weiyi", Age: 38}
  
  // 方法1.User类型的指针数组
  users := []*User{&u1, &u2, &u3}
  err = BatchInsertUsers(users)
  if err != nil {
    fmt.Printf("BatchInsertUsers failed, err:%v\n", err)
  }

  // 方法2.空接口类型的数组
  users2 := []interface{}{u1, u2, u3}
  err = BatchInsertUsers2(users2)
  if err != nil {
    fmt.Printf("BatchInsertUsers2 failed, err:%v\n", err)
  }

  // 方法3.User类型的指针数组
  users3 := []*User{&u1, &u2, &u3}
  err = BatchInsertUsers3(users3)
  if err != nil {
    fmt.Printf("BatchInsertUsers3 failed, err:%v\n", err)
  }
}

本文参与腾讯云自媒体分享计划，分享自作者个人站点/博客。

原始发表：2020-04-27，如有侵权请联系 cloudcommunity@tencent.com 删除

sqlite