Go语言学习之操作MYSQL实现CRUD

go get -u github.com/go-sql-driver/mysql

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

Open(driverName, dataSourceName string) (*DB, error)

package main

import (
    "database/sql"
    "fmt"
    _ "github.com/go-sql-driver/mysql"
    "time"
)
func main() {
    // 连接数据库
    open, err := sql.Open("mysql", "root:root@tcp(127.0.0.1:3306)/test?charset=utf8")
    checkError(err)
  //插入数据
    //add(open)
    // 更新数据
    //update(open)
    // 删除数据
    del(open)
}
//插入数据
func add(open *sql.DB)  {
    //插入数据
    prepare, err := open.Prepare("insert user set username=?,passWord=?,mobile=?,createtime=?")
    checkError(err)
    exec, err := prepare.Exec("李四", "123456", "17600000000", time.Now().Unix())
    checkError(err)
    id, err := exec.LastInsertId()
    checkError(err)
    fmt.Printf("插入数据ID: %d \n",id)
}
// 更新
func update(open *sql.DB)  {
    prepare, err := open.Prepare("update user set username=? where id =?")
    checkError(err)
    exec, err := prepare.Exec("王五", "18")
    checkError(err)
    rows, err := exec.RowsAffected()
    checkError(err)
    fmt.Printf("更新数据成功,影响条数 %d \n",rows)
}
// 删除数据
func del(open *sql.DB)  {
    prepare, err := open.Prepare("delete from user  where id =?")
    checkError(err)
    exec, err := prepare.Exec( "8")
    checkError(err)
    rows, err := exec.RowsAffected()
    checkError(err)
    fmt.Printf("删除数据成功,影响条数 %d \n",rows)
}
//检测错误
func checkError(err error)  {
    if err != nil {
        panic("操作失败:"+err.Error())
    }
}

package main
import (
    "database/sql"
    "fmt"
    _ "github.com/go-sql-driver/mysql"
)
func main() {
    // 连接数据库
    db, err := sql.Open("mysql", "root:root@tcp(127.0.0.1:3306)/nsbd_app?charset=utf8")
    checkError(err)
    //查询多条数据
    rows := queryRows(db)
    fmt.Printf("多条返回: \n%+v\n",rows)
    // 查询单条数据
    row := queryRow(db)
    fmt.Printf("单条返回: \n%+v\n",row)
}

// 创建表的映射对象
type User struct {
    Uid        int
    UserName   string
    CreateTime int
    Birthday   sql.NullString //有的值可能为NULL
}

//查询多条数据
func queryRows(db *sql.DB) []User {
    stmt, err := db.Prepare("select id,username,createtime,birthday from nsbd_user where id < ?")
    checkError(err)
    rows, err := stmt.Query(30)
    // 延迟关闭
    defer rows.Close()
    checkError(err)
    user := new(User)
    //users := make([]User,5)
    var users []User
    for rows.Next() {
        // rows.Scan()方法的参数顺序很重要，必须和查询结果的column相对应（数量和顺序都需要一致）
        err := rows.Scan(&user.Uid, &user.UserName, &user.CreateTime, &user.Birthday)
        checkError(err)
        users = append(users, *user)
    }
    return users
}
// 查询单条数据
func queryRow(db *sql.DB) User {
    stmt, err := db.Prepare("select id,username,createtime,birthday from nsbd_user where id = ?")
    checkError(err)
    user := new(User)
    err = stmt.QueryRow(4).Scan(&user.Uid, &user.UserName, &user.CreateTime, &user.Birthday)
    checkError(err)
    return *user
}
//检测错误
func checkError(err error) {
    if err != nil {
        panic("操作失败:" + err.Error())
    }
}