Background
What is a prepared statement?
A prepared statement is SQL that is parsed and saved by the DBMS, typically containing placeholders but with no actual parameter values. Later, the statement can be executed with a set of parameter values.
How you use prepared statements
When you expect to execute the same SQL repeatedly, you can use an sql.Stmt to prepare the SQL statement in advance, then execute it as needed.
The following example creates a prepared statement that selects a specific album from the database. DB.Prepare returns an sql.Stmt representing a prepared statement for a given SQL text. You can pass the parameters for the SQL statement to Stmt.Exec, Stmt.QueryRow, or Stmt.Query to run the statement.
// AlbumByID retrieves the specified album.
func AlbumByID(id int) (Album, error) {
// Define a prepared statement. You'd typically define the statement
// elsewhere and save it for use in functions such as this one.
stmt, err := db.Prepare("SELECT * FROM album WHERE id = ?")
if err != nil {
log.Fatal(err)
}
var album Album
// Execute the prepared statement, passing in an id value for the
// parameter whose placeholder is ?
err := stmt.QueryRow(id).Scan(&album.ID, &album.Title, &album.Artist, &album.Price, &album.Quantity)
if err != nil {
if err == sql.ErrNoRows {
// Handle the case of no rows returned.
}
return album, err
}
return album, nil
}
Demo
package main
import (
"database/sql"
"fmt"
"log"
"time"
_ "github.com/go-sql-driver/mysql"
)
func main() {
db, err := sql.Open("mysql", "root:123456@/task2")
if err != nil {
panic(err)
}
// See "Important settings" section.
db.SetConnMaxLifetime(time.Minute * 3)
db.SetMaxOpenConns(10)
db.SetMaxIdleConns(10)
results, _ := db.Query("select * from core_channel where name = ?", "name1")
fmt.Println(results)
for results.Next() {
var channel Channel
// for each row, scan the result into our channel composite object
err = results.Scan(&channel.ID, &channel.Name)
if err != nil {
panic(err.Error()) // proper error handling instead of panic in your app
}
// and then print out the channel's Name attribute
log.Printf(channel.Name)
}
}
type Channel struct {
ID int `json:"id"`
Name string `json:"name"`
}
Query vs Exec vs Prepare
Query
Scenario 1 - plaintext
We should always use db.Query whenever we want to do a select and we should never ignore the returned rows of Query but iterate over it (else we’ll leak the db connection!)
Doing Query(query) will not use a prepared statement (see the wireshark capture below)
results, _ := db.Query("select * from core_channel where name = 'name1'" )
- Notice that only 1 TCP request was sent from client to server(minus login)
- Connection will be released automatically to the pool when the returned rows are iterated, or we can call rows.Close() explicitly when we are done.
Scenario 2 - prepared
Doing Query(queryTemplate, params) will use a prepared statement under the covers.
results, _ := db.Query("select * from core_channel where name = ?", "name1")
- Notice that 3 TCP requests were sent from client to server(minus login)
- Connection will be released automatically to the pool when the returned rows are iterated, or we can call rows.Close() explicitly when we are done.
Source Code
所谓prepared，即带有占位符的sql语句，客户端将该语句和参数发给mysql服务器。mysql服务器编译成一个prepared语句，这个语句可以根据不同的参数多次调用。prepared语句执行的方式如下：
- 准备prepare语句
- 执行prepared语句和参数
- 关闭prepared语句
之所以会出现prepare语句方式，主要因为这样有下面的两个好处：
- 避免通过引号组装拼接sql语句。避免sql注入带来的安全风险
- 可以多次执行的sql语句。
func (db *DB) queryDC(ctx, txctx context.Context, dc *driverConn, releaseConn func(error), query string, args []interface{}) (*Rows, error) {
...
if ok {
var nvdargs []driver.NamedValue
var rowsi driver.Rows
var err error
withLock(dc, func() {
...
rowsi, err = ctxDriverQuery(ctx, queryerCtx, queryer, query, nvdargs) // if len(args) != 0, return driver.ErrSkip. Else, directly query
})
if err != driver.ErrSkip { // 不带prepared param的query
if err != nil {
releaseConn(err)
return nil, err
}
// Note: ownership of dc passes to the *Rows, to be freed
// with releaseConn.
rows := &Rows{
dc: dc,
releaseConn: releaseConn,
rowsi: rowsi,
}
rows.initContextClose(ctx, txctx)
return rows, nil
}
}
// prepared param的query
var si driver.Stmt
var err error
withLock(dc, func() {
si, err = ctxDriverPrepare(ctx, dc.ci, query) // call ci.Prepare internally
})
if err != nil {
releaseConn(err)
return nil, err
}
ds := &driverStmt{Locker: dc, si: si}
rowsi, err := rowsiFromStatement(ctx, dc.ci, ds, args...) // call stmt.Query internally
if err != nil {
ds.Close()
releaseConn(err)
return nil, err
}
// Note: ownership of ci passes to the *Rows, to be freed
// with releaseConn.
rows := &Rows{
dc: dc,
releaseConn: releaseConn,
rowsi: rowsi,
closeStmt: ds,
}
rows.initContextClose(ctx, txctx)
return rows, nil
}
Exec
We should always use db.Exec whenever we want to do an insert or update or delete.
Scenario 1
Doing Exec(query) will not use a prepared statement, so lesser TCP calls to the SQL server
results, _ := db.Exec("INSERT INTO core_channel (name) VALUES ('name11')")
- Notice that only 1 TCP request was sent from client to server(minus login)
- Releases the connection automatically to the pool.
Scenario 2
Doing Exec(queryTemplate, params) will use prepared statement under the covers, so more number of TCP calls to the SQL server.
results, _ := db.Exec("INSERT INTO core_channel (name) VALUES (?)", "name11")
- Notice that 3 TCP requests were sent from client to server(minus login)
- Releases the connection automatically to the pool.
Prepare
This should be used only when we want to prepare once at the start of the program and execute N number of times during the course of the program.
- Notice that 2 TCP requests were sent from client to server(minus login).
- We need to close the statement explicitly when we don’t need the prepared statement anymore. Else, we’ll fail to free up allocated resources both on the client as well as server!
Source Code
func (db *DB) PrepareContext(ctx context.Context, query string) (*Stmt, error) {
var stmt *Stmt
var err error
for i := 0; i < maxBadConnRetries; i++ {
stmt, err = db.prepare(ctx, query, cachedOrNewConn)
if err != driver.ErrBadConn {
break
}
}
if err == driver.ErrBadConn {
return db.prepare(ctx, query, alwaysNewConn)
}
return stmt, err
}
func (db *DB) prepare(ctx context.Context, query string, strategy connReuseStrategy) (*Stmt, error) {
...
return db.prepareDC(ctx, dc, dc.releaseConn, nil, query)
}