美文网首页Golang程序员go语言学习
Golang 学习笔记（10）—— mysql操作

Golang 学习笔记（10）—— mysql操作

作者: ChainZhang | 来源:发表于2018-01-04 15:43 被阅读107次

    本文为转载，原文：Golang 学习笔记（10）—— mysql操作

    Golang

    go-sql-driver/mysql

    go操作mysql的驱动包很多，这里讲解当下比较流行的go-sql-driver/mysql

    安装

    执行下面两个命令：

    go get github.com/go-sql-driver/mysql  //下载
go install github.com/go-sql-driver/mysql //安装

    安装完成以后的文件截图


    安装完成
    安装完成

    使用

    package

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

    数据库

    在mysql中建一张测试的表，sql如下：

    CREATE TABLE `userinfo` (
    `uid` INT(10) NOT NULL AUTO_INCREMENT,
    `username` VARCHAR(64) NULL DEFAULT NULL,
    `departname` VARCHAR(64) NULL DEFAULT NULL,
    `created` DATE NULL DEFAULT NULL,
    PRIMARY KEY (`uid`)
)

    连接

    db, err := sql.Open("mysql", "用户名:密码@tcp(IP:端口)/数据库?charset=utf8")

    insert

    有2种方法。

    1. 直接使用Exec函数添加
    result, err := db.Exec("INSERT INTO userinfo (username, departname, created) VALUES (?, ?, ?)","chain","dev","2018-01-04")
    1. 首先使用Prepare获得stmt，然后调用Exec添加
    stmt, err := db.Prepare("INSERT userinfo SET username=?,departname=?,created=?")
res, err := stmt.Exec("iris", "test", "2018-01-04")
    • 另一个经常用到的功能，获得刚刚添加数据的自增ID
    id, err := res.LastInsertId()
    • 示例
    package main

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

var (
    dbhost = "xxx.xxx.xxx.xxx:3306"
    dbusername = "xxxx"
    dbpassword = "xxxx"
    dbname = "xxx"
)


func main(){
    Insert("chain", "dev", "1")
    Insert("chain", "dev", "2")
    Insert("iris", "test", "1")
    Insert("iris", "test", "2")
}
/*
  获取sql.DB对象
*/
func GetDB() *sql.DB{
    db, err := sql.Open("mysql", fmt.Sprintf("%s:%s@tcp(%s)/%s?charset=utf8", dbusername, dbpassword, dbhost, dbname))
    CheckErr(err)
    return db
}

/*
  插入数据
*/
func Insert(username, departname, method string)bool{
    db := GetDB()
    defer db.Close()

    if method == "1"{
        _, err := db.Exec("insert into userinfo(username,departname,created) values(?,?,?)",username,departname,time.Now())
        if err != nil{
            fmt.Println("insert err: ", err.Error())
            return false
        }
        fmt.Println("insert success!")
        return true
    }else if method == "2"{
        stmt, err := db.Prepare("INSERT userinfo SET username=?,departname=?,created=?")
        if err != nil{
            fmt.Println("insert prepare error: ", err.Error())
            return false
        }
        _, err = stmt.Exec(username, departname, time.Now())
        if err != nil{
            fmt.Println("insert exec error: ", err.Error())
            return false
        }
        fmt.Println("insert success!")
        return true
    }
    return false
}
    运行结果
    数据库结果

    delete

    与insert所用的方法一致，只是将sql语句改为对应的功能就行。

    func main(){
    Delete(15)
}
/*
  根据id删除数据
*/
func Delete(id int) bool {
    db := GetDB()
    defer db.Close()

    stmt, err := db.Prepare("delete from userinfo where uid=?")
    if err != nil{
        fmt.Println("delete prepare error: ", err.Error())
        return false
    }
    _, err = stmt.Exec(id)
    if err != nil{
        fmt.Println("delete exec error: ", err.Error())
        return false
    }
    fmt.Println("delete success!")
    return true
}
    运行结果
    数据库结果

    update

    与insert所用的方法一致，只是将sql语句改为对应的功能就行。

    func main(){
    UpdateName(13,"chairis")
}
/*
  根据id，修改名称
*/
func UpdateName(id int, name string)bool{
    db := GetDB()
    defer db.Close()

    stmt, err := db.Prepare("update userinfo set username=? where uid=?")
    if err != nil{
        fmt.Println("update name prepare error: ", err.Error())
        return false
    }
    _, err = stmt.Exec(name, id)
    if err != nil{
        fmt.Println("update name exec error: ", err.Error())
        return false
    }
    fmt.Println("update name success!")
    return true
}
    运行结果
    数据库结果

    select

    • 查询单条数据，QueryRow 函数
    func main(){
    GetOne(13)
}
func GetOne(id int){
    db := GetDB()
    defer db.Close()
    var username, departname, created string
    err := db.QueryRow("select username, departname, created from userinfo where uid=?",id).Scan(&username, &departname, &created)
    if err != nil{
        fmt.Println("get one error: ", err.Error())
        return
    }
    fmt.Println("username: ", username, "departname: ", departname, "created: ", created)
}
    运行结果
    • 查询多条数据，并遍历
      Query 获取数据，for xxx.Next() 遍历数据
    func main(){
    GetAll()
}
func GetAll(){
    db := GetDB()
    defer db.Close()
    rows, err := db.Query("select username, departname, created from userinfo")
    if err != nil{
        fmt.Println("get all error: ", err.Error())
        return
    }
    for rows.Next(){
        var username, departname, created string
        if err := rows.Scan(&username, &departname, &created); err == nil{
            fmt.Println("username: ", username, "departname: ", departname, "created: ", created)
        }
    }
}
    运行结果

    事务

    在操作数据库之前执行，db.Begin()
    例：tx, err := db.Begin()
    保存到数据库：err := tx.Commit()
    回滚：err := tx.Rollback()
    注意设置事务以后操作数据库就不是db了，而是tx
    请看以下示例：

    func main(){
    Trans()
}

func Trans(){
    db := GetDB()
    defer db.Close();

    tx, err := db.Begin()
    if err != nil{
        fmt.Println("db.Begin error: ", err.Error())
        return
    }
    isCommit := true
    defer func(){
        if isCommit{
            tx.Commit()
            fmt.Println("commit")
        }else{
            tx.Rollback()
            fmt.Println("Rollback")
        }
    }()
    _, err = tx.Exec("insert into userinfo(username,departname,created) values(?,?,?)","username","departname",time.Now())
    if err != nil{
        isCommit = false
    }
    _, err = tx.Exec("insert into userinfo(username,departname,created) values(?,?,?)","username","departname",time.Now())
    if err != nil{
        isCommit = false
    }
    _, err = tx.Exec("insert into userinfo(username,departname,created) values(?,?,?)","username","departname",time.Now())
    if err != nil{
        isCommit = false
    }
}
    运行结果

    全部代码

    package main

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

var (
    dbhost = "xxx.xxx.xxx.xxx:3306"
    dbusername = "xxxxxx"
    dbpassword = "xxxxxx"
    dbname = "xxxxxx"
)


func main(){
    Trans()
}

func Trans(){
    db := GetDB()
    defer db.Close();

    tx, err := db.Begin()
    if err != nil{
        fmt.Println("db.Begin error: ", err.Error())
        return
    }
    isCommit := true
    defer func(){
        if isCommit{
            tx.Commit()
            fmt.Println("commit")
        }else{
            tx.Rollback()
            fmt.Println("Rollback")
        }
    }()
    _, err = tx.Exec("insert into userinfo(username,departname,created) values(?,?,?)","username","departname",time.Now())
    if err != nil{
        isCommit = false
    }
    _, err = tx.Exec("insert into userinfo(username,departname,created) values(?,?,?)","username","departname",time.Now())
    if err != nil{
        isCommit = false
    }
    _, err = tx.Exec("insert into userinfo(username,departname,created) values(?,?,?)","username","departname",time.Now())
    if err != nil{
        isCommit = false
    }
}

/*
  获取sql.DB对象
*/
func GetDB() *sql.DB{
    db, err := sql.Open("mysql", fmt.Sprintf("%s:%s@tcp(%s)/%s?charset=utf8", dbusername, dbpassword, dbhost, dbname))
    CheckErr(err)
    return db
}

/*
  插入数据
*/
func Insert(username, departname, method string)bool{
    db := GetDB()
    defer db.Close()

    if method == "1"{
        _, err := db.Exec("insert into userinfo(username,departname,created) values(?,?,?)",username,departname,time.Now())
        if err != nil{
            fmt.Println("insert err: ", err.Error())
            return false
        }
        fmt.Println("insert success!")
        return true
    }else if method == "2"{
        stmt, err := db.Prepare("INSERT userinfo SET username=?,departname=?,created=?")
        if err != nil{
            fmt.Println("insert prepare error: ", err.Error())
            return false
        }
        _, err = stmt.Exec(username, departname, time.Now())
        if err != nil{
            fmt.Println("insert exec error: ", err.Error())
            return false
        }
        fmt.Println("insert success!")
        return true
    }
    return false
}

/*
  根据id，修改名称
*/
func UpdateName(id int, name string)bool{
    db := GetDB()
    defer db.Close()

    stmt, err := db.Prepare("update userinfo set username=? where uid=?")
    if err != nil{
        fmt.Println("update name prepare error: ", err.Error())
        return false
    }
    _, err = stmt.Exec(name, id)
    if err != nil{
        fmt.Println("update name exec error: ", err.Error())
        return false
    }
    fmt.Println("update name success!")
    return true
}

/*
  根据id删除数据
*/
func Delete(id int) bool {
    db := GetDB()
    defer db.Close()

    stmt, err := db.Prepare("delete from userinfo where uid=?")
    if err != nil{
        fmt.Println("delete prepare error: ", err.Error())
        return false
    }
    _, err = stmt.Exec(id)
    if err != nil{
        fmt.Println("delete exec error: ", err.Error())
        return false
    }
    fmt.Println("delete success!")
    return true
}

func GetOne(id int){
    db := GetDB()
    defer db.Close()
    var username, departname, created string
    err := db.QueryRow("select username, departname, created from userinfo where uid=?",id).Scan(&username, &departname, &created)
    if err != nil{
        fmt.Println("get one error: ", err.Error())
        return
    }
    fmt.Println("username: ", username, "departname: ", departname, "created: ", created)
}

func GetAll(){
    db := GetDB()
    defer db.Close()
    
    rows, err := db.Query("select username, departname, created from userinfo")
    if err != nil{
        fmt.Println("get all error: ", err.Error())
        return
    }
    for rows.Next(){
        var username, departname, created string
        if err := rows.Scan(&username, &departname, &created); err == nil{
            fmt.Println("username: ", username, "departname: ", departname, "created: ", created)
        }
    }
    
}

func CheckErr(err error){
    if err != nil{
        fmt.Println("err: ", err.Error())
        panic(err)
    }
}

    源码

    github 源码地址

    完

    转载请注明出处：
    Golang 学习笔记（10）—— mysql操作

    目录
    上一节：Golang 学习笔记（09）—— json和xml解析
    下一节：Golang 学习笔记（11）—— 反射

    相关文章

      网友评论

        本文标题：Golang 学习笔记（10）—— mysql操作

        本文链接：https://www.haomeiwen.com/subject/yhbrnxtx.html

        延伸阅读

        深度阅读

        • 您也可以注册成为美文阅读网的作者，发表您的原创作品、分享您的心情！

        栏目导航

        热点阅读

        Golang

        程序员

        go语言学习

        Go语言

        Go知识库

        关于我们|服务条款|联系我们|Golang 学习笔记（10）—— mysql操作|投稿指南|网站地图|RSS订阅|排版工具|手机版

        提供经典美文摘抄,优美散文欣赏,现代诗歌精选,短篇小说,心情随笔,表白情书范文,故事会在线阅读欣赏

        Copyright © 2014-2023 Haomeiwen.com All Rights Reserved. 好美文阅读网 版权所有

        备案信息：桂公网安备 45052102000051号 · 桂ICP备13007215号-3

        本站所收录作品、热点评论等信息部分来源互联网，目的只是为了系统归纳学习和传递资讯

        所有作品版权归原创作者所有，与本站立场无关，如不慎侵犯了你的权益，请联系我们告知，我们将做删除处理！