golang mysql 基本操作

「爱情、让人受尽委屈。」 2021-07-28 11:22 494阅读 0赞

### 导入数据库驱动 ###

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"`
    }

### 数据表结构 ###

![format_png][]

### 查询单行 ###

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)
        }
    }

执行结果：

![format_png 1][]

在执行到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)
    }

[format_png]: /images/20210728/04aa3cc9f1fd4ea08b506f83beb6a074.png
[format_png 1]: /images/20210728/61445404e042423ba35816090cbf2f72.png