《Go语言四十二章经》第三十九章 MySql数据库

作者：李骁

39.1 database/sql包

Go 提供了database/sql包用于对关系型数据库的访问，作为操作数据库的入口对象sql.DB，主要为我们提供了两个重要的功能：

  • sql.DB 通过数据库驱动为我们提供管理底层数据库连接的打开和关闭操作.
  • sql.DB 为我们管理数据库连接池

需要注意的是，sql.DB表示操作数据库的抽象访问接口, 而非一个数据库连接对象;它可以根据driver打开关闭数据库连接，管理连接池。正在使用的连接被标记为繁忙，用完后回到连接池等待下次使用。所以，如果你没有把连接释放回连接池，会导致过多连接使系统资源耗尽。

具体到某一类型的关系型数据库，需要导入对应的数据库驱动。下面以MySQL8.0为例，来讲讲怎么在Go语言中调用。

首先，需要下载第三方包：

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

在代码中导入mysql数据库驱动：

  1. import (
  2.    "database/sql"
  3.    _ "github.com/go-sql-driver/mysql"
  4. )

通常来说，不应该直接使用驱动所提供的方法，而是应该使用 sql.DB，因此在导入 mysql 驱动时，这里使用了匿名导入的方式(在包路径前添加 _)，当导入了一个数据库驱动后，此驱动会自行初始化并注册自己到Go的database/sql上下文中，因此我们就可以通过 database/sql 包提供的方法访问数据库了。

39.2 MySQL数据库操作

我们先建立表结构：

  1. CREATE TABLE t_article_cate (
  2. `cid` int(10) NOT NULL AUTO_INCREMENT, 
  3.   `cname` varchar(60) NOT NULL, 
  4.   `ename` varchar(100), 
  5.   `cateimg` varchar(255), 
  6.   `addtime` int(10) unsigned NOT NULL DEFAULT '0', 
  7.   `publishtime` int(10) unsigned NOT NULL DEFAULT '0', 
  8.   `scope` int(10) unsigned NOT NULL DEFAULT '10000', 
  9.   `status` tinyint(1) unsigned NOT NULL DEFAULT '0', 
  10.   PRIMARY KEY (`cid`), 
  11.   UNIQUE  KEY catename (`cname`)
  12. ) ENGINE=InnoDB AUTO_INCREMENT=99 DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

由于预编译语句(PreparedStatement)提供了诸多好处，可以实现自定义参数的查询，通常来说，比手动拼接字符串 SQL 语句高效，可以防止SQL注入攻击。

下面代码使用预编译的方式，来进行增删改查的操作，并通过事务来批量提交一批数据。

在Go语言中对数据类型要求很严格，一般查询数据时先定义数据类型，但是查询数据库中的数据存在三种可能:存在值，存在零值，未赋值NULL 三种状态，因此可以将待查询的数据类型定义为sql.Nullxxx类型，可以通过判断Valid值来判断查询到的值是否为赋值状态还是未赋值NULL状态。如: sql.NullInt64 sql.NullString 

  1. package main
  3. import (
  4.     "database/sql"
  5.     "fmt"
  6.     "strings"
  7.     "time"
  9.     _ "github.com/go-sql-driver/mysql"
  10. )
  12. type DbWorker struct {
  13.     Dsn string
  14.     Db  *sql.DB
  15. }
  17. type Cate struct {
  18.     cid     int
  19.     cname   string
  20.     addtime int
  21.     scope   int
  22. }
  26. func main() {
  27.     dbw := DbWorker{Dsn: "root:123456@tcp(localhost:3306)/mydb?charset=utf8mb4"}
  28.     // 支持下面几种DSN写法，具体看MySQL服务端配置，常见为第2种
  29.     // user@unix(/path/to/socket)/dbname?charset=utf8
  30.     // user:password@tcp(localhost:5555)/dbname?charset=utf8
  31.     // user:password@/dbname
  32.     // user:password@tcp([de:ad:be:ef::ca:fe]:80)/dbname
  34.     dbtemp,  err := sql.Open("mysql",  dbw.Dsn)
  35.     dbw.Db = dbtemp
  37.     if err != nil {
  38.         panic(err)
  39.         return
  40.     }
  41.     defer dbw.Db.Close()
  43.     // 插入数据测试
  44.     dbw.insertData()
  46.     // 删除数据测试
  47.     dbw.deleteData()
  49.     // 修改数据测试
  50.     dbw.editData()
  52.     // 查询数据测试
  53.     dbw.queryData()
  55.     // 事务操作测试
  56.     dbw.transaction()
  57. }

每次db.Query操作后，都建议调用rows.Close()。 因为 db.Query() 会从数据库连接池中获取一个连接，这个底层连接在结果集(rows)未关闭前会被标记为处于繁忙状态。当遍历读到最后一条记录时，会发生一个内部EOF错误，自动调用rows.Close(), 但如果提前退出循环，rows不会关闭，连接不会回到连接池中，连接也不会关闭，则此连接会一直被占用。 因此通常我们使用 defer rows.Close() 来确保数据库连接可以正确放回到连接池中。

插入数据：

  1. // 插入数据，sql预编译
  2. func (dbw *DbWorker) insertData() {
  3.     stmt,  _ := dbw.Db.Prepare(`INSERT INTO t_article_cate (cname, addtime, scope) VALUES (?, ?, ?)`)
  4.     defer stmt.Close()
  6.     ret,  err := stmt.Exec("栏目1",  time.Now().UNIX(),  10)
  8.     // 通过返回的ret可以进一步查询本次插入数据影响的行数
  9.     // RowsAffected和最后插入的Id(如果数据库支持查询最后插入Id)
  10.     if err != nil {
  11.         fmt.Printf("insert data error: %v\n",  err)
  12.         return
  13.     }
  14.     if LastInsertId,  err := ret.LastInsertId(); nil == err {
  15.         fmt.Println("LastInsertId:",  LastInsertId)
  16.     }
  17.     if RowsAffected,  err := ret.RowsAffected(); nil == err {
  18.         fmt.Println("RowsAffected:",  RowsAffected)
  19.     }
  20. }

删除数据：

  1. // 删除数据，预编译
  2. func (dbw *DbWorker) deleteData() {
  3.     stmt,  err := dbw.Db.Prepare(`DELETE FROM t_article_cate WHERE cid=?`)
  4.     ret,  err := stmt.Exec(122)
  5.     // 通过返回的ret可以进一步查询本次插入数据影响的行数RowsAffected和
  6.     // 最后插入的Id(如果数据库支持查询最后插入Id).
  7.     if err != nil {
  8.         fmt.Printf("insert data error: %v\n",  err)
  9.         return
  10.     }
  11.     if RowsAffected,  err := ret.RowsAffected(); nil == err {
  12.         fmt.Println("RowsAffected:",  RowsAffected)
  13.     }
  14. }

修改数据：

  1. // 修改数据，预编译
  2. func (dbw *DbWorker) editData() {
  3.     stmt,  err := dbw.Db.Prepare(`UPDATE t_article_cate SET scope=? WHERE cid=?`)
  4.     ret,  err := stmt.Exec(111,  123)
  5.     // 通过返回的ret可以进一步查询本次插入数据影响的行数RowsAffected和
  6. // 最后插入的Id(如果数据库支持查询最后插入Id).
  7.     if err != nil {
  8.         fmt.Printf("insert data error: %v\n",  err)
  9.         return
  10.     }
  11.     if RowsAffected,  err := ret.RowsAffected(); nil == err {
  12.         fmt.Println("RowsAffected:",  RowsAffected)
  13.     }
  14. }

查询数据：

  1. // 查询数据，预编译
  2. func (dbw *DbWorker) queryData() {
  3.     // 如果方法包含Query，那么这个方法是用于查询并返回rows的。其他用Exec()
  4. // 另外一种写法
  5.     // rows, err := db.Query("select id, name from users where id = ?", 1) 
  6.     stmt,  _ := dbw.Db.Prepare(`SELECT cid, cname, addtime, scope From t_article_cate where status=?`)
  7.     //err = db.QueryRow("select name from users where id = ?", 1).Scan(&name) // 单行查询，直接处理
  8.     defer stmt.Close()
  10.     rows,  err := stmt.Query(0)
  11.     defer rows.Close()
  12.     if err != nil {
  13.         fmt.Printf("insert data error: %v\n",  err)
  14.         return
  15.     }
  17.     // 构造scanArgs、values两个slice，
  18. // scanArgs的每个值指向values相应值的地址
  19.     columns,  _ := rows.Columns()
  20.     fmt.Println(columns)
  21.     rowMaps := make([]map[string]string,  9)
  22.     values := make([]sql.RawBytes,  len(columns))
  23.     scans := make([]interface{},  len(columns))
  24.     for i := range values {
  25.         scans[i] = &values[i]
  26.         scans[i] = &values[i]
  27.     }
  28.     i := 0
  29.     for rows.Next() {
  30.         //将行数据保存到record字典
  31.         err = rows.Scan(scans...)
  33.         each := make(map[string]string,  4)
  34.     // 由于是map引用，放在上层for时，rowMaps最终返回值是最后一条。
  35.         for i,  col := range values {
  36.             each[columns[i]] = string(col)
  37.         }
  39. // 切片追加数据，索引位置有意思。不这样写就不是希望的样子。
  40.         rowMaps = append(rowMaps[:i],  each) 
  41.         fmt.Println(each)
  42.         i++
  43.     }
  44.     fmt.Println(rowMaps)
  46.     for i,  col := range rowMaps {
  47.         fmt.Println(i,  col)
  48.     }
  50.     err = rows.Err()
  51.     if err != nil {
  52.         fmt.Printf(err.Error())
  53.     }
  54. }

事务处理：db.Begin()开始事务，Commit() 或 Rollback()关闭事务。Tx从连接池中取出一个连接，在关闭之前都使用这个连接。Tx不能和DB层的BEGIN，COMMIT混合使用。

  1. func (dbw *DbWorker) transaction() {
  2.     tx,  err := dbw.Db.Begin()
  3.     if err != nil {
  5.         fmt.Printf("insert data error: %v\n",  err)
  6.         return
  7.     }
  8.     defer tx.Rollback()
  9.     stmt,  err := tx.Prepare(`INSERT INTO t_article_cate (cname, addtime, scope) VALUES (?, ?, ?)`)
  10.     if err != nil {
  12.         fmt.Printf("insert data error: %v\n",  err)
  13.         return
  14.     }
  16.     for i := 100; i < 110; i++ {
  17.         cname := strings.Join([]string{"栏目-",  string(i)},  "-")
  18.         _,  err = stmt.Exec(cname,  time.Now().UNIX(),  i+20)
  19.         if err != nil {
  20.             fmt.Printf("insert data error: %v\n",  err)
  21.             return
  22.         }
  23.     }
  24.     err = tx.Commit()
  25.     if err != nil {
  26.         fmt.Printf("insert data error: %v\n",  err)
  27.         return
  28.     }
  29.     stmt.Close()
  30. }

第三十八章 数据序列化

第四十章 LevelDB与BoltDB

本书《Go语言四十二章经》内容在github上同步地址：https://github.com/ffhelicopter/Go42

虽然本书中例子都经过实际运行，但难免出现错误和不足之处，烦请您指出；如有建议也欢迎交流。