Compared with the previous section, go language access to MySql database can be better written, today we will talk about connection pooling. At the same time, it also demonstrates the processing of go language when the table field content is NULL.
First, we build a new database, cofoxdb and data table user.

Added Administrator

Switch tab

Setting User Rights

New database cofoxdb

Double-click the database to become the current library, click the icon and write the SQL table building script

Table-building SQL script

drop TABLE if exists `user`;
CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Serial number',
  `userName` varchar(45) NOT NULL COMMENT 'Username [not changeable]',
  `password` varchar(255) NOT NULL COMMENT 'Password',
  `nickName` varchar(45) NOT NULL COMMENT 'Nickname?',
  `registTime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'User registration time',
  `lastTimeLogin` datetime DEFAULT NULL COMMENT 'Last logon time',
  `newLoginTime` datetime DEFAULT NULL COMMENT 'Latest login time (current login time)',
  `bak` varchar(1000) DEFAULT NULL COMMENT 'Remarks',
  `online` char(1) DEFAULT 'N' COMMENT 'Currently online, Y/N\nY: On-line\nN: Not online',
  `createTime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Record creation time',
  `creator` varchar(45) DEFAULT NULL COMMENT 'Record Creator',
  `updateTime` datetime DEFAULT NULL COMMENT 'Record modification time',
  `updator` varchar(45) DEFAULT NULL COMMENT 'Record Modifier',
  PRIMARY KEY (`id`,`userName`,`nickName`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COMMENT='All Registered users';

Since there will be multiple code links to the database in an application, we prepare a global variable for all users to call. error variables are also declared.

var db *sql.DB
var err error

db needs to be instantiated and an init() function is created so that the database links can be initialized before the main() function is executed.

func init()  {
    db, err = sql.Open("mysql", "cofox:Q1w2e3r4@tcp(127.0.0.1:3306)/cofoxdb?charset=utf8")
    check(err)

    db.SetMaxOpenConns(2000)
    db.SetMaxIdleConns(1000)
    check(db.Ping())
}

Db. SetMax OpenConns (2000) sets the maximum number of links in this connection pool to 2000.
db.SetMaxIdleConns(1000) sets a minimum of 1000 standby links in the connection pool. This allows an existing link to be allocated from the connection pool when there is a program request for access. Improve access efficiency.
db.Ping() is for real links between programs and databases (sql.Open does not establish a real connection relationship, but initializes it.)

insert data

Use db.Prepare directly because DB has been initialized.
Returns the latest id after execution of res.LastInsertId(). If batch data is inserted, this returns the id of the first record.

func insert()  {
    stmt, err := db.Prepare(`INSERT user (userName, password, nickName) VALUES (?, ?, ?)`)
    check(err)

    res, err := stmt.Exec("cofox_1","123456","A calm Fox")
    check(err)

    id, err := res.LastInsertId()
    check(err)

    fmt.Println(id)
    stmt.Close()

}

Modifying data

Also directly using db
res.RowsAffected() commits execution and returns the number of modified records.

func update() {
    stmt, err := db.Prepare("UPDATE user set nickName=?, updateTime=?, updator=?, bak=? WHERE id=?")
    check(err)

    res, err := stmt.Exec("Hot Soil Flame Mountain",time.Now().Format("2006-01-02 15:04:05"),"root","Test update\r\ngo Direct connection database", 1)
    check(err)

    num, err := res.RowsAffected()
    check(err)

    fmt.Println(num)
    stmt.Close()
}

Delete data

Ditto
res.RowsAffected() commits execution, returning the number of deleted records.

func remove() {
    stmt, err := db.Prepare("DELETE FROM cofoxdb WHERE id=?")
    check(err)

    res, err := stmt.Exec(7)
    check(err)

    num, err := res.RowsAffected()
    check(err)

    fmt.Println(num)
    stmt.Close()

}

Query data

Because there are many table fields, many fields may not write the corresponding data after they are added. If these fields do not have default values, they will be NULL values.
NULL values cannot be written to string time.Time in go language. So here we use the sql.NullString type provided by "database/sql". Of course, Null** types also have many NullInt64, NullFloat64, NullBool

        var id int
        var userName string
        var password string
        var nickName string
        var registTime string
        var lastTimeLogin sql.NullString
        var newLoginTime sql.NullString
        var bak sql.NullString
        var online sql.NullString
        var createTime sql.NullString
        var creator sql.NullString
        var updateTime sql.NullString
        var updator sql.NullString

We use this type to process data whose fields may be NULL. In this way, the recorded values can be read normally.
The type structure of these NullString s is like this

type NullString struct {
    String string
    Valid  bool // Valid is true if String is not NULL
}

There are two fields in it. String fields are what we want in the end. So, when we output or use it, we organize the code like this

lastTimeLogin.String, newLoginTime.String, bak.String, online.String, createTime.String, creator.String, updateTime.String, updator.String

After insert(), we execute query2(), and we get the following results

id = "3", userName = "cofox_1", password = "123456", nickName = "A calm Fox", registTime = "2017-09-07 17:39:02", lastTimeLogin = "", newLoginTime = "", bak = "", online = "N", createTime = "2017-09-07 17:39:02", creator = "", updateTime = "", updator = ""

Look at the complete code example

package main

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

var db *sql.DB
var err error

func init()  {
    db, err = sql.Open("mysql", "cofox:Q1w2e3r4@tcp(127.0.0.1:3306)/cofoxdb?charset=utf8")
    check(err)

    db.SetMaxOpenConns(2000)
    db.SetMaxIdleConns(1000)
    check(db.Ping())
}

func main() {
    //query()
    query2()
    //insert()
    //update()
    //remove()
}



//Query data
func query() {
    rows, err := db.Query("SELECT * FROM user")
    check(err)

    for rows.Next() {
        columns, _ := rows.Columns()

        scanArgs := make([]interface{}, len(columns))
        values := make([]interface{}, len(columns))

        for i := range values {
            scanArgs[i] = &values[i]
        }

        //Save data to record dictionary
        err = rows.Scan(scanArgs...)
        record := make(map[string]string)
        for i, col := range values {
            if col != nil {
                record[columns[i]] = string(col.([]byte))
            }
        }
        fmt.Println(record)
    }
    rows.Close()

}
func query2()  {
    rows, err := db.Query("SELECT id, userName, password, nickName, registTime, lastTimeLogin, newLoginTime, bak, online, createTime, creator, updateTime, updator FROM user")
    check(err)

    for rows.Next(){
        var id int
        var userName string
        var password string
        var nickName string
        var registTime string
        var lastTimeLogin sql.NullString
        var newLoginTime sql.NullString
        var bak sql.NullString
        var online sql.NullString
        var createTime sql.NullString
        var creator sql.NullString
        var updateTime sql.NullString
        var updator sql.NullString

        //Note that the order of parameters in Scan parentheses here is consistent with the order of fields in SELECT.
        if err := rows.Scan(&id, &userName, &password, &nickName, &registTime, &lastTimeLogin, &newLoginTime, &bak, &online, &createTime, &creator, &updateTime, &updator); err != nil {
            log.Fatal(err)
        }

        fmt.Printf("id = \"%d\", userName = \"%s\", password = \"%s\", nickName = \"%s\", registTime = \"%s\", lastTimeLogin = \"%s\", newLoginTime = \"%s\", bak = \"%s\", online = \"%s\", createTime = \"%s\", creator = \"%s\", updateTime = \"%s\", updator = \"%s\"\n",id, userName, password, nickName, registTime, lastTimeLogin.String, newLoginTime.String, bak.String, online.String, createTime.String, creator.String, updateTime.String, updator.String)

    }

    if err := rows.Err(); err != nil {
        log.Fatal(err)
    }
    rows.Close()
}

//insert data
func insert()  {
    stmt, err := db.Prepare(`INSERT user (userName, password, nickName) VALUES (?, ?, ?)`)
    check(err)

    res, err := stmt.Exec("cofox_1","123456","A calm Fox")
    check(err)

    id, err := res.LastInsertId()
    check(err)

    fmt.Println(id)
    stmt.Close()

}

//Modifying data
func update() {
    stmt, err := db.Prepare("UPDATE user set nickName=?, updateTime=?, updator=?, bak=? WHERE id=?")
    check(err)

    res, err := stmt.Exec("Hot Soil Flame Mountain",time.Now().Format("2006-01-02 15:04:05"),"root","Test update\r\ngo Direct connection database", 1)
    check(err)

    num, err := res.RowsAffected()
    check(err)

    fmt.Println(num)
    stmt.Close()
}

//Delete data
func remove() {
    stmt, err := db.Prepare("DELETE FROM cofoxdb WHERE id=?")
    check(err)

    res, err := stmt.Exec(7)
    check(err)

    num, err := res.RowsAffected()
    check(err)

    fmt.Println(num)
    stmt.Close()

}

func check(err error) {
    if err != nil{
        fmt.Println(err)
        panic(err)
    }
}