go操作 mysql示例

出处：http://www.01happy.com/golang-mysql-demo/

修改案例：

package main

import (

    "database/sql"

    "fmt"

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

)

func main() {

    insert()

}

func insert() {

    db, err := sql.Open("mysql", "root:000000@/test?charset=utf8")

    checkErr(err)

    cr_table := "create table if not exists user(user_id int auto_increment primary key, user_name varchar(128),user_age int,user_sex int not null default 0)"

    stmt1, err := db.Prepare(cr_table)

    checkErr(err)

    _, err = stmt1.Exec()

    checkErr(err)

    stmt, err := db.Prepare(`INSERT user (user_name,user_age,user_sex) values (?,?,?)`)

    checkErr(err)

    res, err := stmt.Exec("费大幅", 20, 1)

    checkErr(err)

    id, err := res.LastInsertId()

    checkErr(err)

    fmt.Println(id)

    //下面取消注释进入查询

    //query()

}

func query() {

    db, err := sql.Open("mysql", "root:000000@/test?charset=utf8")

    checkErr(err)

    rows, err := db.Query("SELECT * FROM user where user_name ='费大幅'")

    checkErr(err)

    for rows.Next() {

        var userId int

        var userName string

        var userAge int

        var userSex int

        rows.Columns()

        err = rows.Scan(&userId, &userName, &userAge, &userSex)

        checkErr(err)

        fmt.Println(userId, userName, userAge, userSex)

    }

    //下面取消注释进入更改

    //update()

}

func update() {

    db, err := sql.Open("mysql", "root:000000@/test?charset=utf8")

    checkErr(err)

    stmt, err := db.Prepare(`UPDATE user SET user_name=?,user_age=?,user_sex=? WHERE user_id=?`)

    checkErr(err)

    res, err := stmt.Exec("胖大海", 18, 3, 10)

    checkErr(err)

    num, err := res.RowsAffected()

    checkErr(err)

    fmt.Println(num)

    //下面取消注释进入删除

    //remove()

}

func check() {

    db, err := sql.Open("mysql", "root:000000@/test?charset=utf8")

    checkErr(err)

    rows, err := db.Query("SELECT * FROM user")

    checkErr(err)

    for rows.Next() {

        var userId, userAge, userSex int

        var userName string

        rows.Columns()

        err = rows.Scan(&userId, &userName, &userAge, &userSex)

        checkErr(err)

        fmt.Println(userId, userName, userAge, userSex, "update")

    }

}

func remove() {

    db, err := sql.Open("mysql", "root:000000@/test?charset=utf8")

    checkErr(err)

    check()

    stmt, err := db.Prepare(`DELETE FROM user WHERE user_name=?`)

    checkErr(err)

    res, err := stmt.Exec("sony")

    checkErr(err)

    num, err := res.RowsAffected()

    checkErr(err)

    fmt.Println(num)

    check()

}

func checkErr(err error) {

    if err != nil {

        panic(err)

    }

}