Go operating Mysql database is so smooth

Go operation Mysql database

There is a database/sql package in go, which defines the methods of connecting and operating the database,
And the native supports connection pool, which is concurrent and safe.
This standard library has no specific implementation, but lists the specific contents that need to be implemented by a third-party library.

After connecting the sql package, we need to download the third-party driver

Download driver

go get github.com/go-sql-driver/mysql

Remember to configure the environment first.

There may be some errors during installation, so it is important to see whether your GOPATH is configured correctly.

Then let's take a look at the code

package main

import (
	"database/sql"
	"fmt"
	_ "github.com/go-sql-driver/mysql"
)


func main() {
	// database information 
	dsn := "root:root@tcp(127.0.0.1:3306)/community"
	// Connect to database
	_, err := sql.Open("mysql", dsn)
	if err != nil {
		fmt.Printf("open %s failed,err:%v\n", dsn, err)
		return
	}
	fmt.Println("Database connection succeeded")
}

This means success!!!!

We can extract this code into an initialization function, and then query and insert it later

var db *sql.DB   // Is a connection pool object

func initDB() (err error) {
	// Database information user name: password @ (local: 3306) / specific database
	dsn := "root:root@tcp(127.0.0.1:3306)/junmu"
	// Connect to the database. Pay attention to using global variables for this db variable. Do not use self-defined variables.
	db, err = sql.Open("mysql", dsn)
	if err != nil {
		return
	}
	err = db.Ping()   // Trying to connect to the database
	if err != nil {
		return
	}
	return
}

Then we define a structure entity class to store the data we read from the database

type user struct {
	id   int
	age  int
	name string
}

Then we read a sentence first

func queryRow(id int){
	sqlStr := "select id,name,age from user where id = ?"
	var u user
	err := db.QueryRow(sqlStr,id).Scan(&u.id,&u.name,&u.age)
	if err != nil {
		fmt.Printf("queryRow failed! err:%v",err)
	}
	fmt.Println("id:",u.id," name:",u.name, " age:",u.age)
}

Write an sql statement first. You can try it from the database and write it into the code after success. The usage of this statement is similar to that of the JDBC template in java.

You can see that it has been queried from the database

Then we query multiple statements

// Multi row data range query based on id
func query(id int) {
	sqlStr := "select id,name,age from user where id < ?"
	rows,err := db.Query(sqlStr,id)
	if err != nil {
		fmt.Printf("query rows failed! err:%v\n",err)
		return
	}
	defer rows.Close()  // Close and free the resources of the database
	// Loop traversal for data query
	for rows.Next() {
		var u user
		err := rows.Scan(&u.id,&u.name,&u.age)
		if err != nil {
			fmt.Printf("rowsNext query failed! err:%v\n",err)
			return
		}
		fmt.Println("id:",u.id," name:",u.name, " age:",u.age)
	}
}

Traverse and query each group of data through Rows.Next(), and then print it out in turn.

insert data

The Exec method is used for insertion, modification and deletion,

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

Exec executes commands (including query, delete, update, insert, etc.) once, and the returned Result is a summary of the executed SQL commands. The parameter args represents the placeholder parameter in query.
Let's fight

func insertRow(name string,age int){
	sqlStr := "insert into user(name,age) values(?,?)"
	ans ,err := db.Exec(sqlStr,name,age)
	if err != nil {
		fmt.Printf("insertRow failed! err:%v\n",err)
		return
	}
	theID,err := ans.LastInsertId()  // Gets the id of the newly inserted data
	if err != err {
		fmt.Printf("id query failed! err:%v\n",err)
		return
	}
	fmt.Printf("insert success! the id is %d.\n",theID)

}

A data call can be completed by calling. args is a parameter
For these question marks, if you want to insert multiple groups of data, you can set multiple question marks and implement them.

Update operation (modify)

The Exec method is also used. Then a ret result
We can get RowsAffected from the result. The return value of this method is the number of rows in the database affected by your data modification
We can judge whether our method runs successfully by the number of rows.

func updataRow(id,age int){
	sqlStr := "update user set age = ? where id = ?"
	ret,err := db.Exec(sqlStr,age,id)
	if err != nil {
		fmt.Printf("updateRow failed! err:%v",err)
		return
	}
	// Get affected functions
	total,err := ret.RowsAffected()
	if err != nil {
		fmt.Printf("get RowsAffected failed! err:%v\n",err)
		return
	}
	fmt.Println("update success! Affected rows:",total)
}

From the picture above, we can clearly see that the value in the database has changed.

Delete operation

It's also the Exec method Oh, good memory!!!

// Delete data operation
func deleteRow(id int){
	sqlStr := "delete from user where id = ?"
	ret, err := db.Exec(sqlStr,id)
	if err != nil {
		fmt.Printf("delete failed! err:%v\n",err)
		return
	}
	ans,err := ret.RowsAffected()
	if err != nil {
		fmt.Printf("get RowsAffected failed! err:%v\n",err)
		return
	}
	fmt.Println("delete success!! RowsAffected: ",ans)
}

You can see that ha, we delete it after query. If we query again, we can't find it!!!
Let's take a look at the pretreatment. The key point is ha

Database 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.

So why do we preprocess?

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 implementation preprocessing

The following Prepare method is used in database/sql to implement preprocessing operations.

Then come to realize it!

// Preprocessing to implement mysql insertion
func prepareInsert() {
	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()
	var m = map[string]int {
		"Koko son":30,
		"Brother Tao Zi":20,
		"Ah, chicken":88,
		"Water son":16,
		"Fei Zhang":56,
		"Guan Yu":45,
		"Liu Bei":44,
	}
	// Insertion of traversal data
	for k,v := range m {
		stmt.Exec(k,v)
	}
}

This operation will be much faster than ordinary insertion.

Similarly, you can get: the relative deletion and modification operations are the same as this!!!

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 point, we output

sqlInjectDemo("xxx' or 1=1#")
sqlInjectDemo("xxx' union select * from user #")
sqlInjectDemo("xxx' and (select count(*) from user) <10 #")

You'll find a big problem! Ha ha ha ha ha ha
You can always set it to true, or merge and add your data, or get the data in your database.
So be sure to guard against it!!!

summary

Database has been learned once in Java, so it's still very fast to learn again, that is, it's very urgent to start downloading drivers
Finally, I found a problem with GOPATH configuration. If you encounter the same reference failure, you can go and have a look

See if this GOPATH is the one you set, because it is in a file under Disk c by default. It's annoying.
Well, come on, wish my paper once!!!! Successful internship!!!!

Posted by malec on Wed, 10 Nov 2021 21:56:03 -0800

Programmer Group