The basic use

The first is to import the driver package

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

Then connect to local mysql

Package main import ("database/ SQL ") The init method in the library _ "github.com/go-sql-driver/mysql") func main() {DSN := is automatically executed when representing import "Root :1234qwer@tcp(127.0.0.1:3306)/dbname" db, error := sql.open ("mysql", DSN) // defer db.close () can't defer in front of error judgment // because if open fails, db is usually nil ! = nil {panic(error)} // Make sure db is not nil at close() defer db.close ()}Copy the code

Note, however, that the above code does not actually link to a database

The open function simply verifies that the arguments are properly formatted and that the ping method is used to actually link to the database

Package main import ("database/ SQL "" FMT" The init method in the library _ "github.com/go-sql-driver/mysql") func main() {DSN := is automatically executed when representing import "Root :1234qwer@tcp(127.0.0.1:3306)/dbname" db, error := sql.open ("mysql", DSN) // defer db.close () can't defer in front of error judgment // because if open fails, db is usually nil ! = nil {panic(error)} // After checking for errors make sure db is not nil defer db.close () error = db.ping () if error! = nil { fmt.Println("db connect failed") panic(error) } fmt.Println("db connect success") }Copy the code

If you look at it, you can obviously see that the connection failed because we don’t have a database named dbname

I’ll change that and link to our local Go Test database so that it’s correct

Modify the above code to extract a function to do init SQL

Package main import ("database/ SQL "" FMT" // define a global object that is concurrency-safe var db * sql.db func InitMysql () (err error) {DSN := "root:1234qwer@tcp(127.0.0.1:3306)/go_test" db, err = sql.open ("mysql", DSN) // defer db.close () can't defer in front of error judgment // because if open fails, db is usually nil. If Close of nil, error is reported and key database cannot be printed. = nil { return err } err = db.Ping() if err ! = nil { return err } return nil } func main() { err := initMysql() if err ! = nil { panic(err) } defer db.Close() fmt.Println("db connect success") }Copy the code

Of course, under normal circumstances we also need to configure the db connection number attribute according to the specific situation of the business

Db.setconnmaxlifetime (time.second * 10) // Set the maximum number of connections. The default value is unlimited. If this limit is exceeded, db.setMaxOpenConns (200) // Db.setmaxidleconns (10) Set the maximum number of idle connections. The default value is unlimited.Copy the code

SQL driver workflow is briefly introduced

We talked a little bit about the idea of an anonymous package guide and there’s an init function that does the initialization for us

You can see that there are some lock operations in this register function. What is this lock?

It’s pretty obvious that we’re declaring a global object, kind of like the Singleton in Java where we have a lock for reading and writing, and a map where the key is a string and the value is a driver interface

Obviously, map types do not support concurrency, so we need a global lock for driversMu to ensure concurrency safety when we operate on maps

So let’s go back to the open function that we called earlier

The overall code is not difficult to understand, and we finally generated the DB structure.

The important thing to note here is SQL as a standard library

The most important is this driver file, which is a bunch of interfaces, standard library SQL operations in the operation are interfaces, any developer who wants to implement a specific database driver as long as the corresponding interface can be implemented

You can then use the init function to inject your own drivers into the execution of the SQL annotation library

Is a clever way of writing interface – oriented programming

Simple CRUD operations

Single line query:

func queryTest() { sqlStr := "select id,age,name from user where id=?" Var u user row := db.QueryRow(sqlStr, 1) // QueryRow must run scan otherwise the database connection will not be released err := row. scan (&u.id, &u.age, &u.name) if err ! = nil { fmt.Println(err) } fmt.Println("result:", u) }Copy the code

Here must pay attention to scan method yo otherwise will always hold a database connection, when the connection number is full is online failure

So we usually write it like this: chain call

func queryTest() { sqlStr := "select id,age,name from user where id=?" Error := db.queryRow (sqlStr, 1).Scan(&u.id, &u.age, &u.name) if err! = nil { fmt.Println(err) } fmt.Println("result:", u) }Copy the code

Multi-line query:

func queryRows() { sqlStr := "select id,age,name from user where id>?" rows, err := db.Query(sqlStr, 0) if err ! = nil {fmt.println (err)} // Please remember to defer rows.close () // Actually next will also release the connection at the end, For rows.next () {var u user err := rows.scan (&u.id, &u.age,) {var u user err := rows.scan (&u.id, &u.age, &u.name) if err ! = nil { fmt.Println(err) } fmt.Println(u) } }Copy the code

The add operation:

func insert() { sqlStr := "insert into user(name,age) values(? ,?) "Ret, err := db.Exec(sqlStr," ma ", 99) if err! = nil { fmt.Println(err) return } id, err2 := ret.LastInsertId() if err2 ! = nil { fmt.Println("get lastid error:", err2) return } fmt.Println("insert success,id: ", id) }Copy the code

Update operations are the same as delete operations and add operations, there is no difference, they are the same template,

All it does is change the SQL statement and return an affected number of rows

Mysql pretreatment

The ABOVE CRUD example is an ordinary SQL statement execution process,

First of all, we process placeholders in the SQL statement to get a complete SQL statement and then the client sends the complete SQL statement to mysql and finally the mysql server executes the complete SQL statement and returns the result to the client

Pretreatment is different

The client sends the command part of the SQL statement to the server and mysql preprocesses the command part and the client sends the data part and mysql replaces the placeholders of the SQL statement and returns the result

Benefits of pretreatment:

Optimize the mysql server to execute SQL repeatedly to improve mysql performance

It also avoids SQL injection problems

Here’s an example:

func prepareTest() { sqlStr := "select id,age,name from user where id>?" stmt, err := db.Prepare(sqlStr) if err ! = nil { fmt.Println(err) return } defer stmt.Close() rows, err2 := stmt.Query(0) if err2 ! = nil { fmt.Println(err2) return } defer rows.Close() for rows.Next() { var u user err := rows.Scan(&u.id, &u.age, &u.name) if err ! = nil { fmt.Println(err) } fmt.Println(u) } }Copy the code

Mysql transaction

func transactionDemo() { tx, err := db.Begin() if err ! = nil { if tx ! = nil { tx.Rollback() } fmt.Println("begin trans failed") return } sqlstr1 := "update user set age=30 where id=?" _, err2 := tx.Exec(sqlstr1, 1) if err2 ! = nil { tx.Rollback() fmt.Println(err2) return } sqlstr2 := "update user set age=30 where id=?" _, err3 := tx.Exec(sqlstr2, 2) if err3 ! = nil { tx.Rollback() fmt.Println(err3) return } err4 := tx.Commit() if err4 ! = nil { tx.Rollback() fmt.Println(err4) return } }Copy the code

There are only three operations to focus on: begin Commit and rollback