go mysql 操作

package main

import (
    "database/sql"
    "fmt"

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

type User struct {
    id, age, sex int
    name         string
}

var db *sql.DB //通用的连接池对象

func initDB() (err error) {
    dsn := "root:123456@tcp(127.0.0.1:3306)/db1?charset=utf8mb4&parseTime=True"

    db, err = sql.Open("mysql", dsn)

    if err != nil {
        panic("数据库连接失败!")
    }

    err = db.Ping()

    if err != nil {
        panic("连接数据库参数错误!")
    }

    return nil
}

func main() {

    err := initDB()
    if err != nil {
        panic("初始化数据库失败!")
    }
    fmt.Println("数据库连接成功")
    defer db.Close()

    insertOne()

    selectOne()

    upDateOne()

    deleteOne()

    selectAll()

    insertOne()

}

1：插入一条 db.Exec() ret.LastInsertId()

func insertOne() {

    sqlStr := "insert into user(id,age,name,sex) value(100,200,'cc',2)"

    ret, err := db.Exec(sqlStr)

    if err != nil {
        fmt.Println("插入语句错误!")
        return
    }

    id, err := ret.LastInsertId()

    if err != nil {
        fmt.Println("插入失败")
        return
    }
    fmt.Println("成功", id)
}

2：查询一条 db.QueryRow() db.Scan() 添加到结构体里面

type User struct {
    id, age, sex int
    name         string
}

func selectOne() {
    var u User

    sqlStr := "select age,id,name,sex from user where age = 200;"

    err := db.QueryRow(sqlStr).Scan(&u.id, &u.age, &u.name, &u.sex)

    if err != nil {
        fmt.Println("查询语句错误!")
        return
    }

    fmt.Printf("id:%d name:%s age:%d\n", u.id, u.name, u.age, u.sex)
}

3：更新一条 db.Exec() ret.RowsAffected()

func upDateOne() {

    sqlStr := "update user set id = 300 , name = '白酒永远的神' where age = 200"

    ret, err := db.Exec(sqlStr)

    if err != nil {
        fmt.Println("更新语句错误!")
        return
    }

    id, err := ret.RowsAffected()

    if err != nil {
        fmt.Println("更新失败", id)
        return
    }
    fmt.Println("更新成功", id)

}

4：删除一条 db.Exec() db.RowsAffected()

func deleteOne() {

    sqlStr := "delete from user where id >= 0"

    ret, err := db.Exec(sqlStr)

    if err != nil {
        fmt.Printf("删除语句错误!%s\n", err)
        return
    }

    id, err := ret.RowsAffected()

    if err != nil {
        fmt.Printf("删除失败!%s\n", err)
        return
    }

    fmt.Println("删除成功", id)
}

5：查询全部 db.Query() db.Scan()

func selectAll() {

    sqlStr := "select * from user"

    rows, err := db.Query(sqlStr)

    if err != nil {
        fmt.Printf("查询语句错误!%s\n", err)
        return
    }

    defer rows.Close()

    for rows.Next() {
        var u User
        err := rows.Scan(&u.id, &u.age, &u.name, &u.sex)

        if err != nil {
            fmt.Printf("查询失败!%s\n", err)
            return
        }
        fmt.Printf("id:%d name:%s age:%d\n", u)
    }
    fmt.Println("查询成功")
}