• mysql_example

     

    package main
    
    import (
        "database/sql"
        "fmt"
    
        _ "github.com/go-sql-driver/mysql"
    )
    
    var DB *sql.DB
    
    func initDb() error {
        var err error
        dsn := "root:[email protected](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()
    }

     

    package main
    
    import (
        "database/sql"
        "fmt"
    
        _ "github.com/go-sql-driver/mysql"
        "github.com/jmoiron/sqlx"
    )
    
    var DB *sqlx.DB
    
    func initDb() error {
        var err error
        dsn := "root:[email protected](localhost:3306)/golang_db"
        DB, err = sqlx.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:"name"`
        Age  int            `db:"age"`
    }
    
    func testQuery() {
        sqlstr := "select id, name, age from user where id=?"
        var user User
    
        err := DB.Get(&user, sqlstr, 2)
        if err != nil {
            fmt.Printf("get failed, err:%v\n", err)
            return
        }
    
        fmt.Printf("user:%#v\n", user)
    }
    
    func testQueryMulti() {
        sqlstr := "select id, name, age from user where id>?"
        var user []User
    
        err := DB.Select(&user, sqlstr, 1)
        if err != nil {
            fmt.Printf("get failed, err:%v\n", err)
            return
        }
    
        fmt.Printf("user:%#v\n", user)
    }
    
    func testUpdate() {
        sqlstr := "update user set name=? where id=?"
        result, err := DB.Exec(sqlstr, "abc", 1)
        if err != nil {
            fmt.Printf("update failed, err:%v\n", err)
            return
        }
        count, err := result.RowsAffected()
        if err != nil {
            fmt.Printf("affected rows failed, err:%v\n", err)
            return
        }
        fmt.Printf("affect rows:%d\n", count)
    }
    
    func queryDB(name string) {
        sqlstr := fmt.Sprintf("select id, name, age from user where name=‘%s‘", name)
        fmt.Printf("sql:%s\n", sqlstr)
        var user []User
        err := DB.Select(&user, sqlstr)
        if err != nil {
            fmt.Printf("select failed, err:%v\n", err)
            return
        }
    
        for _, v := range user {
            fmt.Printf("user:%#v\n", v)
        }
    }
    
    func queryDBBySqlx(name string) {
        sqlstr := "select id, name, age from user where name=?"
        //fmt.Printf("sql:%s\n", sqlstr)
        var user []User
        err := DB.Select(&user, sqlstr, name)
        if err != nil {
            fmt.Printf("select failed, err:%v\n", err)
            return
        }
    
        for _, v := range user {
            fmt.Printf("user:%#v\n", v)
        }
    }
    
    func testSqlInject() {
        //queryDB("abc‘ or 1 = 1 #")
        //queryDB("name=abc‘ and (select count(*) from user ) < 10#")
        //queryDB("name=123‘ union select *from user #")
        queryDBBySqlx("name=123‘ union select *from user #")
    }
    
    func main() {
    
        err := initDb()
        if err != nil {
            fmt.Printf("init db failed, err:%v\n", err)
            return
        }
    
        //testQuery()
        //testQueryMulti()
        //testUpdate()
        testSqlInject()
    }
    相关文章
    相关标签/搜索
    本站公众号
       欢迎关注本站公众号,获取更多程序园信息
    开发小院
    香港藏宝阁精选资料大全 内丘县| 连江县| 奉化市| 德昌县| 彭水| 海宁市| 迁西县| 萨迦县| 广南县| 汉寿县| 海原县| 溧水县| 汝州市| 玛纳斯县| 乌恰县| 安丘市| 岳普湖县| 山阳县| 县级市| 车险| 林西县| 厦门市| 福州市| 伊宁市| 南木林县| 灵台县| 河曲县| 怀安县| 汉源县| 石家庄市| 东源县| 济南市| 镇安县| 故城县| 北安市| 高安市| 衡东县| http://fa.hz0j1r5vo.fun http://fa.hz0j1r7vo.fun http://fa.hz0j2r5vo.fun http://fa.hz0j2r5vo.fun http://fa.hz0j0r0vo.fun