路漫漫其修远兮
吾将上下而求索
        找回密码

go学习：mysql操作

2018-01-04 分类：go 评论(0)

MySQL驱动安装 

Golang支持DB操作位于database包下,支持基本CRUD操作、事务和Prepared Statement,本文以MySQL为例。

使用数据库之前,必须安装相关MySQL数据驱动golang mysql driver 

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

代码中还需注册数据库驱动,通过引入空白倒入mysql包来完成

import(
"database/sql"
// 引入数据库驱动注册及初始化
_ "github.com/go-sql-driver/mysql" ) 
这段空白倒入代码实际执行mysql包的初始化代码,位于%GOPATH%/github.com/go-sql-driver/mysql/driver.go

func init() {
    sql.Register("mysql", &MySQLDriver{})
}

准备测试数据

连接MySQL 
mysql -uroot -p 
选择数据库test 
use test 
创建测试用的users表和order表，并插入测试数据

#创建user表
DROP TABLE IF EXISTS `order`;
DROP TABLE IF EXISTS `user`;

CREATE TABLE IF NOT EXISTS `user` (
`uid` SERIAL PRIMARY KEY, 
`name` VARCHAR(20) NOT NULL, 
`password` VARCHAR(20) NOT NULL
) ENGINE=`innodb`, CHARACTER SET=utf8;

#创建order表
CREATE TABLE IF NOT EXISTS `order`(
`oid` SERIAL PRIMARY KEY, 
`uid`  BIGINT(20) UNSIGNED NOT NULL,
`date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, 
FOREIGN KEY (`uid`) REFERENCES `user`(`uid`)
)ENGINE=innodb,CHARACTER SET=utf8;

#插入测试数据
INSERT INTO  `user`(`name`,`password`) VALUES('nick', 'nick'),('jacky', 'jacky');
INSERT INTO `order`(`uid`) VALUES(1),(2);

连接数据库 

连接数据的DSN格式为: 

username:password@protocol(address)/dbname?param=value

示例代码：

db, err := sql.Open("mysql", "root:root@tcp(127.0.0.1:3306)/test?charset=utf8")
if err != nil {
    fmt.Println("failed to open database:", err.Error())
    return
}
if err := db.Ping(); err != nil {
    fmt.Println("%s error ping database: %s", err.Error())
    return
}
defer db.Close()

返回的DB对象,实际封装了一个数据库连接池,对于goroutine是线程安全的，可以放心使用。这个数据库连接池由"database/sql"包负责自动创建和回收。连接池的大小可以由SetMaxIdleConns指定。 

需要注意的是，创建DB对象成功，并不代表已经成功的连接了数据库，数据库连接只有在真正需要的时候才会被创建。因此如果，在创建DB对象后想验证数据库连接是否有效，可以调用Ping()或者通过

关闭数据库

defer db.Close()

关闭数据库并释放全部已打开的资源。实际上，很少需要进行关闭操作，DB对象实际上长期存活并在所有的goroutine之间共享

–

DB中执行SQL通过Exec和Query方法，查询操作是通过Query完成，它会返回一个sql.Rows的结果集，包含一个游标用来遍历查询结果；Exec方法返回的是sql.Result对象，用于检测操作结果，及被影响记录数

查询

// 获取USERS表中的前十行记录
rows, err := db.Query("SELECT * FROM user")
if err != nil {
    fmt.Println("fetech data failed:", err.Error())
    return
}
defer rows.Close()
for rows.Next() {
    var uid int
    var name, password string
    rows.Scan(&uid, &name, &password)
    fmt.Println("uid:", uid, "name:", name, "password:", password)
}

注意：rows必须进行关闭否则会导致数据库连接无法关闭，长时间运行会导致"too many connections" 

插入

// 插入一条新数据
result, err := db.Exec("INSERT INTO `user`(`name`,`password`) VALUES('tom', 'tom')")
if err != nil {
    fmt.Println("insert data failed:", err.Error())
    return
}
id, err := result.LastInsertId()
if err != nil {
    fmt.Println("fetch last insert id failed:", err.Error())
    return
}
fmt.Println("insert new record", id)

更新

// 更新一条数据
result, err = db.Exec("UPDATE `user` SET `password`=? WHERE `name`=?", "tom_new_password", "tom")
if err != nil {
    fmt.Println("update data failed:", err.Error())
    return
}
num, err := result.RowsAffected()
if err != nil {
    fmt.Println("fetch row affected failed:", err.Error())
    return
}
fmt.Println("update recors number", num)

删除

// 删除数据
result, err = db.Exec("DELETE FROM `user` WHERE `name`=?", "tom")
if err != nil {
    fmt.Println("delete data failed:", err.Error())
    return
}
num, err = result.RowsAffected()
if err != nil {
    fmt.Println("fetch row affected failed:", err.Error())
    return
}
fmt.Println("delete record number", num)

事务支持 

sql.Tx用来支持事务处理

// 事务处理
// 完全删除用户编号为2的用户数据
tx, err := db.Begin()
result, err = tx.Exec("DELETE FROM `order` WHERE uid=? ", 2)
if err != nil {
    fmt.Println("delete data failed:", err.Error())
    return
}
num, err = result.RowsAffected()
if err != nil {
    fmt.Println("fetch row affected failed:", err.Error())
    return
}
fmt.Println("delete record number", num)
result, err = tx.Exec("DELETE FROM `user` WHERE uid=? ", 2)
if err != nil {
    fmt.Println("delete data failed:", err.Error())
    return
}
num, err = result.RowsAffected()
if err != nil {
    fmt.Println("fetch row affected failed:", err.Error())
    return
}
fmt.Println("delete record number", num)
// 根据条件回滚或者提交
// tx.Rollback()
tx.Commit()
Prepared Statement 
sql.Stmt支持预备表达式，可以用来优化SQL查询提高性能，减少SQL注入的风险, DB.Prepare()和Tx.Prepare()都提供了对于预备表达式的支持。

// 预备表达式
stmt, err := db.Prepare("DELETE FROM `order` WHERE `oid`=?")
if err != nil {
    fmt.Println("fetch row affected failed:", err.Error())
    return
}
result, err = stmt.Exec(1)
if err != nil {
    fmt.Println("delete data failed:", err.Error())
    return
}
num, err = result.RowsAffected()
if err != nil {
    fmt.Println("fetch row affected failed:", err.Error())
    return
}
fmt.Println("delete record number", num)

效率分析

问题：db.exec和statement.exec和tx.exec的区别？（此例子和上面的例子不是同一个，区别对待）

准备工作

use test;
create table if not exists user (uid int(10) primary key auto_increment, username varchar(50) not null, age int(10));

–

package main

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

var db = &sql.DB{}

func init(){
    db ,_ = sql.Open("mysql", "root:123@tcp(192.168.170.10:3306)/test")
}

func main() {
    insert()
    fmt.Println()
    query()
    fmt.Println()
    update()
    fmt.Println()
    query()
    fmt.Println()
    delete()
}

func update(){
    //方式1 update
    start := time.Now()
    for i := 1001;i<=1100;i++{
        db.Exec("update user set age=? where uid=? ",i,i)
    }
    end := time.Now()
    fmt.Println("方式1 update total time:",end.Sub(start).Seconds())

    //方式2 update
    start = time.Now()
    for i := 1101;i<=1200;i++{
        stm,_ := db.Prepare("update user set age=? where uid=? ")
        stm.Exec(i,i)
        stm.Close()
    }
    end = time.Now()
    fmt.Println("方式2 update total time:",end.Sub(start).Seconds())

    //方式3 update
    start = time.Now()
    stm,_ := db.Prepare("update user set age=? where uid=?")
    for i := 1201;i<=1300;i++{
        stm.Exec(i,i)
    }
    stm.Close()
    end = time.Now()
    fmt.Println("方式3 update total time:",end.Sub(start).Seconds())

    //方式4 update
    start = time.Now()
    tx,_ := db.Begin()
    for i := 1301;i<=1400;i++{
        tx.Exec("update user set age=? where uid=?",i,i)
    }
    tx.Commit()

    end = time.Now()
    fmt.Println("方式4 update total time:",end.Sub(start).Seconds())

    //方式5 update
    start = time.Now()
    for i := 1401;i<=1500;i++{
        tx,_ := db.Begin()
        tx.Exec("update user set age=? where uid=?",i,i)
        tx.Commit()
    }
    end = time.Now()
    fmt.Println("方式5 update total time:",end.Sub(start).Seconds())

}

func delete(){
    //方式1 delete
    start := time.Now()
    for i := 1001;i<=1100;i++{
        db.Exec("delete from user where uid=?",i)
    }
    end := time.Now()
    fmt.Println("方式1 delete total time:",end.Sub(start).Seconds())

    //方式2 delete
    start = time.Now()
    for i := 1101;i<=1200;i++{
        stm,_ := db.Prepare("delete from user where uid=?")
        stm.Exec(i)
        stm.Close()
    }
    end = time.Now()
    fmt.Println("方式2 delete total time:",end.Sub(start).Seconds())

    //方式3 delete
    start = time.Now()
    stm,_ := db.Prepare("delete from user where uid=?")
    for i := 1201;i<=1300;i++{
        stm.Exec(i)
    }
    stm.Close()
    end = time.Now()
    fmt.Println("方式3 delete total time:",end.Sub(start).Seconds())

    //方式4 delete
    start = time.Now()
    tx,_ := db.Begin()
    for i := 1301;i<=1400;i++{
        tx.Exec("delete from user where uid=?",i)
    }
    tx.Commit()

    end = time.Now()
    fmt.Println("方式4 delete total time:",end.Sub(start).Seconds())

    //方式5 delete
    start = time.Now()
    for i := 1401;i<=1500;i++{
        tx,_ := db.Begin()
        tx.Exec("delete from user where uid=?",i)
        tx.Commit()
    }
    end = time.Now()
    fmt.Println("方式5 delete total time:",end.Sub(start).Seconds())

}

func query(){

    //方式1 query
    start := time.Now()
    rows,err := db.Query("select uid,username from user")
    if err != nil {
        fmt.Println("fetech data failed:", err.Error())
        return
    }
    defer rows.Close()
    for rows.Next(){
        var name string
        var id int
        if err := rows.Scan(&id,&name); err != nil {
            fmt.Println("scan data failed:", err)
        }
        //fmt.Printf("name:%s ,id:is %d\n", name, id)
    }
    end := time.Now()
    fmt.Println("方式1 query total time:",end.Sub(start).Seconds())

    //方式2 query
    start = time.Now()
    stm,_ := db.Prepare("select uid,username from user")
    defer stm.Close()
    rows,_ = stm.Query()
    defer rows.Close()
    for rows.Next(){
        var name string
        var id int
        if err := rows.Scan(&id,&name); err != nil {
            log.Fatal(err)
        }
        // fmt.Printf("name:%s ,id:is %d\n", name, id)
    }
    end = time.Now()
    fmt.Println("方式2 query total time:",end.Sub(start).Seconds())


    //方式3 query
    start = time.Now()
    tx,_ := db.Begin()
    defer tx.Commit()
    rows,_ = tx.Query("select uid,username from user")
    defer rows.Close()
    for rows.Next(){
        var name string
        var id int
        if err := rows.Scan(&id,&name); err != nil {
            log.Fatal(err)
        }
        //fmt.Printf("name:%s ,id:is %d\n", name, id)
    }
    end = time.Now()
    fmt.Println("方式3 query total time:",end.Sub(start).Seconds())
}

func insert() {

    //方式1 insert
    //strconv,int转string:strconv.Itoa(i)
    start := time.Now()
    for i := 1001;i<=1100;i++{
        db.Exec("insert into user(uid,username,age) values(?,?,?)",i,"user"+strconv.Itoa(i),i-1000)
    }
    end := time.Now()
    fmt.Println("方式1 insert total time:",end.Sub(start).Seconds())

    //方式2 insert
    start = time.Now()
    for i := 1101;i<=1200;i++{
        stm,_ := db.Prepare("insert into user(uid,username,age) values(?,?,?)")
        stm.Exec(i,"user"+strconv.Itoa(i),i-1000)
        stm.Close()
    }
    end = time.Now()
    fmt.Println("方式2 insert total time:",end.Sub(start).Seconds())

    //方式3 insert
    start = time.Now()
    stm,_ := db.Prepare("insert into user(uid,username,age) values(?,?,?)")
    for i := 1201;i<=1300;i++{
        stm.Exec(i,"user"+strconv.Itoa(i),i-1000)
    }
    stm.Close()
    end = time.Now()
    fmt.Println("方式3 insert total time:",end.Sub(start).Seconds())

    //方式4 insert
    start = time.Now()
    //Begin函数内部会去获取连接
    tx,_ := db.Begin()
    for i := 1301;i<=1400;i++{
        tx.Exec("insert into user(uid,username,age) values(?,?,?)",i,"user"+strconv.Itoa(i),i-1000)
    }
    //最后释放tx内部的连接
    tx.Commit()

    end = time.Now()
    fmt.Println("方式4 insert total time:",end.Sub(start).Seconds())

    //方式5 insert
    start = time.Now()
    for i := 1401;i<=1500;i++{
        tx,_ := db.Begin()
        tx.Exec("insert into user(uid,username,age) values(?,?,?)",i,"user"+strconv.Itoa(i),i-1000)
        //Commit执行后连接也释放了
        tx.Commit()
    }
    end = time.Now()
    fmt.Println("方式5 insert total time:",end.Sub(start).Seconds())
}

结果

[root@master ~]#go run abc.go 
方式1 insert total time: 0.324389662
方式2 insert total time: 0.045003734
方式3 insert total time: 0.01829143
方式4 insert total time: 0.031759063
方式5 insert total time: 0.048263937

方式1 query total time: 0.000801736
方式2 query total time: 0.001953356
方式3 query total time: 0.001642992

方式1 update total time: 0.034765841
方式2 update total time: 0.031339371
方式3 update total time: 0.017740032
方式4 update total time: 0.03041274
方式5 update total time: 0.049557649

方式1 query total time: 0.000704776
方式2 query total time: 0.000980934
方式3 query total time: 0.001797192

方式1 delete total time: 0.033581878
方式2 delete total time: 0.031594028
方式3 delete total time: 0.018266297
方式4 delete total time: 0.030766535
方式5 delete total time: 0.049880578

参考文档：

https://www.zybuluo.com/NickYu/note/58572

https://studygolang.com/articles/3022

–

–

–

未经允许不得转载：江哥架构师笔记 » go学习：mysql操作

分享到：更多 ()

评论 抢沙发

评论前必须登录！