Golang SQL Operation First Experience

This is a creation in Article, where the information may have evolved or changed.

Brief introduction

Golang provides the Database/sql package for accessing SQL database, the most important thing in this package is SQL. DB out.
For SQL. DB, we need to emphasize that, 它并不代表一个数据库连接 it is an abstract access interface for an existing database. sql. The DB provides us with two important features:

• Sql. DB drives the open and close operations for us to manage the underlying database connections.

• Sql. DB manages database connection pool for us

One thing to note is that SQL is the reason for this. DB is the connection pool to manage the database connection, every time we do database operations, we need to pull a connection from the connection pool, when the operation task is completed, we need to return this connection to the connection pool, so if we do not correctly return the connection to the connection pool, then it will cause DB. SQL opens too many database connections so that database connection resources are exhausted.

Basic operation of MySQL database

Database-driven Import

A friend with experience in database development knows that we need a database driver to connect to a specific database. This is no exception in the Golang. For example, take the MySQL database as an example:

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

It is important to note that, in general, we should not use the methods provided by the driver directly, but should use SQL instead. DB, so when we import the MySQL driver, we used the anonymous import method (add _before the package path).
When a database driver is imported, this driver initializes itself and registers itself in the database/sql context of Golang, so we can access the database through the methods provided by the Database/sql package.

Connection to the database

When the MySQL driver is imported, we open the database connection:

func main() {    db, err := sql.Open("mysql",        "user:password@tcp(127.0.0.1:3306)/test")    if err != nil {        log.Fatal(err)    }    defer db.Close()}

Through SQL. The Open function allows you to create a database abstraction operation interface that returns a SQL if opened successfully. DB pointer.
Sql. The signature of the Open function is as follows:

func Open(driverName, dataSourceName string) (*DB, error)

It receives two parameters:

• DriverName, the driver name used. This name is actually the name that the database driver uses when registering to Database/sql.

• DataSourceName, a link to the second database connection. This link contains information such as the user name of the database, the password, the database host, and the name of the database that needs to be connected.

需要注意的是, golang 对数据库的连接是延时初始化的(lazy init), 即 sql.Open 并不会立即建立一个数据库的网络连接, 也不会对数据库链接参数的合法性做检验, 它仅仅是初始化一个 sql.DB 对象.When we do the first database query operation, the network connection is actually established at this time.
If we want to immediately check that the database connection is available, we can take advantage of SQL. The Ping method for DB, for example:

err = db.Ping()if err != nil {    log.Fatal(err)}

Sql. Best Practices for DB:
Sql. DB objects are used as long-lived objects, and we should avoid frequent calls to Open () and Close (). That is, in general, when we are working on a database, we create a SQL. DB and save it, passing this SQL every time the database is manipulated. DB object, and finally close the corresponding SQL when it is necessary to access the database. DB object.

Queries for databases

The general steps for database queries are as follows:

• Call DB. Query executes the SQL statement, and this method returns a Rows as the result of the query

• Through rows. Next () Iterates over the query data.

• Through rows. Scan () reads the value of each row

• Call DB. Close () closes the query

For example, we have one of the following database tables:

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=3 DEFAULT CHARSET=utf8mb4

We insert a record into it:

func insertData(db *sql.DB) {    rows, err := db.Query(`INSERT INTO user (id, name, age) VALUES (1, "xys", 20)`)    defer rows.Close()    if err != nil {        log.Fatalf("insert data error: %v\n", err)    }    var result int    rows.Scan(&result)    log.Printf("insert result %v\n", result)}

by calling Db. Query, we executed an INSERT statement to insert a piece of data. When execution is complete, the first thing to do is to check that the statement executes successfully and, when there is no error, pass through rows. Scan Gets the result of the execution. Because insert returns the number of rows of inserted data, the statement we print is "Insert result 0".

Next, we take the inserted data out of the database:

func selectData(db *sql.DB) {    var id int    var name string    var age int    rows, err := db.Query(`SELECT * From user where id = 1`)    if err != nil {        log.Fatalf("insert data error: %v\n", err)        return    }    for rows.Next() {        rows.Scan(&id, &age, &name)        if err != nil {            log.Fatal(err)        }        log.Printf("get data, id: %d, name: %s, age: %d", id, name, age)    }    err = rows.Err()    if err != nil {        log.Fatal(err)    }}

The process of the above code is basically not very different, but one thing we need to be aware of is rows. the order of the Scan parameters is important and corresponds to the column of the query's results. For example, the column order of the row for "select * from user where id = 1" is "ID, name, age", so rows. Scan also needs to be in this order of rows. Scan (&id, &name, &age), otherwise it will cause the dislocation of data reading.

注意:

1. For each database operation, you need to check for an error to return

2. Per Db. After the Query operation, you need to call rows. Close (). Because of the DB. Query () Gets a connection from the database connection pool if we do not call rows. Close (), the connection is always occupied. So usually we use defer rows. Close () to ensure that database connections are correctly placed back into the connection pool.

3. Called Rows multiple times. Close () does not have side effects, so even if we have already shown the rows to be called. Close (), we should still use defer rows. Close () to dismiss the query.

The complete example is as follows:

Func insertdata (db *sql. db) {rows, err: = db. Query (' INSERT into user (ID, name, age) VALUES (1, ' XYs ', ') ') defer rows. Close () if err! = Nil {log. Fatalf ("Insert data error:%v\n", err)} var result int rows. Scan (&result) log. Printf ("Insert result%v\n", result)}func selectdata (db *sql. db) {var id int var name string var age int rows, err: = db. Query (' SELECT ID, name, age from user where id = 1 ') If err! = Nil {log. Fatalf ("Insert data error:%v\n", err) return} for rows. Next () {err = rows. Scan (&id, &name, &age) if err! = Nil {log. Fatal (Err)} log. Printf ("Get data, ID:%d, Name:%s, Age:%d", ID, Name, age)} err = rows. ERR () if err! = Nil {log. Fatal (Err)}}func main () {db, err: = SQL. Open ("MySQL", "root:root@tcp (127.0.0.1:3306)/test") defer db. Close () if err! = Nil {fmt. Printf ("Connect to DB 127.0.0.1:3306 error:%v\n", err)       return} insertdata (db) Selectdata (db)} 

Precompiled statements (Prepared Statement)

Precompiled statements (PreparedStatement) provide many benefits, so we use it as much as possible in development. The features provided by using precompiled statements are listed below:

• PreparedStatement can implement a custom parameter query

• PreparedStatement is typically more efficient than manually stitching string SQL statements.

• PreparedStatement to prevent SQL injection attacks

Below we will use Prepared Statement to rewrite the example in the previous section:

Func deleteData (db *sql. db) {stmt, _: = db. Prepare (' DELETE from user WHERE id =? ') rows, err: = stmt. Query (1) Defer stmt. Close () rows. Close () if err! = Nil {log. Fatalf ("Delete data error:%v\n", err)} rows, err = stmt. Query (2) rows. Close () if err! = Nil {log. Fatalf ("Delete data error:%v\n", err)}}func insertdata (db *sql. db) {stmt, _: = db. Prepare (' INSERT into user (ID, name, age) VALUES (?,?,?) ') rows, err: = stmt. Query (1, "XYs", defer stmt). Close () rows. Close () if err! = Nil {log. Fatalf ("Insert data error:%v\n", err)} rows, err = stmt. Query (2, "test", +) var result int rows. Scan (&result) log. Printf ("Insert result%v\n", result) rows. Close ()}func selectdata (db *sql. db) {var id int var name string var age int stmt, _: = db. Prepare (' SELECT * from user where >? ') rows, err: = stmt. Query (Ten) defer stmt. Close () Defer rows. Close () if err! = Nil {loG.fatalf ("Select data error:%v\n", err) return} for rows. Next () {err = rows. Scan (&id, &name, &age) if err! = Nil {log. Fatal (Err)} log. Printf ("Get data, ID:%d, Name:%s, Age:%d", ID, Name, age)} err = rows. ERR () if err! = Nil {log. Fatal (Err)}}func main () {db, err: = SQL. Open ("MySQL", "root:root@tcp (127.0.0.1:3306)/test") defer db. Close () if err! = Nil {fmt. Printf ("Connect to DB 127.0.0.1:3306 error:%v\n", err) return} deleteData (db) InsertData (db) Selectda TA (db)}