Golang原生库操作MySQL | ZackYJ’s Blog

type

Post

status

Published

date

Jul 4, 2020

slug

summary

database/sql 包实现基本 MYSQL 操作示例

连接

database/sql包提供数据库的泛用接口，不提供具体驱动

使用时必须注入数据库驱动，例如MySQL驱动

下载依赖


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

获取DB对象

使用驱动中提供的Open 函数获取数据库对象


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

driverName: 指定的数据库名称

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 方法

Open 函数应该仅被调用一次，很少需要关闭这个DB对象


import (
    "database/sql"
    "fmt"
    _ "github.com/go-sql-driver/mysql" //只引入包中数据库引擎而不是使用其中函数
)
var db *sql.DB  //全局的db对象

func initDB()(err error)  {
    dsn :="root:jjbbkk@tcp(127.0.0.1:3306)/user"
    //指定要连接的是mysql的引擎
    db,err =sql.Open("mysql",dsn)  //不校验用户名和密码，只看格式对不对
    if err != nil {
        return err
    }
    //尝试与数据库建立连接，校验dsn是否正确
    err = db.Ping()  //校验用户名密码
    if err != nil {
        return err
    }
    fmt.Println("数据库连接成功")
    return nil
}

func main() {
    err := initDB()
    if err != nil {
        fmt.Printf("数据库初始化失败：%v\n",err)
        return
    }
}

设置最大连接数


func (db *DB) SetMaxIdleConns(n int)

使用db的 SetMaxIdleConns 方法设置连接池中的最大闲置连接数

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

CRUD

查询

单行查询


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

db.QueryRow() 执行一次查询，并返回最多一行结果（即Row）

QueryRow 总是返回非nil的值，直到返回值的Scan方法被调用时，才会返回延迟的错误

必须调用返回值的Scan方法


type user struct {
    username string
    password string
    sex string
}

func query()  {
    //1.写单条查询的sql语句
    sqlStr := "select username,password,sex from user where username = ?" //？是占位符
    //2.定义保存查询结果的结构体
    var u user
    //3，执行，并拿到结果（必须调用scan方法）
    err := db.QueryRow(sqlStr, "admin").Scan(&u.username, &u.password, &u.sex)//要修改，所以传指针
    if err != nil {
        fmt.Printf("scan from db failed,err:%v\n",err)
        return
    }
    fmt.Printf("name = %s password = %s sex= %s",u.username,u.password,u.sex)
}

多行查询

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

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

一定要记得注册defer 关闭 rows 持有的连接

rows.Next()查询下一行，有下一条记录就返回 true，可以用来作为循环条件


func queryMultiRow() {
    sqlStr := "select username,password,sex from user where sex = ?" //？是占位符
    rows, err := db.Query(sqlStr, "女")
    if err != nil {
        fmt.Println(err)
    }

    //重要：释放当前数据库持有的连接
    defer rows.Close()

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

}

插入

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


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

Exec执行一次命令（包括查询、删除、更新、插入等）

返回的Result是对已执行的SQL命令的总结

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


func insert() {
    sqlStr := "insert into user(username, password,sex) values (?,?,?)"
    ret, err := db.Exec(sqlStr, "zackyj", "jjbbkk", "男")
    if err != nil {
        fmt.Println(err)
    }
    id, err := ret.LastInsertId() //新插入的
    if err != nil {
        fmt.Println(err)
    }
    fmt.Printf("新插入的id是：%d",id)
}

更新


// 更新数据
func updateRowDemo() {
    sqlStr := "update user set age=? where id = ?"
    ret, err := db.Exec(sqlStr, 39, 3)
    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, 3)
    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)
}

预处理

概念

普通SQL语句执行过程

客户端对SQL语句进行占位符替换得到完整的SQL语句。

客户端发送完整SQL语句到MySQL服务端

MySQL服务端执行完整的SQL语句并将结果返回给客户端。

预处理执行过程

把SQL语句分成两部分，命令部分与数据部分。

先把命令部分发送给MySQL服务端，MySQL服务端进行SQL预处理。

然后把数据部分发送给MySQL服务端，MySQL服务端对SQL语句进行占位符替换。

MySQL服务端执行完整的SQL语句并将结果返回给客户端。

为什么要预处理

优化MySQL服务器重复执行SQL的方法，提升服务器性能

提前让服务器编译，一次编译多次执行，节省后续编译的成本

避免SQL注入问题

实现

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


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

Prepare方法会先将sql语句发送给MySQL服务端，返回一个准备好的状态 statment 用于之后的查询和命令

返回值可以同时执行多个查询和命令

使用 Prepare 返回的预处理状态对象 statment 的Query 或 Excu方法，传入占位值进行操作


func prepareQueryDemo() {
    sqlStr := "select id, name, age from user where id > ?"
    stmt, err := db.Prepare(sqlStr)  //预处理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 (db *DB) Begin() (*Tx, error)

提交：func (tx *Tx) Commit() error

回滚：func (tx *Tx) Rollback() error

示例


func transactionDemo() {
    tx, err := db.Begin() // 开启事务
    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, 2)
    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, 3)
    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!")
}