golang對mysql的基本操作

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 mainimport (    "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)}