[大数据]Go语言操作MySql相关学习

假期关于mysql相关的操作学习，总结记录，以飨读者

Mysql是业届常用的关系型数据库，本文以go语言学习如何操作mysql数据库。

主要包括以下几个部分：

• Ping测试mysql
• mysql连接池相关参数配置
• CURD相关操作
• mysql超时操作
• mysql事务操作

完整代码参考我的github工程go语言操作mysql

零、环境准备及go工程创建

环境：centos、mysql8.0
首先保证mysql服务正常，可以使用sql相关命令进行连接测试，创建几条测试数据方便后续操作。

mysql -u你的名字 -p你的密码
 
 CREATE DATABASE sql_test;
 use sql_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 user (name , age) VALUES 
('张三', '18'),
('李四', '28'),
('翠花', '38');

select * from user;

在工作目录建立go工程，同时使用go mod进行初始化

mkdir go-sql-database-example
# you can name the module according to your directory
go mod init github.com/wsqyouth/tech_note/go-sql-database-example

Go语言sql包提供了操作SQL的接口，但并不提供具体数据库驱动，因此我们须引入第三方的驱动包。

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

至此，我们拥有了测试数据、安装了第三方驱动包、同时创建了go工程，让我们开始吧！

一、Ping测试mysql

// file: main.go
package main

import (
	"context"
	"database/sql"
	"fmt"
	"log"
	"time"

	// we have to import the driver, but don't use it in our code
	// so we use the `_` symbol
	_ "github.com/go-sql-driver/mysql"
)

func main() {
	pingDemo()
}

func pingDemo() {
	// The `sql.Open` function opens a new `*sql.DB` instance. We specify the driver name
	// and the URI for our database.
	dsn := "你的名字:你的密码@tcp(127.0.0.1:3306)/sql_test?charset=utf8mb4&parseTime=True"
	db, err := sql.Open("mysql", dsn)
	if err != nil {
		log.Fatalf("could not connect to database: %v", err)
	}

	// To verify the connection to our database instance, we can call the `Ping`
	// method. If no error is returned, we can assume a successful connection
	if err := db.Ping(); err != nil {
		log.Fatalf("unable to reach database: %v", err)
	}
	fmt.Println("database is reachable")
	defer db.Close()
}

我们运行该文件，可以发现测试ok:

二、mysql连接池相关参数配置

为了方便测试后续CURD各功能函数，本着高内聚、低耦合的原则，我们定义一个全局变量db，用来保存连接数据库的句柄。将上面的示例代码拆分为独立的函数initDB，在程序启动时完成初始化，其他功能函数就可以直接使用全局变量了。
初始化连接函数

// 2.0 定义初始化数据库函数
func initDB() (err error) {
	dsn := "你的账号:你的密码@tcp(127.0.0.1:3306)/sql_test?charset=utf8mb4&parseTime=True"
	db, err = sql.Open("mysql", dsn) //注意使用全局对象进行赋值
	if err != nil {
		log.Fatalf("could not connect to database: %v", err)
	}
	if err := db.Ping(); err != nil {
		log.Fatalf("unable to reach database: %v", err)
	}
	// Maximum Idle Connections
	db.SetMaxIdleConns(5)
	// Maximum Open Connections
	db.SetMaxOpenConns(10)
	// Idle Connection Timeout
	db.SetConnMaxIdleTime(1 * time.Second)
	// Connection Lifetime
	db.SetConnMaxLifetime(30 * time.Second)
	fmt.Println("init succ")
	return nil
}

关于数据库的连接配置参考，这里我直接借鉴国外的一篇博客Using an SQL Database in Go进行摘录:

• Maximum Open Connections are the maximum number of parallel connections that can be made to the database at any time.
• Maximum Idle Connections are the maximum number of connections that can be inactive at any time. A connection is idle, if no queries are being executed on it. This can happen if the number of queries being executed are less than the current pool of connections can handle.
• Idle Connection Timeout is the maximum time for which any given connection can be idle. After this time had elapsed, the connection to the database will be closed.
  *** Connection Lifetime** is the maximum amount of time that a connection can be open (regardless of whether it’s idle or not).
  
  这几个参数是可以灵活设置的，但是总体上我们要考虑：
• 最好让一小部分连接处于空闲状态，以应对查询吞吐量的突然峰值
• 应该根据我们的数据库服务器和应用程序服务器的网络容量来设置打开连接的最大数量，其中较小的将是限制因素。

三、mysql相关CURD操作

其实就是CURD的封装，记得我们使用了上文提及的全局变量句柄db，代码如下：

// 2.1 查询单条数据
func queryRowDemo(id int) {
	sqlStr := "select id,name,age from user where id = ? limit 1"
	// `QueryRow` always returns a single row from the database
	row := db.QueryRow(sqlStr, id)

	var u user
	if err := row.Scan(&u.id, &u.name, &u.age); err != nil {
		log.Fatalf("could not scan row: %v", err)
	}
	fmt.Printf("user:%+v\n", u)
}

// 2.2 查询多条数据
func queryMultiRowDemo() {
	sqlStr := "select id,name,age from user where id > ?"
	// `QueryRow` always returns a single row from the database
	rows, err := db.Query(sqlStr, 1)
	if err != nil {
		log.Fatalf("could not execute query: %v", err)
	}
	//循环读取结果集数据
	users := []user{}
	for rows.Next() {
		var u user
		// create an instance of `user` and write the result of the current row into it
		if err := rows.Scan(&u.id, &u.name, &u.age); err != nil {
			log.Fatalf("could not scan row: %v", err)
		}
		users = append(users, u)
	}
	// print the length, and all the birds
	fmt.Printf("len:%v,users:%+v\n", len(users), users)
}

// 插入数据
func insertRowDemo() {
	newUser := user{
		name: "王五",
		age:  23,
	}
	sqlStr := "insert into user(name,age) values (?,?)"
	// the `Exec` method returns a `Result` type instead of a `Row`
	// we follow the same argument pattern to add query params
	result, err := db.Exec(sqlStr, newUser.name, newUser.age)
	if err != nil {
		log.Fatalf("could not execute query: %v", err)
	}

	theID, err := result.LastInsertId() //新插入的id
	if err != nil {
		log.Fatalf("get LastInsertId falid. err:%v", err)
	}
	fmt.Printf("insert succ. the id is %d\n", theID)

}

// 更新数据
func updateRowDemo() {
	newUser := user{
		name: "王五",
		age:  28,
	}
	sqlStr := "update user set age=? where name = ?"
	result, err := db.Exec(sqlStr, newUser.age, newUser.name)
	if err != nil {
		log.Fatalf("update falid. err:%v", err)
	}

	// the `Result` type has special methods like `RowsAffected` which returns the
	// total number of affected rows reported by the database
	// In this case, it will tell us the number of rows that were inserted using
	// the above query
	rowsAffected, err := result.RowsAffected()
	if err != nil {
		log.Fatalf("could not get affected rows: %v", err)
	}
	// we can log how many rows were update
	fmt.Println("update succ, affected rows:%v", rowsAffected)

}

// 删除数据
func deleteRowDemo() {
	newUser := user{
		name: "张三",
	}
	sqlStr := "delete from user where name = ?"
	result, err := db.Exec(sqlStr, newUser.name)
	if err != nil {
		log.Fatalf("update falid. err:%v", err)
	}

	// the `Result` type has special methods like `RowsAffected` which returns the
	// total number of affected rows reported by the database
	// In this case, it will tell us the number of rows that were inserted using
	// the above query
	rowsAffected, err := result.RowsAffected()
	if err != nil {
		log.Fatalf("could not get affected rows: %v", err)
	}
	// we can log how many rows were delete
	fmt.Println("delete succ, affected rows:%v", rowsAffected)
}

四、超时相关功能

sql的慢查询也是一个需要考虑的问题，如果某个请求的查询比较耗时，就会阻塞其他请求拿到连接，因此我们需要在连接sql时考虑到如果超时则取消功能。这里我们使用context进行测试：

//3.0 测试超时
func timeOutDemo() {
	// create a parent context
	ctx := context.Background()
	// create a context from the parent context with a 300ms timeout
	ctx, _ = context.WithTimeout(ctx, 300*time.Millisecond)
	// The context variable is passed to the `QueryContext` method as
	// the first argument
	// the provided number of seconds. We can use this to simulate a
	// slow query
	_, err := db.QueryContext(ctx, "select sleep(1)")
	if err != nil {
		log.Fatalf("could not execute query: %v", err)
	}
}

我们设置超时时间为300ms，但是这条sql语句需要1s才能执行完成，运行该demo预期输出：

五、mysql事务操作

事务的操作也是需要考虑的，要么一起成功、要么一起失败，因此这里的demo示例如下：

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

	sqlStr := "Update user set age = 34 where id = ?"
	result, err := tx.Exec(sqlStr, 2)
	if err != nil {
		if tx != nil {
			tx.Rollback() //回滚
		}
		fmt.Printf("update trans failed. err:%v\n", err)
		return
	}
	rowsAffected, err := result.RowsAffected()
	if err != nil {
		if tx != nil {
			tx.Rollback() //回滚
		}
		log.Fatalf("could not get affected rows: %v", err)
		return
	}
	fmt.Println(rowsAffected)

	if rowsAffected == 1 {
		fmt.Println("only one user affect. trans commit")
		tx.Commit() //提交事务
	} else {
		fmt.Println("affect num err. trans rollback")
		tx.Rollback()
	}

	fmt.Println("exec trans end")
}

六、展望

至此，Go语言操作mysql常见的操作就算测试完了，但是这只是入门，站在这里，你可能会考虑一下问题：

• sql预处理和sql注入问题
• sql连接池监控问题
• 本文使用的是原生sql处理，是否有orm框架来解决新增字段扩展性问题
• 事务、分布式事务又将如何开展

文章参考

本文工程重点参考文章，特此感谢
国外谷歌工程师的文章：该文章是go语言操作pgsql，但是工程风格及讲解一流，引人入胜
Using an SQL Database in Go
A Guide On SQL Database Transactions In Go
李文周博客-go操作mysql