[Go] use Go to access MySQL

Access MySQL with Go

The database/sql package of Go language provides a general interface to connect SQL databases or SQL like databases, but does not provide specific database drivers. When using it, at least one database driver must be injected.

Implement basic CRUD

1. Create database and database tables

  1. Enter the database cmd through the mysql -u root -p command, and then create a go_mysql database:

    CREATE DATABASE go_mysql;
    
  2. Enter the database: USE go_mysql;

  3. Create user table:

    CREATE TABLE `user` {
    	`uid` BIGINT(20) NOT NULL AUTO_INCREMENT,
    	`name` VARCHAR(20) DEFAULT '',
    	`phone` VARCHAR(20) DEFAULT '',
    	PRIMARY KEY(`uid`)
    }ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;
    

2. Download MySQL driver

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

3. Use MySQL driver

Import dependent packages directly:

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

In the above statement, GitHub COM / go SQL driver / MySQL is a dependency package. Because the objects in the package are not used directly, they are underlined in front of the imported package.

The Open() function is provided in the database/sql package to connect to the database. Its definition is as follows:

func Open(driverName, dataSourceName string) (*DB, error)

Connection example:

package main

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

func main() {
	db, err := sql.Open("mysql",
		"<user>:<password>@tcp(127.0.0.1:3306)/hello")
	if err != nil {
		log.Fatal(err)
	}
	defer db.Close()
}

4. Initialize the connection

After establishing the connection with the Open() function, if you want to check whether the name of the data source is legal, you can call the Ping method. The returned DB object can be safely used by multiple goroutine s at the same time, and it will maintain its own idle connection pool. In this way, the Open function only needs to be called once, because DB objects are rarely closed after startup. The example code of initializing the connection with the Open function is as follows:

package main

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

var db *sql.DB

// Define a function to initialize the database
func initDB() (err error) {
	//Connect database
	db, err = sql.Open("mysql", "root:a123456@tcp(127.0.0.1:3306)/go_mysql")
	if err != nil {
		return err
	}
	// Try to establish a connection with the database (verify that the dsn is correct)
	err = db.Ping()
	if err != nil {
		return err
	}
	return nil
}

func main() {
	err := initDB() // Call the function of outputting database
	if err != nil {
		fmt.Printf("init db failed,err:%v\n", err)
		return
	}
}

Where, SQL DB is the operation handle of a database, which represents a connection pool with zero to multiple underlying connections. It can be safely used by multiple goroutine s at the same time. The database/sql package will automatically create and release connections and maintain a connection pool of idle connections.

5. SQL query

(1) Check

First, define a structure to store the data returned by the database:

type User struct {
	Uid   int
	Name  string
	Phone string
}

There are two query methods:

  • QueryRow() to query a single line
  • Query() to query multiple lines

Example of single line query:

// Single line test
func queryRow() {
    var u User
	// Very important: ensure that the Scan method is called after QueryRow, otherwise the database link held will not be released.
	err := db.QueryRow("select uid,name,phone from `user` where uid=?", 1).Scan(&u.Uid, &u.Name, &u.Phone)
	if err != nil {
		fmt.Printf("scan failed, err:%v\n", err)
		return
	}
	fmt.Printf("uid:%d name:%s phone:%s\n", u.Uid, u.Name, u.Phone)
}

Example of multi line query:

// Example of querying multiple pieces of data
func queryMultiRow() {
    var u User
	rows, err := db.Query("select uid,name,phone from `user` where uid > ?", 0)
	if err != nil {
		fmt.Printf("query failed, err:%v\n", err)
		return
	}
	// Close rows to release held database links
	defer rows.Close()
	// Cycle through the data in the result set
	for rows.Next() {
		err := rows.Scan(&u.Uid, &u.Name, &u.Phone)
		if err != nil {
			fmt.Printf("scan failed, err:%v\n", err)
			return
		}
		fmt.Printf("uid:%d name:%s phone:%s\n", u.Uid, u.Name, u.Phone)
	}
}

(2) Insert, modify and delete

Use Exec() method

MySQL preprocessing

What is preprocessing

To understand preprocessing, you need to first understand the execution process of ordinary SQL statements:

  1. The client replaces the placeholder of the SQL statement to obtain the complete SQL statement;
  2. The client sends a complete SQL statement to the MySQL server;
  3. The MySQL server executes complete SQL statements and returns the results to the client.

Execution process of preprocessing

  1. The SQL statement is divided into two parts - command part and data part;
  2. Send the command part to the MySQL server, and the MySQL server performs SQL preprocessing;
  3. Send the data part to the MySQL server, and the MySQL server replaces the placeholder of the SQL statement;
  4. The MySQL server executes complete SQL statements and returns the results to the client

Why pretreatment?

Preprocessing is used to optimize the repeated execution of SQL statements by MySQL server, which can improve the performance of the server. Allowing the server to compile in advance and execute multiple times at a time can save the cost of subsequent compilation and avoid SQL injection problems.

Preprocessing of MySQL in Go language

In the Go language, the prepare () method sends the SQL statement to the MySQL server and returns a prepared state for subsequent queries and commands. The return value can execute multiple queries and commands at the same time. The Prepare() method is defined as follows:

func (db *DB) Prepare(query string) (*Stmt, error)

Sample code for preprocessing:

// Preprocessing query example
func prepareQuery() {
	stmt, err := db.Prepare("select uid,name,phone from `user` where uid > ?")
	if err != nil {
		fmt.Printf("prepare failed, err:%v\n", err)
		return
	}
	defer stmt.Close()
	rows, err := stmt.Query(0)
	if err != nil {
		fmt.Printf("query failed, err:%v\n", err)
		return
	}
	defer rows.Close()
	// Cycle through the data in the result set
    var user User
	for rows.Next() {
		err := rows.Scan(&u.Uid, &u.Name, &u.Phone)
		if err != nil {
			fmt.Printf("scan failed, err:%v\n", err)
			return
		}
		fmt.Printf("uid:%d name:%s phone:%s\n", u.Uid, u.Name, u.Phone)
	}
}

Implementing MySQL transaction with Go

1. What is a transaction

Transaction is the smallest non separable unit of work. Usually, A transaction corresponds to A complete business (such as bank account transfer business, which is the smallest work unit). At the same time, this complete business needs to execute multiple DML (insert, UPDATE, DELETE, etc.) statements to complete it jointly. For example, when A transfers money to B, you need to perform two UPDATE operations. In MySQL, only databases or tables that use the Innodb database engine can support transactions. Transaction processing is used to maintain the integrity of the database and ensure that batch SQL statements are either executed or not executed.

2. ACID attribute of transaction

Generally, a transaction must meet four conditions (ACID): Atomicity (or indivisibility), consistency (Consistency), Isolation (also known as independence), and durability. The ACID attribute of the transaction.

3. Relevant methods of transaction

The Go language uses the following three methods to implement transaction operations in MySQL:

  • The Begin() method is used to start the transaction:

    func (db *DB) Begin() (*Tx, error)
    
  • The Commit() method is used to commit the transaction:

    func (tx *Tx) Commit() error
    
  • The Rollback() method is used to roll back the transaction:

    func (tx *Tx) Rollback() error
    

The following code demonstrates a simple transaction operation, which can ensure that the two update operations either succeed or fail at the same time, and there will be no intermediate state:

func transaction() {
	tx, err := db.Begin() // Open transaction
	if err != nil {
		if tx != nil {
			tx.Rollback() // RollBACK 
		}
		fmt.Printf("begin trans failed, err:%v\n", err)
		return
	}
	_, err = tx.Exec("update user set username='james' where uid=?", 1)
	if err != nil {
		tx.Rollback() // RollBACK 
		fmt.Printf("exec sql1 failed, err:%v\n", err)
		return
	}
	_, err = tx.Exec("update user set username='james' where uid=?", 3)
	if err != nil {
		tx.Rollback() // RollBACK 
		fmt.Printf("exec sql2 failed, err:%v\n", err)
		return
	}
	err = tx.Commit() // Commit transaction
	if err != nil {
		tx.Rollback() // RollBACK 
		fmt.Printf("commit failed, err:%v\n", err)
		return
	}
	fmt.Println("exec transaction success!")
}

SQL injection and defense

When writing SQL scripts, try not to splice SQL statements by yourself.

Common defense measures against SQL injection include:

  1. It is forbidden to write variables directly to SQL statements.
  2. Carry out hierarchical management of users and strictly control the permissions of users.
  3. Check the user input to ensure the security of data input. When checking the input or submitted variables, convert or filter the characters such as single quotation marks, double quotation marks and colons.
  4. Encrypt the database information.

Keywords: Go Database MySQL SQL

Added by JeditL on Fri, 28 Jan 2022 16:48:47 +0200