vlambda
2022-03-18

数据操作——go操作MySQL

Go操作数据库

github源码：https://github.com/go-sql-driver/mysql

注册驱动

终端执行命令

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

发现报错

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

go get github.com/go-sql-driver/mysql: module github.com/go-sql-driver/mysql: Get "https://proxy.golang.org/github.com/go-sql-driver/mysql/@v/list": dial tcp
 172.217.163.49:443: connectex: A connection attempt failed because the connected party did not properly respond after a period of time, or established conne
ction failed because connected host has failed to respond.

墙的问题，配置下代理就行，可以用七牛的代理，配置如下

go env -w GOPROXY=https://goproxy.cn

设置完成之后可以查看是否设置成功

go env

驱动下载成功

测试是否连接成功

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

func main() {
     db, err := sql.Open("mysql", "root:333@tcp(127.0.0.1:3306)/bjpowernode")
     if err != nil {
          log.Fatal(err)
    }
     defer db.Close()
}

说明

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

sql.Open的第一个参数是，驱动程序名称。这是驱动程序用来在database/sql中进行自身注册的字符串，通常与包名称相同，以避免混淆。例如，mysql 用于 github.com/go-sql-driver/mysql 。某些驱动程序不遵循约定，而是使用数据库名称，例如，用于 github.com/mattn/go-sqlite3 的sqlite3 和用于 github.com/lib/pq 的 postgres。
第二个参数是，特定于驱动程序的语法，它告诉驱动程序如何访问基础数据存储。在此示例中，我们将连接到本地 MySQL 服务器实例内的「hello」数据库。
您(几乎)应该始终检查并处理从所有 database/sql 操作返回的错误。我们将在以后讨论一些特殊情况，以解决此问题。
如果 sql.DB 的生存期不应超出该功能的范围，则推迟 db.Close() 是习惯的做法。

sql.Open() 不会建立与数据库的任何连接，也不会验证驱动程序的连接参数。而是它只准备数据库抽象以备后用。与基础数据存储区的第一个实际连接将延迟到第一次需要时建立。如果要立即检查数据库是否可用和可访问(例如，检查是否可以建立网络连接并登录)，请使用 db.Ping() 进行操作，并记录检查错误

err4ping := db.Ping()
if err4ping != nil {
    fmt.Println(err4ping)
}

虽然在完成后对 Close() 数据库很习惯，但是 sql.DB 对象被设计为长期存在。不要经常使用 Open() 和 Close()。而是为每个需要访问的不同数据存储创建一个 sql.DB 对象，并保留该对象直到程序完成对该数据存储的访问为止。根据需要传递它，或以某种方式使其在全局范围内可用，但保持打开状态。不要通过短期函数来 Open() 和 Close()。而是将 sql.DB 传递给该短期函数作为参数。

如果不将 sql.DB 视为长期对象，则可能会遇到诸如重用和连接共享不良，可用网络资源用尽或由于很多原因而导致的偶发性故障等问题 TIME_WAIT 状态中剩余的 TCP 连接数。这些问题表明您没有使用设计的 database/sql。

处理结果集

查询

有几种常用的操作可以从数据库中检索结果。

执行返回一条数据的查询操作。
准备要重复使用的语句，在多次执行该语句后进行销毁。
一次性的执行语句，并且不打算将其重复使用。
执行返回一条数据的查询，这种特殊情况有一个快捷方式。

Go 的 database/sql 包中函数名称很重要。如果一个函数名字包含 Query, 那么该函数旨在向数据库发出查询问题, 并且即使它为空，也将返回一组行。不返回行的语句不应该使用 Query 函数; 而应使用 Exec()

func query() {
     var (
          username string
          password string
    )
     // Query()将请求发送给mysql查询数据
     rows, err := db.Query("select username, password from t_user where id in (?,?) ", 1, 5)
     if err != nil {
          log.Fatal(err)
    }
     // 关闭资源
    defer rows.Close()
     // 遍历rows结果集
     for rows.Next() {
          // Scan()遍历并赋值给变量
          err := rows.Scan(&username, &password)
          if err != nil {
               log.Fatal(err)
          }    
          log.Println(username, password)
    }
     // 遍历后，检查是否出错
     err = rows.Err()
     if err != nil {
          log.Fatal(err)
    }
}

注意

始终需要检查for rows.Next()循环末尾是否有错，如果循环中存在错误，需要知道它
要存在打开的结果集(由 rows 表示)，底层连接就很忙，不能用于任何其他查询。这意味着它在连接池中不可用。如果使用 rows.Next() 迭代所有行，最终将读取最后一行，并且rows.Next() 将遇到内部 EOF 错误并为您调用 rows.Close()。但是，如果出于某种原因您退出该循环——提前返回，等等情况——那么 rows 不会关闭，连接保持打开。(不过，如果rows.Next() 由于错误返回 false，那么它会自动关闭)。这是耗尽资源的一种简单方式
始终 defer rows.Close()，循环内，不要使用defer，延迟语句不会在循环中执行，会导致内存溢出，如果在循环中重复使用查询和使用结果集，应该在每个处理完后显式调用row.Close()

Query()函数分析

Query() 将返回一个 sql.Rows，它将保留数据库连接，直到 sql.Rows 关闭。由于可能存在未读数据 (例如，更多的数据行)，因此无法使用该连接。

如果不关闭sql.Rows，连接将永远不会被释放。垃圾收集器最终将为您关闭底层 net.Conn，但这可能需要很长时间。此外，database/sql 包会在连接池中继续跟踪连接，希望您在某个时候释放它，以便可以再次使用该连接。因此，此反模式是耗尽资源 (例如，连接过多) 的好方法

Scan()怎么工作

当遍历行并将其扫描到目标变量中时，Go 会在后台执行数据类型转换。它基于目标变量的类型。

预处理查询

通常，您应该始终对要多次使用的查询执行预处理。预处理查询的结果是一个预处理语句，该语句可以具有占位符(也称为绑定值)，用于执行该语句时将提供的参数。出于所有常见原因(例如，避免 SQL 注入攻击)，这比串联字符串好得多

func prepareQuery() {
     stmt, err := db.Prepare("select username, password from t_user where id = ? ")
     if err != nil {
          log.Fatal(nil)
    }
     defer stmt.Close()
     rows, err := stmt.Query(11)
     if err != nil {
          log.Fatal(err)
    }
     defer rows.Close()
     for rows.Next() {
          //..
    }
     if err = rows.Err(); err != nil {
          log.Fatal(err)
    }
}

更新

使用 Exec()，最好是使用预编译语句，来完成 INSERT，UPDATE，DELETE 或其他不返回行的语句。以下示例显示如何插入行并检查有关该操作的元数据：

错误处理函数

func logErr(err error) {
     if err != nil {
          log.Fatal(err)
    }
}

修改操作：

func insert() {
     stmt, err := db.Prepare("insert into t_user(username, password) VALUES (?, ?)")
     logErr(err)
     result, err := stmt.Exec("zhangsan", "77625")
     logErr(err)
     lastId, err := result.LastInsertId()
     logErr(err)
     rowCnt, err := result.RowsAffected()
     logErr(err)
     log.Printf("ID = %d, affected = %d\n", lastId, rowCnt)
}

执行该语句将产生一个 sql.Result，它提供对语句元数据的访问：最后插入的 ID 和受影响的行数

删除操作

func delete() {
     stmt, err := db.Prepare("delete FROM t_user where id = ?")
     logErr(err)
     result, err := stmt.Exec(19)
     logErr(err)
     lastId, err := result.LastInsertId()
     logErr(err)
     rowCnt, err := result.RowsAffected()
     logErr(err)
     log.Printf("ID = %d, affected = %d\n", lastId, rowCnt)
}

事务

Go中，事务本质上是一个保留与数据存储区连接的对象。它可以让您执行到目前为止所看到的所有操作，但可以保证它们将在同一连接上执行

可以通过调用 db.Begin() 开启事务，然后使用该函数生成的 Tx 对象上的 Commit() 或 Rollback() 方法来结束事务。在后台，Tx 从池中获得连接，并将其保留，以仅用于该事务。Tx 上的方法一对一映射到您可以在数据库本身上调用的方法，例如 Query() 等

func testTx() {
     tx, err := db.Begin()
     if err != nil {
          panic(err)
    }
     // 如果执行过程中都没有 commit和rollback那就由这个来收尾
     defer clearTransaction(tx)
     stmt, err := tx.Prepare("insert into t_user(username, password) VALUES (?, ?)")
     if err != nil {
          tx.Rollback()
          return
    }
    defer stmt.Close()
     result, err := stmt.Exec("王五", "123")
     if err != nil {
          tx.Rollback()
          return
    }
     affected, err := result.RowsAffected()
     if err != nil {
          tx.Rollback()
          return
    }    
     fmt.Println(affected)
     tx.Commit()
}

func clearTransaction(tx *sql.Tx) {
     err := tx.Rollback()
     if err != nil {
          return
     } else {
      fmt.Println(err)
    }
}

在事务内部进行操作时，应注意不要调用 db 变量。进行所有对您使用 db.Begin() 创建的 Tx 变量的调用。db 不在事务中，只有 Tx 对象在事务中。如果您进一步调用 db.Exec() 或类似方法，则这些调用将在事务范围之外发生在其他连接上

如果需要使用多个修改连接状态的语句，即使您本身不需要事务，也需要 Tx。例如：

创建临时表，仅对一个连接可见。
设置变量，例如 MySQL 的 SET @var:= somevalue 语法。
更改连接选项，例如字符集或超时。

如果您需要执行上述任何操作，则需要将活动绑定到单个连接，而 Go 中唯一的方法是使用 Tx。

预处理

MySQL预处理

普通SQL语句执行过程

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

预处理执行过程

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

为什么要预处理？

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

Go语言实现MySQL预处理

事务那里已经展示过了

sqlx

注册驱动

go get github.com/jmoiron/sqlx

sqlx带来的便利

通过反射获取struct的数据类型指针进行赋值，不需要逐个的传递参数

err := rows.Scan(&username, &password)

    import (
     "fmt"
     _ "github.com/go-sql-driver/mysql"
     "github.com/jmoiron/sqlx"
     "log"
)

var db *sqlx.DB

type User struct {
     // 注意这里的属性必须大写
     Id       int
     Username string
     Password string
}

func initDB() (err error) {
     db, err = sqlx.Connect("mysql", "root:333@tcp(127.0.0.1:3306)/spring5")
     if err != nil {
          return
    }
     // 最大连接数
     db.SetMaxOpenConns(10)
     //最大空闲连接数
     db.SetMaxOpenConns(5)
     return
}
func main() {
     err := initDB()
     if err != nil {
          log.Fatalln(err)
    }
     var user User
     err = db.Get(&user, "select id, username, password from t_user where id = 1")
     if err != nil {
          log.Fatalln(err)
    }
     fmt.Println(user)
}

查询多个结果

var users []User
err = db.Select(&users, "select id, username, password from t_user where id > ?", 5)
if err != nil {
    log.Fatalln(err)
}
for _, user := range users {
    fmt.Println(user)
}

vlambda博客
学习文章列表

数据操作——go操作MySQL

Go操作数据库

注册驱动

处理结果集

查询

预处理查询

更新

事务

预处理

MySQL预处理

Go语言实现MySQL预处理

sqlx

标签:

推荐阅读

相关文章

vlambda博客 学习文章列表

数据操作——go操作MySQL

Go操作数据库

注册驱动

处理结果集

查询

预处理查询

更新

事务

预处理

MySQL预处理

Go语言实现MySQL预处理

sqlx

标签:

推荐阅读

相关文章

vlambda博客
学习文章列表