美文网首页
Golang操作mysql

Golang操作mysql

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

    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操作mysql

          本文链接：https://www.haomeiwen.com/subject/xzdnrktx.html

          延伸阅读

          深度阅读

          • 您也可以注册成为美文阅读网的作者，发表您的原创作品、分享您的心情！

          栏目导航

          热点阅读

          关于我们|服务条款|联系我们|Golang操作mysql|投稿指南|网站地图|RSS订阅|排版工具|手机版

          提供经典美文摘抄,优美散文欣赏,现代诗歌精选,短篇小说,心情随笔,表白情书范文,故事会在线阅读欣赏

          Copyright © 2014-2023 Haomeiwen.com All Rights Reserved. 好美文阅读网 版权所有

          备案信息：桂公网安备 45052102000051号 · 桂ICP备13007215号-3

          本站所收录作品、热点评论等信息部分来源互联网，目的只是为了系统归纳学习和传递资讯

          所有作品版权归原创作者所有，与本站立场无关，如不慎侵犯了你的权益，请联系我们告知，我们将做删除处理！