Preface
MySQL is a commonly used relational database in the industry. It often deals with MySql database during normal development, so I will introduce how to use Go language to operate MySql database.
Download MySql connection driver
The database/sql package in Go provides a universal interface to ensure SQL or SQL-like databases, and 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 basically have complete third-party implementations. for example:MySQL driver
**Download dependencies**
go get -u /go-sql-driver/mysql
**Use 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.
Sample code:
import ( "database/sql" _ "/go-sql-driver/mysql")func main() { // DSN:Data Source Name dsn := "user:password@tcp(127.0.0.1:3306)/dbname" db, err := ("mysql", dsn) if err != nil { panic(err) } defer () // Note that this line of code should be written below the above err judgment}
Initialize the connection
The Open function may just verify that its parameter format is correct and does not actually create a connection to the database. If you want to check whether the name of the data source is true or not, you should call the Ping method.
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.
The sample code is as follows:
// Define a global object dbvar db *// Define a function that initializes 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 // Note! ! ! Don't use := here, we assign values to global variables, and then use the global variables db db, err = ("mysql", dsn) if err != nil { return err } // Try to establish a connection with the database (check whether dsn is correct) err = () if err != nil { return err } return nil}func main() { err := initDB() // Call the function that outputs the database if err != nil { ("init db failed,err:%v\n", err) return }}
It is the 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.
**Set the maximum number of connections**
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 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).
**Set the maximum number of idle connections**
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.
MySql library building table building
Let's first create a database named `sql_test` in MySQL:
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;
Operation MySql 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}
**Single-line query**
A single-line query `()` executes a query and expects 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. (For example: no results were found)
func (db *DB) QueryRow(query string, args ...interface{}) *Row
Sample code:
// Example of querying single data func queryRowDemo() { sqlStr := "select id, name, age from user where id=?" var u user // Very important: Make sure that the Scan method is called after QueryRow, otherwise the database link held 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", , )}
**Multi-line query**
Multi-line query() executes a query and returns multiple-line 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)
Sample code:
// Example of querying multiple data func queryMultiRowDemo() { sqlStr := "select id, name, age from user where id > ?" rows, err := (sqlStr, 0) if err != nil { ("query failed, err:%v\n", err) return } // Very important: close rows to release the database link held defer () // Loop to read 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", , , ) }}
Operation MySql insert data
The Exec method is used for inserting, updating, and deleting operations.
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 data func 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)}
Operation MySql to update data
The specific example code for updating data is as follows:
// Update data func 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)}
Operation MySql to delete data
The specific example code for deleting data is as follows:
// Delete data func 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)}
SQL injection security issues
We should never splice SQL statements ourselves!
Here we demonstrate an example of splicing SQL statements by yourself, and write a function to query the user table based on the name field as follows:
// Sample sql injection func sqlInjectDemo(name string) { sqlStr := ("select id, name, age from user where name='%s'", name) ("SQL:%s\n", sqlStr) var u user err := (sqlStr).Scan(&, &, &) if err != nil { ("exec failed, err:%v\n", err) return } ("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 #")
Full sample code archive GitHub
Golang operates MySql database sample code
This is the article about the complete steps of Golang operating MySql database recording. For more related content on Golang operating MySql database, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!