This is the 7th day of my participation in the Gwen Challenge in November. Check out the details: The Last Gwen Challenge in 2021.

The Go language operates Mysql

::: TIP Database/SQL in Go does not contain database drivers. Database/SQL provides a generic interface to guarantee SQL or SQL-like databases. You must inject (at least) one database driver when using SQL packages. See golang.org/s/sqldriver… Gets the driver list. For more usage examples, see the Wiki page: golang.org/s/sqlwiki. : : :

1 connect MySQL

Access the database as a client through the GO page

  • 1. Since GO language does not provide any official database drivers, it is necessary to install a third-party function library.
  • 2. Install git software and git tutorial on Github

1.1 Download Dependency

go get -u github.com/go-sql-driver/mysql
Copy the code

1.2 Using the MySQL driver

Open Opens a database driver specified by driverName. DataSourceName specifies that the data source has a connection information string in a certain format.

func Open(driverName, dataSourceName string) (*DB, error)
Copy the code

1.3 Sample Code

package main

import (
	"database/sql"
	"fmt"
// Register a driver for mysql with "database/ SQL "by executing init() in driver.go
	_ "github.com/go-sql-driver/mysql" 
)

func main(a) {
	dsn := "Root: admin @ TCP/go_test (127.0.0.1:3306)? charset=utf8"
	//Open opens a driverName specified database, dataSourceName specified data source
	// The user name and password are not checked, only the format of the DSN is checked
	db, err := sql.Open("mysql", dsn)
	iferr ! =nil { // If the DSN format is incorrect, an error will be reported
		fmt.Printf("Failed to open database,err:%v\n", err)
		return
	}
	// Try to connect to the database and Ping to check whether the data source name and account password are valid.
	err = db.Ping()
	iferr ! =nil {
		fmt.Printf("Database connection failed,err:%v\n", err)
		return
	}
	fmt.Println("Database connection successful!")}Copy the code

2 Initialize the connection

2.1 DB (Database Object)

SQL DB type represents the database, database operation, other languages need to create a connection, is the need to create a database for the Go type, it is not a database connection, Go the connection from the internal implementation of connection pool, the establishment of the connection is inert, connection will be at the time of operation, from the connection pool is created and maintained.

// Create a database type using sql.Open. The first is the database driver name and the second is a string of connection information
var Db *sqlx.DB
db, err := sqlx.Open("mysql"."username:password@tcp(ip:port)/database? charset=utf8")
Db = db
Copy the code

2.2 DB Connection Pool

The Open function may simply validate its arguments without creating a connection to the database. If you want to check that the name of the data source is valid, call the Ping method that returns the value.

The returned DB can safely be used by multiple Goroutines simultaneously and maintains its own pool of idle connections. In this way, the Open function only needs to be called once. DB rarely needs to be shut down.

package main

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

var (
	// Define a global object db
	 db *sql.DB
	// Define database connection parameter values
	// User name to connect to the database
	userName  string = "root"
	// Password to connect to the database
	password  string = "admin"
	// Address to connect to the database
	ipAddress string = "127.0.0.1"
	// Port number to connect to the database
	port      int    = 3306
	// The name of the database to connect to
	dbName    string = "go_test"
	// The encoding format of the connection database
	charset   string = "utf8"
)

func initDB(a) (err error) {
	dsn := fmt.Sprintf("%s:%s@tcp(%s:%d)/%s? charset=%s", userName, password, ipAddress, port, dbName, charset)
	//Open opens a driverName specified database, dataSourceName specified data source
	// The user name and password are not checked, only the format of the DSN is checked
	db, err = sql.Open("mysql", dsn)
	iferr ! =nil { // If the DSN format is incorrect, an error will be reported
		return err
	}
	// Try to connect to the database to verify the DSN is correct
	err = db.Ping()
	iferr ! =nil {
		fmt.Println("Verification failed,err", err)
		return err
	}
	// Set the maximum number of connections
	db.SetMaxOpenConns(50)
	// Set the maximum number of idle connections
	// db.SetMaxIdleConns(20)
	fmt.Println("Database connection successful!")
	return nil
}
func main(a) {
	err := initDB()
	iferr ! =nil {
		fmt.Println("Failed to initialize database,err", err)
		return}}Copy the code

3 Database CRUD

3.1 Build database build table sentence

CREATE DATABASE go_test;
use go_test;
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=utf8;
Copy the code

3.2 DB.exec method description

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

  • The primary key ID of the inserted data can be obtained using the LastInsertId() method
  • The number of affected rows is available from RowsAffected blocks
  • Insert data through the Exec() method and return a Result of type SQL.result
func (db *DB) Exec(query string, args ...interface{}) (Result, error)
Copy the code

Note that different databases use different placeholders. Mysql uses? As placeholders, the remaining database placeholders are described as follows:

The database Placeholder syntax
MySQL ?
PostgreSQL
1 . 1, 2 etc.
SQLite ? And $1
Oracle :name
Insert data
“`go
package main

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

Var (// define a global object db db *sql. db // define parameters related to database connection values // userName for connecting to the database userName string = “root” // password for connecting to the database password string = “admin” // database connection ipAddress ipAddress string = “127.0.0.1” // database connection port int = 3306 // database connection name dbName string = “go_test” Charset string = “utf8”)

func initDB() (err error) { dsn := fmt.Sprintf(“%s:%s@tcp(%s:%d)/%s? Charset =%s”, userName, password, ipAddress, port, dbName, charset) Db, err = sql.Open(“mysql”, DSN) if err! Return err} // Try to connect to the database to check whether the DSN is correct err = db.ping () if err! Println(” error,err”, Db.setmaxopenconns (50) // Set the maximum number of idle connections. // db.setMaxIdleconns (20) fmt.Println(” Database connection succeeded!” ) return nil }

Func main() {err := initDB() if err! Type user struct {id int name string age int} func main() {err := initDB() if err! = nil {fmt.Println(” failed to initialize database,err”, err) return} newUser := user{name: “siu “, age: 98} insertRow(newUser) }

NewUser —-user structure func insertRow(newUser user) {// The SQL statement to be inserted,? SQL > insert into user(name,age) values(? ,?) Ret, err := db.Exec(sqlStr, newuser.name, newuser.age) if err! Println(” insert failed,err”, err) return} newID, err := ret.lastinsertid () // The ID of the newly inserted data, Default primary key //rowsNumber, err:= ret.rowsaffected () // If err! = nil {fmt.Println(” failed to get ID,err”, err) return} fmt.Println(” inserted successfully, id: “, newID)}

Go package main import ("database/ SQL "" FMT" _ "github.com/go-sql-driver/mysql") var (// define a global object db Db *sql. db // define parameters related to database connection // userName for connecting to the database userName string = "root" // password for connecting to the database password string = "admin" // address for connecting to the database IpAddress string = "127.0.0.1" // Port number for connecting to the database Port int = 3306 // Database name for connecting to the database dbName string = "go_test" // Coding format for connecting to the database charset string = "utf8" ) func initDB() (err error) { dsn := fmt.Sprintf("%s:%s@tcp(%s:%d)/%s?charset=%s", userName, Password, ipAddress, port, dbName, charset) //Open Open a driverName specified database, dataSourceName specified data source // does not verify the user name and password is correct, Db, err = sql.Open("mysql", DSN) if err! Return err} // Try to connect to the database to check whether the DSN is correct err = db.ping () if err! Println(" error,err", Db.setmaxopenconns (50) // Set the maximum number of idle connections. // db.setMaxIdleconns (20) fmt.Println(" Database connection succeeded!" Type user struct {id int name string age int} func main() {err := initDB() if err ! Println(" failed to initialize database,err", err) return} // updateUser := user{id: 7, name: "updateUser ", age: 98} updateRow(updateUser)} // Update data // updateUser ---- User structure to be updated func updateRow(updateUser user) {sqlStr := "update user set age=? ,name=? where id = ?" ret, err := db.Exec(sqlStr, updateUser.age, updateUser.name, updateUser.id) if err ! Printf(" update failed,err:%v\n", err) return} n, err: = ret.rowsaffected () if err! = nil {fmt.Printf(" error,err:%v\n", err) return} fmt.Printf(" error, %d\n", n)}Copy the code

3.5 Deleting Data

package main

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

var (
	// Define a global object db
	 db *sql.DB
	// Define database connection parameter values
	// User name to connect to the database
	userName  string = "root"
	// Password to connect to the database
	password  string = "admin"
	// Address to connect to the database
	ipAddress string = "127.0.0.1"
	// Port number to connect to the database
	port      int    = 3306
	// The name of the database to connect to
	dbName    string = "go_test"
	// The encoding format of the connection database
	charset   string = "utf8"
)

func initDB(a) (err error) {
	dsn := fmt.Sprintf("%s:%s@tcp(%s:%d)/%s? charset=%s", userName, password, ipAddress, port, dbName, charset)
	//Open opens a driverName specified database, dataSourceName specified data source
	// The user name and password are not checked, only the format of the DSN is checked
	db, err = sql.Open("mysql", dsn)
	iferr ! =nil { // If the DSN format is incorrect, an error will be reported
		return err
	}
	// Try to connect to the database to verify the DSN is correct
	err = db.Ping()
	iferr ! =nil {
		fmt.Println("Verification failed,err", err)
		return err
	}
	// Set the maximum number of connections
	db.SetMaxOpenConns(50)
	// Set the maximum number of idle connections
	// db.SetMaxIdleConns(20)
	fmt.Println("Database connection successful!")
	return nil
}

// Create an organization with the same structure as the database table
type user struct {
	id   int
	name string
	age  int
}
func main(a) {
	err := initDB()
	iferr ! =nil {
		fmt.Println("Failed to initialize database,err", err)
		return
	}
	// The id of the user structure corresponding to the database record to be modified cannot be empty
	deleteUser := user{
		id:   6,
		name: "Crayon Shin.",
		age:  98}
	deleteRow(deleteUser)
}

// Delete data
// deleteUser ---- Specifies the user structure to be deleted
func deleteRow(deleteUser user) {
	sqlStr := "DELETE FROM user WHERE 1=1 AND id = ?"
	ret, err := db.Exec(sqlStr, deleteUser.id)
	iferr ! =nil {
		fmt.Printf("Failed to delete data,err:%v\n", err)
		return
	}
	n, err := ret.RowsAffected() // The number of rows affected by the operation
	iferr ! =nil {
		fmt.Printf("Failed to get number of affected rows,err:%v\n", err)
		return
	}
	fmt.Printf("Delete data successfully, number of rows affected :%d\n", n)
}
Copy the code

3.6 Querying Data

3.6.1 Single-line Query

Single-row query db.queryRow () executes a query and expects to return at most one Row of results (that is, rows). The syntax is as follows:

func (db *DB) QueryRow(query string, args ...interface{}) *Row
Copy the code
package main

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

var (
	// Define a global object db
	 db *sql.DB
	// Define database connection parameter values
	// User name to connect to the database
	userName  string = "root"
	// Password to connect to the database
	password  string = "admin"
	// Address to connect to the database
	ipAddress string = "127.0.0.1"
	// Port number to connect to the database
	port      int    = 3306
	// The name of the database to connect to
	dbName    string = "go_test"
	// The encoding format of the connection database
	charset   string = "utf8"
)

func initDB(a) (err error) {
	dsn := fmt.Sprintf("%s:%s@tcp(%s:%d)/%s? charset=%s", userName, password, ipAddress, port, dbName, charset)
	//Open opens a driverName specified database, dataSourceName specified data source
	// The user name and password are not checked, only the format of the DSN is checked
	db, err = sql.Open("mysql", dsn)
	iferr ! =nil { // If the DSN format is incorrect, an error will be reported
		return err
	}
	// Try to connect to the database to verify the DSN is correct
	err = db.Ping()
	iferr ! =nil {
		fmt.Println("Verification failed,err", err)
		return err
	}
	// Set the maximum number of connections
	db.SetMaxOpenConns(50)
	// Set the maximum number of idle connections
	// db.SetMaxIdleConns(20)
	fmt.Println("Database connection successful!")
	return nil
}

// Create an organization with the same structure as the database table
type user struct {
	id   int
	name string
	age  int
}
func main(a) {
	err := initDB()
	iferr ! =nil {
		fmt.Println("Failed to initialize database,err", err)
		return
	}
	// The id of the user structure corresponding to the database record to be modified cannot be empty
	queryUser := user{
		id:   3}
	QueryRow(queryUser)
}
// Query data
func QueryRow(queryUser user)  {
	sqlStr := "SELECT id,name,age from user WHERE 1=1 AND id = ?"
	row := db.QueryRow(sqlStr, queryUser.id)
	var u user
	// Then use the Scan() method to assign a value to the corresponding type variable to fetch the result. Note that the pointer is passed in
	err:=row.Scan(&u.id, &u.name, &u.age)
	iferr ! =nil {
		fmt.Printf("Error getting data, err:%v\n", err)
		return
	}
	fmt.Printf("Data query succeeded %#v",u)
}
Copy the code

3.6.2 Multi-Line Query

Db.query () executes a Query that returns multiple Rows (that is, a result set of type SQL.rows), iterates the latter using the Next() method, then uses the Scan() method to assign the corresponding type variable to the result set, and finally closes the result set (free the connection).

func (db *DB) Query(query string, args ...interface{}) (*Rows, error)
Copy the code
package main

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

var (
	// Define a global object db
	db *sql.DB
	// Define database connection parameter values
	// User name to connect to the database
	userName  string = "root"
	// Password to connect to the database
	password  string = "admin"
	// Address to connect to the database
	ipAddress string = "127.0.0.1"
	// Port number to connect to the database
	port      int    = 3306
	// The name of the database to connect to
	dbName    string = "go_test"
	// The encoding format of the connection database
	charset   string = "utf8"
)

func initDB(a) (err error) {
	dsn := fmt.Sprintf("%s:%s@tcp(%s:%d)/%s? charset=%s", userName, password, ipAddress, port, dbName, charset)
	//Open opens a driverName specified database, dataSourceName specified data source
	// The user name and password are not checked, only the format of the DSN is checked
	db, err = sql.Open("mysql", dsn)
	iferr ! =nil { // If the DSN format is incorrect, an error will be reported
		return err
	}
	// Try to connect to the database to verify the DSN is correct
	err = db.Ping()
	iferr ! =nil {
		fmt.Println("Verification failed,err", err)
		return err
	}
	// Set the maximum number of connections
	db.SetMaxOpenConns(50)
	// Set the maximum number of idle connections
	// db.SetMaxIdleConns(20)
	fmt.Println("Database connection successful!")
	return nil
}

// Create an organization with the same structure as the database table
type user struct {
	id   int
	name string
	age  int
}
func main(a) {
	err := initDB()
	iferr ! =nil {
		fmt.Println("Failed to initialize database,err", err)
		return
	}

	queryRows()
}
// multi-line query
func queryRows(a)  {
	sqlStr := "select id,name,age from user where id>?"
	rows,err := db.Query(sqlStr,0)
	iferr ! =nil{
		fmt.Println("Query failed,err",err)
		return
	}
	defer rows.Close()  // Close the connection
	// loop to read data
	for rows.Next(){
		var u user
		err := rows.Scan(&u.id,&u.name,&u.age)
		iferr ! =nil{
			fmt.Println("Err scan fail,",err)
			return
		}
		fmt.Printf("id:%d name:%s age:%d\n", u.id, u.name, u.age)
	}
}
Copy the code

4 MySQL Preprocessing

4.1 What is pre-processing?

::: tip Process of executing common SQL statements

  • 1. The client performs placeholder replacement on 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 the complete SQL statement and returns the result to the client.

::: ::: TIP preprocessing execution process:

  • 1. Divide the SQL statement into two parts, the command part and the data part.
  • 2, first send the command part to MySQL server, MySQL server for SQL preprocessing.
  • 3. The data portion is then sent to the MySQL server, which performs placeholder replacements for SQL statements.
  • 4. The MySQL server executes the complete SQL statement and returns the result to the client.

: : :

4.2 Why pretreatment?

  • By optimizing the MySQL server to execute SQL repeatedly, the server performance can be improved and the server can be compiled in advance. In this way, the cost of subsequent compilation can be saved.
  • Avoid SQL injection problems.

4.3 MySQL Preprocessing

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 simultaneously.

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

4.3.1 Query preprocessing

package main

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

var (
	// Define a global object db
	db *sql.DB
	// Define database connection parameter values
	// User name to connect to the database
	userName  string = "root"
	// Password to connect to the database
	password  string = "admin"
	// Address to connect to the database
	ipAddress string = "127.0.0.1"
	// Port number to connect to the database
	port      int    = 3306
	// The name of the database to connect to
	dbName    string = "go_test"
	// The encoding format of the connection database
	charset   string = "utf8"
)

func initDB(a) (err error) {
	dsn := fmt.Sprintf("%s:%s@tcp(%s:%d)/%s? charset=%s", userName, password, ipAddress, port, dbName, charset)
	//Open opens a driverName specified database, dataSourceName specified data source
	// The user name and password are not checked, only the format of the DSN is checked
	db, err = sql.Open("mysql", dsn)
	iferr ! =nil { // If the DSN format is incorrect, an error will be reported
		return err
	}
	// Try to connect to the database to verify the DSN is correct
	err = db.Ping()
	iferr ! =nil {
		fmt.Println("Verification failed,err", err)
		return err
	}
	// Set the maximum number of connections
	db.SetMaxOpenConns(50)
	// Set the maximum number of idle connections
	// db.SetMaxIdleConns(20)
	fmt.Println("Database connection successful!")
	return nil
}

// Create an organization with the same structure as the database table
type user struct {
	id   int
	name string
	age  int
}
func main(a) {
	err := initDB()
	iferr ! =nil {
		fmt.Println("Failed to initialize database,err", err)
		return
	}

	prepareQueryRow()
}
// Query preprocessing
func prepareQueryRow(a)  {
	sqlStr := "select id,name,age from user where id > ?"
	stmt,err := db.Prepare(sqlStr)
	iferr ! =nil{
		fmt.Println("Preprocessing failed,err",err)
		return
	}
	defer stmt.Close()
	rows,err := stmt.Query(0)
	iferr ! =nil{
		fmt.Println("Query failed,err",err)
		return
	}
	defer rows.Close()
	// loop read
	for rows.Next(){
		var u user
		err := rows.Scan(&u.id,&u.name,&u.age)
		iferr ! =nil{
			fmt.Println("Err scan fail,",err)
			return
		}
		fmt.Printf("id:%d name:%s age:%d\n", u.id,u.name,u.age)
	}
}
Copy the code

4.3.2 Pretreatment of addition, deletion and modification

The preprocessing of insert, update and delete operations is very similar. Here we take the preprocessing of insert operations as an example:

package main

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

var (
	// Define a global object db
	db *sql.DB
	// Define database connection parameter values
	// User name to connect to the database
	userName  string = "root"
	// Password to connect to the database
	password  string = "admin"
	// Address to connect to the database
	ipAddress string = "127.0.0.1"
	// Port number to connect to the database
	port      int    = 3306
	// The name of the database to connect to
	dbName    string = "go_test"
	// The encoding format of the connection database
	charset   string = "utf8"
)

func initDB(a) (err error) {
	dsn := fmt.Sprintf("%s:%s@tcp(%s:%d)/%s? charset=%s", userName, password, ipAddress, port, dbName, charset)
	//Open opens a driverName specified database, dataSourceName specified data source
	// The user name and password are not checked, only the format of the DSN is checked
	db, err = sql.Open("mysql", dsn)
	iferr ! =nil { // If the DSN format is incorrect, an error will be reported
		return err
	}
	// Try to connect to the database to verify the DSN is correct
	err = db.Ping()
	iferr ! =nil {
		fmt.Println("Verification failed,err", err)
		return err
	}
	// Set the maximum number of connections
	db.SetMaxOpenConns(50)
	// Set the maximum number of idle connections
	// db.SetMaxIdleConns(20)
	fmt.Println("Database connection successful!")
	return nil
}

// Create an organization with the same structure as the database table
type user struct {
	id   int
	name string
	age  int
}
func main(a) {
	err := initDB()
	iferr ! =nil {
		fmt.Println("Failed to initialize database,err", err)
		return
	}

	prepareInsertDemo()
}
// Batch insert
func prepareInsertDemo(a) {
	sqlStr := "insert into user (name,age) values(? ,?) "
	stmt, err := db.Prepare(sqlStr) // Send the command to the MySQL server for preprocessing
	iferr ! =nil {
		fmt.Printf("Preprocessing failed, err:%v\n", err)
		return
	}
	defer stmt.Close()
	// Repeat the insert command
	for i := 10; i < 15; i++ {
		name := fmt.Sprintf("name%02d", i)
		stmt.Exec(name, i)
	}
	fmt.Println("Batch insert succeeded")}Copy the code

5 the MySQL transaction

5.1 What are Transactions?

1. One of the smallest non-divisible units of work; Usually a transaction corresponds to a complete business (for example, a bank account transfer business, which is a minimal unit of work), and this complete business requires multiple DML(INSERT, UPDATE, DELETE) statements to be executed. A transfers money to B, which requires two update operations.

Transactions are only supported in MySQL for databases or tables that use the Innodb database engine. Transaction processing can be used to maintain database integrity by ensuring that batches of SQL statements are either all executed or none executed.

5.2 Transaction ACID

Generally transactions must meet 4 ACID conditions: Atomicity, Consistency, Isolation, and Durability.

conditions explain
atomic All operations in a transaction either complete or do not complete and do not end somewhere in between. The transaction will be rolled back if an error occurs during execution (Rollback) to the state before the transaction started, as if the transaction had never been executed.
consistency The integrity of the database is not compromised before and after a transaction. This means that the data written must conform to all the preset rules, including the accuracy of the data, the concatenation of the data, and the ability of the subsequent database to do its predetermined work spontaneously.
Isolation, The ability of a database to allow multiple concurrent transactions to read, write, and modify its data at the same time. Isolation prevents data inconsistencies due to cross-execution when multiple transactions are executed concurrently. There are different levels of transaction isolation, including read uncommitted (Read uncommitted), read submit (read committed), repeatable (repeatable read) and serialization (Serializable).
persistence After a transaction, changes to the data are permanent and will not be lost even if the system fails.

5.3 Transaction related methods

The following three methods are used to implement transactions in MySQL in Go.

// Start transaction
func (db *DB) Begin(a) (*Tx, error)

// Commit the transaction
func (tx *Tx) Commit(a) error

// Rollback the transaction
 func (tx *Tx) Rollback(a) error
Copy the code

5.4 Transaction Examples

package main

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

var (
	// Define a global object db
	db *sql.DB
	// Define database connection parameter values
	// User name to connect to the database
	userName  string = "root"
	// Password to connect to the database
	password  string = "admin"
	// Address to connect to the database
	ipAddress string = "127.0.0.1"
	// Port number to connect to the database
	port      int    = 3306
	// The name of the database to connect to
	dbName    string = "go_test"
	// The encoding format of the connection database
	charset   string = "utf8"
)

func initDB(a) (err error) {
	dsn := fmt.Sprintf("%s:%s@tcp(%s:%d)/%s? charset=%s", userName, password, ipAddress, port, dbName, charset)
	//Open opens a driverName specified database, dataSourceName specified data source
	// The user name and password are not checked, only the format of the DSN is checked
	db, err = sql.Open("mysql", dsn)
	iferr ! =nil { // If the DSN format is incorrect, an error will be reported
		return err
	}
	// Try to connect to the database to verify the DSN is correct
	err = db.Ping()
	iferr ! =nil {
		fmt.Println("Verification failed,err", err)
		return err
	}
	// Set the maximum number of connections
	db.SetMaxOpenConns(50)
	// Set the maximum number of idle connections
	// db.SetMaxIdleConns(20)
	fmt.Println("Database connection successful!")
	return nil
}

// Create an organization with the same structure as the database table
type user struct {
	id   int
	name string
	age  int
}
func main(a) {
	err := initDB()
	iferr ! =nil {
		fmt.Println("Failed to initialize database,err", err)
		return
	}

	transDemo()
}


func transDemo(a)  {
	tx,err := db.Begin()
	iferr ! =nil{
		iftx ! =nil {
			tx.Rollback() / / rollback
		}
		fmt.Println("Failed to start transaction,err",err)
		return
	}
	sql1 := "update user set age=age+? where id=?"
	_,err = tx.Exec(sql1,2.1)
	iferr ! =nil{
		tx.Rollback()
		fmt.Println("Failed to execute SQL1,err",err)
		return
	}
	sql2 := "update user set age=age-? where id=?"
	_,err = tx.Exec(sql2,2.2)
	iferr ! =nil{
		tx.Rollback()
		fmt.Println("Failed to execute SQl2,err",err)
		return
	}
	err = tx.Commit()
	iferr ! =nil{
		tx.Rollback()
		fmt.Println("Transaction commit failed,err",err)
		return
	}
	fmt.Println("Data update successful!")}Copy the code

6 ORM framework learning

Gorm, a third-party library, simplifies operations and improves development efficiency. Especially for structures

Start learning about the GORM framework