Go 学习笔记（55）— Go 标准库 sql （初始化数据库、插入、更新、删除数据库表、单行查询、多行查询、事务处理）

1. 标准库说明

Go 的标准库中是没有数据库驱动，只提供了驱动接口，有很多第三方实现了驱动，我们这里选择 go-sql-driver 这个实现是目前使用最多的。github 地址是：https://github.com/go-sql-driver/mysql

Go 标准库中的 SQL 安装包是在 $GOROOT/src/database/sql/ 目录下，如下图所示：

ubuntu@ubuntu:~$ ls /usr/local/go/src/database/sql/
convert.go       doc.txt              example_service_test.go  sql.go
convert_test.go  driver               example_test.go          sql_test.go
ctxutil.go       example_cli_test.go  fakedb_test.go
ubuntu@ubuntu:~$ 

2. 创建数据库

在进行数据库操作之前，我们先定义数据库表结构：

CREATE TABLE IF NOT EXISTS test_user (
  `id` INT(3) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL DEFAULT '',
  `age` smallint(3) unsigned NOT NULL DEFAULT '0',
  `gender` tinyint(1) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) 
ENGINE=InnoDB
DEFAULT CHARACTER SET = utf8;

3. 初始化数据库

1. 导入 go 自带的标准库 database/sql ；
2. 命令行安装三方库 go get github.com/go-sql-driver/mysql ；
3. 导入步骤 2 安装的 mysql ；
4. 打开数据库格式是 ⽤户名:密码@/数据库名称?编码⽅式（包含了数据库的用户名、密码、数据库主机，以及需要连接的数据库名等信息）；
5. 最后关闭数据库；

Go MySQL 驱动是 Go 标准库 database/sql/driver 驱动程序接口的实现，我们只需要导入驱动程序就可以完整地使用 database/sql 的 API 。

使用 mysql 作为驱动名称，有效的 DSN 作为数据源名称， 使用示例：

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

DSN := "user:password@/dbname"
db, err := sql.Open("mysql", DSN)

其中数据源名称 DSN 通用格式如下：

[username[:password]@][protocol[(address)]]/dbname[?param1=value1&...&paramN=valueN]

完整的格式为：

username:password@protocol(address)/dbname?param=value

除了 dbname 之外，其余所有值都是可选的，所以最小的 DSN 为：

/dbname

当然如果不选择一个 DSN ，那么可以设置 dbname 为空，不过这样没有意义。

完整代码示例如下：

package main

import (
	"database/sql"
	"fmt"

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

var DB *sql.DB

func initDB() error {
	var err error
	// 打开数据库格式
	dsn := "root:1234567@tcp(127.0.0.1:3306)/test?charset=utf8mb4"
	DB, err = sql.Open("mysql", dsn)
	if err != nil {
		return err
	}
	// defer DB.Close()
	return err

}

func main() {
	err := initDB()
	fmt.Println(err)
}

其中：

1. sql.Open 不会立即建立网络连接，只有下一次操作时才会进行连接；
2. sql.Open 返回的 sql.DB 对象是协程并发安全的，不需要我们自己去加解锁；
3. sql.DB 可作为长连接使用，不需要频繁 Open 、 Close 操作；

4. 更新删除

• sql.DB.Exec() 方法，可以对数据库进行新增、更新、删除操作，只是 SQL 语句不同而已；
• 返回的 Result 类型主要有，影响的行数 RowsAffected 和最后插入的 LastInsertId 自增 ID ；

// sql.DB.Exec()  方法，可以对数据库进行新增、更新、删除操作，只是 SQL 语句不同而已
func insertUpdateDelete() {

	insertSQL := "INSERT INTO `test_user`(`name`,`age`) VALUES(?,?)"
	// query 语句中的 value 值在 DB.Exec 中来填充
	result, err := DB.Exec(insertSQL, "王五", 25)
	if err != nil {
		fmt.Printf("insert failed, err: %v\n", err)
		return
	}
	// 最新的自增 ID
	id, err := result.LastInsertId()
	if err != nil {
		fmt.Printf("LastInsertId failed, err: %v\n", err)
		return
	}
	// 获取执行(影响)的行数
	//result.RowsAffected()
	fmt.Printf("last id=%d\n", id)
}

5. 单行查询

• 使用 sql.DB.QueryRow() 方法来执行查询语句；
• 使用 sql.Row.Scan() 方法来获取查询结果；

我们定义一个 User 的结构体来存放数据。

// 用于存放用户数据
type User struct {
	Id   int    `db:"id"`
	Name string `db:"name"`
	Age  int    `db:"age"`
}

func queryResult() {
	querySQL := "SELECT `id`,`name`,`age` FROM `test_user` WHERE `id`=?"
	// 执行查询语句
	row := DB.QueryRow(querySQL, 1)
    // 定义 user 为结构体 User 类型
	var user User
	// 此处获取结果的顺序一定要和 SELECT 语句取出的顺序一样
	err := row.Scan(&user.Id, &user.Name, &user.Age)
	if err != nil {
		fmt.Printf("query result failed, err: %v\n", err)
		return
	}
	fmt.Printf("query result: id=%d name=%s age=%d\n", user.Id, user.Name, user.Age)
}

需要说明的是，单行查询时下面查询的参数必须为 1，否则查询的多余结果将被丢弃。

row := DB.QueryRow(querySQL, 1)

6. 多行查询

多行查询相对单行查询来讲要复杂一些。

• sql.DB.Query() 用来执行要查询多行的 SQL 语句；
• sql.Rows.Next() 用来迭代查询下一个数据；
• sql.Rows.Scan() 用来读取每一行的值；
• sql.Rows.Close() 关闭查询；

func selectMultiRow(id int) {
	querySQL := "SELECT `id`,`name`,`age` FROM `test_user` WHERE `id`<?"
	// 执行多行查询，注意此处用到的是 Query，单行查询时用到的是 QueryRow
	rows, err := DB.Query(querySQL, id)
	if err != nil {
		fmt.Printf("querySQL failed, err: %v\n", err)
		return
	}
	defer rows.Close()

	var users = make([]User, 0, 5) // 创建一个容量为 5 个结构体类型的切片
	for rows.Next() {
		var user User
		// 获取的顺序一定要和 SELECT 语句取出的顺序一样
		err := rows.Scan(&user.Id, &user.Name, &user.Age)
		if err != nil {
			fmt.Printf("Next Scan failed, err: %v\n", err)
			return
		}
		users = append(users, user) // 将查询到的结果添加到切片元素中去
	}
	fmt.Printf("selectMultiRow: %v\n", users)
}

7. 事务处理

1. Begin 开始事务；
2. Commit() 提交事务；
3. Rollback() 回退事务；

我们这例子就是修改两条数据：

func Trans(){
    // 开启事务
    conn, err := DB.Begin()
    if err != nil {
        if conn != nil {
            // 出错回滚 事务
            conn.Rollback()
        }
        fmt.Printf("begin faile, err: %v\n", err)
        return
    }
    query := "UPDATE `user` SET `age`=`age`+1 WHERE `id`=?"
    _, err = conn.Exec(query, 1)
    if err != nil {
        conn.Rollback()
        fmt.Printf("exec sql: %s failed, err: %v\n", query, err)
        return
    }
    query = "UPDATE `user` SET `name`='-name-' WHERE `id`=?"
    _, err = conn.Exec(query, 2)
    if err != nil {
        conn.Rollback()
        fmt.Printf("exec sql: %s failed, err: %v\n", query, err)
        return
    }
    // 提交事务
    err = conn.Commit()
    if err != nil {
        conn.Rollback()
        fmt.Printf("Commit failed, err: %v\n", err)
        return
    }
    fmt.Println("Commit ok")
}

参考：
https://gitbook.cn/books/5e7637996ba17a6d2c9a3352/index.html
https://github.com/go-sql-driver/mysql