Summary of knowledge about Go language operation MySQL

1. Connection

In Godatabase/sqlThe package provides a universal interface to ensure SQL or SQL-like databases, and does not provide specific database drivers. usedatabase/sqlAt least one database driver must be injected when packaged.

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

1.1 Download dependencies

go get -u /go-sql-driver/mysql

1.2 Using MySQL Driver

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

Open opens a database specified by dirverName, and dataSourceName specifies the data source, which generally includes at least the database file name and other necessary information for connection.

import (
	"database/sql"

	_ "/go-sql-driver/mysql"
)

func main() {
	dsn := "user:password@tcp(127.0.0.1:3306)/dbname"
	db, err := ("mysql", dsn)
	if err != nil {
		panic(err)
	}
	defer ()  

1.3 Initialize the connection

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

Next, we define a global variabledb, used to save database connection objects. Split the above sample code into a separate oneinitDBFunctions, you only need to call the function once when the program starts to complete the initialization of the global variable db. Other functions can directly use global variables.dbNow.

// Define a global object dbvar db *

// Define a function that initializes the databasefunc initDB() (err error) {
	// DSN:Data Source Name
	dsn := "user:password@tcp(127.0.0.1:3306)/sql_test?charset=utf8mb4&parseTime=True"
	// Notice!  !  !  Don't use := here, we assign values ​​to global variables, and then use global variable db in main function	db, err = ("mysql", dsn)
	if err != nil {
		return err
	}
	// Try to establish a connection with the database	err = ()
	if err != nil {
		return err
	}
	return nil
}

func main() {
	err := initDB() 
	if err != nil {
		("init db failed,err:%v\n", err)
		return
	}
}

inIt is a database object (struct instance) representing the connection, which saves all information related to the connection database. It maintains a connection pool with zero to multiple underlying connections internally, which can be safely used by multiple goroutines at the same time.

1.4 SetMaxOpenConns

func (db *DB) SetMaxOpenConns(n int)

SetMaxOpenConnsSets the maximum number of connections to the database. If n is greater than 0 and less than the maximum number of idle connections, the maximum number of idle connections will be reduced to the limit that matches the maximum number of open connections. If n<=0, the maximum number of open connections will not be limited, and the default is 0 (unlimited).

1.5 SetMaxIdleConns

func (db *DB) SetMaxIdleConns(n int)

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

2. CRUD

2.1 Construction of database and tables

Let's create a name in MySQLsql_testDatabase

CREATE DATABASE sql_test;

Enter the database:

use sql_test;

Run the following command to create a data table for testing:

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;

2.2 Query

To facilitate query, we define a structure in advance to store the data of the user table.

type user struct {
	id   int
	age  int
	name string
}

2.3 Single-line query

Single-line query()Execute a query and expect to return up to one row of results (i.e. Row). QueryRow always returns a value that is not nil, and will not return a delayed error until the Scan method that returns the value is called.

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

Specific example code:

// Example of querying single datafunc queryRowDemo() {
	sqlStr := "select id, name, age from user where id=?"
	var u user
	// Make sure that the Scan method is called after QueryRow, otherwise the database link you hold will not be released	err := (sqlStr, 1).Scan(&, &, &)
	if err != nil {
		("scan failed, err:%v\n", err)
		return
	}
	("id:%d name:%s age:%d\n", , , )
}

2.4 Multi-line query

Multi-line query()Execute a query and return multiple rows of results (i.e. Rows), which is generally used to execute select commands. The parameter args represents the placeholder parameter in the query.

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

Specific example code:

// Example of querying multiple datafunc queryMultiRowDemo() {
	sqlStr := "select id, name, age from user where id > ?"
	rows, err := (sqlStr, 0)
	if err != nil {
		("query failed, err:%v\n", err)
		return
	}
	// Close rows to release the database link held	defer ()

	// Looping the data in the result set	for () {
		var u user
		err := (&, &, &)
		if err != nil {
			("scan failed, err:%v\n", err)
			return
		}
		("id:%d name:%s age:%d\n", , , )
	}
}

2.5 Insert data

Insert, update and delete operations are usedExecmethod.

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

Exec executes a command once (including query, delete, update, insert, etc.), and the returned Result is a summary of the executed SQL commands. The parameter args represents the placeholder parameter in the query.

The specific example code for inserting data is as follows:

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

2.6 Update data

The specific example code for updating data is as follows:

// Update datafunc updateRowDemo() {
	sqlStr := "update user set age=? where id = ?"
	ret, err := (sqlStr, 39, 3)
	if err != nil {
		("update failed, err:%v\n", err)
		return
	}
	n, err := () // Number of rows affected by the operation	if err != nil {
		("get RowsAffected failed, err:%v\n", err)
		return
	}
	("update success, affected rows:%d\n", n)
}

2.7 Delete data

The specific example code for deleting data is as follows:

// Delete datafunc deleteRowDemo() {
	sqlStr := "delete from user where id = ?"
	ret, err := (sqlStr, 3)
	if err != nil {
		("delete failed, err:%v\n", err)
		return
	}
	n, err := () // Number of rows affected by the operation	if err != nil {
		("get RowsAffected failed, err:%v\n", err)
		return
	}
	("delete success, affected rows:%d\n", n)
}

III. Affairs

3.1 What is a transaction

Transaction: a smallest non-dividable work unit; 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 requires multiple DML (insert, update, delete) statements to be executed together to complete. When A transfers money to B, two update operations are required.

Only used in MySQLInnodbOnly databases or tables of the database engine support transactions. Transaction processing can be used to maintain the integrity of the database, ensuring that batches of SQL statements are either executed or not.

3.2 ACID of transactions

Usually, transactions must meet four conditions (ACID): atomicity (Atomicity, or inseparability), consistency, isolation (Isolation, also known as independence), and persistence.

3.3 Transaction-related methods

The following three methods are used in Go language to implement transaction operations in MySQL. Start a transaction

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

Submit transactions

func (tx *Tx) Commit() error

Roll back transactions

func (tx *Tx) Rollback() error

3.4 Transaction Example

The following code demonstrates a simple transaction operation that ensures that two update operations either succeed or fail at the same time, without an intermediate state.

// Transaction operation examplefunc transactionDemo() {
	tx, err := () // Start transactions	if err != nil {
		if tx != nil {
			() // rollback		}
		("begin trans failed, err:%v\n", err)
		return
	}
	sqlStr1 := "Update user set age=30 where id=?"
	ret1, err := (sqlStr1, 2)
	if err != nil {
		() // rollback		("exec sql1 failed, err:%v\n", err)
		return
	}
	affRow1, err := ()
	if err != nil {
		() // rollback		("exec () failed, err:%v\n", err)
		return
	}

	sqlStr2 := "Update user set age=40 where id=?"
	ret2, err := (sqlStr2, 3)
	if err != nil {
		() // rollback		("exec sql2 failed, err:%v\n", err)
		return
	}
	affRow2, err := ()
	if err != nil {
		() // rollback		("exec () failed, err:%v\n", err)
		return
	}

	(affRow1, affRow2)
	if affRow1 == 1 && affRow2 == 1 {
		("The transaction is submitted...")
		() // Submit transaction	} else {
		()
		("The transaction is rolled back...")
	}

	("exec trans success!")
}

This is the end of this article about the summary of Go language operation MySQL. For more information about Go language operation MySQL, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!