【Go】用 Go 访问 MySQL

2023-01-28 20:47:21

用 Go 访问 MySQL

Go 语言的 database/sql 包提供了连接 SQL 数据库或类 SQL 数据库的泛用接口，但并不提供具体的数据库驱动程序，在使用它时，必须注入至少一个数据库驱动程序。

实现基本的 CRUD

1. 创建数据库和数据库表

通过 mysql -u root -p 命令进入数据库cmd，然后创建一个 go_mysql 数据库：
```
CREATE DATABASE go_mysql;
```
进入该数据库： USE go_mysql;

创建 user 表：

CREATE TABLE `user` {
	`uid` BIGINT(20) NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(20) DEFAULT '',
	`phone` VARCHAR(20) DEFAULT '',
	PRIMARY KEY(`uid`)
}ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

2. 下载 MySQL 的驱动程序

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

3. 使用 MySQL 驱动程序

直接导入依赖包就可以了：

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

在以上语句中，github.com/go-sql-driver/mysql 就是依赖包。因为没有直接使用该包中的对象，所以在导入包前面被架上了下划线。

database/sql 包中提供了 Open() 函数用来连接数据库，其定义如下：

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

连接示例：

package main

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

func main() {
	db, err := sql.Open("mysql",
		"<user>:<password>@tcp(127.0.0.1:3306)/hello")
	if err != nil {
		log.Fatal(err)
	}
	defer db.Close()
}

4. 初始化连接

在用 Open() 函数建立连接后，如果要检查数据源的名称是否合法，则可以调用 Ping 方法。返回的 DB 对象可以安全地被多个 goroutine 同时使用，并且它会维护自身的闲置连接池。这样 Open 函数只需调用一次，因为一般启动后很少关闭 DB 对象。用 Open 函数初始化连接的示例代码如下：

package main

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

var db *sql.DB

// 定义一个初始化数据库的函数
func initDB() (err error) {
	//连接数据库
	db, err = sql.Open("mysql", "root:a123456@tcp(127.0.0.1:3306)/go_mysql")
	if err != nil {
		return err
	}
	// 尝试与数据库建立连接（校验dsn是否正确）
	err = db.Ping()
	if err != nil {
		return err
	}
	return nil
}

func main() {
	err := initDB() // 调用输出化数据库的函数
	if err != nil {
		fmt.Printf("init db failed,err:%v\n", err)
		return
	}
}

其中，sql.DB是一个数据库的操作句柄，代表一个具有零到多个底层连接的连接池。它可以安全地被多个 goroutine同时使用。 database/sql包会自动创建和释放连接，也会维护一个闲置连接的连接池。

5. SQL 查询

（1）查

首先定义一个结构体用来存储数据库返回的数据：

type User struct {
	Uid   int
	Name  string
	Phone string
}

有两种查询方式：

QueryRow() 进行单行查询
Query() 进行多行查询

单行查询示例：

// 单行测试
func queryRow() {
    var u User
	// 非常重要：确保QueryRow之后调用Scan方法，否则持有的数据库链接不会被释放
	err := db.QueryRow("select uid,name,phone from `user` where uid=?", 1).Scan(&u.Uid, &u.Name, &u.Phone)
	if err != nil {
		fmt.Printf("scan failed, err:%v\n", err)
		return
	}
	fmt.Printf("uid:%d name:%s phone:%s\n", u.Uid, u.Name, u.Phone)
}

多行查询示例：

// 查询多条数据示例
func queryMultiRow() {
    var u User
	rows, err := db.Query("select uid,name,phone from `user` where uid > ?", 0)
	if err != nil {
		fmt.Printf("query failed, err:%v\n", err)
		return
	}
	// 关闭rows释放持有的数据库链接
	defer rows.Close()
	// 循环读取结果集中的数据
	for rows.Next() {
		err := rows.Scan(&u.Uid, &u.Name, &u.Phone)
		if err != nil {
			fmt.Printf("scan failed, err:%v\n", err)
			return
		}
		fmt.Printf("uid:%d name:%s phone:%s\n", u.Uid, u.Name, u.Phone)
	}
}

（2）插、改、删

使用 Exec() 方法

MySQL 预处理

什么是预处理

要了解预处理，需要首先了解普通SQL语句的执行过程：

客户端对SQL语句进行占位符替换，得到完整的SQL语句；
客户端发送完整的SQL语句到 MySQL 服务器端；
MySQL服务器端执行完整的SQL语句，并将结果返给客户端。

预处理的执行过程

把SQL语句分成两部分——命令部分与数据部分；
把命令部分发送给 MySQL 服务器端， MySQL 服务器端进行SQL预处理；
把数据部分发送给 MySQL 服务器端， MySQL 服务器端对SQL语句进行占位符替换；
MySQL 服务器端执行完整的 SQL 语句，并将结果返回给客户端

为什么要预处理？

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

Go 语言中 MySQL 的预处理

在Go语言中， Prepare()方法会将SQL语句发送给 MySQL服务器端，返回一个准备好的状态用于之后的查询和命令。返回值可以同时执行多个查询和命令。 Prepare() 方法的定义如下：

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

预处理的示例代码：

// 预处理查询示例
func prepareQuery() {
	stmt, err := db.Prepare("select uid,name,phone from `user` where uid > ?")
	if err != nil {
		fmt.Printf("prepare failed, err:%v\n", err)
		return
	}
	defer stmt.Close()
	rows, err := stmt.Query(0)
	if err != nil {
		fmt.Printf("query failed, err:%v\n", err)
		return
	}
	defer rows.Close()
	// 循环读取结果集中的数据
    var user User
	for rows.Next() {
		err := rows.Scan(&u.Uid, &u.Name, &u.Phone)
		if err != nil {
			fmt.Printf("scan failed, err:%v\n", err)
			return
		}
		fmt.Printf("uid:%d name:%s phone:%s\n", u.Uid, u.Name, u.Phone)
	}
}

用 Go 实现 MySQL 事务

1. 什么是事务

事务是一个最小的不可再分的工作单元。通常一个事务对应一个完整的业务(例如银行账户转账业务，该业务就是一个最小的工作单元),同时这个完整的业务需要执行多次 DML ( INSERT、 UPDATE、 DELETE等)语句，共同联合完成。例如，A转账给B,就需要执行两次 UPDATE操作。在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。 事务处理用来维护数据库的完整性，保证成批的SQL语句要么全部执行，要么全部不执行。

2. 事务的 ACID 属性

通常事务必须满足4个条件(ACID)：原子性(Atomicity,或称不可分割性)、一致性 ( Consistency)、隔离性( Isolation,又称独立性)、持久性( Durability)事务的 ACID 属性。

3. 事务的相关方法

Go 语言使用以下三个方法来实现 MySQL 中的事务操作：

Begin() 方法用于开始事务：
```
func (db *DB) Begin() (*Tx, error)
```
Commit() 方法用于提交事务：
```
func (tx *Tx) Commit() error
```
Rollback() 方法用于回滚事务：
```
func (tx *Tx) Rollback() error
```

以下代码演示了一个简单的事务操作，该事务操作能够确保两次更新操作要么同时成功，要么同时失败，不会有中间状态：

func transaction() {
	tx, err := db.Begin() // 开启事务
	if err != nil {
		if tx != nil {
			tx.Rollback() // 回滚
		}
		fmt.Printf("begin trans failed, err:%v\n", err)
		return
	}
	_, err = tx.Exec("update user set username='james' where uid=?", 1)
	if err != nil {
		tx.Rollback() // 回滚
		fmt.Printf("exec sql1 failed, err:%v\n", err)
		return
	}
	_, err = tx.Exec("update user set username='james' where uid=?", 3)
	if err != nil {
		tx.Rollback() // 回滚
		fmt.Printf("exec sql2 failed, err:%v\n", err)
		return
	}
	err = tx.Commit() // 提交事务
	if err != nil {
		tx.Rollback() // 回滚
		fmt.Printf("commit failed, err:%v\n", err)
		return
	}
	fmt.Println("exec transaction success!")
}

SQL 注入与防御

在编写 SQL 脚本时，尽量不要自己拼接 SQL 语句。

针对SQL注入问题，常见的防御措施有：

禁止将变量直接写入SQL语句。
对用户进行分级管理，严格控制用户的权限。
对用户输入进行检查，确保数据输入的安全性。在具体检查输入或提交的变量时，对单引号、双引号、冒号等字符进行转换或者过滤。
对数据库信息进行加密。

码农公寓