golang对mysql的基本操作

我的饭卡呢 · · 11987 次点击 · · 开始浏览    
这是一个创建于 的文章,其中的信息可能已经有所发展或是发生改变。

golang 操作mysql

导入数据库驱动

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

mysql连接配置

const (
    USERNAME = "root"
    PASSWORD = "*******"
    NETWORK  = "tcp"
    SERVER   = "localhost"
    PORT     = 3306
    DATABASE = "blog"
)

建立连接

    dsn := fmt.Sprintf("%s:%s@%s(%s:%d)/%s",USERNAME,PASSWORD,NETWORK,SERVER,PORT,DATABASE)
    DB,err := sql.Open("mysql",dsn)
    if err != nil{
        fmt.Printf("Open mysql failed,err:%v\n",err)
        return
    }
    DB.SetConnMaxLifetime(100*time.Second)  //最大连接周期,超过时间的连接就close
    DB.SetMaxOpenConns(100)//设置最大连接数
    DB.SetMaxIdleConns(16) //设置闲置连接数

数据模型

type User struct {
    ID int64 `db:"id"`
    Name sql.NullString  `db:"name"`  //由于在mysql的users表中name没有设置为NOT NULL,所以name可能为null,在查询过程中会返回nil,如果是string类型则无法接收nil,但sql.NullString则可以接收nil值
    Age int `db:"age"`
}

数据表结构

15318255510282.jpg

查询单行

func queryOne(DB *sql.DB){
        fmt.Println("query times:",i)
        user := new(User)
        row := DB.QueryRow("select * from users where id=?",1)
        //row.scan中的字段必须是按照数据库存入字段的顺序,否则报错
        if err :=row.Scan(&user.ID,&user.Name,&user.Age); err != nil{
            fmt.Printf("scan failed, err:%v",err)
            return
        }
        fmt.Println(*user)
    }
}

注意:
row必须scan,不然会导致连接无法关闭,会一直占用连接,直到超过设置的生命周期
下面是未scan导致连接被占用的,最大连接数为100

func queryOne(DB *sql.DB){
    for i:=0;i< 150;i++ {
        fmt.Println("query times:",i)
        user := new(User)
        row := DB.QueryRow("select * from users where id=?",1)
        continue
        if err :=row.Scan(&user.ID,&user.Name,&user.Age); err != nil{
            fmt.Printf("scan failed, err:%v",err)
            return
        }
        fmt.Println(*user)
    }
}

执行结果:


15318261414512.jpg

在执行到100后会发生阻塞,等待连接池释放。

查询多行

func queryMulti(DB *sql.DB){
        user := new(User)
        rows, err := DB.Query("select * from users where id > ?", 1)
        defer func() {
            if rows != nil {
                rows.Close() //可以关闭掉未scan连接一直占用
            }
        }()
        if err != nil {
            fmt.Printf("Query failed,err:%v", err)
            return
        }
        for rows.Next() {
            err = rows.Scan(&user.ID, &user.Name, &user.Age) //不scan会导致连接不释放
            if err != nil {
                fmt.Printf("Scan failed,err:%v", err)
                return
            }
            fmt.Print(*user)
        }
}

插入数据

func insertData(DB *sql.DB){
    result,err := DB.Exec("insert INTO users(name,age) values(?,?)","YDZ",23)
    if err != nil{
        fmt.Printf("Insert failed,err:%v",err)
        return
    }
    lastInsertID,err := result.LastInsertId()  //插入数据的主键id
    if err != nil {
        fmt.Printf("Get lastInsertID failed,err:%v",err)
        return
    }
    fmt.Println("LastInsertID:",lastInsertID)
    rowsaffected,err := result.RowsAffected()  //影响行数
    if err != nil {
        fmt.Printf("Get RowsAffected failed,err:%v",err)
        return
    }
    fmt.Println("RowsAffected:",rowsaffected)
}

更新数据

func updateData(DB *sql.DB){
    result,err := DB.Exec("UPDATE users set age=? where id=?","30",3)
    if err != nil{
        fmt.Printf("Insert failed,err:%v",err)
        return
    }
    rowsaffected,err := result.RowsAffected()
    if err != nil {
        fmt.Printf("Get RowsAffected failed,err:%v",err)
        return
    }
    fmt.Println("RowsAffected:",rowsaffected)
}

注意:更新数据不返回LastInsertID,所以result.LastInsertID一直为0

删除数据

func deleteData(DB *sql.DB){
    result,err := DB.Exec("delete from users where id=?",1)
    if err != nil{
        fmt.Printf("Insert failed,err:%v",err)
        return
    }
    lastInsertID,err := result.LastInsertId()
    if err != nil {
        fmt.Printf("Get lastInsertID failed,err:%v",err)
        return
    }
    fmt.Println("LastInsertID:",lastInsertID)
    rowsaffected,err := result.RowsAffected()
    if err != nil {
        fmt.Printf("Get RowsAffected failed,err:%v",err)
        return
    }
    fmt.Println("RowsAffected:",rowsaffected)
}

注意:更新数据不返回LastInsertID,所以result.LastInsertID一直为0

最终代码

/**
*FileName: mysql
*Create on 2018/7/17 下午4:57
*Create by mok
*golang中mysql的用法
 */

package main

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


type User struct {
    ID   int64          `db:"id"`
    Name sql.NullString `db:"name"`
    Age  int            `db:"age"`
}

const (
    USERNAME = "root"
    PASSWORD = "chen19950210"
    NETWORK  = "tcp"
    SERVER   = "localhost"
    PORT     = 3306
    DATABASE = "blog"
)

func main() {
    dsn := fmt.Sprintf("%s:%s@%s(%s:%d)/%s", USERNAME, PASSWORD, NETWORK, SERVER, PORT, DATABASE)
    DB, err := sql.Open("mysql", dsn)
    if err != nil {
        fmt.Printf("Open mysql failed,err:%v\n", err)
        return
    }
    DB.SetConnMaxLifetime(100 * time.Second)
    DB.SetMaxOpenConns(100)
    DB.SetMaxIdleConns(16)
    queryOne(DB)
    queryMulti(DB)
    insertData(DB)
    updateData(DB)
    deleteData(DB)
}

//查询单行
func queryOne(DB *sql.DB) {
    user := new(User)
    row := DB.QueryRow("select * from users where id=?", 1)
    if err := row.Scan(&user.ID, &user.Name, &user.Age); err != nil {
        fmt.Printf("scan failed, err:%v", err)
        return
    }
    fmt.Println(*user)
}

//查询多行
func queryMulti(DB *sql.DB) {
    user := new(User)
    rows, err := DB.Query("select * from users where id > ?", 1)
    defer func() {
        if rows != nil {
            rows.Close()
        }
    }()
    if err != nil {
        fmt.Printf("Query failed,err:%v", err)
        return
    }
    for rows.Next() {
        err = rows.Scan(&user.ID, &user.Name, &user.Age)
        if err != nil {
            fmt.Printf("Scan failed,err:%v", err)
            return
        }
        fmt.Print(*user)
    }

}


//插入数据
func insertData(DB *sql.DB){
    result,err := DB.Exec("insert INTO users(name,age) values(?,?)","YDZ",23)
    if err != nil{
        fmt.Printf("Insert failed,err:%v",err)
        return
    }
    lastInsertID,err := result.LastInsertId()
    if err != nil {
        fmt.Printf("Get lastInsertID failed,err:%v",err)
        return
    }
    fmt.Println("LastInsertID:",lastInsertID)
    rowsaffected,err := result.RowsAffected()
    if err != nil {
        fmt.Printf("Get RowsAffected failed,err:%v",err)
        return
    }
    fmt.Println("RowsAffected:",rowsaffected)
}

//更新数据
func updateData(DB *sql.DB){
    result,err := DB.Exec("UPDATE users set age=? where id=?","30",3)
    if err != nil{
        fmt.Printf("Insert failed,err:%v",err)
        return
    }
    rowsaffected,err := result.RowsAffected()
    if err != nil {
        fmt.Printf("Get RowsAffected failed,err:%v",err)
        return
    }
    fmt.Println("RowsAffected:",rowsaffected)
}

//删除数据
func deleteData(DB *sql.DB){
    result,err := DB.Exec("delete from users where id=?",1)
    if err != nil{
        fmt.Printf("Insert failed,err:%v",err)
        return
    }
    rowsaffected,err := result.RowsAffected()
    if err != nil {
        fmt.Printf("Get RowsAffected failed,err:%v",err)
        return
    }
    fmt.Println("RowsAffected:",rowsaffected)
}

有疑问加站长微信联系(非本文作者)

本文来自:简书

感谢作者:我的饭卡呢

查看原文:golang对mysql的基本操作

入群交流(和以上内容无关):加入Go大咖交流群,或添加微信:liuxiaoyan-s 备注:入群;或加QQ群:701969077

11987 次点击  
加入收藏 微博
被以下专栏收入,发现更多相似内容
暂无回复
添加一条新回复 (您需要 登录 后才能回复 没有账号 ?)
  • 请尽量让自己的回复能够对别人有帮助
  • 支持 Markdown 格式, **粗体**、~~删除线~~、`单行代码`
  • 支持 @ 本站用户;支持表情(输入 : 提示),见 Emoji cheat sheet
  • 图片支持拖拽、截图粘贴等方式上传