【Reprint】Go Language Operations on MySQL

title: 【转载】Go Language Operations on MySQL
date: 2021-08-11 10:52:59
comment: false
toc: true
category:

Golang
tags:
转载
Go
MySQL
Operations

This article is reprinted from: Go Language Operations on MySQL | Li Wenzhou's Blog

MySQL is a commonly used relational database in the industry. This article introduces how to operate MySQL databases using the Go language.

Go Operations on MySQL#

Connection#

The database/sql package in Go provides a generic interface for SQL or SQL-like databases but does not provide specific database drivers. When using the database/sql package, at least one database driver must be injected.

The databases we commonly use generally have complete third-party implementations. For example: MySQL Driver

Download Dependencies#

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

Using MySQL Driver#

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

Open opens a database specified by driverName and dataSourceName, which generally includes at least the database filename and other necessary connection information.

import (  
	"database/sql"  

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

func main() {  
   // DSN: Data Source Name  
	dsn := "user:password@tcp(127.0.0.1:3306)/dbname"  
	db, err := sql.Open("mysql", dsn)  
	if err != nil {  
		panic(err)  
	}  
	defer db.Close()  // Note that this line should be written below the err check  
}

Thought Question: Why should the defer db.Close() statement in the above code not be written before the if err != nil check?

Initialize Connection#

The Open function may only validate whether its parameter format is correct and does not actually create a connection to the database. To check whether the data source name is real and valid, the Ping method should be called.

The returned DB object can be safely used concurrently by multiple goroutines and maintains its own pool of idle connections. Therefore, the Open function should only be called once, and it is rarely necessary to close this DB object.

// Define a global object db  
var db *sql.DB  

// Define a function to initialize the database  
func initDB() (err error) {  
	// DSN: Data Source Name  
	dsn := "user:password@tcp(127.0.0.1:3306)/sql_test?charset=utf8mb4&parseTime=True"  
	// Will not verify if the username and password are correct  
	// Note!!! Do not use := here, we are assigning to the global variable and then using the global variable db in the main function  
	db, err = sql.Open("mysql", dsn)  
	if err != nil {  
		return err  
	}  
	// Attempt to establish a connection with the database (verify if dsn is correct)  
	err = db.Ping()  
	if err != nil {  
		return err  
	}  
	return nil  
}  

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

Among them, sql.DB represents the database object (struct instance) that connects to the database, saving all information related to connecting to the database. It internally maintains a connection pool with zero to multiple underlying connections, which can be safely used by multiple goroutines simultaneously.

SetMaxOpenConns#

func (db *DB) SetMaxOpenConns(n int)

SetMaxOpenConns sets the maximum number of connections to the database. If n is greater than 0 and less than the maximum idle connections, it will reduce the maximum idle connections to match the limit of the maximum open connections. If n <= 0, there will be no limit on the maximum open connections, which defaults to 0 (unlimited).

SetMaxIdleConns#

func (db *DB) SetMaxIdleConns(n int)

SetMaxIdleConns sets the maximum number of idle connections in the connection pool. If n is greater than the maximum open connections, the new maximum idle connections will be reduced to match the limit of the maximum open connections. If n <= 0, idle connections will not be retained.

CRUD#

Create Database and Table#

First, we create a database named sql_test in MySQL

CREATE DATABASE sql_test;

Enter this database:

use sql_test;

Execute the following command to create a test data table:

CREATE TABLE `user` (  
    `id` BIGINT(20) NOT NULL AUTO_INCREMENT,  
    `name` VARCHAR(20) DEFAULT '',  
    `age` INT(11) DEFAULT '0',  
    PRIMARY KEY(`id`)  
)ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

Query#

To facilitate querying, we first define a struct to store the data from the user table.

type user struct {  
	id   int  
	age  int  
	name string  
}

Single Row Query#

The single row query db.QueryRow() executes a query and expects to return at most one row result (i.e., Row). QueryRow always returns a non-nil value, and only when the Scan method of the return value is called will it return the delayed error (e.g., no result found).

func (db *DB) QueryRow(query string, args ...interface{}) *Row

Specific example code:

// Example of querying a single piece of data  
func queryRowDemo() {  
	sqlStr := "select id, name, age from user where id=?"  
	var u user  
	// Very important: ensure to call Scan after QueryRow, otherwise the held database connection will not be released  
	err := db.QueryRow(sqlStr, 1).Scan(&u.id, &u.name, &u.age)  
	if err != nil {  
		fmt.Printf("scan failed, err:%v\n", err)  
		return  
	}  
	fmt.Printf("id:%d name:%s age:%d\n", u.id, u.name, u.age)  
}

Multi-Row Query#

The multi-row query db.Query() executes a query and returns multiple row results (i.e., Rows), generally used for executing select commands. The args parameter represents the placeholder parameters in the query.

func (db *DB) Query(query string, args ...interface{}) (*Rows, error)

Specific example code:

// Example of querying multiple pieces of data  
func queryMultiRowDemo() {  
	sqlStr := "select id, name, age from user where id > ?"  
	rows, err := db.Query(sqlStr, 0)  
	if err != nil {  
		fmt.Printf("query failed, err:%v\n", err)  
		return  
	}  
	// Very important: close rows to release the held database connection  
	defer rows.Close()  

	// Loop to read data from the result set  
	for rows.Next() {  
		var u user  
		err := rows.Scan(&u.id, &u.name, &u.age)  
		if err != nil {  
			fmt.Printf("scan failed, err:%v\n", err)  
			return  
		}  
		fmt.Printf("id:%d name:%s age:%d\n", u.id, u.name, u.age)  
	}  
}

Insert Data#

Insert, update, and delete operations all use the Exec method.

func (db *DB) Exec(query string, args ...interface{}) (Result, error)

Exec executes a command (including query, delete, update, insert, etc.) and returns a Result summarizing the executed SQL command. The args parameter represents the placeholder parameters in the query.

Specific example code for inserting data:

// Insert data  
func insertRowDemo() {  
	sqlStr := "insert into user(name, age) values (?,?)"  
	ret, err := db.Exec(sqlStr, "Wang Wu", 38)  
	if err != nil {  
		fmt.Printf("insert failed, err:%v\n", err)  
		return  
	}  
	theID, err := ret.LastInsertId() // ID of the newly inserted data  
	if err != nil {  
		fmt.Printf("get lastinsert ID failed, err:%v\n", err)  
		return  
	}  
	fmt.Printf("insert success, the id is %d.\n", theID)  
}

Update Data#

Specific example code for updating data:

// Update data  
func updateRowDemo() {  
	sqlStr := "update user set age=? where id = ?"  
	ret, err := db.Exec(sqlStr, 39, 3)  
	if err != nil {  
		fmt.Printf("update failed, err:%v\n", err)  
		return  
	}  
	n, err := ret.RowsAffected() // Number of rows affected  
	if err != nil {  
		fmt.Printf("get RowsAffected failed, err:%v\n", err)  
		return  
	}  
	fmt.Printf("update success, affected rows:%d\n", n)  
}

Delete Data#

Specific example code for deleting data:

// Delete data  
func deleteRowDemo() {  
	sqlStr := "delete from user where id = ?"  
	ret, err := db.Exec(sqlStr, 3)  
	if err != nil {  
		fmt.Printf("delete failed, err:%v\n", err)  
		return  
	}  
	n, err := ret.RowsAffected() // Number of rows affected  
	if err != nil {  
		fmt.Printf("get RowsAffected failed, err:%v\n", err)  
		return  
	}  
	fmt.Printf("delete success, affected rows:%d\n", n)  
}

MySQL Prepared Statements#

What is a Prepared Statement?#

The execution process of a regular SQL statement:

The client replaces placeholders in the SQL statement to obtain the complete SQL statement.
The client sends the complete SQL statement to the MySQL server.
The MySQL server executes the complete SQL statement and returns the result to the client.

The execution process of a prepared statement:

Split the SQL statement into two parts: the command part and the data part.
First, send the command part to the MySQL server, where the MySQL server performs SQL preprocessing.
Then send the data part to the MySQL server, where the MySQL server replaces the placeholders in the SQL statement.
The MySQL server executes the complete SQL statement and returns the result to the client.

Why Use Prepared Statements?#

Optimize the method for the MySQL server to repeatedly execute SQL, which can improve server performance by allowing the server to compile once and execute multiple times, saving subsequent compilation costs.
Avoid SQL injection issues.

Go Implementation of MySQL Prepared Statements#

In database/sql, the following Prepare method is used to implement prepared statement operations.

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

The Prepare method first sends the SQL statement to the MySQL server and returns a prepared statement for subsequent queries and commands. The return value can execute multiple queries and commands simultaneously.

Example code for a prepared query:

// Example of a prepared query  
func prepareQueryDemo() {  
	sqlStr := "select id, name, age from user where id > ?"  
	stmt, err := db.Prepare(sqlStr)  
	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()  
	// Loop to read data from the result set  
	for rows.Next() {  
		var u user  
		err := rows.Scan(&u.id, &u.name, &u.age)  
		if err != nil {  
			fmt.Printf("scan failed, err:%v\n", err)  
			return  
		}  
		fmt.Printf("id:%d name:%s age:%d\n", u.id, u.name, u.age)  
	}  
}

Insert, update, and delete operations are very similar in prepared statements. Here is an example of a prepared insert operation:

// Example of a prepared insert  
func prepareInsertDemo() {  
	sqlStr := "insert into user(name, age) values (?,?)"  
	stmt, err := db.Prepare(sqlStr)  
	if err != nil {  
		fmt.Printf("prepare failed, err:%v\n", err)  
		return  
	}  
	defer stmt.Close()  
	_, err = stmt.Exec("Little Prince", 18)  
	if err != nil {  
		fmt.Printf("insert failed, err:%v\n", err)  
		return  
	}  
	_, err = stmt.Exec("Sha He Na Zha", 18)  
	if err != nil {  
		fmt.Printf("insert failed, err:%v\n", err)  
		return  
	}  
	fmt.Println("insert success.")  
}

SQL Injection Issues#

We should never manually concatenate SQL statements!

Here we demonstrate an example of manually concatenating SQL statements by writing a function to query the user table based on the name field:

// SQL injection example  
func sqlInjectDemo(name string) {  
	sqlStr := fmt.Sprintf("select id, name, age from user where name='%s'", name)  
	fmt.Printf("SQL:%s\n", sqlStr)  
	var u user  
	err := db.QueryRow(sqlStr).Scan(&u.id, &u.name, &u.age)  
	if err != nil {  
		fmt.Printf("exec failed, err:%v\n", err)  
		return  
	}  
	fmt.Printf("user:%#v\n", u)  
}

At this point, the following input strings can trigger SQL injection issues:

sqlInjectDemo("xxx' or 1=1#")  
sqlInjectDemo("xxx' union select * from user #")  
sqlInjectDemo("xxx' and (select count(*) from user) <10 #")

Note: Different databases use different placeholder syntax in SQL statements.

Database	Placeholder Syntax
MySQL	`?`
PostgreSQL	`$1`, `$2`, etc.
SQLite	`?` and `$1`
Oracle	`:name`

Go Implementation of MySQL Transactions#

What is a Transaction?#

A transaction is the smallest indivisible unit of work; typically, a transaction corresponds to a complete business operation (for example, a bank account transfer operation, which is the smallest unit of work), and this complete business operation requires multiple DML (insert, update, delete) statements to be executed together. A transfers money to B, which requires two update operations.

In MySQL, only databases or tables using the InnoDB storage engine support transactions. Transaction processing can be used to maintain the integrity of the database, ensuring that a batch of SQL statements either all execute or none execute.

ACID of Transactions#

Typically, a transaction must meet four conditions (ACID): Atomicity, Consistency, Isolation, and Durability.

Condition	Explanation
Atomicity	All operations in a transaction must either complete entirely or not at all, and cannot end in an intermediate state. If an error occurs during the execution of the transaction, it will be rolled back to the state before the transaction began, as if the transaction had never been executed.
Consistency	The integrity of the database is not violated before the transaction begins and after it ends. This means that the written data must fully comply with all preset rules, including data accuracy, consistency, and the ability of the subsequent database to autonomously complete the scheduled work.
Isolation	The ability of the database to allow multiple concurrent transactions to read and modify its data simultaneously, isolation can prevent data inconsistency caused by cross-execution of multiple transactions. Transaction isolation is divided into different levels, including Read Uncommitted, Read Committed, Repeatable Read, and Serializable.
Durability	Once a transaction is completed, the modifications to the data are permanent and will not be lost even in the event of a system failure.

In Go, the following three methods are used to implement transaction operations in MySQL. Start a transaction

func (db *DB) Begin() (*Tx, error)

Commit a transaction

func (tx *Tx) Commit() error

Rollback a transaction

func (tx *Tx) Rollback() error

Transaction Example#

The following code demonstrates a simple transaction operation that ensures that two update operations either both succeed or both fail, with no intermediate state.

// Transaction operation example  
func transactionDemo() {  
	tx, err := db.Begin() // Start transaction  
	if err != nil {  
		if tx != nil {  
			tx.Rollback() // Rollback  
		}  
		fmt.Printf("begin trans failed, err:%v\n", err)  
		return  
	}  
	sqlStr1 := "Update user set age=30 where id=?"  
	ret1, err := tx.Exec(sqlStr1, 2)  
	if err != nil {  
		tx.Rollback() // Rollback  
		fmt.Printf("exec sql1 failed, err:%v\n", err)  
		return  
	}  
	affRow1, err := ret1.RowsAffected()  
	if err != nil {  
		tx.Rollback() // Rollback  
		fmt.Printf("exec ret1.RowsAffected() failed, err:%v\n", err)  
		return  
	}  

	sqlStr2 := "Update user set age=40 where id=?"  
	ret2, err := tx.Exec(sqlStr2, 3)  
	if err != nil {  
		tx.Rollback() // Rollback  
		fmt.Printf("exec sql2 failed, err:%v\n", err)  
		return  
	}  
	affRow2, err := ret2.RowsAffected()  
	if err != nil {  
		tx.Rollback() // Rollback  
		fmt.Printf("exec ret1.RowsAffected() failed, err:%v\n", err)  
		return  
	}  

	fmt.Println(affRow1, affRow2)  
	if affRow1 == 1 && affRow2 == 1 {  
		fmt.Println("Transaction committed...")  
		tx.Commit() // Commit transaction  
	} else {  
		tx.Rollback()  
		fmt.Println("Transaction rolled back...")  
	}  

	fmt.Println("exec trans success!")  
}

More powerful and easier-to-use sqlx library

Exercise#

Combine net/http and database/sql to implement a simple web program for user registration and login using MySQL.