Golang操作mysql

作者: 强某某 | 来源:发表于2020-08-05 10:09 被阅读0次

Golang 学习笔记（10）—— mysql操作
golang操作Redis&Mysql&RabbitMQ方法
golang操作mysql
Golang操作mysql
golang 操作mysql
golang 操作mysql
Golang操作MySQL
golang对mysql的基本操作
golang简单操作mysql
Golang对MySQL的封装

golang&mysql

https://github.com/go-sql-driver/mysql
Go本身不提供具体数据库驱动，只提供驱动接口和管理。
各个数据库驱动需要第三方实现，并且注册到Go中的驱动管理中。
go内部实现了数据库连接池，不需要编码人员关心，优于其他语言
避免sql注入：使用占位符或预处理，不要手动拼接sql
mysql驱动，注册示例

1.jpg

mysql预处理

一般sql处理流程
- 客户端拼接好sql语句
- 客户端发送sql语句到mysql服务器
- mysql服务器解析sql语句并执行，把执行结果发送给客户端
预处理流程
- 把sql分为两部分，命令部分和数据部分
- 首先把命令部分发送给mysql服务器，mysql进行sql预处理
- 然后把数据部分发送给mysql服务器，mysql进行占位符替换
- mysql服务器执行sql语句并返回结果给客户端
Mysql预处理优势
- 同一条sql反复执行，性能会很高。因为命令部分解析已经完成并被缓存
- 避免sql注入问题

package main

import (
    "database/sql"
    "fmt"

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


//_ "github.com/go-sql-driver/mysql"之所以导入不使用，
//是因为其实现的是database/sql接口，实现之后，直接使用database/sql操作即可
var DB *sql.DB

func initDb() error {
    var err error
    dsn := "root:root@tcp(localhost:3306)/golang_db"
    DB, err = sql.Open("mysql", dsn)
    if err != nil {
        return err
    }

    DB.SetMaxOpenConns(100)//连接池最大连接数
    DB.SetMaxIdleConns(16)//空闲时候保持的连接数
    return nil
}

type User struct {
    Id   int64          `db:"id"`
    Name sql.NullString `db:"string"`
    Age  int            `db:"age"`
}

func testQueryMultilRow() {
    sqlstr := "select id, name, age from user where id > ?"
    rows, err := DB.Query(sqlstr, 0)
    //重点关注， rows对象一定要close掉，如果出错，不关掉则会很迅速的达到设置最大连接数，
    //后面的链接过来直接报错或拒绝，实际上也没有起效果
    defer func() {
        if rows != nil {
            rows.Close()
        }
    }()

    if err != nil {
        fmt.Printf("query failed, err:%v\n", err)
        return
    }

    for rows.Next() {
        var user User
        //不Scan也会导致等待，该链接实际处于未工作的状态，然后也会导致连接数迅速达到最大
        err := rows.Scan(&user.Id, &user.Name, &user.Age)
        if err != nil {
            fmt.Printf("scan failed, err:%v\n", err)
            return
        }
        fmt.Printf("user:%#v\n", user)
    }

}

func testQueryData() {
    for i := 0; i < 101; i++ {
        fmt.Printf("query %d times\n", i)
        sqlstr := "select id, name, age from user where id=?"
        row := DB.QueryRow(sqlstr, 2)  //单行查询
        /*if row != nil {
            continue
        }*/
        var user User
        err := row.Scan(&user.Id, &user.Name, &user.Age)
        if err != nil {
            fmt.Printf("scan failed, err:%v\n", err)
            return
        }

        fmt.Printf("id:%d name:%v age:%d\n", user.Id, user.Name, user.Age)
    }

}

func testInsertData() {
    sqlstr := "insert into user(name, age) values(?, ?)"
    result, err := DB.Exec(sqlstr, "tom", 18)
    if err != nil {
        fmt.Printf("insert failed, err:%v\n", err)
        return
    }

    id, err := result.LastInsertId()
    if err != nil {
        fmt.Printf("get last insert id failed, err:%v\n", err)
        return
    }
    fmt.Printf("id is %d\n", id)
}

func testUpdateData() {
    sqlstr := "update user set name=? where id=?"
    result, err := DB.Exec(sqlstr, "jim", 3)
    if err != nil {
        fmt.Printf("insert failed, err:%v\n", err)
        return
    }

    affected, err := result.RowsAffected()
    if err != nil {
        fmt.Printf("get affected rows failed, err:%v\n", err)
    }
    fmt.Printf("update db succ, affected rows:%d\n", affected)
}

func testDeleteData() {
    sqlstr := "delete from user where id=?"
    result, err := DB.Exec(sqlstr, 3)
    if err != nil {
        fmt.Printf("insert failed, err:%v\n", err)
        return
    }

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

//预处理-查询
func testPrepareData() {
    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 func() {
        if stmt != nil {
            stmt.Close()
        }
    }()

    rows, err := stmt.Query(0)//多行查询
    //重点关注， rows对象一定要close掉
    defer func() {
        if rows != nil {
            rows.Close()
        }
    }()

    if err != nil {
        fmt.Printf("query failed, err:%v\n", err)
        return
    }

    for rows.Next() {
        var user User
        err := rows.Scan(&user.Id, &user.Name, &user.Age)
        if err != nil {
            fmt.Printf("scan failed, err:%v\n", err)
            return
        }
        fmt.Printf("user:%#v\n", user)
    }
}

//预处理-插入
func testPrepareInsertData() {
    sqlstr := "insert into user(name, age) values(?, ?)"
    stmt, err := DB.Prepare(sqlstr)
    if err != nil {
        fmt.Printf("insert failed, err:%v\n", err)
        return
    }

    defer func() {
        if stmt != nil {
            stmt.Close()
        }
    }()
    result, err := stmt.Exec("jim", 100)
    id, err := result.LastInsertId() //获取插入的主键id
    if err != nil {
        fmt.Printf("get last insert id failed, err:%v\n", err)
        return
    }
    fmt.Printf("id is %d\n", id)
}

func testTrans() {

    conn, err := DB.Begin()
    if err != nil {
        if conn != nil {
            conn.Rollback()
        }
        fmt.Printf("begin failed, err:%v\n", err)
        return
    }

    sqlstr := "update user set age = 1 where id = ?"
    _, err = conn.Exec(sqlstr, 1)
    if err != nil {
        conn.Rollback()
        fmt.Printf("exec sql:%s failed, err:%v\n", sqlstr, err)
        return
    }

    sqlstr = "update user set age = 2 where id = ?"
    _, err = conn.Exec(sqlstr, 2)
    if err != nil {
        conn.Rollback()
        fmt.Printf("exec second sql:%s failed, err:%v\n", sqlstr, err)
        return
    }
    err = conn.Commit()
    if err != nil {
        fmt.Printf("commit failed, err:%v\n", err)
        conn.Rollback()
        return
    }
}

func main() {
    err := initDb()
    if err != nil {
        fmt.Printf("init db failed, err:%v\n", err)
        return
    }

    //testQueryData()
    //testQueryMultilRow()
    //testInsertData()
    //testUpdateData()
    //testDeleteData()
    //testPrepareData()
    //testPrepareInsertData()
    testTrans()
}

sqlx库

使用更简单,例如关闭，传参，scan都可省略
支持多数据库，mysql、postgresql、oracle、sqlite

package main

import (
    "database/sql"
    "fmt"

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

var DB *sql.DB

func initDb() error {
    var err error
    dsn := "root:root@tcp(localhost:3306)/golang_db"
    DB, err = sql.Open("mysql", dsn)
    if err != nil {
        return err
    }

    DB.SetMaxOpenConns(100)
    DB.SetMaxIdleConns(16)
    return nil
}

type User struct {
    Id   int64          `db:"id"`
    Name sql.NullString `db:"string"`
    Age  int            `db:"age"`
}

func testQueryMultilRow() {
    sqlstr := "select id, name, age from user where id > ?"
    rows, err := DB.Query(sqlstr, 0)
    //重点关注， rows对象一定要close掉
    defer func() {
        if rows != nil {
            rows.Close()
        }
    }()

    if err != nil {
        fmt.Printf("query failed, err:%v\n", err)
        return
    }

    for rows.Next() {
        var user User
        err := rows.Scan(&user.Id, &user.Name, &user.Age)
        if err != nil {
            fmt.Printf("scan failed, err:%v\n", err)
            return
        }
        fmt.Printf("user:%#v\n", user)
    }

}

func testQueryData() {
    for i := 0; i < 101; i++ {
        fmt.Printf("query %d times\n", i)
        sqlstr := "select id, name, age from user where id=?"
        row := DB.QueryRow(sqlstr, 2)
        /*if row != nil {
            continue
        }*/
        var user User
        err := row.Scan(&user.Id, &user.Name, &user.Age)
        if err != nil {
            fmt.Printf("scan failed, err:%v\n", err)
            return
        }

        fmt.Printf("id:%d name:%v age:%d\n", user.Id, user.Name, user.Age)
    }

}

func testInsertData() {
    sqlstr := "insert into user(name, age) values(?, ?)"
    result, err := DB.Exec(sqlstr, "tom", 18)
    if err != nil {
        fmt.Printf("insert failed, err:%v\n", err)
        return
    }

    id, err := result.LastInsertId()
    if err != nil {
        fmt.Printf("get last insert id failed, err:%v\n", err)
        return
    }
    fmt.Printf("id is %d\n", id)
}

func testUpdateData() {
    sqlstr := "update user set name=? where id=?"
    result, err := DB.Exec(sqlstr, "jim", 3)
    if err != nil {
        fmt.Printf("insert failed, err:%v\n", err)
        return
    }

    affected, err := result.RowsAffected()
    if err != nil {
        fmt.Printf("get affected rows failed, err:%v\n", err)
    }
    fmt.Printf("update db succ, affected rows:%d\n", affected)
}

func testDeleteData() {
    sqlstr := "delete from user where id=?"
    result, err := DB.Exec(sqlstr, 3)
    if err != nil {
        fmt.Printf("insert failed, err:%v\n", err)
        return
    }

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

func testPrepareData() {
    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 func() {
        if stmt != nil {
            stmt.Close()
        }
    }()

    rows, err := stmt.Query(0)
    //重点关注， rows对象一定要close掉
    defer func() {
        if rows != nil {
            rows.Close()
        }
    }()

    if err != nil {
        fmt.Printf("query failed, err:%v\n", err)
        return
    }

    for rows.Next() {
        var user User
        err := rows.Scan(&user.Id, &user.Name, &user.Age)
        if err != nil {
            fmt.Printf("scan failed, err:%v\n", err)
            return
        }
        fmt.Printf("user:%#v\n", user)
    }
}

func testPrepareInsertData() {
    sqlstr := "insert into user(name, age) values(?, ?)"
    stmt, err := DB.Prepare(sqlstr)
    if err != nil {
        fmt.Printf("insert failed, err:%v\n", err)
        return
    }

    defer func() {
        if stmt != nil {
            stmt.Close()
        }
    }()
    result, err := stmt.Exec("jim", 100)
    id, err := result.LastInsertId()
    if err != nil {
        fmt.Printf("get last insert id failed, err:%v\n", err)
        return
    }
    fmt.Printf("id is %d\n", id)
}

func testTrans() {

    conn, err := DB.Begin()
    if err != nil {
        if conn != nil {
            conn.Rollback()
        }
        fmt.Printf("begin failed, err:%v\n", err)
        return
    }

    sqlstr := "update user set age = 1 where id = ?"
    _, err = conn.Exec(sqlstr, 1)
    if err != nil {
        conn.Rollback()
        fmt.Printf("exec sql:%s failed, err:%v\n", sqlstr, err)
        return
    }

    sqlstr = "update user set age = 2 where id = ?"
    _, err = conn.Exec(sqlstr, 2)
    if err != nil {
        conn.Rollback()
        fmt.Printf("exec second sql:%s failed, err:%v\n", sqlstr, err)
        return
    }
    err = conn.Commit()
    if err != nil {
        fmt.Printf("commit failed, err:%v\n", err)
        conn.Rollback()
        return
    }
}

func main() {
    err := initDb()
    if err != nil {
        fmt.Printf("init db failed, err:%v\n", err)
        return
    }

    //testQueryData()
    //testQueryMultilRow()
    //testInsertData()
    //testUpdateData()
    //testDeleteData()
    //testPrepareData()
    //testPrepareInsertData()
    testTrans()
}

Golang 学习笔记（10）—— mysql操作
本文为转载，原文：Golang 学习笔记（10）—— mysql操作 go-sql-driver/mysql go...
golang操作Redis&Mysql&RabbitMQ方法
golang[https://www.php.cn/be/go/]操作Redis&Mysql&RabbitMQ： ...
golang操作mysql
Golang操作mysql
golang&mysql https://github.com/go-sql-driver/mysql Go本身不...
golang 操作mysql
1 数据库表 2 连接数据库 mysql.go 3 增删改查 user.go 4 入口调用测试 main.go 最...
golang 操作mysql
mysql 和 mongodb 是两个非常重要的数据库，一个是关系数据库，一个是非关系数据库。也是日常开发的必...
Golang操作MySQL
数据库创建demo表创建DB连接，创建结构体Demo 使用QueryRow查询使用Query查询新增INSE...
golang对mysql的基本操作
golang 操作mysql 导入数据库驱动 mysql连接配置建立连接数据模型数据表结构查询单行注意：...
golang简单操作mysql
首先我们要找到自己的gopath目录然后在cd 到 GOPATH目录下面。 mysql 文件 bert.sql...
Golang对MySQL的封装
Golang操作MySQL的正确姿势https://www.jianshu.com/p/3ec3df7103cf