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

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种方法。

直接使用Exec函数添加 result, err := db.Exec("INSERT INTO userinfo (username, departname, created) VALUES (?, ?, ?)","chain","dev","2018-01-04") 首先使用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) } } 源码

作者：ChainZhang 链接：https://www.jianshu.com/p/e9709747f5a7 來源：简书 著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。