golang sql 数据库链接

2019-02-27  本文已影响0人  copyLeft

sql

创建数据库对象


var (
    userName = "root"
    password = 1111
    ip       = "127.0.0.1"
    port     = "3306"
    dbName   = "todo"
    dbPath   = fmt.Sprintf("%s:%d@tcp(%s:%s)/%s?charset=utf8", userName, password, ip, port, dbName)
    driver   = "mysql"
)
    
    // 创建连接
    DB, dbOpenErr := sql.Open(driver, dbPath)
    
    // 关闭连接
    defer DB.Close() 
      
    if dbOpenErr != nil{
        fmt.Println("err: ", dbOpenErr)
    }

执行sql语句


    // 返回执行结果概述和错误信息
    if r, e := DB.Exec("UPDATE user SET age=100 WHERE id=4"); e != nil {
        fmt.Println("err: ", e)
    } else {
        fmt.Println("success: ", r)
    }

查询




type User struct {
    Id       int64  `json:"id"`
    Nickname string `json:"nickname"`
    Age      int    `json:"age"`
    Img      string `json:"img"`
}


    // QueryRow => *Row 查询单条数据
        DB, dbOpenErr := sql.Open(driver, dbPath)

    if dbOpenErr != nil {
        fmt.Println("open err: ", dbOpenErr)
    }
    
    
    r := DB.QueryRow("SELECT id, nickname FROM user")
    /*  带参数的查询
        ? 作为占位符号
        r := DB.QueryRow("SELECT ?, ? FROM ?", "id", "nickname", "user")
    */

    var user User

    r.Scan(&user.Id, &user.Nickname)

    fmt.Println(user)


// -------------------------------------


    // Query => Rows 返回多条匹配数据
    r, _ := DB.Query("SELECT id, nickname, age, img FROM user")

    users := []User{}
    
    //遍历数据行, Rows.Next() => bool 调用下一条 Row
    for r.Next() {

        var user User
        
        /* Rows.Scan(dest ...interface{}) 将数据注入参数中, 
        参数数量必须与查询结果结构相同.
        */
        if err := r.Scan(&user.Id, &user.Nickname, &user.Age, &user.Img); err != nil {
            fmt.Println("get err: ", err)
        } else {

            users = append(users, user)
        }

    }

执行事务



tx, err := DB.Begin()

    if err != nil {
        fmt.Println("begin error: ", err)
    }

    u := NewUser()

    // 查询数据
    cocoInfo := tx.QueryRow("SELECT nickname, age FROM user WHERE nickname='coco'")
    cocoInfo.Scan(&u.Nickname, &u.Age)
    fmt.Println("\ncocoInfo: ", u)

    // 执行sql语句
    tx.Exec("UPDATE user SET img='./head.png' WHERE id=4")

    // 提交
    tx.Commit()

其他

上一篇 下一篇

猜你喜欢

热点阅读