MySQL is a commonly used relational database in the industry. In normal development, WE often deal with MySQL database. Therefore, how to use Go language to operate MySQL database will be introduced in the next chapter.

Download the MySql connection driver

The DATABASE/SQL package in the Go language provides a generic interface to guarantee SQL or SQL-like databases and does not provide specific database drivers. You must inject (at least) one database driver when using the Database/SQL package.

Most of the databases we use have full third-party implementations. For example, MySQL driver

** Downloads depend on ** 

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

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

Open Opens a dirverName database, dataSourceName specifies the data source, usually including at least the database file name and other information necessary for connection.

Sample code:

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 ()Copy the code

Initializing a connection

The Open function may simply verify that its arguments are properly formatted and does not actually create a connection to the database. If you want to check that the name of the data source is valid, you should call the Ping method.

The returned DB object can be safely used concurrently by multiple Goroutines and maintains its own pool of free connections. Therefore, the Open function should only be called once and the DB object should rarely be closed.

Example code is as follows:

// define a function to initialize the database func initDB() (err error) {// DSN:Data Source Name DSN: = User: "@" TCP (127.0.0.1:3306)/sql_test? Charset =utf8mb4&parseTime=True" charset=utf8mb4&parseTime=True Db db, err = sql.Open("mysql", DSN) if err! Err = db.ping () if err! Func main() {err := initDB() // Call the output database function if err! = nil { fmt.Printf("init db failed,err:%v\n", err) return }}Copy the code

Where SQL. DB is the database object (structure instance) representing the connection, which holds all information about the connection to the database. It maintains an internal connection pool with zero to multiple underlying connections, which can safely be used simultaneously by multiple Goroutines.

** Sets the maximum number of connections ** 

func (db *DB) SetMaxOpenConns(n int)
Copy the code

SetMaxOpenConns Sets the maximum number of connections to the database. If n is greater than 0 and smaller than the maximum number of idle connections, the maximum number of idle connections is reduced to match the maximum number of open connections. If n<=0, there is no limit to the maximum number of open connections, which defaults to 0 (unrestricted).

** Sets the maximum number of idle connections ** 

func (db *DB) SetMaxIdleConns(n int)
Copy the code
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 is reduced to the limit matching the maximum number of open connections. If n<=0, idle connections are not retained.

MySql > create table

MySQL > create database ‘sql_test’; 
CREATE DATABASE sql_test;
Copy the code
To enter the database: 
use sql_test;
Copy the code
Create a table for testing by executing the following command: 
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;
Copy the code

MySql > alter database

To facilitate the query, we define a structure to store the data of the user table. 
type user struct {  id   int  age  int  name string}
Copy the code
** Single line query **

Single-row query ‘db.queryRow ()’ performs a query and expects to return at most one Row of results (that is, rows). QueryRow always returns non-nil values and does not return delayed errors until the Scan method that returns the value is called. (e.g. : No result found) 

func (db *DB) QueryRow(query string, args ... interface{}) *RowCopy the code
Sample code: 
Func queryRowDemo() {sqlStr := "select id, name, age from user where id=? Var u user // important: 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)}Copy the code

** Multi-line query **

Db.query () executes a Query that returns multiple Rows of results (that is, Rows), typically used to execute the select command. The args parameter represents the placeholder parameter in query.

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

Sample code:

Func queryMultiRowDemo() {sqlStr := "select id, name, age from user where id >? rows, err := db.Query(sqlStr, 0) if err ! Printf("query failed, err:%v\n", err) return} Defer rows.close () // loop to read 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) }}Copy the code

Insert data from MySql

The Exec methods are used for insert, update, and delete operations.

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

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.

Example code for inserting data is as follows:

Func insertRowDemo() {sqlStr := "insert into user(name, age) values (? ,?) "Ret, err := db.Exec(sqlStr, 38) if err! Printf("insert failed, err:%v\n", err) return} theID, err: = ret.lastinsertid () // theID 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)}Copy the code

Operation MySql update data

Example code for updating data is as follows:

Func updateRowDemo() {sqlStr := "Update user set age=? where id = ?" ret, err := db.Exec(sqlStr, 39, 3) if err ! Printf("update failed, err:%v\n", err) return} n, err: = ret.rowsaffected () // If err! = nil { fmt.Printf("get RowsAffected failed, err:%v\n", err) return } fmt.Printf("update success, affected rows:%d\n", n)}Copy the code

Delete data from MySql

Example code for deleting data is as follows:

Func deleteRowDemo() {sqlStr := "delete from user where id =?" ret, err := db.Exec(sqlStr, 3) if err ! Printf("delete failed, err:%v\n", err) return} n, err: = ret.rowsaffected () // If err! = nil { fmt.Printf("get RowsAffected failed, err:%v\n", err) return } fmt.Printf("delete success, affected rows:%d\n", n)}Copy the code

SQL injection security issues

We should never concatenate SQL statements ourselves!

Select * from user; select * from user; select * from user;

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)}Copy the code

Any of 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 #")
Copy the code

Complete sample code archived at GitHub

Golang operates MySql database sample code

The above is about the Go language operation of MySql, MySql transactions will be introduced in the future article, thank you for reading, if you have any questions or comments, please timely comment.

Article source “Go keyboard man” public number, welcome to pay attention to exchange learning together!