golang使用sqlx操作MySQL
Published on 2023-01-29 14:20 in 分类: Golang with wushaoyu
分类: Golang

golang使用sqlx操作MySQL

     

    复制代码
    package main

//sqlx示例

import (
    "errors"
    "fmt"

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

var DB *sqlx.DB

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

func initDB() (err error) {
    dsn := "user:password@tcp(xx.xx.xx.xx:3306)/go_db"
    DB, err = sqlx.Connect("mysql", dsn)
    if err != nil {
        return
    }
    return nil
}

//单条查询操作
func queryDemo() {
    sqlStr := "select id,name,age from info where id = ?"
    user := User{}
    err := DB.Get(&user, sqlStr, 1)
    if err != nil {
        fmt.Printf("query failed,err:%v\n", err)
        return
    }
    fmt.Printf("user:%#v\n", user)
    fmt.Printf("user:%v\n", user)
}

//查询多条
func queryMultiDemo() {
    sqlStr := "select id,name,age from info where id > ?"
    var users []User
    err := DB.Select(&users, sqlStr, 0)
    if err != nil {
        fmt.Println("query multi failed,err:%v\n", err)
        return
    }
    fmt.Printf("users: %v\n", users)
    for _, user := range users {
        fmt.Printf("user:%v\n", user)
    }
}

// 插入数据
func insertRowDemo() {
    sqlStr := "insert into info(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 info set age=? where id = ?"
    ret, err := DB.Exec(sqlStr, 39, 1)
    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 info where id = ?"
    ret, err := DB.Exec(sqlStr, 1)
    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 transDemo() {
    tx, err := DB.Beginx() //开启事务
    // tx.MustExec() //带Must的一般标识出错就直接panic
    if err != nil {
        if tx != nil {
            tx.Rollback()
        }
        fmt.Printf("begin trans failed,err:%v\n", err)
        return
    }
    sql1 := "update info set age=age-? where id=?"
    tx.MustExec(sql1, 2, 1)
    err = tx.Commit()
    if err != nil {
        tx.Rollback()
        fmt.Printf("commit failed,err:%v\n", err)
    }
    fmt.Println("数据更新成功")

}

//事务操作2
func transDemo2() (err error) {
    tx, err := DB.Beginx()
    if err != nil {
        fmt.Println("begin trans failed,err:", err)
        return
    }

    defer func() {
        if p := recover(); p != nil {
            tx.Rollback()
            panic(p)
        } else if err != nil {
            fmt.Println("rollback")
            tx.Rollback()
        } else {
            err = tx.Commit()
            fmt.Println("sql commit")
        }
    }()

    sqlStr1 := "update info set age=99 where id = ?"
    r, err := tx.Exec(sqlStr1, 1)
    if err != nil {
        fmt.Println("sqlstr1执行失败,err:", err)
        return
    }
    n, err := r.RowsAffected()
    if err != nil {
        return
    }
    if n != 1 {
        return errors.New("exec sqlStr1 failed")
    }
    return err
}

func main() {
    err := initDB()
    if err != nil {
        fmt.Printf("init DB failed,err:%v\n", err)
        return
    }
    //查询单条
    // queryDemo()
    // queryMultiDemo()
    // insertRowDemo()
    // updateRowDemo()
    // transDemo()
    transDemo2()
}
    复制代码

     

    package main

    //sqlx示例

    import (
        "errors"
        "fmt"

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

    var DB *sqlx.DB

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

    func initDB() (err error) {
        dsn := "TestVenus:xxxxxxxxx@tcp(172.23.102.xx:3306)/go_db"
        DB, err = sqlx.Connect("mysql", dsn)
        if err != nil {
            return
        }
        return nil
    }

    //单条查询操作
    func queryDemo() {
        sqlStr := "select id,name,age from info where id = ?"
        user := User{}
        err := DB.Get(&user, sqlStr, 1)
        if err != nil {
            fmt.Printf("query failed,err:%v\n", err)
            return
        }
        fmt.Printf("user:%#v\n", user)
        fmt.Printf("user:%v\n", user)
    }

    //查询多条
    func queryMultiDemo() {
        sqlStr := "select id,name,age from info where id > ?"
        var users []User
        err := DB.Select(&users, sqlStr, 0)
        if err != nil {
            fmt.Println("query multi failed,err:%v\n", err)
            return
        }
        fmt.Printf("users: %v\n", users)
        for _, user := range users {
            fmt.Printf("user:%v\n", user)
        }
    }

    // 插入数据
    func insertRowDemo() {
        sqlStr := "insert into info(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 info set age=? where id = ?"
        ret, err := DB.Exec(sqlStr, 39, 1)
        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 info where id = ?"
        ret, err := DB.Exec(sqlStr, 1)
        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 transDemo() {
        tx, err := DB.Beginx() //开启事务
        // tx.MustExec() //带Must的一般标识出错就直接panic
        if err != nil {
            if tx != nil {
                tx.Rollback()
            }
            fmt.Printf("begin trans failed,err:%v\n", err)
            return
        }
        sql1 := "update info set age=age-? where id=?"
        tx.MustExec(sql1, 2, 1)
        err = tx.Commit()
        if err != nil {
            tx.Rollback()
            fmt.Printf("commit failed,err:%v\n", err)
        }
        fmt.Println("数据更新成功")

    }

    //事务操作2
    func transDemo2() (err error) {
        tx, err := DB.Beginx()
        if err != nil {
            fmt.Println("begin trans failed,err:", err)
            return
        }

        defer func() {
            if p := recover(); p != nil {
                tx.Rollback()
                panic(p)
            } else if err != nil {
                fmt.Println("rollback")
                tx.Rollback()
            } else {
                err = tx.Commit()
                fmt.Println("sql commit")
            }
        }()

        sqlStr1 := "update info set age=99 where id = ?"
        r, err := tx.Exec(sqlStr1, 1)
        if err != nil {
            fmt.Println("sqlstr1执行失败,err:", err)
            return
        }
        n, err := r.RowsAffected()
        if err != nil {
            return
        }
        if n != 1 {
            return errors.New("exec sqlStr1 failed")
        }
        return err
    }

    func main() {
        err := initDB()
        if err != nil {
            fmt.Printf("init DB failed,err:%v\n", err)
            return
        }
        //查询单条
        // queryDemo()
        // queryMultiDemo()
        // insertRowDemo()
        // updateRowDemo()
        // transDemo()
        transDemo2()
    }
    posted @   wushaoyu  阅读(215)  评论(0)  编辑  收藏  举报
    相关博文：
    ·  Go语言sqlx包
    ·  golang连接操作mysql
    ·  09-golang连接mysql数据库-03-sqlx
    ·  golang操作mysql
    ·  【go笔记】使用sqlx操作MySQL
    阅读排行：
    · TypeScript + Deepseek 打造卜卦网站：技术与玄学的结合
    · Manus的开源复刻OpenManus初探
    · AI 智能体引爆开源社区「GitHub 热点速览」
    · 三行代码完成国际化适配，妙~啊~
    · .NET Core 中如何实现缓存的预热？
    ← → ↓ ↑
    点击右上角即可分享
    微信分享提示
    SQL AI 助手