Golang basic operation for MySQL

Golang Operation MySQL

Importing Database Drivers

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

MySQL Connection Configuration

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

Establish a connection

    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) //设置闲置连接数

Data model

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

Data table structure

15318255510282.jpg

Query line

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

Attention:
Row must scan, or it will cause the connection to fail to close, and will continue to occupy the connection until the set life cycle is exceeded
The following is no scan causing the connection to be occupied, the maximum number of connections is 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)    }}

Execution Result:

15318261414512.jpg

Blocking occurs after execution to 100, waiting for the connection pool to be released.

Querying multiple rows

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

Inserting data

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

Update data

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

Note: The update data does not return Lastinsertid, so result. Lastinsertid has been a 0

Delete data

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

Note: The update data does not return Lastinsertid, so result. Lastinsertid has been a 0

Final code

/***filename:mysql*create on 2018/7/17 pm 4:57*create by Mok*golang MySQL usage */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 ' NETW ORK = "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 (* time. Second) DB. Setmaxopenconns (+) DB. Setmaxidleconns (+) Queryone (db) Querymulti (db) InsertData (db) UpdateData (db) deleteData (db)}//query line func que Ryone (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)}//queries multi-line 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)}}//Insert data func insertdata (DB *sql. db) {result,err: = db. Exec ("INSERT into users (name,age) VALUES (?,?)", "Ydz", "all" 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)}//Update data func updatedata (DB *sql. db) {result,err: = db. Exec ("UPDATE users set age=?") Where id=? "," 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)}//Delete data 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)}