connect
The database/sql package in Go language provides a universal interface to ensure SQL or SQL like databases, and does not provide specific database drivers. When using database/sql package, you must inject (at least) one database driver.
Our commonly used databases basically have a complete third-party implementation. For example: MySQL driver
Download dependency
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 dirverName. dataSourceName specifies the data source. Generally, it includes at least the database file name and other necessary information for connection.
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 of code should be written below err judgment above
}
Question: why is defer dB in the above code The close() statement should not be written in if err= In front of nil?
Initialize connection
The Open function may just verify whether its parameters are in the correct format, but it does not actually create a connection to the database. If you want to check whether the name of the data source is true and valid, you should call the Ping method.
The returned DB object can be safely used by multiple goroutine s concurrently, and maintain its own free connection pool. Therefore, the Open function should only be called once and it is rarely necessary to close the 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"
// It will not verify whether the account password is correct
// be careful!!! Don't use: =, we assign a value to the global variable, and then use the global variable db in the main function
db, err = sql.Open("mysql", dsn)
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 the database
if err != nil {
fmt.Printf("init db failed,err:%v\n", err)
return
}
}
Where SQL DB is a database object (structure instance) that represents a connection. It stores 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 goroutine s at the same time.
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 number of idle connections, the maximum number of idle connections will be reduced to match the limit of 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).
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 number of open connections, the new maximum number of idle connections will be reduced to match the limit of the maximum number of open connections. If n < = 0, idle connections are not retained.
CRUD
Database and table building
Let's first create a database named SQL in MySQL_ Test database
CREATE DATABASE sql_test;
Enter the database:
use sql_test;
Execute 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;
query
In order 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
}
Single line query
Single line query dB QueryRow() executes a query once and expects to return up to one Row of results (i.e. Row). QueryRow always returns non nil values and will not return delayed errors until the Scan method that returns the value is called. (for example, no result is 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 that the Scan method is called after QueryRow, otherwise the database link held 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
Multi row query dB Query() executes a query and returns multiple lines of results (i.e. Rows). It is generally used to execute the select command. The parameter args represents the placeholder parameter in query.
func (db *DB) Query(query string, args ...interface{}) (*Rows, error)
Specific example code:
// Query multiple data examples
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 and release the database links held
defer rows.Close()
// Cycle through the data in 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
The Exec method is used for insert, update, and delete operations.
func (db *DB) Exec(query string, args ...interface{}) (Result, error)
Exec executes commands 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 query.
The specific insert data example code is as follows:
// 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
The specific update data example code is as follows:
// 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 by the operation
if err != nil {
fmt.Printf("get RowsAffected failed, err:%v\n", err)
return
}
fmt.Printf("update success, affected rows:%d\n", n)
}
Delete data
The specific example code for deleting data is as follows:
// 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 by the operation
if err != nil {
fmt.Printf("get RowsAffected failed, err:%v\n", err)
return
}
fmt.Printf("delete success, affected rows:%d\n", n)
}
MySQL preprocessing
What is preprocessing?
Normal SQL statement execution process:
- The client replaces the placeholder of the SQL statement to get the complete SQL statement.
- The client sends a complete SQL statement to the MySQL server
- The MySQL server executes complete SQL statements and returns the results to the client.
Preprocessing execution process:
- The SQL statement is divided into two parts, command part and data part.
- First, send the command part to the MySQL server for SQL preprocessing.
- Then the data part is sent to the MySQL server, which replaces the placeholder of the SQL statement.
- The MySQL server executes complete SQL statements and returns the results to the client.
Why preprocessing?
- Optimizing the method of repeated SQL execution of MySQL server can improve the server performance, let the server compile in advance, compile and execute multiple times at a time, and save the cost of subsequent compilation.
- Avoid SQL injection problems.
Go implements MySQL preprocessing
The following Prepare method is used in database/sql to implement preprocessing operations.
func (db *DB) Prepare(query string) (*Stmt, error)
The Prepare method will first send the sql statement to the MySQL server and return a prepared status for subsequent queries and commands. The return value can execute multiple queries and commands at the same time.
The preprocessing example code of query operation is as follows:
// Preprocessing query example
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()
// Cycle through the data in 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)
}
}
The preprocessing of insert, update and delete operations is very similar. Here, take the preprocessing of insert operations as an example:
// Preprocessing insert example
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("princeling", 18)
if err != nil {
fmt.Printf("insert failed, err:%v\n", err)
return
}
_, err = stmt.Exec("Shahe Naza", 18)
if err != nil {
fmt.Printf("insert failed, err:%v\n", err)
return
}
fmt.Println("insert success.")
}
SQL injection problem
We should not splice SQL statements ourselves at any time!
Here we demonstrate an example of splicing SQL statements by ourselves. Write a function to query the user table according to the name field as follows:
// 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 time, the following input strings can cause SQL injection problems:
sqlInjectDemo("xxx' or 1=1#")
sqlInjectDemo("xxx' union select * from user #")
sqlInjectDemo("xxx' and (select count(*) from user) <10 #")
Supplement: the placeholder syntax used by SQL statements is different in different databases.
| database | Placeholder syntax |
|---|---|
| MySQL | ? |
| PostgreSQL | $1, $2, etc |
| SQLite | ? And $1 |
| Oracle | :name |
Go implements MySQL transactions
What is a transaction?
Transaction: a 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 be completed jointly by executing multiple DML(insert, update, delete) statements. A transfers money to B, which requires two update operations.
In MySQL, only databases or tables that use the Innodb database engine support transactions. Transaction processing can be used to maintain the integrity of the database and ensure that batch SQL statements are either executed or not executed.
ACID of the transaction
Generally, transactions must meet four conditions (ACID): Atomicity (or indivisibility), Consistency, Isolation (also known as independence) and persistence.
| condition | explain |
|---|---|
| Atomicity | All operations in a transaction are either completed or not completed, and will not end at an intermediate stage. If an error occurs during the execution of the transaction, it will be rolled back to the state before the transaction starts, as if the transaction had never been executed. |
| uniformity | Before and after the transaction, the integrity of the database is not destroyed. This means that the written data must fully comply with all preset rules, including the accuracy and serialization of the data, and the subsequent database can spontaneously complete the predetermined work. |
| Isolation | The database allows multiple concurrent transactions to read, write and modify their data at the same time. Isolation can prevent data inconsistency caused by cross execution when multiple transactions are executed concurrently. Transaction isolation is divided into different levels, including Read uncommitted, read committed, repeatable read, and Serializable. |
| persistence | After the transaction is completed, the data modification is permanent and will not be lost even if the system fails. |
Transaction related methods
Go language uses the following three methods to implement transaction operations in MySQL. Start transaction
func (db *DB) Begin() (*Tx, error)
Commit transaction
func (tx *Tx) Commit() error
Rollback transaction
func (tx *Tx) Rollback() error
Transaction example
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.
// Transaction operation example
func transactionDemo() {
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
}
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("The transaction is committed...")
tx.Commit() // Commit transaction
} else {
tx.Rollback()
fmt.Println("Transaction rollback...")
}
fmt.Println("exec trans success!")
}