go 连接数据库，增删改查

go 连接数据库，增删改查

package main

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

// 表对应的结构体
type User struct {
    Id   int     `json:"id"`
    Name string  `json:"name"`
    Gold float64 `json:"gold"`
}

var db *sql.DB

// 连接数据库
func initDB() (err error) {
    fmt.Println("测试数据库连接")
    db, err = sql.Open("mysql", "root:@tcp(localhost:3306)/godb?charset=utf8mb4&parseTime=True")
    if err != nil {
       fmt.Println(err)
    }
    err = db.Ping()
    if err != nil {
       fmt.Println(err)
    } else {
       fmt.Println("链接成功")
    }
    return err
}

// 插入数据
func insertData() {
    // id没设置成主键自增的情况下需要添加
    sqlStr := "insert into user (id,name,gold) values (?, ?, ?)"
    res, err := db.Exec(sqlStr, 10, "zhangsan", 6.66)
    if err != nil {
       fmt.Println(err)
       return
    }
    theId, err := res.LastInsertId() // 获取新增数据的id 「当id为自增的才行。」
    fmt.Println("新增数据的id:", theId)
}

// 更新数据
func updateData(name string, gold float64) {
    sqlStr := "update user set name = ? , gold = ? where id = 3"
    res, err := db.Exec(sqlStr, name, gold)
    if err != nil {
       fmt.Println(err)
       return
    }
    rows, err := res.RowsAffected()
    if err != nil {
       fmt.Println(err)
       return
    }
    fmt.Printf("更新成功，更新的行数：==%v", rows)
}

// 删除数据
func deleteData(id int) {
    sqlStr := "delete from user where id = ?"
    res, err := db.Exec(sqlStr, id)
    if err != nil {
       fmt.Println(err)
       return
    }
    rows, err := res.RowsAffected()
    if err != nil {
       fmt.Println(err)
       return
    }
    fmt.Printf("删除成功，删除的行数：==%v\n", rows)
}

// 查询一行数据
func queryOneRow() {
    sqlStr := "select * from user where id = ?"
    var u User
    err := db.QueryRow(sqlStr, 3).Scan(&u.Id, &u.Name, &u.Gold)
    if err != nil {
       fmt.Println(err)
       return
    }
    fmt.Printf("user===%v", u)
}

// 查询多行
func queryManyRow() {
    sqlStr := "select * from user where id > ?"
    rows, err := db.Query(sqlStr, 0)
    defer rows.Close()
    if err != nil {
       fmt.Println(err)
       return
    }

    for rows.Next() {
       var u User
       err := rows.Scan(&u.Id, &u.Name, &u.Gold)
       if err != nil {
          fmt.Println(err)
          break
       }
       fmt.Printf("user: == %v \n", u)
    }
}

func main() {
    initDB()
    defer db.Close()
    queryManyRow()
    //insertData()
    //queryOneRow()
    //updateData("xiaoming", 26)
    deleteData(10)
    queryManyRow()
}