<>Go operation Mysql database

go There is one in it database/sql Package for , It defines how to connect and operate the database ,
And native supports connection pooling , Is concurrency safe .
This standard library has no specific implementation , It just lists the specific contents that need to be implemented by the third-party library .

* connect sql After the condition of the 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 .
<> Some errors may occur during installation , Then look at your own GOPATH Is the configuration correct , This is very important .

* 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(" // Connect to the database _, err := sql.Open("mysql",
dsn) if err != nil { fmt.Printf("open %s failed,err:%v\n", dsn, err) return }
fmt.Println(" Database connection successful ") }

* That means success !!!!
We can extract this code into an initialization function , Then query and insert later
var db *sql.DB // Is a connection pool object func initDB() (err error) { // database information
user name : password @( Local machine :3306)/ Specific database dsn := "root:root@tcp(" // Connect to the database
, this db Variables pay attention to using global variables , Do not use self defined . db, err = sql.Open("mysql", dsn) if err != nil {
return } err = db.Ping() // Try to connect to the database if err != nil { return } return }
* Then we define a structure entity class , It is used 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 one first sql sentence , You can try it from the database first , If it succeeds, write it into the code , The usage of this and java Inside JDBCTemplate be similar .

<> You can see that it has been queried from the database

* Then we query multiple statements // according to id Query multi row data range 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 release 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) } }
* adopt Rows.Next() To traverse and query each group of data , Then print it out in turn .
<> insert data

* insert , Both modification and deletion use Exec method , func (db *DB) Exec(query string, args ...interface{})
(Result, error)
* Exec Execute the command once ( Include query , delete , to update , Insert, etc ), Returned Result Is for the executed SQL Summary of commands . parameter args express query Placeholder parameters in .
* 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() // Get new inserted data id 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 , Multiple question marks can be set , Then implement .
<> update operation ( modify )

* Also used Exec method . Then one ret result
* We can get it through the results RowsAffected, The return value of this method is the number of rows that affect the database when you modify the data
* 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) }

* We can see clearly from the picture above , The value in the database has changed .
<> Delete operation

* Also 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 it , Delete after our inquiry , If you check again, you won't find it !!!
* Next, let's look at pretreatment , Key point ha
<> Database preprocessing

What is pretreatment ?

ordinary SQL Statement execution procedure :

* Client pair SQL Statement to get the complete SQL sentence .
* Client send complete SQL Statement to MySQL Server
* MySQL The server executes complete SQL Statement and return the result to the client .
Pretreatment execution process :

* hold SQL The statement is divided into two parts , Command part and data part .
* Send the command part to MySQL Server ,MySQL Server side SQL Pretreatment .
* Then send the data part to MySQL Server ,MySQL Service end pair SQL Statement for placeholder replacement .
* MySQL The server executes complete SQL Statement and return the result to the client .
So why do we need pretreatment ?

* optimization MySQL Server repeat SQL Method of , Can improve server performance , Let the server compile in advance , Compile and execute multiple times at once , Save the cost of subsequent compilation .
* avoid SQL Injection problem .
<>Go Realize pretreatment

* database/sql Use the following in Prepare Method to implement the preprocessing operation .

* Then come to realize it ! // Preprocessing implementation mysql insert 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, " Zhang Fei ":56, " Guan Yu ":45, " Liu Bei ":44,
} // Data traversal insertion for k,v := range m { stmt.Exec(k,v) } }

* This operation will be much faster than ordinary insertion .
<> Similarly, it can be obtained : Relative deletion , The modification operation is the same as this one !!!

<>SQL Injection problem

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

* Here we demonstrate a self splicing SQL Example of statement , Write a basis name Field query user The functions of the table are 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 ! Hahahahahaha
* Can be set to true forever , Or add your data volume to your merge , Or get the data in your database .
* So we must take precautions !!!
<> summary

* Database in Java I have learned it once since I was born , So learning again is fast , It's time to start downloading drivers
* The last problem found is GOPATH There is a problem with the configuration , If you encounter a friend with the same invalid reference, you can go and have a look

* Look at this GOPATH Is it the one you set up , Because the default is in c In a file under the disk . It's annoying .
* okay , Come on , Wish my thesis once !!!! Successful internship !!!!

©2019-2020 Toolsou All rights reserved,
Solve in servlet The Chinese output in is a question mark C String function and character function in language MySQL management 35 A small coup optimization Java performance —— Concise article Seven sorting algorithms (java code ) use Ansible Batch deployment SSH Password free login to remote host according to excel generate create Build table SQL sentence Spring Source code series ( sixteen )Spring merge BeanDefinition Principle of Virtual machine installation Linux course What are the common exception classes ?