The complete step record of golang operating MySQL database

Time:2021-2-23

preface

MySQL is a commonly used relational database in the industry, and it often deals with MySQL database in normal development, so in the next part, we will introduce how to use go language to operate MySQL database.

Download MySQL connection driver

The database / SQL package in go language provides a universal interface to ensure SQL or SQL like database, and does not provide specific database driver. You must inject (at least) one database driver when using the database / SQL package.

Our commonly used databases basically have 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, and datasourcename specifies the data source, which generally includes at least the database file name and other necessary information for connection.

Example code:

import ( "database/sql"​ _  " github.com/go -sql-driver/mysql")​func main() { //  DSN:Data Source  Name dsn := " user:[email protected] (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 the err judgment above}

Initialize connection

The open function may only verify whether its parameter format is correct, but it does not actually create a connection to the database. If you want to check whether the name of the data source is real and valid, you should call the ping method.

The returned DB object can be safely used by multiple goroutines simultaneously, and maintain its own free connection pool. Therefore, the open function should only be called once, and there is little need to close the DB object.

The sample code is as follows:

//Define a global object dbvar dB* sql.DB // define a func initdb() function to initialize the database (error error) {// DSN:Data Source  Name dsn := " user:[email protected] (127.0.0.1:3306)/sql_ Test? Charset = utf8mb4 & parsetime = true "// will not verify whether the account password is correct // Note!!! Don't use: =, we assign values to global variables, and then use global variables dB, err in the main function= sql.Open ("mysql",  DSN) if err! = nil {return err} // try to establish a connection with the database (verify whether DSN is correct) err= db.Ping () if err! = nil {return err} return nil} func main() {err: = initdb() // call the output database function if err! = nil{ fmt.Printf ("init db failed,err:%v\n", err) return }}

among sql.DB Is the database object (structure instance) that represents the connection. It holds all the information related to the connection to the database. It maintains a connection pool with zero to multiple underlying connections, 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 match 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 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 maximum number of open connections. If n < = 0, idle connections are not retained.

Creating database and table in MySQL

Let’s create a MySQL database called ` SQL ‘_ 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;

Operating MySQL query

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


type user struct { id int age int name string}

**Single line query**

Single line query` db.QueryRow ()’executes a query and expects to return up to one row of results (row). Queryrow always returns a value other than nil. It does not return the delayed error until the scan method of the returned value is called. (e.g. no result found)


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

Example code:

// query the single data example func queryRowDemo () {sqlStr: = select id, name, age from user where id=? "Where // very important: make sure that after calling, the database method will not be released. 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 line query**

Multi line query db.Query () to execute a query and return multi row results (i.e. rows), which is generally used to execute the select command. The args parameter represents the space occupying parameter in query.


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

Example code:

//Example of querying multiple data: 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 held database link defer rows.Close () // read the data in the result set circularly 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) }}

Operate Mysql to insert data

The insert, update, and delete operations all use the exec method.


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

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

The specific insert data sample 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 if err! = nil of newly inserted data{ fmt.Printf ("get lastinsert ID failed, err:%v\n",  err) return }  fmt.Printf ("insert success, the id is %d.\n", theID)}

Operating Mysql to 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 operation if err! = nil{ fmt.Printf ("get RowsAffected failed, err:%v\n", err) return }  fmt.Printf ("update success,  affected rows:%d\n", n)}

Operating 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:= 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 operation if err! = nil{ fmt.Printf ("get RowsAffected failed, err:%v\n", err) return }  fmt.Printf ("delete success,  affected rows:%d\n", n)}

SQL injection security

We should not splice SQL statements by ourselves at any time!

Here we demonstrate an example of self splicing SQL statements, and 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 point, 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 #")

Complete sample code archive GitHub

Example code of golang operating MySQL database

So far, this article about the complete step record of golang operating MySQL database is introduced here. For more information about golang operating MySQL database, please search previous articles of developer or continue to browse the following related articles. I hope you can support developer more in the future!

Recommended Today

Use of Android WebView (super detailed usage)

1.1 overview of WebView Android WebView is a special view on the Android platform. It can be used to display web pages. This WebView class can be used to display only one online web page in the app. Of course, it can also be used to develop browsers. The internal implementation of WebView uses WebKit […]