golang连接操作mysql

golang操作mysql

package main

import (
    "database/sql"
    "fmt"
    "time"

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

//定义一个全局db对象
var db *sql.DB

func initDB() (err error) {
    dsn := "user:password@tcp(172.xxx.102.xxx:3306)/go_db?charset=utf8mb4&parseTime=True"
    db, err = sql.Open("mysql", dsn)
    if err != nil {
        panic(err)
    }

    // fmt.Printf("db=%v", db)
    // defer db.Close()
    err = db.Ping()
    if err != nil {
        fmt.Printf("connect db failed:%v", err)
        return
    }
    db.SetMaxIdleConns(10)
    db.SetMaxOpenConns(200)
    db.SetConnMaxLifetime(time.Second * 10)
    return
}

// func insert() {
//     s := "insert into info(name,age) values(?,?)"
//     r, err := db.Exec(s, "zhangsan", 22)
//     if err != nil {
//         fmt.Printf("err:%v\n", err)
//     } else {
//         i, _ := r.LastInsertId()
//         fmt.Printf("i:%v\n", i)
//     }
// }

// 插入数据
func insertRowDemo() {
    sqlStr := "insert into info(id,name, age) values (?,?,?)"
    ret, err := db.Exec(sqlStr, 3, "王五", 38)
    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)

}

type User struct {
    id   int
    name string
    age  int
}

func queryOneRow() {
    s := "select * from info where id = ?"
    var u User
    err := db.QueryRow(s, 1).Scan(&u.id, &u.name, &u.age)
    if err != nil {
        fmt.Printf("err: %v\n", err)
    } else {
        fmt.Printf("u: %v\n", u)
    }
}

// 查询多条数据示例
func queryMultiRowDemo() {
    sqlStr := "select id, name, age from info where id > ?"
    rows, err := db.Query(sqlStr, 0)
    if err != nil {
        fmt.Printf("query failed, err:%v\n", err)
        return
    }
    // fmt.Printf("rows=%v", rows)
    // 非常重要：关闭rows释放持有的数据库链接
    defer rows.Close()

    // 循环读取结果集中的数据
    for rows.Next() {
        var u User
        err := rows.Scan(&u.id, &u.name, &u.age)
        if err != nil {
            fmt.Printf("scan failed, err:%v\n", err)
            return
        }
        fmt.Printf("id:%d name:%s age:%d\n", u.id, u.name, u.age)
    }
}

func update() {
    s := "update info set name=? where id=?"
    r, err := db.Exec(s, "王二麻子", 3)
    if err != nil {
        fmt.Printf("err: %v\n", err)
        return
    }
    i, err2 := r.RowsAffected()
    if err2 != nil {
        fmt.Println("get afectd rows failed")
        return
    }
    fmt.Println("update success,afected rows:%d\n", i)
}

// 删除数据
func deleteRowDemo() {
    sqlStr := "delete from info where id = ?"
    ret, err := db.Exec(sqlStr, 3)
    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)
}

// ### sql预处理
//预处理插入
func prepareInsertDemo() {
    sqlStr := "insert into info(id,name,age) values(?,?,?)"
    s, err := db.Prepare(sqlStr)
    if err != nil {
        fmt.Printf("prepare failed,err:%v\n", err)
        return
    }
    defer s.Close()
    _, err2 := s.Exec(10, "小王子", 20)
    if err2 != nil {
        fmt.Printf("insert failed,err2:%v\n", err2)
        return
    }
    fmt.Println("insert success...")
}

//预处理查询
func prepareQueryDemo() {
    sqlStr := "select id,name,age from info where id > ?"
    s, err := db.Prepare(sqlStr)
    if err != nil {
        fmt.Println("prepare failed,err:%v\n", err)
        return
    }
    defer s.Close()
    r, err2 := s.Query(0)
    if err2 != nil {
        fmt.Printf("query failed,err:$%v\n", err2)
        return
    }
    defer r.Close()
    //循环读取结果
    for r.Next() {
        var u User
        err := r.Scan(&u.id, &u.name, &u.age)
        if err != nil {
            fmt.Printf("scan failed,err:%v\n", err)
            return
        }
        fmt.Printf("id:%d name:%s age:%d\n", u.id, u.name, u.age)
    }
}

func main() {
    err := initDB() //调用初始化数据的函数
    if err != nil {
        fmt.Printf("init db failed,err:%v\n", err)
        return
    } else {
        fmt.Println("连接成功")
    }

    // insert()
    // insertRowDemo()
    // defer db.Close()
    // fmt.Println("===========")
    // queryOneRow()
    // queryMultiRowDemo()
    // update()
    // deleteRowDemo()
    // prepareInsertDemo()
    prepareQueryDemo()
}