Golang Mysql Prepare
针对单次操作的查询 Golang Mysql 有两类操作Query和Exec
Query返回数据库记录
Exec返回数据库影响或插入的结果
批量插入数据 使用Prepared语句
查询 Query方式查询 提供了两种选择
第一种 参数是拼接好的sql
另一种情况 第一参数是带有占位符的sql 第二个参数为sql的实际参数
rows, err := db.Query("SELECT * FROM user WHERE gid = 1")
rows, err := db.Query("SELECT * FROM user WHERE gid = ?", 1)
两种方式的底层通信不完全一样
一种是 plaintext方式 另外一种是prepared方式
prepared 带有占位符的sql语句 客户端将该语句和参数发给mysql服务器
mysql服务器编译成一个prepared语句 这个语句可以根据不同的参数多次调用
prepared语句执行的方式
准备prepare语句
执行prepared语句和参数
关闭prepared语句
prepare语句好处
避免通过引号组装拼接sql语句 避免sql注入带来的安全风险 可以多次执行的sql语句
golang的pliantext和prepare查询方式
Qeury函数的两个操作 第一个操作执行pliantext的sql语句
先看db.Query方法
// Query executes a query that returns rows, typically a SELECT.
// The args are for any placeholder parameters in the query.
func (db *DB) Query(query string, args ...interface{}) (*Rows, error) {
var rows *Rows
var err error
for i := 0; i < maxBadConnRetries; i++ {
rows, err = db.query(query, args, cachedOrNewConn) // 查询
if err != driver.ErrBadConn {
break
}
}
if err == driver.ErrBadConn {
return db.query(query, args, alwaysNewConn)
}
return rows, err
}
Query方法内部调用了db.query 方法 并且根据连接重连的状况选择是 cachedOrNewConn 模式还是 alwaysNewConn 模式
cachedOrNewConn 从返回一个cached连接或者等待一个可用连接 甚至也可能建立一个新的连接
alwaysNewConn 打开连接时的策略为每次建立一个新的连接
func (db *DB) query(query string, args []interface{}, strategy connReuseStrategy) (*Rows, error) {
ci, err := db.conn(strategy)
if err != nil {
return nil, err
}
return db.queryConn(ci, ci.releaseConn, query, args)
}
query方法 通过db.conn方法 返回一个新创建或者缓存的空闲连接
driverConn
随机调用queryConn方法
// queryConn executes a query on the given connection.
// The connection gets released by the releaseConn function.
func (db *DB) queryConn(dc *driverConn, releaseConn func(error), query string, args []interface{}) (*Rows, error) {
// 判断驱动是否实现了 Queryer
if queryer, ok := dc.ci.(driver.Queryer); ok {
dargs, err := driverArgs(nil, args)
if err != nil {
releaseConn(err)
return nil, err
}
dc.Lock()
rowsi, err := queryer.Query(query, dargs) // 调用驱动的查询方法 connection.go 第305行
dc.Unlock()
if err != driver.ErrSkip { // 不带参数的返回
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,
}
return rows, nil
}
}
dc.Lock()
si, err := dc.ci.Prepare(query) // 带参数的返回 创建prepare对象
dc.Unlock()
if err != nil {
releaseConn(err)
return nil, err
}
ds := driverStmt{dc, si}
rowsi, err := rowsiFromStatement(ds, args...) // 执行语句
if err != nil {
dc.Lock()
si.Close()
dc.Unlock()
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: si,
}
return rows, nil
}
queryConn 函数 先判断驱动是否实现了 Queryer
如果实现了即调用其 Query 方法 方法会针对sql查询语句做查询
例如mysql的驱动如下 connection.go 第305行左右
func (mc *mysqlConn) Query(query string, args []driver.Value) (driver.Rows, error) {
if mc.netConn == nil {
errLog.Print(ErrInvalidConn)
return nil, driver.ErrBadConn
}
if len(args) != 0 {
if !mc.cfg.InterpolateParams {
return nil, driver.ErrSkip
}
// try client-side prepare to reduce roundtrip
prepared, err := mc.interpolateParams(query, args)
if err != nil {
return nil, err
}
query = prepared
args = nil
}
// Send command
err := mc.writeCommandPacketStr(comQuery, query)
if err == nil {
// Read Result
var resLen int
resLen, err = mc.readResultSetHeaderPacket()
if err == nil {
rows := new(textRows)
rows.mc = mc
if resLen == 0 {
// no columns, no more data
return emptyRows{}, nil
}
// Columns
rows.columns, err = mc.readColumns(resLen)
return rows, err
}
}
return nil, err
}
Query 先检查参数是否为 0 然后调用 writeCommandPacketStr 方法执行sql
并通过 readResultSetHeaderPacket 读取数据库服务返回的结果
如果参数不为 0 会先判断是否是 prepared 语句
这里会返回一个 driver.ErrSkip 错误
把函数执行权再返回到 queryConn 函数中
然后再调用si, err := dc.ci.Prepare(query)创建Stmt对象 接下来调用 rowsiFromStatement 执行查询：
func rowsiFromStatement(ds driverStmt, args ...interface{}) (driver.Rows, error) {
ds.Lock()
want := ds.si.NumInput()
ds.Unlock()
// -1 means the driver doesn't know how to count the number of
// placeholders, so we won't sanity check input here and instead let the
// driver deal with errors.
if want != -1 && len(args) != want {
return nil, fmt.Errorf("sql: statement expects %d inputs; got %d", want, len(args))
}
dargs, err := driverArgs(&ds, args)
if err != nil {
return nil, err
}
ds.Lock()
rowsi, err := ds.si.Query(dargs)
ds.Unlock()
if err != nil {
return nil, err
}
return rowsi, nil
}
rowsiFromStatement 方法会调用驱动的ds.si.Query(dargs)方法 执行最后的查询
statement.go 第84行
func (stmt *mysqlStmt) Query(args []driver.Value) (driver.Rows, error) {
if stmt.mc.netConn == nil {
errLog.Print(ErrInvalidConn)
return nil, driver.ErrBadConn
}
// Send command
err := stmt.writeExecutePacket(args)
if err != nil {
return nil, err
}
mc := stmt.mc
// Read Result
resLen, err := mc.readResultSetHeaderPacket()
if err != nil {
return nil, err
}
rows := new(binaryRows)
if resLen > 0 {
rows.mc = mc
// Columns
// If not cached, read them and cache them
if stmt.columns == nil {
rows.columns, err = mc.readColumns(resLen)
stmt.columns = rows.columns
} else {
rows.columns = stmt.columns
err = mc.readUntilEOF()
}
}
return rows, err
}
调用 stmt和参数执行sql查询
查询完毕之后 返回到queryConn方法中 使用releaseConn释放查询的数据库连接
自定义 prepare 查询
对于占位符的prepare语句 go内部通过的dc.ci.Prepare(query)会自动创建一个 stmt对象
自定义stmt语句 使用方式
stmt, err := db.Prepare("SELECT * FROM user WHERE gid = ?")
if err != nil {
log.Fatalln(err)
}
defer stmt.Close()
rows, err := stmt.Query(1)
if err != nil{
log.Fatalln(err)
}
即通过 Prepare 方法创建一个 stmt 对象 然后执行 stmt 对象的 Query / Exec 方法得到 sql.Rows 结果集
最后关闭stmt.Close
创建stmt的 Prepare 方式是 golang 的一个设计 其目的是 Prepare once, execute many times
为了批量执行sql语句 但是通常会造成所谓的三次网络请求 three network round-trips
即preparing executing和closing三次请求
对于大多数数据库 prepare 的过程都是 先发送一个带占位符的sql语句到服务器 服务器返回一个statement id 然后再把这个id和参数发送给服务器执行 最后再发送关闭statement命令
golang的实现了连接池 处理 prepare 方式也需要特别注意
调用 Prepare 方法返回stmt的时候 golang会在某个空闲的连接上进行prepare语句
然后就把连接释放回到连接池 可是golang会记住这个连接 当需要执行参数的时候 就再次找到之前记住的连接进行执行
等到stmt.Close调用的时候 再释放该连接
在执行参数的时候 如果记住的连接正处于忙碌阶段 此时golang将会从新选一个新的空闲连接进行prepare（re-prepare）
即使是重新reprepare 同样也会遇到刚才的问题 将会一而再再而三的进行reprepare 直到找到空闲连接进行查询的时候
这种情况将会导致leak连接的情况 尤其是再高并发的情景 会导致大量的prepare过程
因此使用stmt的情况需要仔细考虑应用场景 通常在应用程序中 多次执行同一个sql语句的情况并不多 因此减少prepare语句的使用
对于 query(prepare, args) 的方式
database/sql 使用ds.si.Query(dargs)创建stmt 然后就立即执行prepare和参数 最后关闭stmt
整个过程都是同一个连接上完成 因此不存在reprepare的情况 当然也没有达到创建一次 执行多次的目
prepare 好处和缺点
目前需要注意的大致就是
单次查询不需要使用prepared 每次使用stmt语句都是三次网络请求次数 prepared execute close
不要循环中创建prepare语句
注意关闭 stmt
尽管会有reprepare过程 这些操作依然是database/sql帮所做的
与连接retry10次一样 开发者无需担心
对于Qeruy操作如此 同理Exec操作也一样
总结
database/sql提供两类查询操作 Query和Exec方法
都可以使用plaintext和 prepare 方式查询
prepare 可以有效的避免数据库注入
prepare 方式又可以有显示的声明stmt对象 也有隐藏的方式
显示的创建stmt会有3次网络请求 创建->执行->关闭
再批量操作可以考虑这种做法
另外一种方式创建 prepare 后就执行 因此不会因为reprepare导致高并发下的leak连接问题
针对单次操作的查询 Golang Mysql 有两类操作Query和Exec
Query返回数据库记录
Exec返回数据库影响或插入的结果
批量插入数据 使用Prepared语句
查询 Query方式查询 提供了两种选择
第一种 参数是拼接好的sql
另一种情况 第一参数是带有占位符的sql 第二个参数为sql的实际参数
rows, err := db.Query("SELECT * FROM user WHERE gid = 1")
rows, err := db.Query("SELECT * FROM user WHERE gid = ?", 1)
两种方式的底层通信不完全一样
一种是 plaintext方式 另外一种是prepared方式
prepared 带有占位符的sql语句 客户端将该语句和参数发给mysql服务器
mysql服务器编译成一个prepared语句 这个语句可以根据不同的参数多次调用
prepared语句执行的方式
准备prepare语句
执行prepared语句和参数
关闭prepared语句
prepare语句好处
避免通过引号组装拼接sql语句 避免sql注入带来的安全风险 可以多次执行的sql语句
golang的pliantext和prepare查询方式
Qeury函数的两个操作 第一个操作执行pliantext的sql语句
先看db.Query方法
// Query executes a query that returns rows, typically a SELECT.
// The args are for any placeholder parameters in the query.
func (db *DB) Query(query string, args ...interface{}) (*Rows, error) {
var rows *Rows
var err error
for i := 0; i < maxBadConnRetries; i++ {
rows, err = db.query(query, args, cachedOrNewConn) // 查询
if err != driver.ErrBadConn {
break
}
}
if err == driver.ErrBadConn {
return db.query(query, args, alwaysNewConn)
}
return rows, err
}
Query方法内部调用了db.query 方法 并且根据连接重连的状况选择是 cachedOrNewConn 模式还是 alwaysNewConn 模式
cachedOrNewConn 从返回一个cached连接或者等待一个可用连接 甚至也可能建立一个新的连接
alwaysNewConn 打开连接时的策略为每次建立一个新的连接
func (db *DB) query(query string, args []interface{}, strategy connReuseStrategy) (*Rows, error) {
ci, err := db.conn(strategy)
if err != nil {
return nil, err
}
return db.queryConn(ci, ci.releaseConn, query, args)
}
query方法 通过db.conn方法 返回一个新创建或者缓存的空闲连接
driverConn
随机调用queryConn方法
// queryConn executes a query on the given connection.
// The connection gets released by the releaseConn function.
func (db *DB) queryConn(dc *driverConn, releaseConn func(error), query string, args []interface{}) (*Rows, error) {
// 判断驱动是否实现了 Queryer
if queryer, ok := dc.ci.(driver.Queryer); ok {
dargs, err := driverArgs(nil, args)
if err != nil {
releaseConn(err)
return nil, err
}
dc.Lock()
rowsi, err := queryer.Query(query, dargs) // 调用驱动的查询方法 connection.go 第305行
dc.Unlock()
if err != driver.ErrSkip { // 不带参数的返回
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,
}
return rows, nil
}
}
dc.Lock()
si, err := dc.ci.Prepare(query) // 带参数的返回 创建prepare对象
dc.Unlock()
if err != nil {
releaseConn(err)
return nil, err
}
ds := driverStmt{dc, si}
rowsi, err := rowsiFromStatement(ds, args...) // 执行语句
if err != nil {
dc.Lock()
si.Close()
dc.Unlock()
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: si,
}
return rows, nil
}
queryConn 函数 先判断驱动是否实现了 Queryer
如果实现了即调用其 Query 方法 方法会针对sql查询语句做查询
例如mysql的驱动如下 connection.go 第305行左右
func (mc *mysqlConn) Query(query string, args []driver.Value) (driver.Rows, error) {
if mc.netConn == nil {
errLog.Print(ErrInvalidConn)
return nil, driver.ErrBadConn
}
if len(args) != 0 {
if !mc.cfg.InterpolateParams {
return nil, driver.ErrSkip
}
// try client-side prepare to reduce roundtrip
prepared, err := mc.interpolateParams(query, args)
if err != nil {
return nil, err
}
query = prepared
args = nil
}
// Send command
err := mc.writeCommandPacketStr(comQuery, query)
if err == nil {
// Read Result
var resLen int
resLen, err = mc.readResultSetHeaderPacket()
if err == nil {
rows := new(textRows)
rows.mc = mc
if resLen == 0 {
// no columns, no more data
return emptyRows{}, nil
}
// Columns
rows.columns, err = mc.readColumns(resLen)
return rows, err
}
}
return nil, err
}
Query 先检查参数是否为 0 然后调用 writeCommandPacketStr 方法执行sql
并通过 readResultSetHeaderPacket 读取数据库服务返回的结果
如果参数不为 0 会先判断是否是 prepared 语句
这里会返回一个 driver.ErrSkip 错误
把函数执行权再返回到 queryConn 函数中
然后再调用si, err := dc.ci.Prepare(query)创建Stmt对象 接下来调用 rowsiFromStatement 执行查询：
func rowsiFromStatement(ds driverStmt, args ...interface{}) (driver.Rows, error) {
ds.Lock()
want := ds.si.NumInput()
ds.Unlock()
// -1 means the driver doesn't know how to count the number of
// placeholders, so we won't sanity check input here and instead let the
// driver deal with errors.
if want != -1 && len(args) != want {
return nil, fmt.Errorf("sql: statement expects %d inputs; got %d", want, len(args))
}
dargs, err := driverArgs(&ds, args)
if err != nil {
return nil, err
}
ds.Lock()
rowsi, err := ds.si.Query(dargs)
ds.Unlock()
if err != nil {
return nil, err
}
return rowsi, nil
}
rowsiFromStatement 方法会调用驱动的ds.si.Query(dargs)方法 执行最后的查询
statement.go 第84行
func (stmt *mysqlStmt) Query(args []driver.Value) (driver.Rows, error) {
if stmt.mc.netConn == nil {
errLog.Print(ErrInvalidConn)
return nil, driver.ErrBadConn
}
// Send command
err := stmt.writeExecutePacket(args)
if err != nil {
return nil, err
}
mc := stmt.mc
// Read Result
resLen, err := mc.readResultSetHeaderPacket()
if err != nil {
return nil, err
}
rows := new(binaryRows)
if resLen > 0 {
rows.mc = mc
// Columns
// If not cached, read them and cache them
if stmt.columns == nil {
rows.columns, err = mc.readColumns(resLen)
stmt.columns = rows.columns
} else {
rows.columns = stmt.columns
err = mc.readUntilEOF()
}
}
return rows, err
}
调用 stmt和参数执行sql查询
查询完毕之后 返回到queryConn方法中 使用releaseConn释放查询的数据库连接
自定义 prepare 查询
对于占位符的prepare语句 go内部通过的dc.ci.Prepare(query)会自动创建一个 stmt对象
自定义stmt语句 使用方式
stmt, err := db.Prepare("SELECT * FROM user WHERE gid = ?")
if err != nil {
log.Fatalln(err)
}
defer stmt.Close()
rows, err := stmt.Query(1)
if err != nil{
log.Fatalln(err)
}
即通过 Prepare 方法创建一个 stmt 对象 然后执行 stmt 对象的 Query / Exec 方法得到 sql.Rows 结果集
最后关闭stmt.Close
创建stmt的 Prepare 方式是 golang 的一个设计 其目的是 Prepare once, execute many times
为了批量执行sql语句 但是通常会造成所谓的三次网络请求 three network round-trips
即preparing executing和closing三次请求
对于大多数数据库 prepare 的过程都是 先发送一个带占位符的sql语句到服务器 服务器返回一个statement id 然后再把这个id和参数发送给服务器执行 最后再发送关闭statement命令
golang的实现了连接池 处理 prepare 方式也需要特别注意
调用 Prepare 方法返回stmt的时候 golang会在某个空闲的连接上进行prepare语句
然后就把连接释放回到连接池 可是golang会记住这个连接 当需要执行参数的时候 就再次找到之前记住的连接进行执行
等到stmt.Close调用的时候 再释放该连接
在执行参数的时候 如果记住的连接正处于忙碌阶段 此时golang将会从新选一个新的空闲连接进行prepare（re-prepare）
即使是重新reprepare 同样也会遇到刚才的问题 将会一而再再而三的进行reprepare 直到找到空闲连接进行查询的时候
这种情况将会导致leak连接的情况 尤其是再高并发的情景 会导致大量的prepare过程
因此使用stmt的情况需要仔细考虑应用场景 通常在应用程序中 多次执行同一个sql语句的情况并不多 因此减少prepare语句的使用
对于 query(prepare, args) 的方式
database/sql 使用ds.si.Query(dargs)创建stmt 然后就立即执行prepare和参数 最后关闭stmt
整个过程都是同一个连接上完成 因此不存在reprepare的情况 当然也没有达到创建一次 执行多次的目
prepare 好处和缺点
目前需要注意的大致就是
单次查询不需要使用prepared 每次使用stmt语句都是三次网络请求次数 prepared execute close
不要循环中创建prepare语句
注意关闭 stmt
尽管会有reprepare过程 这些操作依然是database/sql帮所做的
与连接retry10次一样 开发者无需担心
对于Qeruy操作如此 同理Exec操作也一样
总结
database/sql提供两类查询操作 Query和Exec方法
都可以使用plaintext和 prepare 方式查询
prepare 可以有效的避免数据库注入
prepare 方式又可以有显示的声明stmt对象 也有隐藏的方式
显示的创建stmt会有3次网络请求 创建->执行->关闭
再批量操作可以考虑这种做法
另外一种方式创建 prepare 后就执行 因此不会因为reprepare导致高并发下的leak连接问题
尊贵的董事大人
英文标题不为空时 视为本栏投稿
需要关键字 描述 英文标题