16-Go操作MySQL

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

我们常用的数据库基本上都有完整的第三方实现。例如：MySQL驱动

该库以高度的接口抽象，封装数据库操作，便于引入各种其他数据库类型。

下载依赖

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

启动mysql

docker run --name mysql8019 -p 3306:13306 -d -e MYSQL_ROOT_PASSWORD=root1234 mysql:8.0.19

docker run -d -p 3306:3306 --name mysql  -v /opt/mysql:/var/lib/mysql -e MYSQL_DATABASE=myblog -e MYSQL_ROOT_PASSWORD=123456 mysql:5.7 --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci

docker run -it --network host --rm mysql:8.0.19 mysql -h127.0.0.1 -P13306 -uroot -p

MariaDB [(none)]> grant all privileges on *.* to root@'%' identified by 'yuchao666';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.01 sec)

使用MySQL驱动

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

Open打开一个dirverName指定的数据库，dataSourceName指定数据源，一般至少包括数据库文件名和其它连接必要的信息。

import (
    "database/sql"

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

func main() {
   // DSN:Data Source Name
    dsn := "user:password@tcp(127.0.0.1:3306)/dbname"
    db, err := sql.Open("mysql", dsn)
    if err != nil {
        panic(err)
    }
    defer db.Close()  // 注意这行代码要写在上面err判断的下面
}

初始化连接

Open函数可能只是验证其参数格式是否正确，实际上并不创建与数据库的连接。如果要检查数据源的名称是否真实有效，应该调用Ping方法。

返回的DB对象可以安全地被多个goroutine并发使用，并且维护其自己的空闲连接池。因此，Open函数应该仅被调用一次，很少需要关闭这个DB对象。

接下来，我们定义一个全局变量db，用来保存数据库连接对象。将上面的示例代码拆分出一个独立的initDB函数，只需要在程序启动时调用一次该函数完成全局变量db的初始化，其他函数中就可以直接使用全局变量db了。（注意下方的注意）

package main

import (
    "database/sql"
    "fmt"
    _ "github.com/go-sql-driver/mysql" //只使用该包的 init初始化方法
)

// 定义一个全局对象db
var db *sql.DB

// 定义一个初始化数据库的函数
func initDB() (err error) {
    // 1.DSN:Data Source Name
    // sql_test   库名sql_test
    // ?charset=utf8mb4&parseTime=True  数据库属性

    dsn := "user:pwd@tcp(yuchaoit.cn:3306)/sql_test?charset=utf8mb4&parseTime=True"

    // 2.注意！！！这里不要使用:=，我们是给全局变量赋值，然后在main函数中使用全局变量db
    // 不会校验账号密码是否正确，粗略校验格式
    // 不会 真正连接数据库
    db, err = sql.Open("mysql", dsn)
    //注意，先对Open结果错误判断
    if err != nil {
        fmt.Println("数据库连接失败：", err)
        return err
    }
    //3.必须写这里！！
    defer db.Close() //延迟注册，数据库关闭，注意必须写 err判断底下防止db变量，拿到的是nil.Close()

    //     // 4.尝试与数据库建立连接（校验dsn是否正确），这里才是和mysql建立socket链接
    err = db.Ping()
    if err != nil {
        return err
    }
    return nil

    //5.后面可以增删改查了

}

func main() {
    err := initDB() // 调用输出化数据库的函数
    if err != nil {
        fmt.Printf("init db failed,err:%v\n", err)
        return
    }
}

其中sql.DB是表示连接的数据库对象（结构体实例），它保存了连接数据库相关的所有信息。

它内部维护着一个具有零到多个底层连接的连接池，它可以安全地被多个goroutine同时使用。

连接参数

SetMaxOpenConns

mysql链接池

func (db *DB) SetMaxOpenConns(n int)

SetMaxOpenConns设置与数据库建立连接的最大数目。 如果n大于0且小于最大闲置连接数，会将最大闲置连接数减小到匹配最大开启连接数的限制。 如果n<=0，不会限制最大开启连接数，默认为0（无限制）。

关于mysql

1.mysql.cnf可以从服务端设置，客户端的数量

2.程序客户端角度也可以设置，自己的链接数量，如go mysql包设置SetMaxOpenConns

SetMaxIdleConns

func (db *DB) SetMaxIdleConns(n int)

SetMaxIdleConns设置连接池中的最大闲置连接数。 如果n大于最大开启连接数，则新的最大闲置连接数会减小到匹配最大开启连接数的限制。 如果n<=0，不会保留闲置连接。

案例

package main

import (
    "database/sql"
    "fmt"
    _ "github.com/go-sql-driver/mysql" //只使用该包的 init初始化方法
)

// 定义一个全局对象db
var db *sql.DB

// 定义一个初始化数据库的函数
func initDB() (err error) {
    // 1.DSN:Data Source Name
    // sql_test   库名sql_test
    // ?charset=utf8mb4&parseTime=True  数据库属性

    dsn := "root:yuchao666@tcp(yuchaoit.cn:3306)/sql_test?charset=utf8mb4&parseTime=True"

    // 2.注意！！！这里不要使用:=，我们是给全局变量赋值，然后在main函数中使用全局变量db
    // 不会校验账号密码是否正确，粗略校验格式
    // 不会 真正连接数据库
    db, err = sql.Open("mysql", dsn)
    //注意，先对Open结果错误判断
    if err != nil {
        fmt.Println("数据库连接失败：", err)
        return err
    }
    //3.必须写这里！！
    defer db.Close() //延迟注册，数据库关闭，注意必须写 err判断底下防止db变量，拿到的是nil.Close()

    // 4.尝试与数据库建立连接（校验dsn是否正确），这里才是和mysql建立socket链接
    err = db.Ping()
    if err != nil {
        return err
    }

    //4.1 如设置客户端的最大链接数
    db.SetMaxOpenConns(100)

    //4.2 如设置客户端的最大空闲时间，等多久释放

    db.SetMaxIdleConns(10)

    return nil

}

func main() {
    err := initDB() // 调用输出化数据库的函数
    if err != nil {
        fmt.Printf("init db failed,err:%v\n", err)
        return
    }
    //5.后面可以增删改查，使用db全局对象操作

}

CRUD

1.建库建表,utf8mb4是最新编码，支持更多符号，如emoji表情的支持，生产下默认编码了

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;


MariaDB [sql_test]> insert into user(id,name,age) values(66,'于超',28);
Query OK, 1 row affected (0.00 sec)

MariaDB [sql_test]>
MariaDB [sql_test]> insert into user(id,name,age) values(67,'三胖',39);
Query OK, 1 row affected (0.00 sec)

单行查询

为了方便查询，我们事先定义好一个结构体来存储user表的数据。

结构体字段、天然对应表字段。

package main

import (
    "database/sql"
    "fmt"
    _ "github.com/go-sql-driver/mysql" //只使用该包的 init初始化方法
)

// 定义一个全局对象db
var db *sql.DB

type user struct {
    id   int
    age  int
    name string
}

// 定义一个初始化数据库的函数
func initDB() (err error) {
    // 1.DSN:Data Source Name
    // sql_test   库名sql_test
    // ?charset=utf8mb4&parseTime=True  数据库属性

    dsn := "root:yuchao666@tcp(yuchaoit.cn:3306)/sql_test?charset=utf8mb4&parseTime=True"

    // 2.注意！！！这里不要使用:=，我们是给全局变量赋值，然后在main函数中使用全局变量db
    // 不会校验账号密码是否正确，粗略校验格式
    // 不会 真正连接数据库
    db, err = sql.Open("mysql", dsn)
    //注意，先对Open结果错误判断
    if err != nil {
        fmt.Println("数据库连接失败：", err)
        return err
    }

    // 4.尝试与数据库建立连接（校验dsn是否正确），这里才是和mysql建立socket链接
    err = db.Ping()
    if err != nil {
        return err
    }

    //4.1 如设置客户端的最大链接数
    db.SetMaxOpenConns(100)

    //4.2 如设置客户端的最大空闲时间，等多久释放

    db.SetMaxIdleConns(10)

    return err
}

//5.后面可以增删改查，使用db全局对象操作

func queryRowDemo() {
    //原生sql改为const，性能优势，以及dba校验，以及面试手写sql
    //orm得额外学习库去实现你的功能，有额外限制
    // id=? 格式化输入id

    sqlStr := "select id, name, age from user where id=?"

    //结构体对应user表
    var u user

    // 非常重要：确保QueryRow之后调用Scan方法，否则持有的数据库链接不会被释放
    //这里是链式调用，常见语法，传入字段，查询id=66
    err := db.QueryRow(sqlStr, 66).Scan(&u.id, &u.name, &u.age)
    if err != nil {
        fmt.Printf("scan failed, err:%v\n", err)
        return
    }
    fmt.Printf("Scan result-- id:%d name:%s age:%d\n", u.id, u.name, u.age)
}

func main() {
    err := initDB() // 调用输出化数据库的函数
    if err != nil {
        fmt.Printf("init db failed,err:%v\n", err)
        return
    }
    fmt.Println("init mysql success...start use!!")

    //3.必须写这里！！
    defer db.Close() //延迟注册，数据库关闭，注意必须写 err判断底下防止db变量，拿到的是nil.Close()
    queryRowDemo()
}

多行查询

db.Query()

多行查询db.Query()执行一次查询，返回多行结果（即Rows），一般用于执行select命令。

参数args表示query中的占位参数。

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

具体示例代码：

package main

import (
    "database/sql"
    "fmt"
    _ "github.com/go-sql-driver/mysql" //只使用该包的 init初始化方法
)

// 定义一个全局对象db
var db *sql.DB

type user struct {
    id   int
    age  int
    name string
}

// 定义一个初始化数据库的函数
func initDB() (err error) {
    // 1.DSN:Data Source Name
    // sql_test   库名sql_test
    // ?charset=utf8mb4&parseTime=True  数据库属性

    dsn := "root:yuchao666@tcp(yuchaoit.cn:3306)/sql_test?charset=utf8mb4&parseTime=True"

    // 2.注意！！！这里不要使用:=，我们是给全局变量赋值，然后在main函数中使用全局变量db
    // 不会校验账号密码是否正确，粗略校验格式
    // 不会 真正连接数据库
    db, err = sql.Open("mysql", dsn)
    //注意，先对Open结果错误判断
    if err != nil {
        fmt.Println("数据库连接失败：", err)
        return err
    }

    // 4.尝试与数据库建立连接（校验dsn是否正确），这里才是和mysql建立socket链接
    err = db.Ping()
    if err != nil {
        return err
    }

    //4.1 如设置客户端的最大链接数
    db.SetMaxOpenConns(100)

    //4.2 如设置客户端的最大空闲时间，等多久释放

    db.SetMaxIdleConns(10)

    return err
}

//5.后面可以增删改查，使用db全局对象操作

func queryRowDemo() {
    //原生sql改为const，性能优势，以及dba校验，以及面试手写sql
    //orm得额外学习库去实现你的功能，有额外限制
    // id=? 格式化输入id

    sqlStr := "select id, name, age from user where id=?"

    //结构体对应user表
    var u user

    // 非常重要：确保QueryRow之后调用Scan方法，否则持有的数据库链接不会被释放
    //这里是链式调用，常见语法，传入字段，查询id=66
    err := db.QueryRow(sqlStr, 66).Scan(&u.id, &u.name, &u.age)
    if err != nil {
        fmt.Printf("scan failed, err:%v\n", err)
        return
    }
    fmt.Printf("Scan result-- id:%d name:%s age:%d\n", u.id, u.name, u.age)
}

// 查询多条数据示例
func queryMultiRowDemo() {
    sqlStr := "select id, name, age from user where id > ?"
    rows, err := db.Query(sqlStr, 60) // args是对应上面的?号，id值
    if err != nil {
        fmt.Printf("query failed, err:%v\n", err)
        return
    }

    // 非常重要：关闭rows释放持有的数据库链接
    defer rows.Close()

    // 循环读取结果集中的数据
    for rows.Next() {
        var u user
        err := rows.Scan(&u.id, &u.name, &u.age)
        if err != nil {
            fmt.Printf("scan failed, err:%v\n", err)
            return
        }
        fmt.Printf("id:%d name:%s age:%d\n", u.id, u.name, u.age)
    }
}

func main() {
    err := initDB() // 调用输出化数据库的函数
    if err != nil {
        fmt.Printf("init db failed,err:%v\n", err)
        return
    }
    fmt.Println("init mysql success...start use!!")

    //3.必须写这里！！
    defer db.Close() //延迟注册，数据库关闭，注意必须写 err判断底下防止db变量，拿到的是nil.Close()
    //queryRowDemo()
    queryMultiRowDemo()
}

插入数据

插入、更新和删除操作都使用Exec方法。

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

Exec执行一次命令（包括查询、删除、更新、插入等），返回的Result是对已执行的SQL命令的总结。参数args表示query中的占位参数。

具体插入数据示例代码如下：

package main

import (
    "database/sql"
    "fmt"
    _ "github.com/go-sql-driver/mysql" //只使用该包的 init初始化方法
)

// 定义一个全局对象db
var db *sql.DB

type user struct {
    id   int
    age  int
    name string
}

// 定义一个初始化数据库的函数
func initDB() (err error) {
    // 1.DSN:Data Source Name
    // sql_test   库名sql_test
    // ?charset=utf8mb4&parseTime=True  数据库属性

    dsn := "root:yuchao666@tcp(yuchaoit.cn:3306)/sql_test?charset=utf8mb4&parseTime=True"

    // 2.注意！！！这里不要使用:=，我们是给全局变量赋值，然后在main函数中使用全局变量db
    // 不会校验账号密码是否正确，粗略校验格式
    // 不会 真正连接数据库
    db, err = sql.Open("mysql", dsn)
    //注意，先对Open结果错误判断
    if err != nil {
        fmt.Println("数据库连接失败：", err)
        return err
    }

    // 4.尝试与数据库建立连接（校验dsn是否正确），这里才是和mysql建立socket链接
    err = db.Ping()
    if err != nil {
        return err
    }

    //4.1 如设置客户端的最大链接数
    db.SetMaxOpenConns(100)

    //4.2 如设置客户端的最大空闲时间，等多久释放

    db.SetMaxIdleConns(10)

    return err
}

//5.后面可以增删改查，使用db全局对象操作

func queryRowDemo() {
    //原生sql改为const，性能优势，以及dba校验，以及面试手写sql
    //orm得额外学习库去实现你的功能，有额外限制
    // id=? 格式化输入id

    sqlStr := "select id, name, age from user where id=?"

    //结构体对应user表
    var u user

    // 非常重要：确保QueryRow之后调用Scan方法，否则持有的数据库链接不会被释放
    //这里是链式调用，常见语法，传入字段，查询id=66
    err := db.QueryRow(sqlStr, 66).Scan(&u.id, &u.name, &u.age)
    if err != nil {
        fmt.Printf("scan failed, err:%v\n", err)
        return
    }
    fmt.Printf("Scan result-- id:%d name:%s age:%d\n", u.id, u.name, u.age)
}

// 查询多条数据示例
func queryMultiRowDemo() {
    sqlStr := "select id, name, age from user where id > ?"
    rows, err := db.Query(sqlStr, 60) // args是对应上面的?号，id值
    if err != nil {
        fmt.Printf("query failed, err:%v\n", err)
        return
    }

    // 非常重要：关闭rows释放持有的数据库链接
    defer rows.Close()

    // 循环读取结果集中的数据
    for rows.Next() {
        var u user
        err := rows.Scan(&u.id, &u.name, &u.age)
        if err != nil {
            fmt.Printf("scan failed, err:%v\n", err)
            return
        }
        fmt.Printf("id:%d name:%s age:%d\n", u.id, u.name, u.age)
    }
}

// 插入数据
func insertRowDemo() {
    // ?号是mysql独有的占位符 SQL语法
    // 如oracle是$符

    sqlStr := "insert into user(id,name, age) values (?,?,?)"
    ret, err := db.Exec(sqlStr, 68, "狗蛋", 66)
    if err != nil {
        fmt.Printf("insert failed, err:%v\n", err)
        return
    }
    theID, err := ret.LastInsertId() // 获取新插入数据的id

    if err != nil {
        fmt.Printf("get lastinsert ID failed, err:%v\n", err)
        return
    }
    fmt.Printf("insert success, the id is %d.\n", theID)
}

func main() {
    err := initDB() // 调用输出化数据库的函数
    if err != nil {
        fmt.Printf("init db failed,err:%v\n", err)
        return
    }
    fmt.Println("init mysql success...start use!!")

    //3.必须写这里！！
    defer db.Close() //延迟注册，数据库关闭，注意必须写 err判断底下防止db变量，拿到的是nil.Close()
    //queryRowDemo()
    queryMultiRowDemo()
    //insertRowDemo()
}

关于表id设计

一般表数据不会直接暴露自增的id（作为索引），而是额外创建uid。

不会对外暴露具体数据。

更新数据

具体更新数据示例代码如下：

package main

import (
    "database/sql"
    "fmt"
    _ "github.com/go-sql-driver/mysql" //只使用该包的 init初始化方法
)

// 定义一个全局对象db
var db *sql.DB

type user struct {
    id   int
    age  int
    name string
}

// 定义一个初始化数据库的函数
func initDB() (err error) {
    // 1.DSN:Data Source Name
    // sql_test   库名sql_test
    // ?charset=utf8mb4&parseTime=True  数据库属性

    dsn := "root:yuchao666@tcp(yuchaoit.cn:3306)/sql_test?charset=utf8mb4&parseTime=True"

    // 2.注意！！！这里不要使用:=，我们是给全局变量赋值，然后在main函数中使用全局变量db
    // 不会校验账号密码是否正确，粗略校验格式
    // 不会 真正连接数据库
    db, err = sql.Open("mysql", dsn)
    //注意，先对Open结果错误判断
    if err != nil {
        fmt.Println("数据库连接失败：", err)
        return err
    }

    // 4.尝试与数据库建立连接（校验dsn是否正确），这里才是和mysql建立socket链接
    err = db.Ping()
    if err != nil {
        return err
    }

    //4.1 如设置客户端的最大链接数
    db.SetMaxOpenConns(100)

    //4.2 如设置客户端的最大空闲时间，等多久释放

    db.SetMaxIdleConns(10)

    return err
}

//5.后面可以增删改查，使用db全局对象操作

func queryRowDemo() {
    //原生sql改为const，性能优势，以及dba校验，以及面试手写sql
    //orm得额外学习库去实现你的功能，有额外限制
    // id=? 格式化输入id

    sqlStr := "select id, name, age from user where id=?"

    //结构体对应user表
    var u user

    // 非常重要：确保QueryRow之后调用Scan方法，否则持有的数据库链接不会被释放
    //这里是链式调用，常见语法，传入字段，查询id=66
    err := db.QueryRow(sqlStr, 66).Scan(&u.id, &u.name, &u.age)
    if err != nil {
        fmt.Printf("scan failed, err:%v\n", err)
        return
    }
    fmt.Printf("Scan result-- id:%d name:%s age:%d\n", u.id, u.name, u.age)
}

// 查询多条数据示例
func queryMultiRowDemo() {
    sqlStr := "select id, name, age from user where id > ?"
    rows, err := db.Query(sqlStr, 60) // args是对应上面的?号，id值
    if err != nil {
        fmt.Printf("query failed, err:%v\n", err)
        return
    }

    // 非常重要：关闭rows释放持有的数据库链接
    defer rows.Close()

    // 循环读取结果集中的数据
    for rows.Next() {
        var u user
        err := rows.Scan(&u.id, &u.name, &u.age)
        if err != nil {
            fmt.Printf("scan failed, err:%v\n", err)
            return
        }
        fmt.Printf("id:%d name:%s age:%d\n", u.id, u.name, u.age)
    }
}

// 插入数据
func insertRowDemo() {
    // ?号是mysql独有的占位符 SQL语法
    // 如oracle是$符

    sqlStr := "insert into user(id,name, age) values (?,?,?)"
    ret, err := db.Exec(sqlStr, 68, "狗蛋", 66)
    if err != nil {
        fmt.Printf("insert failed, err:%v\n", err)
        return
    }
    theID, err := ret.LastInsertId() // 获取新插入数据的id

    if err != nil {
        fmt.Printf("get lastinsert ID failed, err:%v\n", err)
        return
    }
    fmt.Printf("insert success, the id is %d.\n", theID)
}

// 更新数据
func updateRowDemo() {
    sqlStr := "update user set age=? where name = ?"
    //sql格式化?号，和传入的数据位置顺序一致
    ret, err := db.Exec(sqlStr, 999, "于超")
    if err != nil {
        fmt.Printf("update failed, err:%v\n", err)
        return
    }
    n, err := ret.RowsAffected() // 操作影响的行数
    if err != nil {
        fmt.Printf("get RowsAffected failed, err:%v\n", err)
        return
    }
    fmt.Printf("update success, affected rows:%d\n", n)
}

func main() {
    err := initDB() // 调用输出化数据库的函数
    if err != nil {
        fmt.Printf("init db failed,err:%v\n", err)
        return
    }
    fmt.Println("init mysql success...start use!!")

    //3.必须写这里！！
    defer db.Close() //延迟注册，数据库关闭，注意必须写 err判断底下防止db变量，拿到的是nil.Close()
    //queryRowDemo()

    //insertRowDemo()
    updateRowDemo()

    queryMultiRowDemo()
}

删除数据

具体删除数据的示例代码如下：

package main

import (
    "database/sql"
    "fmt"
    _ "github.com/go-sql-driver/mysql" //只使用该包的 init初始化方法
)

// 定义一个全局对象db
var db *sql.DB

type user struct {
    id   int
    age  int
    name string
}

// 定义一个初始化数据库的函数
func initDB() (err error) {
    // 1.DSN:Data Source Name
    // sql_test   库名sql_test
    // ?charset=utf8mb4&parseTime=True  数据库属性

    dsn := "root:yuchao666@tcp(yuchaoit.cn:3306)/sql_test?charset=utf8mb4&parseTime=True"

    // 2.注意！！！这里不要使用:=，我们是给全局变量赋值，然后在main函数中使用全局变量db
    // 不会校验账号密码是否正确，粗略校验格式
    // 不会 真正连接数据库
    db, err = sql.Open("mysql", dsn)
    //注意，先对Open结果错误判断
    if err != nil {
        fmt.Println("数据库连接失败：", err)
        return err
    }

    // 4.尝试与数据库建立连接（校验dsn是否正确），这里才是和mysql建立socket链接
    err = db.Ping()
    if err != nil {
        return err
    }

    //4.1 如设置客户端的最大链接数
    db.SetMaxOpenConns(100)

    //4.2 如设置客户端的最大空闲时间，等多久释放

    db.SetMaxIdleConns(10)

    return err
}

//5.后面可以增删改查，使用db全局对象操作

func queryRowDemo() {
    //原生sql改为const，性能优势，以及dba校验，以及面试手写sql
    //orm得额外学习库去实现你的功能，有额外限制
    // id=? 格式化输入id

    sqlStr := "select id, name, age from user where id=?"

    //结构体对应user表
    var u user

    // 非常重要：确保QueryRow之后调用Scan方法，否则持有的数据库链接不会被释放
    //这里是链式调用，常见语法，传入字段，查询id=66
    err := db.QueryRow(sqlStr, 66).Scan(&u.id, &u.name, &u.age)
    if err != nil {
        fmt.Printf("scan failed, err:%v\n", err)
        return
    }
    fmt.Printf("Scan result-- id:%d name:%s age:%d\n", u.id, u.name, u.age)
}

// 查询多条数据示例
func queryMultiRowDemo() {
    sqlStr := "select id, name, age from user where id > ?"
    rows, err := db.Query(sqlStr, 60) // args是对应上面的?号，id值
    if err != nil {
        fmt.Printf("query failed, err:%v\n", err)
        return
    }

    // 非常重要：关闭rows释放持有的数据库链接
    defer rows.Close()

    // 循环读取结果集中的数据
    for rows.Next() {
        var u user
        err := rows.Scan(&u.id, &u.name, &u.age)
        if err != nil {
            fmt.Printf("scan failed, err:%v\n", err)
            return
        }
        fmt.Printf("id:%d name:%s age:%d\n", u.id, u.name, u.age)
    }
}

// 插入数据
func insertRowDemo() {
    // ?号是mysql独有的占位符 SQL语法
    // 如oracle是$符

    sqlStr := "insert into user(id,name, age) values (?,?,?)"
    ret, err := db.Exec(sqlStr, 68, "狗蛋", 66)
    if err != nil {
        fmt.Printf("insert failed, err:%v\n", err)
        return
    }
    theID, err := ret.LastInsertId() // 获取新插入数据的id

    if err != nil {
        fmt.Printf("get lastinsert ID failed, err:%v\n", err)
        return
    }
    fmt.Printf("insert success, the id is %d.\n", theID)
}

// 更新数据
func updateRowDemo() {
    sqlStr := "update user set age=? where name = ?"
    //sql格式化?号，和传入的数据位置顺序一致
    ret, err := db.Exec(sqlStr, 999, "于超")
    if err != nil {
        fmt.Printf("update failed, err:%v\n", err)
        return
    }
    n, err := ret.RowsAffected() // 操作影响的行数
    if err != nil {
        fmt.Printf("get RowsAffected failed, err:%v\n", err)
        return
    }
    fmt.Printf("update success, affected rows:%d\n", n)
}

// 删除数据
func deleteRowDemo() {
    sqlStr := "delete from user where name = ?"
    ret, err := db.Exec(sqlStr, "狗蛋")
    if err != nil {
        fmt.Printf("delete failed, err:%v\n", err)
        return
    }
    n, err := ret.RowsAffected() // 操作影响的行数
    if err != nil {
        fmt.Printf("get RowsAffected failed, err:%v\n", err)
        return
    }
    fmt.Printf("delete success, affected rows:%d\n", n)
}

func main() {
    err := initDB() // 调用输出化数据库的函数
    if err != nil {
        fmt.Printf("init db failed,err:%v\n", err)
        return
    }
    fmt.Println("init mysql success...start use!!")

    //3.必须写这里！！
    defer db.Close() //延迟注册，数据库关闭，注意必须写 err判断底下防止db变量，拿到的是nil.Close()

    //queryRowDemo()

    //insertRowDemo()
    //updateRowDemo()
    deleteRowDemo()
    queryMultiRowDemo()
}

小结

原生sql库用法

• 原生sql定义
  • 如单表、连表查询，以及格式化参数?
  • 如增删改查的原生SQL
• 格式化执行SQLdb.Exec
• 通过db.Exec返回值ret变量，可以获取操作结果

// A Result summarizes an executed SQL command.
type Result interface {
    // LastInsertId returns the integer generated by the database
    // in response to a command. Typically this will be from an
    // "auto increment" column when inserting a new row. Not all
    // databases support this feature, and the syntax of such
    // statements varies.
    LastInsertId() (int64, error)

    // RowsAffected returns the number of rows affected by an
    // update, insert, or delete. Not every database or database
    // driver may support this.
    RowsAffected() (int64, error)
}

SQL注入问题

• 自己+拼接string的SQL存在SQL注入问题
• SQL自带的?格式化不存在该问题
  • db.Exec包提供的SQL执行，内部实现了SQL格式化，替换变量，不存在SQL注入问题

SQL注入问题

我们任何时候都不应该自己拼接SQL语句！

这里我们演示一个自行拼接SQL语句的示例，编写一个根据name字段查询user表的函数如下：

练习代码

package main

import (
    "bufio"
    "database/sql"
    "fmt"
    _ "github.com/go-sql-driver/mysql" //只使用该包的 init初始化方法
    "os"
    "strings"
)

// 定义一个全局对象db
var db *sql.DB

type user struct {
    id   int
    age  int
    name string
}

// 定义一个初始化数据库的函数
func initDB() (err error) {
    // 1.DSN:Data Source Name
    // sql_test   库名sql_test
    // ?charset=utf8mb4&parseTime=True  数据库属性

    dsn := "root:yuchao666@tcp(yuchaoit.cn:3306)/sql_test?charset=utf8mb4&parseTime=True"

    // 2.注意！！！这里不要使用:=，我们是给全局变量赋值，然后在main函数中使用全局变量db
    // 不会校验账号密码是否正确，粗略校验格式
    // 不会 真正连接数据库
    db, err = sql.Open("mysql", dsn)
    //注意，先对Open结果错误判断
    if err != nil {
        fmt.Println("数据库连接失败：", err)
        return err
    }

    // 4.尝试与数据库建立连接（校验dsn是否正确），这里才是和mysql建立socket链接
    err = db.Ping()
    if err != nil {
        return err
    }

    //4.1 如设置客户端的最大链接数
    db.SetMaxOpenConns(100)

    //4.2 如设置客户端的最大空闲时间，等多久释放

    db.SetMaxIdleConns(10)

    return err
}

//5.后面可以增删改查，使用db全局对象操作

func queryRowDemo() {
    //原生sql改为const，性能优势，以及dba校验，以及面试手写sql
    //orm得额外学习库去实现你的功能，有额外限制
    // id=? 格式化输入id

    sqlStr := "select id, name, age from user where id=?"

    //结构体对应user表
    var u user

    // 非常重要：确保QueryRow之后调用Scan方法，否则持有的数据库链接不会被释放
    //这里是链式调用，常见语法，传入字段，查询id=66
    err := db.QueryRow(sqlStr, 66).Scan(&u.id, &u.name, &u.age)
    if err != nil {
        fmt.Printf("scan failed, err:%v\n", err)
        return
    }
    fmt.Printf("Scan result-- id:%d name:%s age:%d\n", u.id, u.name, u.age)
}

// 查询多条数据示例
func queryMultiRowDemo() {
    sqlStr := "select id, name, age from user where id > ?"
    rows, err := db.Query(sqlStr, 60) // args是对应上面的?号，id值
    if err != nil {
        fmt.Printf("query failed, err:%v\n", err)
        return
    }

    // 非常重要：关闭rows释放持有的数据库链接
    defer rows.Close()

    // 循环读取结果集中的数据
    for rows.Next() {
        var u user
        err := rows.Scan(&u.id, &u.name, &u.age)
        if err != nil {
            fmt.Printf("scan failed, err:%v\n", err)
            return
        }
        fmt.Printf("id:%d name:%s age:%d\n", u.id, u.name, u.age)
    }
}

// 插入数据
func insertRowDemo() {
    // ?号是mysql独有的占位符 SQL语法
    // 如oracle是$符

    sqlStr := "insert into user(id,name, age) values (?,?,?)"
    ret, err := db.Exec(sqlStr, 68, "狗蛋", 66)
    if err != nil {
        fmt.Printf("insert failed, err:%v\n", err)
        return
    }
    theID, err := ret.LastInsertId() // 获取新插入数据的id

    if err != nil {
        fmt.Printf("get lastinsert ID failed, err:%v\n", err)
        return
    }
    fmt.Printf("insert success, the id is %d.\n", theID)
}

// 更新数据
func updateRowDemo() {
    sqlStr := "update user set age=? where name = ?"
    //sql格式化?号，和传入的数据位置顺序一致
    ret, err := db.Exec(sqlStr, 999, "于超")
    if err != nil {
        fmt.Printf("update failed, err:%v\n", err)
        return
    }
    n, err := ret.RowsAffected() // 操作影响的行数
    if err != nil {
        fmt.Printf("get RowsAffected failed, err:%v\n", err)
        return
    }
    fmt.Printf("update success, affected rows:%d\n", n)
}

// 删除数据
func deleteRowDemo() {
    sqlStr := "delete from user where name = ?"
    ret, err := db.Exec(sqlStr, "狗蛋")
    if err != nil {
        fmt.Printf("delete failed, err:%v\n", err)
        return
    }
    n, err := ret.RowsAffected() // 操作影响的行数

    if err != nil {
        fmt.Printf("get RowsAffected failed, err:%v\n", err)
        return
    }
    fmt.Printf("delete success, affected rows:%d\n", n)
}

// 预处理查询示例
func prepareQueryDemo() {
    sqlStr := "select id, name, age from user where id > ?"
    stmt, err := db.Prepare(sqlStr)
    if err != nil {
        fmt.Printf("prepare failed, err:%v\n", err)
        return
    }
    defer stmt.Close()
    rows, err := stmt.Query(0)
    if err != nil {
        fmt.Printf("query failed, err:%v\n", err)
        return
    }
    defer rows.Close()
    // 循环读取结果集中的数据
    for rows.Next() {
        var u user
        err := rows.Scan(&u.id, &u.name, &u.age)
        if err != nil {
            fmt.Printf("scan failed, err:%v\n", err)
            return
        }
        fmt.Printf("id:%d name:%s age:%d\n", u.id, u.name, u.age)
    }
}

// 练习SQL注入
// sql注入示例
func sqlInjectDemo(name string) {
    //此处的sql是自己格式化拼接的，存在注入问题
    //sqlStr := fmt.Sprintf("select id, name, age from user where name='%s'", name)
    //fmt.Printf("SQL:   %s\n", sqlStr)

    //不存在SQL注入写法，内置库做了处理
    sqlStr := "select id, name, age from user where name=?"
    var u user
    err := db.QueryRow(sqlStr, name).Scan(&u.id, &u.name, &u.age)
    if err != nil {
        fmt.Printf("exec failed, err:%v\n", err)
        return
    }
    fmt.Printf("Query res:  %#v\n", u)
}

func main() {
    err := initDB() // 调用输出化数据库的函数
    if err != nil {
        fmt.Printf("init db failed,err:%v\n", err)
        return
    }
    fmt.Println("init mysql success...start use!!")

    //3.必须写这里！！
    defer db.Close() //延迟注册，数据库关闭，注意必须写 err判断底下防止db变量，拿到的是nil.Close()

    //queryRowDemo()

    //insertRowDemo()
    //updateRowDemo()
    //deleteRowDemo()
    //prepareQueryDemo()

    //queryMultiRowDemo()

    reader := bufio.NewReader(os.Stdin)
    name, _ := reader.ReadString('\n')
    name = strings.TrimSpace(name)

    sqlInjectDemo(name)
}

存在sql注入问题

传入如下特殊字符串
xxx' or 1=1#

➜  goStudy go run demo.go
init mysql success...start use!!
xxx' or 1=1#
SQL:   select id, name, age from user where name='xxx' or 1=1#'
Query res:  main.user{id:66, age:999, name:"于超"}


➜  goStudy go run demo.go
init mysql success...start use!!
bbb' union select * from user #
SQL:   select id, name, age from user where name='bbb' union select * from user #'
Query res:  main.user{id:66, age:999, name:"于超"}

不存在sql注入时

➜  goStudy go run demo.go
init mysql success...start use!!
于超
Query res:  main.user{id:66, age:999, name:"于超"}
➜  goStudy go run demo.go
init mysql success...start use!!
xxx' or 1=1#
exec failed, err:sql: no rows in result set
➜  goStudy

不同语言的SQL占位符

补充：不同的数据库中，SQL语句使用的占位符语法不尽相同。

MySQL预处理

什么是预处理？

普通SQL语句执行过程：

1. 客户端对SQL语句进行占位符替换得到完整的SQL语句。
2. 客户端发送完整SQL语句到MySQL服务端
3. MySQL服务端执行完整的SQL语句并将结果返回给客户端。

预处理执行过程：

1. 把SQL语句分成两部分，命令部分与数据部分。
2. 先把命令部分发送给MySQL服务端，MySQL服务端进行SQL预处理。
3. 然后把数据部分发送给MySQL服务端，MySQL服务端对SQL语句进行占位符替换。
4. MySQL服务端执行完整的SQL语句并将结果返回给客户端。

为什么要预处理？

1. 优化MySQL服务器重复执行SQL的方法，可以提升服务器性能，提前让服务器编译，一次编译多次执行，节省后续编译的成本。
2. 避免SQL注入问题。

Go实现MySQL预处理

• db.Prepare该方法。
• 适合大批量处理SQL时使用
• 不用也行

database/sql中使用下面的Prepare方法来实现预处理操作。

func (db *DB) Prepare(query string) (*Stmt, error)

Prepare方法会先将sql语句发送给MySQL服务端，返回一个准备好的状态用于之后的查询和命令。返回值可以同时执行多个查询和命令。

查询操作的预处理示例代码如下：

package main

import (
    "database/sql"
    "fmt"
    _ "github.com/go-sql-driver/mysql" //只使用该包的 init初始化方法
)

// 定义一个全局对象db
var db *sql.DB

type user struct {
    id   int
    age  int
    name string
}

// 定义一个初始化数据库的函数
func initDB() (err error) {
    // 1.DSN:Data Source Name
    // sql_test   库名sql_test
    // ?charset=utf8mb4&parseTime=True  数据库属性

    dsn := "root:yuchao666@tcp(yuchaoit.cn:3306)/sql_test?charset=utf8mb4&parseTime=True"

    // 2.注意！！！这里不要使用:=，我们是给全局变量赋值，然后在main函数中使用全局变量db
    // 不会校验账号密码是否正确，粗略校验格式
    // 不会 真正连接数据库
    db, err = sql.Open("mysql", dsn)
    //注意，先对Open结果错误判断
    if err != nil {
        fmt.Println("数据库连接失败：", err)
        return err
    }

    // 4.尝试与数据库建立连接（校验dsn是否正确），这里才是和mysql建立socket链接
    err = db.Ping()
    if err != nil {
        return err
    }

    //4.1 如设置客户端的最大链接数
    db.SetMaxOpenConns(100)

    //4.2 如设置客户端的最大空闲时间，等多久释放

    db.SetMaxIdleConns(10)

    return err
}

//5.后面可以增删改查，使用db全局对象操作

func queryRowDemo() {
    //原生sql改为const，性能优势，以及dba校验，以及面试手写sql
    //orm得额外学习库去实现你的功能，有额外限制
    // id=? 格式化输入id

    sqlStr := "select id, name, age from user where id=?"

    //结构体对应user表
    var u user

    // 非常重要：确保QueryRow之后调用Scan方法，否则持有的数据库链接不会被释放
    //这里是链式调用，常见语法，传入字段，查询id=66
    err := db.QueryRow(sqlStr, 66).Scan(&u.id, &u.name, &u.age)
    if err != nil {
        fmt.Printf("scan failed, err:%v\n", err)
        return
    }
    fmt.Printf("Scan result-- id:%d name:%s age:%d\n", u.id, u.name, u.age)
}

// 查询多条数据示例
func queryMultiRowDemo() {
    sqlStr := "select id, name, age from user where id > ?"
    rows, err := db.Query(sqlStr, 60) // args是对应上面的?号，id值
    if err != nil {
        fmt.Printf("query failed, err:%v\n", err)
        return
    }

    // 非常重要：关闭rows释放持有的数据库链接
    defer rows.Close()

    // 循环读取结果集中的数据
    for rows.Next() {
        var u user
        err := rows.Scan(&u.id, &u.name, &u.age)
        if err != nil {
            fmt.Printf("scan failed, err:%v\n", err)
            return
        }
        fmt.Printf("id:%d name:%s age:%d\n", u.id, u.name, u.age)
    }
}

// 插入数据
func insertRowDemo() {
    // ?号是mysql独有的占位符 SQL语法
    // 如oracle是$符

    sqlStr := "insert into user(id,name, age) values (?,?,?)"
    ret, err := db.Exec(sqlStr, 68, "狗蛋", 66)
    if err != nil {
        fmt.Printf("insert failed, err:%v\n", err)
        return
    }
    theID, err := ret.LastInsertId() // 获取新插入数据的id

    if err != nil {
        fmt.Printf("get lastinsert ID failed, err:%v\n", err)
        return
    }
    fmt.Printf("insert success, the id is %d.\n", theID)
}

// 更新数据
func updateRowDemo() {
    sqlStr := "update user set age=? where name = ?"
    //sql格式化?号，和传入的数据位置顺序一致
    ret, err := db.Exec(sqlStr, 999, "于超")
    if err != nil {
        fmt.Printf("update failed, err:%v\n", err)
        return
    }
    n, err := ret.RowsAffected() // 操作影响的行数
    if err != nil {
        fmt.Printf("get RowsAffected failed, err:%v\n", err)
        return
    }
    fmt.Printf("update success, affected rows:%d\n", n)
}

// 删除数据
func deleteRowDemo() {
    sqlStr := "delete from user where name = ?"
    ret, err := db.Exec(sqlStr, "狗蛋")
    if err != nil {
        fmt.Printf("delete failed, err:%v\n", err)
        return
    }
    n, err := ret.RowsAffected() // 操作影响的行数

    if err != nil {
        fmt.Printf("get RowsAffected failed, err:%v\n", err)
        return
    }
    fmt.Printf("delete success, affected rows:%d\n", n)
}

// 预处理查询示例
func prepareQueryDemo() {
    sqlStr := "select id, name, age from user where id > ?"
    stmt, err := db.Prepare(sqlStr)
    if err != nil {
        fmt.Printf("prepare failed, err:%v\n", err)
        return
    }
    defer stmt.Close()
    rows, err := stmt.Query(0)
    if err != nil {
        fmt.Printf("query failed, err:%v\n", err)
        return
    }
    defer rows.Close()
    // 循环读取结果集中的数据
    for rows.Next() {
        var u user
        err := rows.Scan(&u.id, &u.name, &u.age)
        if err != nil {
            fmt.Printf("scan failed, err:%v\n", err)
            return
        }
        fmt.Printf("id:%d name:%s age:%d\n", u.id, u.name, u.age)
    }
}

func main() {
    err := initDB() // 调用输出化数据库的函数
    if err != nil {
        fmt.Printf("init db failed,err:%v\n", err)
        return
    }
    fmt.Println("init mysql success...start use!!")

    //3.必须写这里！！
    defer db.Close() //延迟注册，数据库关闭，注意必须写 err判断底下防止db变量，拿到的是nil.Close()

    //queryRowDemo()

    //insertRowDemo()
    //updateRowDemo()
    //deleteRowDemo()
    prepareQueryDemo()

    //queryMultiRowDemo()
}

Go实现MySQL事务

什么是事务？

事务：一个最小的不可再分的工作单元；通常一个事务对应一个完整的业务(例如银行账户转账业务，该业务就是一个最小的工作单元)，同时这个完整的业务需要执行多次的DML(insert、update、delete)语句共同联合完成。A转账给B，这里面就需要执行两次update操作。

在MySQL中只有使用了Innodb数据库引擎的数据库或表才支持事务。事务处理可以用来维护数据库的完整性，保证成批的SQL语句要么全部执行，要么全部不执行。

事务的ACID

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

事务相关方法

Go语言中使用以下三个方法实现MySQL中的事务操作。 开始事务

func (db *DB) Begin() (*Tx, error)

提交事务

func (tx *Tx) Commit() error

回滚事务

func (tx *Tx) Rollback() error

事务示例代码

下面的代码演示了一个简单的事务操作，该事物操作能够确保两次更新操作要么同时成功要么同时失败，不会存在中间状态。

package main

import (
    "database/sql"
    "fmt"
    _ "github.com/go-sql-driver/mysql" //只使用该包的 init初始化方法
)

// 定义一个全局对象db
var db *sql.DB

type user struct {
    id   int
    age  int
    name string
}

// 定义一个初始化数据库的函数
func initDB() (err error) {
    // 1.DSN:Data Source Name
    // sql_test   库名sql_test
    // ?charset=utf8mb4&parseTime=True  数据库属性

    dsn := "root:yuchao666@tcp(yuchaoit.cn:3306)/sql_test?charset=utf8mb4&parseTime=True"

    // 2.注意！！！这里不要使用:=，我们是给全局变量赋值，然后在main函数中使用全局变量db
    // 不会校验账号密码是否正确，粗略校验格式
    // 不会 真正连接数据库
    db, err = sql.Open("mysql", dsn)
    //注意，先对Open结果错误判断
    if err != nil {
        fmt.Println("数据库连接失败：", err)
        return err
    }

    // 4.尝试与数据库建立连接（校验dsn是否正确），这里才是和mysql建立socket链接
    err = db.Ping()
    if err != nil {
        return err
    }

    //4.1 如设置客户端的最大链接数
    db.SetMaxOpenConns(100)

    //4.2 如设置客户端的最大空闲时间，等多久释放

    db.SetMaxIdleConns(10)

    return err
}

//5.后面可以增删改查，使用db全局对象操作

func queryRowDemo() {
    //原生sql改为const，性能优势，以及dba校验，以及面试手写sql
    //orm得额外学习库去实现你的功能，有额外限制
    // id=? 格式化输入id

    sqlStr := "select id, name, age from user where id=?"

    //结构体对应user表
    var u user

    // 非常重要：确保QueryRow之后调用Scan方法，否则持有的数据库链接不会被释放
    //这里是链式调用，常见语法，传入字段，查询id=66
    err := db.QueryRow(sqlStr, 66).Scan(&u.id, &u.name, &u.age)
    if err != nil {
        fmt.Printf("scan failed, err:%v\n", err)
        return
    }
    fmt.Printf("Scan result-- id:%d name:%s age:%d\n", u.id, u.name, u.age)
}

// 查询多条数据示例
func queryMultiRowDemo() {
    sqlStr := "select id, name, age from user where id > ?"
    rows, err := db.Query(sqlStr, 60) // args是对应上面的?号，id值
    if err != nil {
        fmt.Printf("query failed, err:%v\n", err)
        return
    }

    // 非常重要：关闭rows释放持有的数据库链接
    defer rows.Close()

    // 循环读取结果集中的数据
    for rows.Next() {
        var u user
        err := rows.Scan(&u.id, &u.name, &u.age)
        if err != nil {
            fmt.Printf("scan failed, err:%v\n", err)
            return
        }
        fmt.Printf("id:%d name:%s age:%d\n", u.id, u.name, u.age)
    }
}

// 插入数据
func insertRowDemo() {
    // ?号是mysql独有的占位符 SQL语法
    // 如oracle是$符

    sqlStr := "insert into user(id,name, age) values (?,?,?)"
    ret, err := db.Exec(sqlStr, 68, "狗蛋", 66)
    if err != nil {
        fmt.Printf("insert failed, err:%v\n", err)
        return
    }
    theID, err := ret.LastInsertId() // 获取新插入数据的id

    if err != nil {
        fmt.Printf("get lastinsert ID failed, err:%v\n", err)
        return
    }
    fmt.Printf("insert success, the id is %d.\n", theID)
}

// 更新数据
func updateRowDemo() {
    sqlStr := "update user set age=? where name = ?"
    //sql格式化?号，和传入的数据位置顺序一致
    ret, err := db.Exec(sqlStr, 999, "于超")
    if err != nil {
        fmt.Printf("update failed, err:%v\n", err)
        return
    }
    n, err := ret.RowsAffected() // 操作影响的行数
    if err != nil {
        fmt.Printf("get RowsAffected failed, err:%v\n", err)
        return
    }
    fmt.Printf("update success, affected rows:%d\n", n)
}

// 删除数据
func deleteRowDemo() {
    sqlStr := "delete from user where name = ?"
    ret, err := db.Exec(sqlStr, "狗蛋")
    if err != nil {
        fmt.Printf("delete failed, err:%v\n", err)
        return
    }
    n, err := ret.RowsAffected() // 操作影响的行数

    if err != nil {
        fmt.Printf("get RowsAffected failed, err:%v\n", err)
        return
    }
    fmt.Printf("delete success, affected rows:%d\n", n)
}

// 预处理查询示例
func prepareQueryDemo() {
    sqlStr := "select id, name, age from user where id > ?"
    stmt, err := db.Prepare(sqlStr)
    if err != nil {
        fmt.Printf("prepare failed, err:%v\n", err)
        return
    }
    defer stmt.Close()
    rows, err := stmt.Query(0)
    if err != nil {
        fmt.Printf("query failed, err:%v\n", err)
        return
    }
    defer rows.Close()
    // 循环读取结果集中的数据
    for rows.Next() {
        var u user
        err := rows.Scan(&u.id, &u.name, &u.age)
        if err != nil {
            fmt.Printf("scan failed, err:%v\n", err)
            return
        }
        fmt.Printf("id:%d name:%s age:%d\n", u.id, u.name, u.age)
    }
}

// 练习SQL注入
// sql注入示例
func sqlInjectDemo(name string) {
    //此处的sql是自己格式化拼接的，存在注入问题
    //sqlStr := fmt.Sprintf("select id, name, age from user where name='%s'", name)
    //fmt.Printf("SQL:   %s\n", sqlStr)

    //不存在SQL注入写法，内置库做了处理
    sqlStr := "select id, name, age from user where name=?"
    var u user
    err := db.QueryRow(sqlStr, name).Scan(&u.id, &u.name, &u.age)
    if err != nil {
        fmt.Printf("exec failed, err:%v\n", err)
        return
    }
    fmt.Printf("Query res:  %#v\n", u)
}

// 事务操作案例
// 事务操作示例
func transactionDemo() {
    tx, err := db.Begin() // 开启事务
    //先检查err，再处理tx
    if err != nil {
        if tx != nil {
            tx.Rollback() // 回滚
        }
        fmt.Printf("begin trans failed, err:%v\n", err)
        return
    }
    sqlStr1 := "Update user set age=30 where id=?"
    ret1, err := tx.Exec(sqlStr1, 66)
    if err != nil {
        tx.Rollback() // 回滚
        fmt.Printf("exec sql1 failed, err:%v\n", err)
        return
    }
    affRow1, err := ret1.RowsAffected()
    if err != nil {
        tx.Rollback() // 回滚
        fmt.Printf("exec ret1.RowsAffected() failed, err:%v\n", err)
        return
    }

    sqlStr2 := "Update user set age=40 where id=?"
    ret2, err := tx.Exec(sqlStr2, 67)
    if err != nil {
        tx.Rollback() // 回滚
        fmt.Printf("exec sql2 failed, err:%v\n", err)
        return
    }
    affRow2, err := ret2.RowsAffected()
    if err != nil {
        tx.Rollback() // 回滚
        fmt.Printf("exec ret1.RowsAffected() failed, err:%v\n", err)
        return
    }

    fmt.Println(affRow1, affRow2)
    if affRow1 == 1 && affRow2 == 1 {
        fmt.Println("事务提交啦...")
        tx.Commit() // 提交事务
    } else {
        tx.Rollback()
        fmt.Println("事务回滚啦...")
    }

    fmt.Println("exec trans success!")
}

func main() {
    err := initDB() // 调用输出化数据库的函数
    if err != nil {
        fmt.Printf("init db failed,err:%v\n", err)
        return
    }
    fmt.Println("init mysql success...start use!!")

    //3.必须写这里！！
    defer db.Close() //延迟注册，数据库关闭，注意必须写 err判断底下防止db变量，拿到的是nil.Close()

    //queryRowDemo()

    //insertRowDemo()
    //updateRowDemo()
    //deleteRowDemo()
    //prepareQueryDemo()

    //queryMultiRowDemo()
    //
    //reader := bufio.NewReader(os.Stdin)
    //name, _ := reader.ReadString('\n')
    //name = strings.TrimSpace(name)
    //
    //sqlInjectDemo(name)

    transactionDemo()
}

结果

➜  goStudy go run demo.go 
init mysql success...start use!!
1 0
事务回滚啦...
exec trans success!
➜  goStudy go run demo.go
init mysql success...start use!!
1 1
事务提交啦...
exec trans success!
➜  goStudy

难点在于什么样的业务下需要使用事务，主要是金钱类的业务。

其他语言一般是try、eccept捕捉到异常就直接回滚，而go语言需要判断的err较多。

sqlx生产库

在项目中我们通常可能会使用database/sql连接MySQL数据库。本文借助使用sqlx实现批量插入数据的例子，介绍了sqlx中可能被你忽视了的sqlx.In和DB.NamedExec方法。

sqlx介绍

在项目中我们通常可能会使用database/sql连接MySQL数据库。sqlx可以认为是Go语言内置database/sql的超集，它在优秀的内置database/sql基础上提供了一组扩展。这些扩展中除了大家常用来查询的Get(dest interface{}, ...) error和Select(dest interface{}, ...) error外还有很多其他强大的功能。

安装sqlx

go get github.com/jmoiron/sqlx

基本使用

连接数据库

package main

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

// sqlx使用
var db *sqlx.DB

func initDB() (err error) {
    dsn := "user:pwd@tcp(127.0.0.1:3306)/sql_test?charset=utf8mb4&parseTime=True"
    // 也可以使用MustConnect连接不成功就panic
    db, err = sqlx.Connect("mysql", dsn)
    if err != nil {
        fmt.Printf("connect DB failed, err:%v\n", err)
        return
    }
    db.SetMaxOpenConns(20)
    db.SetMaxIdleConns(10)
    return
}
func main() {
    err := initDB()
    if err != nil {
        fmt.Println("err", err)
        return
    }
    err = db.Ping()
    if err != nil {
        fmt.Println("err", err)
        return
    }
    fmt.Println("sqlx测试链接db成功")

}

查询

db.Get

查询单行数据示例代码如下：

package main

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

// sqlx使用
var db *sqlx.DB

// 注意大写，公开变量，便于其他包，通过反射获取结构体字段
type user struct {
    Id   int
    Name string
    Age  int
}

func initDB() (err error) {
    dsn := "root:yuchao666@tcp(yuchaoit.cn:3306)/sql_test?charset=utf8mb4&parseTime=True"
    // 也可以使用MustConnect连接不成功就panic
    db, err = sqlx.Connect("mysql", dsn)
    if err != nil {
        fmt.Printf("connect DB failed, err:%v\n", err)
        return
    }
    db.SetMaxOpenConns(20)
    db.SetMaxIdleConns(10)
    return
}

// 查询单条数据示例
func queryRowDemo() {
    sqlStr := "select id, name, age from user where id=?"
    var u user
    err := db.Get(&u, sqlStr, 67)
    if err != nil {
        fmt.Printf("get failed, err:%v\n", err)
        return
    }
    fmt.Printf("id:%d name:%s age:%d\n", u.Id, u.Name, u.Age)
}

func main() {
    err := initDB()
    if err != nil {
        fmt.Println("err", err)
        return
    }
    err = db.Ping()
    if err != nil {
        fmt.Println("err", err)
        return
    }
    fmt.Println("sqlx测试链接db成功")
    queryRowDemo()

}

结果

➜  goStudy go run demo.go
sqlx测试链接db成功
id:67 name:三胖 age:40

db.Select

多条数据查询

package main

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

// sqlx使用
var db *sqlx.DB

// 注意大写，公开变量，便于其他包，通过反射获取结构体字段
type user struct {
    Id   int
    Name string
    Age  int
}

func initDB() (err error) {
    dsn := "root:yuchao666@tcp(yuchaoit.cn:3306)/sql_test?charset=utf8mb4&parseTime=True"
    // 也可以使用MustConnect连接不成功就panic
    db, err = sqlx.Connect("mysql", dsn)
    if err != nil {
        fmt.Printf("connect DB failed, err:%v\n", err)
        return
    }
    db.SetMaxOpenConns(20)
    db.SetMaxIdleConns(10)
    return
}

// 查询单条数据示例
func queryRowDemo() {
    sqlStr := "select id, name, age from user where id=?"
    var u user
    err := db.Get(&u, sqlStr, 67)
    if err != nil {
        fmt.Printf("get failed, err:%v\n", err)
        return
    }
    fmt.Printf("id:%d name:%s age:%d\n", u.Id, u.Name, u.Age)
}

// 查询多条数据示例
func queryMultiRowDemo() {
    sqlStr := "select id, name, age from user where id > ?"
    var users []user
    err := db.Select(&users, sqlStr, 0)
    if err != nil {
        fmt.Printf("query failed, err:%v\n", err)
        return
    }
    //fmt.Printf("users:%#v\n", users)
    for _, v := range users {
        fmt.Println(v.Id, v.Age, v.Name)
    }
}
func main() {
    err := initDB()
    if err != nil {
        fmt.Println("err", err)
        return
    }
    err = db.Ping()
    if err != nil {
        fmt.Println("err", err)
        return
    }
    fmt.Println("sqlx测试链接db成功")
    //queryRowDemo()

    queryMultiRowDemo()
}

插入、更新、删除

sqlx中的exec方法与原生sql中的exec使用基本一致：

package main

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

// sqlx使用
var db *sqlx.DB

// 注意大写，公开变量，便于其他包，通过反射获取结构体字段
type user struct {
    Id   int
    Name string
    Age  int
}

func initDB() (err error) {
    dsn := "root:yuchao666@tcp(yuchaoit.cn:3306)/sql_test?charset=utf8mb4&parseTime=True"
    // 也可以使用MustConnect连接不成功就panic
    db, err = sqlx.Connect("mysql", dsn)
    if err != nil {
        fmt.Printf("connect DB failed, err:%v\n", err)
        return
    }
    db.SetMaxOpenConns(20)
    db.SetMaxIdleConns(10)
    return
}

// 查询单条数据示例
func queryRowDemo() {
    sqlStr := "select id, name, age from user where id=?"
    var u user
    err := db.Get(&u, sqlStr, 67)
    if err != nil {
        fmt.Printf("get failed, err:%v\n", err)
        return
    }
    fmt.Printf("id:%d name:%s age:%d\n", u.Id, u.Name, u.Age)
}

// 查询多条数据示例
func queryMultiRowDemo() {
    sqlStr := "select id, name, age from user where id > ?"
    var users []user
    err := db.Select(&users, sqlStr, 0)
    if err != nil {
        fmt.Printf("query failed, err:%v\n", err)
        return
    }
    //fmt.Printf("users:%#v\n", users)
    for _, v := range users {
        fmt.Println(v.Id, v.Age, v.Name)
    }
}

// 插入数据
func insertRowDemo() {
    sqlStr := "insert into user(name, age) values (?,?)"
    ret, err := db.Exec(sqlStr, "超哥", 19)
    if err != nil {
        fmt.Printf("insert failed, err:%v\n", err)
        return
    }
    theID, err := ret.LastInsertId() // 新插入数据的id
    if err != nil {
        fmt.Printf("get lastinsert ID failed, err:%v\n", err)
        return
    }
    fmt.Printf("insert success, the id is %d.\n", theID)
}

// 更新数据
func updateRowDemo() {
    sqlStr := "update user set age=? where id = ?"
    ret, err := db.Exec(sqlStr, 6666, 67)
    if err != nil {
        fmt.Printf("update failed, err:%v\n", err)
        return
    }
    n, err := ret.RowsAffected() // 操作影响的行数
    if err != nil {
        fmt.Printf("get RowsAffected failed, err:%v\n", err)
        return
    }
    fmt.Printf("update success, affected rows:%d\n", n)
}

// 删除数据
func deleteRowDemo() {
    sqlStr := "delete from user where id = ?"
    ret, err := db.Exec(sqlStr, 66)
    if err != nil {
        fmt.Printf("delete failed, err:%v\n", err)
        return
    }
    n, err := ret.RowsAffected() // 操作影响的行数
    if err != nil {
        fmt.Printf("get RowsAffected failed, err:%v\n", err)
        return
    }
    fmt.Printf("delete success, affected rows:%d\n", n)
}

func main() {
    err := initDB()
    if err != nil {
        fmt.Println("err", err)
        return
    }
    err = db.Ping()
    if err != nil {
        fmt.Println("err", err)
        return
    }
    fmt.Println("sqlx测试链接db成功")
    //queryRowDemo()

    //queryMultiRowDemo()
    //insertRowDemo()
    //updateRowDemo()
    deleteRowDemo()

}

NamedExec

DB.NamedExec方法用来绑定SQL语句与结构体或map中的同名字段。

支持传入map类型

func insertUserDemo() (err error) {
    sqlStr := "INSERT INTO user (name,age) VALUES (:name2,:age2)"
    _, err = db.NamedExec(sqlStr,
        map[string]interface{}{
            "name2": "土豆地雷",
            "age2":  28,
        })
    return
}

NamedQuery

与DB.NamedExec同理，支持传入map类型。

package main

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

// sqlx使用
var db *sqlx.DB

// 注意大写，公开变量，便于其他包，通过反射获取结构体字段
type user struct {
    Id   int
    Name string
    Age  int
}

func initDB() (err error) {
    dsn := "root:yuchao666@tcp(yuchaoit.cn:3306)/sql_test?charset=utf8mb4&parseTime=True"
    // 也可以使用MustConnect连接不成功就panic
    db, err = sqlx.Connect("mysql", dsn)
    if err != nil {
        fmt.Printf("connect DB failed, err:%v\n", err)
        return
    }
    db.SetMaxOpenConns(20)
    db.SetMaxIdleConns(10)
    return
}

// 查询单条数据示例
func queryRowDemo() {
    sqlStr := "select id, name, age from user where id=?"
    var u user
    err := db.Get(&u, sqlStr, 67)
    if err != nil {
        fmt.Printf("get failed, err:%v\n", err)
        return
    }
    fmt.Printf("id:%d name:%s age:%d\n", u.Id, u.Name, u.Age)
}

// 查询多条数据示例
func queryMultiRowDemo() {
    sqlStr := "select id, name, age from user where id > ?"
    var users []user
    err := db.Select(&users, sqlStr, 0)
    if err != nil {
        fmt.Printf("query failed, err:%v\n", err)
        return
    }
    //fmt.Printf("users:%#v\n", users)
    for _, v := range users {
        fmt.Println(v.Id, v.Age, v.Name)
    }
}

// 插入数据
func insertRowDemo() {
    sqlStr := "insert into user(name, age) values (?,?)"
    for i := 0; i < 10; i++ {
        ret, err := db.Exec(sqlStr, fmt.Sprintf("超哥%d", i), i)
        if err != nil {
            fmt.Printf("insert failed, err:%v\n", err)
            return
        }
        theID, err := ret.LastInsertId() // 新插入数据的id
        if err != nil {
            fmt.Printf("get lastinsert ID failed, err:%v\n", err)
            return
        }
        fmt.Printf("insert success, the id is %d.\n", theID)
    }

}

// 更新数据
func updateRowDemo() {
    sqlStr := "update user set age=? where id = ?"
    ret, err := db.Exec(sqlStr, 6666, 67)
    if err != nil {
        fmt.Printf("update failed, err:%v\n", err)
        return
    }
    n, err := ret.RowsAffected() // 操作影响的行数
    if err != nil {
        fmt.Printf("get RowsAffected failed, err:%v\n", err)
        return
    }
    fmt.Printf("update success, affected rows:%d\n", n)
}

// 删除数据
func deleteRowDemo() {
    sqlStr := "delete from user where id = ?"
    ret, err := db.Exec(sqlStr, 66)
    if err != nil {
        fmt.Printf("delete failed, err:%v\n", err)
        return
    }
    n, err := ret.RowsAffected() // 操作影响的行数
    if err != nil {
        fmt.Printf("get RowsAffected failed, err:%v\n", err)
        return
    }
    fmt.Printf("delete success, affected rows:%d\n", n)
}

func insertUserDemo() (err error) {
    sqlStr := "INSERT INTO user (name,age) VALUES (:name2,:age2)"
    _, err = db.NamedExec(sqlStr,
        map[string]interface{}{
            "name2": "土豆地雷",
            "age2":  28,
        })
    return
}

func namedQuery() {
    sqlStr := "SELECT * FROM user WHERE name=:name"
    // 使用map做命名查询，传入了name，因此sql格式化写 name=:name
    rows, err := db.NamedQuery(sqlStr, map[string]interface{}{"name": "超哥9"})
    if err != nil {
        fmt.Printf("db.NamedQuery failed, err:%v\n", err)
        return
    }
    defer rows.Close()
    for rows.Next() {
        var u user
        err := rows.StructScan(&u)
        if err != nil {
            fmt.Printf("scan failed, err:%v\n", err)
            continue
        }
        fmt.Printf("user:%#v\n", u)
    }
    // 也可以使用结构体命名查询，根据结构体字段的 db tag进行映射
    // 这里等于传入查询条件
    u := user{
        Name: "土豆地雷",
    }
    rows, err = db.NamedQuery(sqlStr, u)
    if err != nil {
        fmt.Printf("db.NamedQuery failed, err:%v\n", err)
        return
    }
    defer rows.Close()
    for rows.Next() {
        var u user
        err := rows.StructScan(&u)
        if err != nil {
            fmt.Printf("scan failed, err:%v\n", err)
            continue
        }
        fmt.Printf("user:%#v\n", u)
    }
}

func main() {
    err := initDB()
    if err != nil {
        fmt.Println("err", err)
        return
    }
    err = db.Ping()
    if err != nil {
        fmt.Println("err", err)
        return
    }
    fmt.Println("sqlx测试链接db成功")
    //queryRowDemo()

    //queryMultiRowDemo()
    //insertRowDemo()
    //updateRowDemo()
    //deleteRowDemo()
    //insertUserDemo()

    namedQuery()
}

事务操作

对于事务操作，我们可以使用sqlx中提供的db.Beginx()和tx.Exec()方法。示例代码如下：

func transactionDemo2()(err error) {
    tx, err := db.Beginx() // 开启事务
    if err != nil {
        fmt.Printf("begin trans failed, err:%v\n", err)
        return err
    }
    defer func() {
        if p := recover(); p != nil {
            tx.Rollback()
            panic(p) // re-throw panic after Rollback
        } else if err != nil {
            fmt.Println("rollback")
            tx.Rollback() // err is non-nil; don't change it
        } else {
            err = tx.Commit() // err is nil; if Commit returns error update err
            fmt.Println("commit")
        }
    }()

    sqlStr1 := "Update user set age=20 where id=?"

    rs, err := tx.Exec(sqlStr1, 1)
    if err!= nil{
        return err
    }
    n, err := rs.RowsAffected()
    if err != nil {
        return err
    }
    if n != 1 {
        return errors.New("exec sqlStr1 failed")
    }
    sqlStr2 := "Update user set age=50 where i=?"
    rs, err = tx.Exec(sqlStr2, 5)
    if err!=nil{
        return err
    }
    n, err = rs.RowsAffected()
    if err != nil {
        return err
    }
    if n != 1 {
        return errors.New("exec sqlStr1 failed")
    }
    return err
}

sqlx.In

sqlx.In是sqlx提供的一个非常方便的函数。

例如，批量查询
select id,name,age from user where id in (66,77,88);

批量插入
insert into user(name,age) values((?,?),(?,?),(?,?),(?,?))

sqlx.In的批量插入示例

表结构

为了方便演示插入数据操作，这里创建一个user表，表结构如下：

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;

结构体

定义一个user结构体，字段通过tag与数据库中user表的列一致。

type User struct {
    Name string `db:"name"`
    Age  int    `db:"age"`
}

bindvars（绑定变量）

查询占位符?在内部称为bindvars（查询占位符）,它非常重要。你应该始终使用它们向数据库发送值，因为它们可以防止SQL注入攻击。

database/sql不尝试对查询文本进行任何验证；它与编码的参数一起按原样发送到服务器。除非驱动程序实现一个特殊的接口，否则在执行之前，查询是在服务器上准备的。因此bindvars是特定于数据库的:

• MySQL中使用?
• PostgreSQL使用枚举的$1、$2等bindvar语法
• SQLite中?和$1的语法都支持
• Oracle中使用:name的语法

bindvars的一个常见误解是，它们用来在sql语句中插入值。

它们其实仅用于参数化，不允许更改SQL语句的结构。

例如，使用bindvars尝试参数化列或表名将不起作用：

// ？不能用来插入表名（做SQL语句中表名的占位符）
db.Query("SELECT * FROM ?", "mytable")

// ？也不能用来插入列名（做SQL语句中列名的占位符）
db.Query("SELECT ?, ? FROM people", "name", "location")

总结

1.主流数据库操作库，pg，mysql，sqlite等

import (
    "database/sql/driver"
    "fmt"
    _ "github.com/go-sql-driver/mysql"  //务必别忘记导入库，加载init函数
    "github.com/jmoiron/sqlx"
)


2.关于dsn，数据库连接信息校验。
sql.Open() 
只是校验dsn，不会建立tcp连接

db.Ping() 建立tcp连接

3.关闭数据库
db.Close() 要写在err错误检查后面，防止出现空指针无法调用Close


func initDB() (err error) {
    dsn := "root:yuchao666@tcp(yuchaoit.cn:3306)/sql_test?charset=utf8mb4&parseTime=True"
    // 也可以使用MustConnect连接不成功就panic
    db, err = sqlx.Connect("mysql", dsn)
    if err != nil {
        fmt.Printf("connect DB failed, err:%v\n", err)
        return
    }
    db.SetMaxOpenConns(20)
    db.SetMaxIdleConns(10)
    return
}


4.设置客户端参数
最大连接数
最大空闲连接数
连接超时时间

5.增删改查
单行查询QueryRow之后
务必要再执行Scan方法，否则持久的数据库链接不会被释放，吃光资源

多行查询需要主动，rows.Close() 也是关闭链接


6.事务操作 begin、commit

7. sqlx库更加强大