Go 连接数据库的增删改查

package main

import (
    "database/sql"
    "fmt"
    _ "github.com/go-sql-driver/mysql"
)

var db *sql.DB

func initDB() (err error) {
    dsn := "root:123456@tcp(127.0.0.1)/ssmd"
    db, err = sql.Open("mysql", dsn)
    if err != nil {
        //格式不正确
        //fmt.Printf("open %s ,err %s", dsn, err)
        return err
    }
    //ping数据库
    err = db.Ping()
    if err != nil {
        //验证是否正确
        //fmt.Printf("open %s ,failad err %s", dsn, err)
        return err
    }
    //设置最大连接数量
    db.SetMaxOpenConns(10)
    fmt.Println("数据库连接成功")
    return
}

var u1 user

func queryRew(id int) {
    sqlStr := "SELECT * FROM `user` WHERE id =?"
    db.QueryRow(sqlStr, id).Scan(&u1.id, &u1.name, &u1.age)
    //rowObj
    fmt.Printf("u1 %#v\n", u1)
}

func query() {
    sqlStr := "SELECT * FROM `user`"
    rows, _ := db.Query(sqlStr)
    defer rows.Close()
    //    循环取值
    for rows.Next() {
        rows.Scan(&u1.id, &u1.name, u1.age)
        fmt.Println(u1)

    }
}
func insert(name string, age int) {
    sqlStr := "INSERT INTO `ssmd`.`user` (`name`, `age`) VALUES (?, ?)"
    ert, err := db.Exec(sqlStr, name, age)
    if err != nil {
        fmt.Println("新增失败")
        return
    }
    id, _ := ert.LastInsertId()
    fmt.Println(id, "新增")
}
func Dle(id int) {
    sqlStr := "DELETE FROM `ssmd`.`user` WHERE `id` = ?"
    del, _ := db.Exec(sqlStr, id)
    insertId, err := del.LastInsertId()
    if err != nil {
        return
    }
    fmt.Println(insertId, "删除")

}
func XiuGai(name string, age, id int) {
    sqlStr := "UPDATE `ssmd`.`user` SET `name` = ?, `age` = ? WHERE `id` = ?"
    exec, err := db.Exec(sqlStr, name, age, id)
    if err != nil {
        return
    }
    insertId, err := exec.LastInsertId()
    if err != nil {
        return
    }
    fmt.Println(insertId, "修改")

}

type user struct {
    id   int
    name string
    age  int
}

func main() {

    err := initDB()
    if err != nil {
        fmt.Printf("init DB failed ,err :%v\n", err)
    }
    for {
        fmt.Println("输入你的操作【1】查询【2】新增【3】修改【4】删除【5退出】【11】查询多条")
        var shu int
        fmt.Scanln(&shu)
        switch shu {
        case 1:
            fmt.Println("输入你要查询的id")
            var id int
            fmt.Scanln(&id)
            queryRew(id)
            continue
        case 11:
            query()
            continue
        case 2:
            var (
                name string
                age  int
            )
            fmt.Print("输入名字")
            fmt.Scanln(&name)
            fmt.Print("输入年龄")
            fmt.Scanln(&age)
            insert(name, age)
            continue
        case 3:
            var (
                name    string
                id, age int
            )
            fmt.Println("输入名字")
            fmt.Scanln(&name)
            fmt.Println("输入年龄")
            fmt.Scanln(&age)
            fmt.Println("输入ID")
            fmt.Scanln(&id)
            XiuGai(name, age, id)
            continue
        case 4:
            var id int
            fmt.Println("输入删除id")
            fmt.Scanln(&id)
            Dle(id)
            continue
        case 5:
            return

        default:
            fmt.Println("输入错误")
            continue

        }

    }
}