【Go Web学习笔记】第五章 Go *** 作MySQL数据库

前言：大家好，以下所有内容都是我学习韩茹老师的教程时所整理的笔记。部分内容有过删改， 推荐大家去看原作者的文档进行学习， 本文章仅作为个人的学习笔记，后续还会在此基础上不断修改。学习Go Web时应该已经熟悉Go语言基本语法以及计算机网络的相关内容。

学习链接：https://www.chaindesk.cn/witbook/17/253
参考书籍：《Go Web编程》谢孟军

第五章、Go *** 作MySQL数据库

使用MySQL数据库

目前Internet上流行的网站构架方式是LAMP，其中的 M 即 MySQL， 作为数据库，MySQL以免费、开源、使用方便为优势成为了很多Web开发的后端数据库存储引擎。

1、MySQL驱动

Go中支持MySQL的驱动目前比较多，有如下几种，有些是支持database/sql标准，而有些是采用了自己的实现接口,常用的有如下几种:

https://github.com/go-sql-driver/mysql 支持database/sql，全部采用go写。

https://github.com/ziutek/mymysql 支持database/sql，也支持自定义的接口，全部采用go写。

https://github.com/philio/gomysql 不支持database/sql，自定义接口，全部采用go写。

接下来我们主要以第一个驱动为例，也推荐大家采用它，主要理由：

这个驱动比较新，维护的比较好完全支持database/sql接口支持keepalive，保持长连接,虽然 mymysql 也支持 keepalive，但不是线程安全的，这个从底层就支持了keepalive。

mac下下载mysql的驱动并安装(windows系统需要先安装git)：

执行下面两个命令：

下载：go get github.com/go-sql-driver/mysql
安装：go install github.com/go-sql-driver/mysql

windows安装完成以后的文件截图：

接下来我们都将采用同一个数据库表结构：数据库mytest，用户表userinfo，关联用户信息表userdetail。

CREATE TABLE `userinfo` (
`uid` INT(10) NOT NULL AUTO_INCREMENT,
`username` VARCHAR(64) NULL DEFAULT NULL,
`departname` VARCHAR(64) NULL DEFAULT NULL,
`created` DATE NULL DEFAULT NULL,
PRIMARY KEY (`uid`)
)
CREATE TABLE `userdetail` (
`uid` INT(10) NOT NULL DEFAULT '0',
`intro` TEXT NULL,
`profile` TEXT NULL,
PRIMARY KEY (`uid`)
)

mac下打开终端的命令：

/usr/local/mysql/bin/mysql -u root -p

然后输入密码。

windows以管理员身份运行命令提示符，开启数据库服务：

net start mysql
mysql -u root -p
然后输入密码

2、连接数据库

要想使用go语言 *** 作mysql，首先需要和mysql数据库建立连接，获取到DB对象。

2.1 导入包：

首先导入包：

import (
        　　"database/sql"
        　　_"github.com/Go-SQL-Driver/MySQL"
)

database/sql，是golang的标准库之一，它提供了一系列接口方法，用于访问关系数据库。它并不会提供数据库特有的方法，那些特有的方法交给数据库驱动去实现。我们正在加载的驱动是匿名的，将其限定符别名为_,因此我们的代码中没有一个到处的名称可见。

当导入了一个数据库驱动后, 此驱动会自行初始化并注册自己到Golang的database/sql上下文中, 因此我们就可以通过 database/sql 包提供的方法访问数据库了。

2.2 建立连接

使用Open函数：

Open函数：

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

其中的两个参数：
    drvierName，这个名字其实就是数据库驱动注册到 database/sql 时所使用的名字.
        "mysql"
    dataSourceName，数据库连接信息，这个连接包含了数据库的用户名, 密码, 数据库主机以及需要连接的数据库名等信息.
        用户名:密码@协议(地址:端口)/数据库?参数=参数值

db, err := sql.Open("mysql", "用户名:密码@tcp(IP:端口)/数据库?charset=utf8")

例如：db, err := sql.Open("mysql", "root:111111@tcp(127.0.0.1:3306)/test?charset=utf8")

说明：

sql.Open并不会立即建立一个数据库的网络连接, 也不会对数据库链接参数的合法性做检验, 它仅仅是初始化一个sql.DB对象. 当真正进行第一次数据库查询 *** 作时, 此时才会真正建立网络连接;sql.DB表示 *** 作数据库的抽象接口的对象，但不是所谓的数据库连接对象，sql.DB对象只有当需要使用时才会创建连接，如果想立即验证连接，需要用Ping()方法;sql.Open返回的sql.DB对象是协程并发安全的.sql.DB的设计就是用来作为长连接使用的。不要频繁Open, Close。比较好的做法是，为每个不同的datastore建一个DB对象，保持这些对象Open。如果需要短连接，那么把DB作为参数传入function，而不要在function中Open, Close。

新建go文件(demo02_mysql_open.go)，示例代码：

package main

import (
    "database/sql"
    "fmt"
    _ "github.com/go-sql-driver/mysql"	// 这一行必须有
)

func main() {
    /*
    连接数据库:func Open(driverName, dataSourceName string) (*DB, error)
    Open打开一个dirverName指定的数据库，dataSourceName指定数据源，
    一般包至少括数据库文件名和（可能的）连接信息。

    driverName: 使用的驱动名. 这个名字其实就是数据库驱动注册到 database/sql 时所使用的名字.
    dataSourceName: 数据库连接信息，这个连接包含了数据库的用户名, 密码, 数据库主机以及需要连接的数据库名等信息.

    drvierName,"mysql"
    dataSourceName,用户名:密码@协议(地址:端口)/数据库?参数=参数值

     */

    //"root:hanru1314@tcp(127.0.0.1:3306)/ruby?charset=utf8"
    db, err := sql.Open("mysql", ""root:root@tcp:(127.0.0.1:3306)/mytest?charset=utf8")
    fmt.Println(db)
    fmt.Println(err)
    if err != nil {
        fmt.Println("连接有误")
        return
    }
    fmt.Println("连接成功")
    db.Close()

}

运行结果：

3、DML *** 作：增删改 3.1 *** 作增删改

有两种方法： 1.直接使用Exec函数添加

func (db *DB) Exec(query string, args ...interface{}) (Result, error)

示例代码：

result, err := db.Exec("UPDATE userinfo SET username = ?, departname = ? WHERE uid = ?", "王二狗","行政部",2)

2.首先使用Prepare获得stmt，然后调用Exec添加。

建立连接后，通过 *** 作DB对象的 Prepare() 方法，可以进行

func (db *DB) Prepare(query string) (*Stmt, error) {
    return db.PrepareContext(context.Background(), query)
}

示例代码：

stmt,err:=db.Prepare("INSERT INTO userinfo(username,departname,created) values(?,?,?)") 
//补充完整sql语句，并执行
result,err:=stmt.Exec("韩茹","技术部","2018-11-21")

预编译语句(Prepared Statement) 预编译语句(PreparedStatement)提供了诸多好处, 因此我们在开发中尽量使用它. 下面列出了使用预编译语句所提供的功能:

PreparedStatement 可以实现自定义参数的查询PreparedStatement 通常来说, 比手动拼接字符串 SQL 语句高效.PreparedStatement 可以防止SQL注入攻击 3.2 处理结果：影响数据库的行数

获取影响数据库的行数，可以根据该数值判断是否插入或删除或修改成功。

 count, err := result.RowsAffected()

获得刚刚添加数据的自增ID

id, err := result.LastInsertId()

4、DQL *** 作：查询 4.1 查询一条

查询单条数据，QueryRow 函数

func (db *DB) QueryRow(query string, args ...interface{}) *Row

示例代码：

var username, departname, created string
err := db.QueryRow("SELECT username,departname,created FROM userinfo WHERE uid=?", 3)
err := row.Scan(&uid, &username, &departname, &created)

//也可以简写：
err := db.QueryRow("SELECT username,departname,created FROM userinfo WHERE uid=?", 3).Scan(&uid, &username, &departname, &created)

扫描并复制当前行中每一列的值，但是要求行必须与行中的列数相同。

func (rs *Rows) Scan(dest ...interface{}) error

rows.Scan 参数的顺序很重要, 需要和查询的结果的 column 对应. 例如 “SELECT * From user where age >=20 AND age < 30” 查询的行的 column 顺序是 “id, name, age” 和插入操作顺序相同, 因此 rows.Scan 也需要按照此顺序 rows.Scan(&id, &name, &age), 不然会造成数据读取的错位.因为golang是强类型语言，所以查询数据时先定义数据类型，但是查询数据库中的数据存在三种可能:存在值，存在零值，未赋值NULL 三种状态, 因为可以将待查询的数据类型定义为sql.Nullxxx类型，可以通过判断Valid值来判断查询到的值是否为赋值状态还是未赋值NULL状态. 4.2 查询多条数据，并遍历

Query 获取数据，for xxx.Next() 遍历数据：

首先使用 Query() 方法进行查询，如果查询无误，返回Rows，就是所有行的信息，类似结果集。

func (db *DB) Query(query string, args ...interface{}) (*Rows, error)

我们可以通过 Next() 方法判断是否存在下一条数据，如果有，可以使用之前的 Scan() 方法读取一行，然后继续判断，继续获取。这个过程的本质就是迭代，所以通常要配合循环使用。

func (rs *Rows) Next() bool

每次 db.Query *** 作后，都建议调用 rows.Close()。因为 db.Query() 会从数据库连接池中获取一个连接， 这个底层连接在结果集(rows)未关闭前会被标记为处于繁忙状态。当遍历读到最后一条记录时，会发生一个内部EOF错误，自动调用 rows.Close()，但如果提前退出循环，rows 不会关闭，连接不会回到连接池中，连接也不会关闭, 则此连接会一直被占用。因此通常我们使用 defer rows.Close() 来确保数据库连接可以正确放回到连接池中；不过阅读源码发现 rows.Close() *** 作是幂等 *** 作，即一个幂等 *** 作的特点是其任意多次执行所产生的影响均与一次执行的影响相同，所以即便对已关闭的rows再执行close()也没关系。

5、示例代码： 5.1 插入数据

新建go文件(demo03_mysql_insert.go)，示例代码：

package main
// step1：导入包
import (
    "database/sql"
    _"github.com/go-sql-driver/mysql"
    "fmt"
)

func main()  {
    // step2：打开数据库，相当于和数据库建立连接：db对象
    /*
    func Open(driverName, dataSourceName string) (*DB, error)
    drvierName,"mysql"
    dataSourceName,用户名:密码@协议(地址:端口)/数据库?参数=参数值
     */
    db, err := sql.Open("mysql","root:root@tcp(127.0.0.1:3306)/mytest?charset=utf8")
    if err !=nil{
        fmt.Println("连接失败")
        return
    }

    //step3：插入一条数据
    stmt, err := db.Prepare("INSERT INTO userinfo(username,departname,created) values(?,?,?)")
    if err !=nil{
        fmt.Println(" *** 作失败")
    }
    //补充完整sql语句，并执行
    result, err := stmt.Exec("韩茹","技术部","2018-11-21")
    if err !=nil{
        fmt.Println("插入数据失败")
    }
    
    //step4：处理sql *** 作后的结果
    lastInsertId, err := result.LastInsertId()		// (补充：这里的err没有用到，可以用 _ 代替)
	rowsAffected, err := result.RowsAffected()		// ~
	fmt.Println("lastInsertId: ", lastInsertId)
	fmt.Println("影响的行数：", rowsAffected)

    //再次插入数据：
    result, _ = stmt.Exec("ruby","人事部","2018-11-11")
    count, _ := result.RowsAffected()
    fmt.Println("影响的行数：",count)

    //step5：关闭资源
    stmt.Close()
    db.Close()
}

运行结果：

数据库显示：

5.2 更新数据

我们可以按照插入数据的方式，使用Prepare()方法，然后再Exec()。也可以直接执行Exec()。

我们修改uid为2的这条数据，将username由原来的ruby改为王二狗，department由原来的人事部改为行政部。

新建go文件(demo04_mysql_update.go)，示例代码：

package main
// step1：导入包
import (
    "database/sql"
    _"github.com/go-sql-driver/mysql"
    "fmt"
)

func main()  {
    // step2：打开数据库，相当于和数据库建立连接：db对象
    /*
    func Open(driverName, dataSourceName string) (*DB, error)
    drvierName,"mysql"
    dataSourceName,用户名:密码@协议(地址:端口)/数据库?参数=参数值
     */
    db, err := sql.Open("mysql","root:root@tcp(127.0.0.1:3306)/mytest?charset=utf8")
    if err != nil{
        fmt.Println("连接失败。。")
        return
    }

    //step3：修改一条数据，我们直接使用Exec()方法。
    //更新数据
    result, err := db.Exec("UPDATE userinfo SET username = ?, departname = ? WHERE uid = ?", "王二狗", "行政部", 2)
    if err != nil{
        fmt.Println("更新数据失败。。", err)
    }

    //step4：处理sql *** 作后的结果
    lastInsertId, err := result.LastInsertId()
    rowsAffected, err := result.RowsAffected()
    fmt.Println("lastInsertId", lastInsertId)
    fmt.Println("影响的行数：", rowsAffected)

    //step5：关闭资源
    db.Close()
}

运行结果：

刷新数据库后，观察数据：

5.3 查询一条

使用QueryRow查询一条数据

新建go文件(demo05_mysql_query_one.go)，示例代码：

package main

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

func main() {
    /*
    查询一条
     */
    db, _ := sql.Open("mysql", "root:hanru1314@tcp(127.0.0.1:3306)/mytest?charset=utf8")

    row := db.QueryRow("SELECT uid,username,departname,created FROM userinfo WHERE uid=?", 1)
    var uid int
    var username, departname, created string
    /*
    row：Scan()-->将查询的结果从row取出
        err对象
        判断err是否为空，
            为空，查询有结果，数据可以成功取出
            不为空，没有数据，sql: no rows in result set
     */
    err := row.Scan(&uid, &username, &departname, &created)

    //fmt.Println(err)
    if err != nil {
        fmt.Println("查无数据。。")
    } else {
        fmt.Println(uid, username, departname, created)

    }
    db.Close()

}

运行结果：

5.4 查询多条

使用Query 获取数据，for xxx.Next() 遍历数据。

新建go文件(demo06_mysql_query.go)，示例代码：

package main

//step1：导入包
import (
    "database/sql"
    _ "github.com/go-sql-driver/mysql"
    "fmt"
)

type User struct {
    uid        int
    username   string
    departname string
    created    string
}

func main() {
    /*
    查询 *** 作：
     */
    //step2:打开数据库，建立连接
    db, _ := sql.Open("mysql", "root:root@tcp(127.0.0.1:3306)/mytest?charset=utf8")

    //stpt3：查询数据库
    rows, err := db.Query("SELECT uid,username,departname,created FROM userinfo")
    if err != nil {
        fmt.Println("查询有误。。")
        return
    }

    //fmt.Println(rows.Columns()) //[uid username departname created]

    //创建slice，存入struct，
    datas := make([]User, 0)
    //step4： *** 作结果集获取数据
    for rows.Next() {
        var uid int
        var username string
        var departname string
        var created string
        if err := rows.Scan(&uid, &username, &departname, &created); err != nil {
            fmt.Println("获取失败。。")
        }

        //每读取一行，创建一个user对象，存入datas2中
        user := User{uid, username, departname, created}
        datas = append(datas, user)
    }
    //step5：关闭资源
    rows.Close()
    db.Close()

    for _, v := range datas {
        fmt.Println(v)
    }

}

/*
查询：处理查询后的结果：
    思路一：创建结构体
    思路二：将数据，存入到map中
 */

运行结果：

也可以 *** 作map来存储查询的结果，这样就不用创建对应的结构体了。

新建go文件(demo07_mysql_query.go)，示例代码：

package main

//step1：导入包
import (
    "database/sql"
    _ "github.com/go-sql-driver/mysql"
    "fmt"
)

func main() {
    /*
    查询 *** 作：
     */
    //step2:打开数据库，建立连接
    db, _ := sql.Open("mysql", "root:root@tcp(127.0.0.1:3306)/mytest?charset=utf8")

    //stpt3：查询数据库
    rows, err := db.Query("SELECT uid,username,departname,created FROM userinfo")
    if err != nil {
        fmt.Println("查询有误。。")
        return
    }

    //fmt.Println(rows.Columns()) //[uid username departname created]
    //定义一个map，用于存储从数据库中查询出来的数据，字段作为key，string，数据作为value，任意类型，空接口

    datas := make([]map[string]interface{}, 0)

    //step4： *** 作结果集获取数据
    for rows.Next() {
        var uid int
        var username string
        var departname string
        var created string
        if err := rows.Scan(&uid, &username, &departname, &created); err != nil {
            fmt.Println("获取失败。。")
        }
        map1 := make(map[string]interface{})
        //将读取到的数据，存入了map中
        map1["uid"] = uid
        map1["username"] = username
        map1["departname"] = departname
        map1["created"] = created
        //将map存入切片中
        datas = append(datas, map1)

    }
    //step5：关闭资源
    rows.Close()
    db.Close()

    for _, v := range datas {
        fmt.Println(v)
    }
}

/*
查询：处理查询后的结果：
    思路一：将数据，存入到map中
    思路二：创建结构体：
 */

运行结果：

5.5 事务

事务 *** 作是通过三个方法实现：

Begin()：开启事务Commit()：提交事务（执行sql)Rollback()：回滚

再创建一个表，并插入两条数据：

CREATE TABLE `account` (
  `id` int(4) NOT NULL,
  `name` varchar(30) DEFAULT NULL,
  `money` float(8, 2) DEFAULT NULL,
  PRIMARY KEY (`id`)
)
INSERT INTO `account` VALUES ('1', 'ruby', '3000.00');
INSERT INTO `account` VALUES ('2', '王二狗', '1000.00');

现在我们想让ruby转账给王二狗2000元。

如果转账成功，修改ruby和王二狗的金额。否则ruby和王二狗的金额保持不变。

新建go文件(demo08_mysql_transaction.go)，示例代码：

package main

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

func main() {
    /*
    事务：
        4大特性：ACID
        原子性：
        一致性：
        隔离性：
        永久性：
     */
    //ruby-->王二狗,2000元
    db, _ := sql.Open("mysql", "root:root@tcp(127.0.0.1:3306)/mytest?charset=utf8")
    //开启事务
    tx, _ := db.Begin()
    //提供一组sql *** 作
    var aff1, aff2 int64 = 0, 0
    result1, _ := tx.Exec("UPDATE account SET money=3000 WHERE id=?", 1)
    result2, _ := tx.Exec("UPDATE account SET money=2000 WHERE id=?", 2)
    //fmt.Println(result2)
    if result1 != nil {
        aff1, _ = result1.RowsAffected()
    }
    if result2 != nil {
        aff2, _ = result2.RowsAffected();
    }
    fmt.Println(aff1)
    fmt.Println(aff2)

    if aff1 == 1 && aff2 == 1 {
        //提交事务
        tx.Commit()
        fmt.Println(" *** 作成功。。")
    } else {
        //回滚
        tx.Rollback()
        fmt.Println(" *** 作失败。。。回滚。。")
    }
}

运行结果：

刷新数据库：

由于事务是一个一直连接的状态，所以Tx对象必须绑定和控制单个连接。一个Tx会在整个生命周期中保存一个连接，然后在调用 commit() 或 Rollback() 的时候释放掉。在调用这几个函数的时候必须十分小心，否则连接会一直被占用直到被垃圾回收。

6、sqlx扩展包[扩展内容]

sqlx包是作为database/sql包的一个额外扩展包，在原有的database/sql加了很多扩展，如直接将查询的数据转为结构体，大大简化了代码书写，当然database/sql包中的方法同样起作用。

安装：

go get "github.com/jmoiron/sqlx"

连接数据库：

var Db *sqlx.DB
db, err := sqlx.Open("mysql","username:password@tcp(ip:port)/database?charset=utf8")
Db = db

处理类型（Handle Types)

sqlx 设计和 database/sql 使用方法是一样的。包含有4中主要的handle types：

sqlx.DB - 和sql.DB相似，表示数据库。sqlx.Tx - 和sql.Tx相似，表示事物。sqlx.Stmt - 和sql.Stmt相似，表示prepared statement。sqlx.NamedStmt - 表示prepared statement（支持named parameters）

所有的handler types都提供了对database/sql的兼容，意味着当你调用sqlx.DB.Query时，可以直接替换为sql.DB.Query.这就使得sqlx可以很容易的加入到已有的数据库项目中。

此外，sqlx还有两个cursor类型：

sqlx.Rows - 和sql.Rows类似，Queryx返回。sqlx.Row - 和sql.Row类似，QueryRowx返回。

相比database/sql方法还多了新语法，也就是实现将获取的数据直接转换结构体实现。

Get(dest interface{}, …) errorSelect(dest interface{}, …) error

Get 和Select（非常常用）

Get和Select是一个非常省时的扩展，可直接将结果赋值给结构体，其内部封装了StructScan进行转化。Get用于获取单个结果然后Scan，Select用来获取结果切片。

新建go文件(demo09_kuozhan.go)，示例代码：

package main

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

var Db *sqlx.DB

type User struct {
    Uid        int
    Username   string
    Departname string
    Created    string
}

func main() {

    db, err := sqlx.Open("mysql", "root:root@tcp(127.0.0.1:3306)/mytest?charset=utf8")

    if err != nil {
        fmt.Println("open mysql failed,", err)
        return
    }

    Db = db
    var users []User
    err = Db.Select(&users, "SELECT uid,username,departname,created FROM userinfo")
    if err != nil {
        fmt.Println("Select error", err)
    }
    fmt.Printf("this is Select res:%v\n", users)
    var user User
    err1 := Db.Get(&user, "SELECT uid,username,departname,created FROM userinfo where uid = ?", 1)
    if err1 != nil {
        fmt.Println("GET error :", err1)
    } else {
        fmt.Printf("this is GET res:%v", user)
    }
}

运行结果：