GO操作MYSQL

驱动包

MySql驱动
Go语言中的database/sql包不包含数据库驱动，使用时必须注入一个数据库驱动。

下载依赖

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

使用mysql驱动

语法:

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

示例代码：

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

func main()  {
    dsn := "root:123456@tcp(127.0.0.1:3306)/go_test"
    //Open打开一个dirverName指定的数据库，dataSourceName指定数据源
    db,err := sql.Open("mysql",dsn)
    if err != nil{
        fmt.Println("打开数据库失败,err:%v\n",err)
        return
    }
    //Open函数可能只是验证其参数，Ping方法可检查数据源名称是否合法。
    err = db.Ping()
    if err != nil{
        fmt.Println("连接数据库失败,err:%v\n",err)
        return
    }
    fmt.Println("连接数据库成功！")
}

初始化连接

返回的DB可以安全的被多个goroutine同时使用，并会维护自身的闲置连接池。

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

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

//定义一个初始化数据库的函数
func initDB() (err error)  {
    dsn := "root:123456@tcp(127.0.0.1:3306)/go_test"
    db,err := sql.Open("mysql",dsn)
    if err != nil{
        return err
    }
    //尝试与数据库连接，校验dsn是否正确
    err = db.Ping()
    if err != nil{
        fmt.Println("校验失败,err",err)
        return err
    }
    // 设置最大连接数
    DB.SetMaxOpenConns(50)
    // 设置最大的空闲连接数
    // DB.SetMaxIdleConns(20)
    fmt.Println("连接数据库成功！")
    return nil
}
func main()  {
    err := initDB()
    if err != nil{
        fmt.Println("init db失败,err",err)
        return
    }
}

其中sql.DB是一个数据库（操作）句柄，代表一个具有零到多个底层连接的连接池。它可以安全的被多个go程同时使用。database/sql包会自动创建和释放连接；它也会维护一个闲置连接的连接池。

SetMaxOpenConns

语法：

func (db *DB) SetMaxOpenConns(n int)
DB.SetMaxOpenConns(10)

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

SetMaxIdleConns

语法:

func (db *DB) SetMaxIdleConns(n int)
DB.SetMaxIdleConns(5)

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

GUID

建库建表语句

> CREATE DATABASE go_test;
> use go_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;

查询

单行查询

单行查询db.QueryRow()执行一次查询，并期望返回最多一行结果（即Row）。
语法:

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

示例:

import (
    "database/sql"
    "fmt"
    _ "github.com/go-sql-driver/mysql"
)
//定义一个user结构体
type User struct {
    id   int64
    name sql.NullString
    age  sql.NullInt64
}
//定义一个全局对象db
var DB *sql.DB

//定义一个初始化数据库的函数
func initDB() (err error)  {
    dsn := "root:123456@tcp(127.0.0.1:3306)/go_test"
    DB, err = sql.Open("mysql", dsn)
    if err != nil{
        return err
    }
    //尝试与数据库连接，校验dsn是否正确
    err = DB.Ping()
    if err != nil{
        fmt.Println("校验失败,err",err)
        return err
    }
    fmt.Println("连接数据库成功！")
    return nil
}
// 单行查询
func queryRow()  {
    sqlStr := "select id,name,age from user where id=?"
    var user User
    err := DB.QueryRow(sqlStr,1).Scan(&user.id, &user.name, &user.age)
    if err != nil{
        fmt.Println("scan失败，err",err)
        return
    }
    fmt.Printf("id:%d name:%s age:%d\n", user.id, user.name, user.age)

}

func main()  {
    err := initDB()
    if err != nil{
        fmt.Println("init db失败,err",err)
        return
    }
    queryRow()
}

多行查询

多行查询db.Query()执行一次查询，返回多行结果（即Rows）。
语法：

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

示例：

//多行查询
func queryRows()  {
    sqlStr := "select id,name,age from user where id>?"
    rows,err := DB.Query(sqlStr,0)
    if err != nil{
        fmt.Println("查询失败,err",err)
        return
    }
    defer rows.Close()  //关闭连接
    //循环读取数据
    for rows.Next(){
        var user User
        err := rows.Scan(&user.id,&user.name,&user.age)
        if err != nil{
            fmt.Println("scan失败,err",err)
            return
        }
        fmt.Printf("id:%d name:%s age:%d\n", user.id, user.name, user.age)

    }
}

插入数据

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

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

示例：

//插入数据
func insertRow()  {
    sqlStr := "insert into user(name,age) values(?,?)"
    ret,err := DB.Exec(sqlStr,"ares4",18)
    if err != nil{
        fmt.Println("插入失败,err",err)
        return
    }
    newID,err := ret.LastInsertId() //新插入数据的ID，默认为主键
    if err != nil{
        fmt.Println("获取id失败,err",err)
        return
    }
    fmt.Println("插入成功，id为：",newID)
}

更新数据

//更新数据
func updateRow()  {
    sqlStr := "update user set age =? where id = ?"
    ret,err := DB.Exec(sqlStr,11,2)
    if err != nil{
        fmt.Println("更新失败,err",err)
        return
    }
    n,err := ret.RowsAffected() //影响行数
    if err != nil{
        fmt.Println("获取影响行数失败,err",err)
        return
    }
    fmt.Println("更新成功，影响行数为:",n)
}

删除数据

//删除数据
func deleteRow()  {
    sqlStr := "delete from user where id = ?"
    ret,err := DB.Exec(sqlStr,4)
    if err != nil{
        fmt.Println("删除失败，err",err)
        return
    }
    n,err := ret.RowsAffected()
    if err != nil{
        fmt.Println("获取影响行数失败,err",err)
        return
    }
    fmt.Println("删除成功，删除行数：",n)
}

MySQL预处理

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

预处理执行过程

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

GO实现MySQL预处理

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

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

查询预处理示例：

//查询预处理
func prepareQueryRow()  {
    sqlStr := "select id,name,age from user where id > ?"
    stmt,err := DB.Prepare(sqlStr)
    if err != nil{
        fmt.Println("预处理失败,err",err)
        return
    }
    defer stmt.Close()
    rows,err := stmt.Query(0)
    if err != nil{
        fmt.Println("查询失败,err",err)
        return
    }
    defer rows.Close()
    //循环读取
    for rows.Next(){
        var user User
        err := rows.Scan(&user.Id,&user.Name,&user.Age)
        if err != nil{
            fmt.Println("scan失败,err",err)
            return
        }
        fmt.Printf("id:%d name:%s age:%d\n", user.Id,user.Name,user.Age)

    }
}

批量插入：

//批量插入
func prepareInsertDemo() {
    sqlStr := "insert into user (name,age) values(?,?)"
    stmt, err := DB.Prepare(sqlStr) // 把要执行的命令发送给MySQL服务端做预处理
    if err != nil {
        fmt.Printf("prepare failed, err:%v\n", err)
        return
    }
    defer stmt.Close()
    // 执行重复的插入命令
    for i := 10; i < 15; i++ {
        name := fmt.Sprintf("ares%02d", i)
        stmt.Exec(name, i)
    }
}

GO MySQL事务

事务相关方法

开始事务：

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

提交事务:

func (tx *Tx) Commit() error

回滚事务：

func (tx *Tx) Rollback() error

事务示例

import (
"database/sql"
"fmt"
_ "github.com/go-sql-driver/mysql"
)
// 定义一个全局对象db
var db *sql.DB

type User struct {
    id   int64
    name sql.NullString
    age  sql.NullInt64
}

// 定义一个初始化数据库的函数
func initDB() (err error) {
    // DSN:Data Source Name
    dsn := "root:123456@tcp(127.0.0.1:3306)/go_test"
    // 不会校验账号密码是否正确
    db, err = sql.Open("mysql", dsn)
    if err != nil {
        return err
    }
    // 尝试与数据库建立连接（校验dsn是否正确）
    err = db.Ping()
    if err != nil {
        return err
    }
    return nil
}

func transDemo()  {
    tx,err := db.Begin()
    if err != nil{
        if tx != nil {
            tx.Rollback() // 回滚
        }
        fmt.Println("事务开启失败,err",err)
        return
    }
    sql1 := "update user set age=age+? where id=?"
    _,err = tx.Exec(sql1,2,1)
    if err != nil{
        tx.Rollback()
        fmt.Println("sql1执行失败,err",err)
        return
    }
    sql2 := "update user set age=age-? where id=?"
    _,err = tx.Exec(sql2,2,2)
    if err != nil{
        tx.Rollback()
        fmt.Println("sql1执行失败,err",err)
        return
    }
    err = tx.Commit()
    if err != nil{
        tx.Rollback()
        fmt.Println("事务提交失败,err",err)
        return
    }
    fmt.Println("数据更新成功！")
}

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

sqlx使用

第三方库sqlx能够简化操作，提高开发效率。

安装

go get github.com/jmoiron/sqlx

连接数据库

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

)
// DB 全局数据库连接对象（内置连接池）
var DB *sqlx.DB

func initDB() (err error)  {
    dsn := "root:123456@tcp(127.0.0.1:3306)/go_test"
    DB,err = sqlx.Connect("mysql",dsn)
    if err != nil{
        return
    }
    DB.SetMaxOpenConns(10)
    DB.SetMaxIdleConns(5)
    fmt.Println("连接成功")
    return
}

func main()  {
    err := initDB()
    if err != nil{
        fmt.Println("init 失败",err)
        return
    }
}

sqlx增删改查

package main

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

)
// DB 全局数据库连接对象（内置连接池）
var DB *sqlx.DB

type User struct {
    Id int
    Name string
    Age int
}

func initDB() (err error)  {
    dsn := "root:123456@tcp(127.0.0.1:3306)/go_test"
    DB,err = sqlx.Connect("mysql",dsn)
    if err != nil{
        return
    }
    DB.SetMaxOpenConns(10)
    DB.SetMaxIdleConns(5)
    fmt.Println("连接成功")
    return
}
//查询单行
func queryRow()  {
    sqlStr := "select id,name,age from user where id = ?"
    var user User
    err := DB.Get(&user,sqlStr,1)
    if err != nil{
        fmt.Println("查询失败,err",err)
        return
    }
    fmt.Println(user)
}
//查询多行
func queryRows()  {
    sqlStr := "select id,name,age from user where id > ?"
    var users []User
    err := DB.Select(&users,sqlStr,0)
    if err != nil{
        fmt.Println("查询失败,err",err)
        return
    }
    fmt.Println(users)
}
//插入数据
func insertRow()  {
    sqlStr := "insert into user(name,age) values(?,?)"
    ret,err := DB.Exec(sqlStr,"王大仙",16)
    if err != nil{
        fmt.Println("插入失败",err)
        return
    }
    thrID,err := ret.LastInsertId()
    if err != nil{
        fmt.Println("获取id失败",err)
        return
    }
    fmt.Println("插入成功，id：",thrID)
}
//更新数据
func updateRow()  {
    sqlStr := "update user set age = ? where id = ?"
    ret,err := DB.Exec(sqlStr,13,2)
    if err != nil{
        fmt.Println("更新失败",err)
        return
    }
    n,err := ret.RowsAffected()
    if err != nil{
        fmt.Println("获取行数失败",err)
        return
    }
    fmt.Println("更新成功，影响行数",n)
}

//删除数据
func deleteRow()  {
    sqlStr := "delete from user where id = ?"
    ret,err := DB.Exec(sqlStr,3)
    if err != nil{
        fmt.Println("删除失败,err",err)
        return
    }
    n,err := ret.RowsAffected()
    if err != nil{
        fmt.Println("获取行数失败",err)
        return
    }
    fmt.Println("删除成功，影响行数",n)
}
func main()  {
    err := initDB()
    if err != nil{
        fmt.Println("init 失败",err)
        return
    }
    queryRow()
    queryRows()
    //insertRow()
    updateRow()
    deleteRow()
}

sqlx事务

// 事务操作
func transDemo() {
    tx, err := DB.Beginx()
    if err != nil {
        if tx != nil {
            tx.Rollback()
        }
        fmt.Printf("begin trnas failed, err:%v\n", err)
        return
    }
    sql1 := "update user set age=age-? where id=?"
    tx.MustExec(sql1, 2, 1) // 名字带Must的一般表示出错就panic:
    sql2 := "update user set age=age+? where id=?"
    tx.MustExec(sql2, 2, 4) // 名字带Must的一般表示出错就panic:
    err = tx.Commit()
    if err != nil {
        tx.Rollback()
        fmt.Printf("commit failed, err:%v\n", err)
    }
    fmt.Println("两条数据更新成功！")
}