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
-
Enter the database cmd through the mysql -u root -p command, and then create a go_mysql database:
CREATE DATABASE go_mysql;
-
Enter the database: USE go_mysql;
-
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:
- The client replaces the placeholder of the SQL statement to obtain 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.
Execution process of preprocessing
- The SQL statement is divided into two parts - command part and data part;
- Send the command part to the MySQL server, and the MySQL server performs SQL preprocessing;
- Send the data part to the MySQL server, and the MySQL server replaces the placeholder of the SQL statement;
- 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:
- It is forbidden to write variables directly to SQL statements.
- Carry out hierarchical management of users and strictly control the permissions of users.
- 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.
- Encrypt the database information.