1. Install MySQL driver

Installation mode: open the command line and execute the go command:

go get -u github.com/go-sql-driver/mysql

2. Connect MySQL

• To build a connection, the format is: "user name: password @ tcp(IP: port) / database? charset=utf8"
• Open the database, the former is the driver name, so to import:_
  "github.com/go-sql-driver/mysql"
• Set the maximum number of database connections and the maximum number of idle connections on the database
• Verify connection: using the Ping() function

The reference code is as follows:

import (
	"database/sql"
	"fmt"
	"strings"

	// Installation method: go get -u github.com/go-sql-driver/mysql
	_ "github.com/go-sql-driver/mysql"
)

var (
	dbhostsip  = "127.0.0.1:3306"
	dbusername = "root"
	dbpassword = "123456"
	dbname     = "chat"
)

// Initialize database
func InitDB() (*sql.DB, error) {
	//Build connection information
	dbinfo := strings.Join([]string{dbusername, ":", dbpassword, "@tcp(", dbhostsip, ")/", dbname, "?charset=utf8"}, "")
	fmt.Println(dbinfo)
	//Open the database with the driver name in front, so import: MySQL driver github.com/go-sql-driver/mysql
	dbins, err := sql.Open("mysql", dbinfo)
	if nil != err {
		fmt.Println("Open Database Error:", err)
		return nil, err
	}
	// Set the maximum number of connections to the database
	dbins.SetConnMaxLifetime(100)
	// Set the maximum number of idle connections in the database
	dbins.SetMaxIdleConns(10)
	// Verify connection
	if err = dbins.Ping(); nil != err {
		fmt.Println("Open Database Fail,Error:", err)
		return nil, err
	}
	fmt.Println("Connect Success!!!")
	return dbins, nil
}

Add, delete, modify and query MySQL

The steps of insert, delete and update of the database are basically the same. The difference is the change of sql statements. The steps are generally as follows:

• Open transaction: TX, err: = dB. Begin()

• Prepare sql statement: stmt, err: = tx.prepare ("DELETE FROM nk_user WHERE id =?")

• Execute sql statement: res, err: = stmt.exec( user.Id)

• Commit transaction: tx.Commit()

  Reference examples are as follows:

// Execute command, which can be used to insert, delete and modify
func ExecuteCommand(dbins *sql.DB, cmd string) (err error) {
	// Open transaction
	tx, err := dbins.Begin()
	if nil != err {
		fmt.Println("Failed to open transaction:", cmd, err)
		return err
	}
	// Failed to prepare SQL statement
	stmt, err := tx.Prepare(cmd)
	if nil != err {
		fmt.Println("Get ready SQL Statement failure:", cmd, err)
		return err
	}
	// Pass parameters to SQL statement for execution
	_, err = stmt.Exec()
	if nil != err {
		fmt.Println("implement SQL Statement failure:", cmd, err)
		return err
	}
	// Submission of affairs
	err = tx.Commit()
	if nil != err {
		fmt.Println("Failed to commit transaction:", err)
	}
	return nil
}
type User struct {
	userid   int
	name     string
	password string
}
// Insert data sample
func InsertUser(dbins *sql.DB, user User) (id int64, err error) {
	tx, err := dbins.Begin()
	if nil != err {
		fmt.Println("Failed to open transaction:", err)
		return -1, err
	}
	// Prepare SQL statement
	stmt, err := tx.Prepare("insert into user(`userid`, `name`,`password`) values(?,?,?)")
	if nil != err {
		fmt.Println("Prepare Failed!")
		return -1, err
	}
	// Pass parameters to SQL statement for execution
	res, err := stmt.Exec(user.userid, user.name, user.password)
	if nil != err {
		fmt.Println("Exec Failed!")
		return -1, err
	}
	// Commit transaction
	tx.Commit()
	id, err = res.LastInsertId()
	return id, nil
}
// Delete data sample
func DeleteUser(dbins *sql.DB, user User) (err error) {
	tx, err := dbins.Begin()
	if nil != err {
		fmt.Println("Begin ", err)
		return err
	}
	// Preparing for SQL
	stmt, err := tx.Prepare("delete from user where userid = ?")
	if nil != err {
		fmt.Println("prepare sql cmd err:", err)
		return err
	}
	// Setting parameters and executing SQL
	res, err := stmt.Exec(user.userid)
	if nil != err {
		fmt.Println("exec failed!")
		return err
	}
	// Submission of affairs
	id, err := res.LastInsertId()
	fmt.Println("LastInsertId:", id)
	err = tx.Commit()
	return err
}

For MySQL query, there are query single lines and query multiple lines, as shown below:

// Query single row data: write SQL statements, use QueryRow, and use Scan to assign query results to corresponding objects
func SelectUserByUserId(dbins *sql.DB, id int) User {
	var user User
	var idx int
	err := dbins.QueryRow("select * from user where userid=?", id).Scan(&idx, &user.userid, &user.name, &user.password)
	if nil != err {
		fmt.Println("QueryRow Error", err)
	}
	return user
}

// Write sql statement and execute Query function
// Next() is used to read the returned result of each line, and Scan is used to assign the value to the corresponding object.
// The reference code is as follows
func SelectAllUser(dbins *sql.DB) ([]User, error) {
	rows, err := dbins.Query("select * from user")
	if nil != err {
		fmt.Println("Query Error:", err)
		return nil, err
	}
	var uers []User
	for rows.Next() {
		var user User
		var id int
		rows.Scan(&id, &user.userid, &user.name, &user.password)

		uers = append(uers, user)
	}
	return uers, nil
}