Principle and usage analysis of sql package in go language

This article illustrates the principle and use of the sql package in the go language. I will share it with you for your reference as follows:

go's sql package is in pkg/database. The two packages inside sql and sql/driver can be viewed from 1. It is recommended to look at doc.txt in the sql folder before looking at the two packages. This document says several important things:

1 these two packages are true Go style packages.

2 using these two packages eliminates the need for concurrent processing and the need to maintain its own database connection pool. 1 creates a connection that can be Shared between goroutine and goroutine.

3 sql/driver provides the interface to the database, and the specific implementation needs to be implemented by itself.

See first database/driver package

The first method used is Register

This method registers the driver that implements driver.Driver into the variable drivers. After writing a driver, you need to register the driver in sql to use the interfaces in the sql package. This implementation of driver.Driver must implement the Open method.

driver.Open returns driver.Conn, its three methods

Prepare: parameter binding
Close: close the connection
Begin: support transactions

So let's look at Prepare, which is the same as php mysql pdo1

Conn.Prepare("select * from test where a=?")

Return the Stmt structure:

Close: close this statement
NumInput: returns how many parameters can be bound
Exec: no use of Insert or update returned
Query: select and other query operations are used
Exec is the binding variable and then returns the Result structure
Query is the bound variable, and then returns the Rows result set

Look at the method in Result:

LastInsertId() : the primary key id obtained after the Insert operation
RowsAffect() : the number of rows affected
Rows:
Columns() : the columns of the returned data are the names of the returned table columns
Close() : close Rows, and nothing can be done after the call
Next() : fetch the data of the next row into des[] Value. The Value interface here can be int64, float64, bool, []byte, string, time.Time

So let's go back to Begin, which returns Tx

After starting the transaction, there are two behaviors in addition to the query: Commit and Rollback, both of which are methods of the Tx interface

The structure in drvier is all about interfaces, which you need to implement and register with Register.

The specific use of the driver is in database/sql

First look at several sql structures once

First of all, the structures in sql are one-layer encapsulation of the structures in driver, such as Rows, which has an internal property of rowsi driver.Rows.

The actual operation of sql is performed using the interface inside driver.Rows, which is actually the operation of driver implemented by yourself.

driver and sql are like plugs and a car full of plugs 1. You implement driver, that is, you implement the configuration of these plugs and you can use the car sql.

Result: corresponding to Result1 in driver, that is, if you implement driver.Result, then you automatically implement sql.Result. It's an interface, but it doesn't really do anything special. If all the Result in the sql package were replaced with driver.Result, it would work, too.

Rows: driver.Rows is based on driver.Rows and extends several other methods on it. Method of ownership:

Close
Cloumns
Err
Next
Scan
Stmt: based on driver.Stmt. Have a method
Close
Exec
Query
QueryRow
Tx: based on driver.Tx. Method of ownership:
Commit
Exec
Prepare
Query
QueryRow
Rollback
Stmt

From sql Open began

The sql.DB structure is returned, which realizes the driver.Conn structure. In addition to the existing Prepare, Begin and Close of Conn, several more query methods are directly added:

Driver() : returns the current driver
Exec() : operates directly
Query() : query and return Rows
QueryRow() : expected to return a single line, returns Row

Both Rows and Row have a nice way of putting data into specified variables, Scan.

For example, the following is a typical use of Rows

rows, err := db.Query("SELECT ...")

...

for rows.Next() {

var id int

var name string

err = rows.Scan(&id, &name)

...

}

Prepare returns the Stmt structure

Exec returns the Result structure

There are different approaches to these structures.

I hope this article has been helpful to you in programming the Go language.