10-中间件 - 10-1-MySQL - 《GoLang》

1. database/sql 包
2. 案例

1. database/sql 包

Go语言中的database/sql包提供了保证SQL或类SQL数据库的泛用接口，并不提供具体的数据库驱动。使用database/sql包时必须注入（至少）一个数据库驱动。

1.1. DB

1.  type DB struct{}
    DB是一个数据库（操作）句柄，代表一个具有零到多个底层连接的连接池。
    它可以安全的被多个go程同时使用。
2.  func Open(driverName, dataSourceName string) (*DB, error)
    打开一个数据库连接池，Open函数可能只是验证其参数，而不创建与数据库的连接。如MySQL:
    sql.Open("mysql", username:passwd@tcp(server:3306)/db_name?charset=utf8mb4&parseTime=True)
3.  func (db *DB) Close() error
    Close关闭数据库，释放任何打开的资源。
4.  func (db *DB) Ping() error
    Ping检查与数据库的连接是否仍有效，如果需要会创建连接。
5.  func (db *DB) SetMaxOpenConns(n int)
    SetMaxOpenConns设置与数据库建立连接的最大数目。
    如果n <= 0，不会限制最大开启连接数，默认为0（无限制）。
6.  func (db *DB) SetMaxIdleConns(n int)
    SetMaxIdleConns设置连接池中的最大闲置连接数。
7.  func (db *DB) Exec(query string, args ...interface{}) (Result, error)
    Exec执行一次命令（包括查询、删除、更新、插入等），不返回任何执行结果。
    参数args表示query中的占位参数。
8.  func (db *DB) QueryRow(query string, args ...interface{}) *Row
    QueryRow执行一次查询，并期望返回最多一行结果（即Row）。
9.  func (db *DB) Query(query string, args ...interface{}) (*Rows, error)
    Query执行一次查询，返回多行结果（即Rows），一般用于执行select命令。参数args表示query中的占位参数。
10. func (db *DB) Prepare(query string) (*Stmt, error)
    Prepare创建一个准备好的状态用于之后的查询和命令。返回值可以同时执行多个查询和命令。
11. func (db *DB) Begin() (*Tx, error)
    Begin开始一个事务。隔离水平由数据库驱动决定。

1.2. Row(s)

1.  type Row struct {}
    QueryRow方法返回Row，代表单行查询结果。
2.  type Rows struct {}
    Query方法返回Rows。它的游标指向结果集的第零行，使用Next方法来遍历各行结果
3.  func (r *Row) Scan(dest ...interface{}) error
    Scan将该行查询结果各列分别保存进dest参数指定的值中。
    如果该查询匹配多行，Scan会使用第一行结果并丢弃其余各行。如果没有匹配查询的行，Scan会返回ErrNoRows。
4.  func (rs *Rows) Columns() ([]string, error)
    Columns返回列名。如果Rows已经关闭会返回错误。
5.  func (rs *Rows) Scan(dest ...interface{}) error
    Scan将当前行各列结果填充进dest指定的各个值中。
6.  func (rs *Rows) Next() bool
    Next准备用于Scan方法的下一行结果。如果成功会返回真，如果没有下一行或者出现错误会返回假。
7.  func (rs *Rows) Close() error
    Close关闭Rows，阻止对其更多的列举。 如果Next方法返回假，Rows会自动关闭。

1.3. Stmt

1.  type Stmt struct
    预处理好的SQL，并且是线程安全的类型，可以被goroutine同时使用
2.  func (s *Stmt) Close() error
    关闭Stmt
3.  SQL语句执行
    func (s *Stmt) Exec(args ...interface{}) (Result, error) 
    func (s *Stmt) Query(args ...interface{}) (*Rows, error) 
    func (s *Stmt) QueryRow(args ...interface{}) *Row

1.4. Tx

1.  type Tx struct
    事务对象，db.Begin() 开启事务
2.  func (tx *Tx) Rollback() error
    回滚事务
3.  func (tx *Tx) Commit() error
    提交事务
4.  func (tx *Tx) Prepare(query string) (*Stmt, error)
    预处理语句
5.  语句的执行
    func (tx *Tx) Exec(query string, args ...interface{}) (Result, error)
    func (tx *Tx) Query(query string, args ...interface{}) (*Rows, error)
    func (tx *Tx) QueryRow(query string, args ...interface{}) *Row

2. 案例

2.1. 连接数据库

import (
    "database/sql"
    "fmt"
    _ "github.com/go-sql-driver/mysql"
    "log"
)
func initDB(username, password, mysqlServer, dbName string, maxConn, idle int) (db *sql.DB, err error) {
    mysqlInfo := fmt.Sprintf("%s:%s@tcp(%s)/%s?charset=utf8mb4&parseTime=True", username, password, mysqlServer, dbName)
     // 只检验参数，不涉及账号密码验证
    if db, err = sql.Open("mysql", mysqlInfo); err != nil {
        return 
    }
    if err = db.Ping(); err != nil { // 验证连接是否正常
        return 
    }
    db.SetMaxOpenConns(maxConn) // 最大连接数，小于等于0表示不限制
    db.SetMaxIdleConns(idle)  // 最大空闲连接数
    return
}
func main() {
    username, password, mysqlServer, dbName := "duduniao", "Duduniao.1992!@#", "10.4.7.30:3306", "golang"
    db,err := initDB(username, password, mysqlServer, dbName, 150, 30) // 初始化数据库连接
    if err != nil {
        log.Fatalf("Init db obj failed, err:%s\n", err.Error())
    }
    defer db.Close()
}

2.2. 增删改查

2.2.1. 单行数据查询

func queryAll(db *sql.DB) *[]dept {
    sqlStr := `SELECT * FROM dept`
    rows, err := db.Query(sqlStr)   // 查询结果为多条
    if err != nil {
        log.Errorf("query failed, err:%s\n", err.Error())
        return nil
    }
    defer rows.Close()  // 必须要执行，否则连接池不能被释放
    var deptRes = make([]dept, 0, 4)
    for rows.Next() {
        var ret = dept{}
        if err := rows.Scan(&ret.deptNo, &ret.dName, &ret.loc); err != nil {
            log.Errorf("get res failed, err:%s\n", err.Error())
            break
        }
        deptRes = append(deptRes, ret)
    }
    return &deptRes
}
func main() {
    username, password, mysqlServer, dbName := "duduniao", "Duduniao.1992!@#", "10.4.7.30:3306", "golang"
    db, err := initDB(username, password, mysqlServer, dbName, 150, 30) // 初始化数据库连接
    if err != nil {
        log.Fatalf("Init db obj failed, err:%s\n", err.Error())
    }
    defer db.Close()
    res := queryAll(db)
    for _, v := range *res {
        log.Infof("deptno:%d, dname:%s, loc:%s\n", v.deptNo, v.dName, v.loc)
    }
}

[root@duduniao mysql]# go run conn.go 
INFO[0000] deptno:10, dname:ACCOUNTING, loc:NEW YORK    
INFO[0000] deptno:20, dname:RESEARCH, loc:DALLAS        
INFO[0000] deptno:30, dname:SALES, loc:CHICAGO          
INFO[0000] deptno:40, dname:OPERATIONS, loc:BOSTON

2.2.3. 插入数据

func (d *dept)insert(db *sql.DB)  {
    result, err := db.Exec(`INSERT INTO dept (deptno,dname,loc) VALUES(?,?,?)`, d.deptNo, d.dName, d.loc)
    if err != nil {
        log.Errorf("Insert db failed, err:%s\n", err.Error())
        return
    }
    id, err := result.LastInsertId()  // 如果没有自增的ID，则返回0
    if err != nil {
        log.Errorf("Get data id failed, err:%s\n", err.Error())
        return
    }
    log.Infof("Insert success, id:%v\n", id)
}
func main() {
    username, password, mysqlServer, dbName := "duduniao", "Duduniao.1992!@#", "10.4.7.30:3306", "golang"
    db, err := initDB(username, password, mysqlServer, dbName, 150, 30) // 初始化数据库连接
    if err != nil {
        log.Fatalf("Init db obj failed, err:%s\n", err.Error())
    }
    defer db.Close()
    dept01 := dept{
        deptNo: 50,
        dName: "DB",
        loc: "Anhui",
    }
    dept01.insert(db)
}

[root@duduniao mysql]# go run conn.go 
INFO[0000] Insert success, id:0   
mysql> select * from dept where deptno=50;
+--------+-------+-------+
| deptno | dname | loc   |
+--------+-------+-------+
|     50 | DB    | Anhui |
+--------+-------+-------+

2.2.4. 更新数据

func (d *dept)update(db *sql.DB)  {
    result, err := db.Exec(`UPDATE dept SET dname=? , loc=? WHERE deptno=?`, d.dName, d.loc, d.deptNo)
    if err != nil {
        log.Errorf("update db failed, err:%s\n", err.Error())
        return
    }
    affected, err := result.RowsAffected()  // 获取受影响的数据行数量
    if err != nil {
        log.Errorf("Get affected rows failed, err:%s\n", err.Error())
        return
    }
    log.Infof("Affected rows:%d\n", affected)
}
func main() {
    username, password, mysqlServer, dbName := "duduniao", "Duduniao.1992!@#", "10.4.7.30:3306", "golang"
    db, err := initDB(username, password, mysqlServer, dbName, 150, 30) // 初始化数据库连接
    if err != nil {
        log.Fatalf("Init db obj failed, err:%s\n", err.Error())
    }
    defer db.Close()
    dept01 := dept{
        deptNo: 50,
        dName: "DB",
        loc: "Jiangsu",
    }
    dept01.update(db)
}

[root@duduniao mysql]# go run conn.go 
INFO[0000] Affected rows:1 
mysql> select * from dept where deptno=50;
+--------+-------+-------+
| deptno | dname | loc   |
+--------+-------+-------+
|     50 | DB    | Anhui |
+--------+-------+-------+

2.2.5. 删除数据

func (d *dept)delete(db *sql.DB)  {
    _, err := db.Exec(`DELETE FROM dept WHERE deptno=?`, d.deptNo)
    if err != nil {
        log.Errorf("delete db failed, err:%s\n", err.Error())
        return
    }
}
func main() {
    username, password, mysqlServer, dbName := "duduniao", "Duduniao.1992!@#", "10.4.7.30:3306", "golang"
    db, err := initDB(username, password, mysqlServer, dbName, 150, 30) // 初始化数据库连接
    if err != nil {
        log.Fatalf("Init db obj failed, err:%s\n", err.Error())
    }
    defer db.Close()
    dept01 := dept{
        deptNo: 50,
        dName: "DB",
        loc: "Jiangsu",
    }
    dept01.delete(db)
}

2.3. 预处理

在上面的增删改查案例中，是通过占位符替换的方式完成SQL语句的拼接，并发送至数据库。这种方式每次都需要数据库进行SQL语句的重新编译和执行，对于批量插入或者查询而言，有大量的重复性操作。SQL预处理是将SQL语句分为两个部分，一个是SQL语句骨架(带占位符的SQL语句)，另一个是占位符对应的参数，其中骨架部分先发送给数据库进行编译，然后将参数传递给编译后的对象，从而实现对批处理数据的一次编译，多次运行，提高了性能。
另外，上面的操作方式中，存在SQL注入的风险，Go程序对于字符串参数的安全校验比较麻烦，而预处理后，将由MySQL来实现参数校验，安全性大大提高。

func prepare(db *sql.DB)  {
    stmt, err := db.Prepare(`INSERT INTO dept VALUES (?,?,?)`)
    if err != nil {
        log.Errorf("prepare SQL failed, err:%s\n", err.Error())
        return
    }
    defer stmt.Close()
    _, _ = stmt.Exec(50, "DB", "Anhui")
    _, _ = stmt.Exec(60, "DEV", "Jangsu")
    _, _ = stmt.Exec(70, "OPS", "Henan")
    _, _ = stmt.Exec(80, "TEST", "Jiangxi")
}
func main() {
    username, password, mysqlServer, dbName := "duduniao", "Duduniao.1992!@#", "10.4.7.30:3306", "golang"
    db, err := initDB(username, password, mysqlServer, dbName, 150, 30) // 初始化数据库连接
    if err != nil {
        log.Fatalf("Init db obj failed, err:%s\n", err.Error())
    }
    defer db.Close()
    prepare(db)
}

mysql> select * from dept where deptno>40;
+--------+-------+---------+
| deptno | dname | loc     |
+--------+-------+---------+
|     50 | DB    | Anhui   |
|     60 | DEV   | Jangsu  |
|     70 | OPS   | Henan   |
|     80 | TEST  | Jiangxi |
+--------+-------+---------+

2.4. 事务

2.4.1. ACID

事务必须满足4个条件（ACID）：原子性（Atomicity，或称不可分割性）、一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）。

条件	解释
原子性	一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
一致性	在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
隔离性	数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
持久性	事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

2.4.2. 事务案例

func transaction(db *sql.DB)  {
    tx, err := db.Begin()
    if err != nil {
        if tx != nil {
            _ = tx.Rollback()
        }
        return
    }
    if _, err = tx.Exec("DELETE FROM dept WHERE deptno=80"); err != nil {
        _ = tx.Rollback()
        return
    }
    if _, err = tx.Exec("DELETE FROM dept WHERE deptno=70"); err != nil {
        _ = tx.Rollback()
        return
    }
    if _, err = tx.Exec("DELETE FROM dept WHERE deptno=60"); err != nil {
        _ = tx.Rollback()
        return
    }
    if err = tx.Commit(); err != nil {
        _ = tx.Rollback()
        return
    }
}
func main() {
    username, password, mysqlServer, dbName := "duduniao", "Duduniao.1992!@#", "10.4.7.30:3306", "golang"
    db, err := initDB(username, password, mysqlServer, dbName, 150, 30) // 初始化数据库连接
    if err != nil {
        log.Fatalf("Init db obj failed, err:%s\n", err.Error())
    }
    defer db.Close()
    transaction(db)
}