去SQL查询不一致 [英] Go SQL query inconsistency

问题描述

我在执行查询时遇到了一些非常奇怪的不一致之处，并且想知道是否有人知道原因.

I am experiencing some really weird inconsistencies when executing queries, and was wondering if anyone knew why.

想象一下，我有一个结构定义如下:

Imagine I have a struct defined as follows:

type Result struct {
    Afield string      `db:"A"`
    Bfield interface{} `db:"B"`
    Cfield string      `db:"C"`
    Dfield string      `db:"D"`
}

还有一个带有以下列的MySQL表:

And a MySQL Table with the following cols:

A : VARCHAR(50)
B : INT
C : VARCHAR(50)
D : VARCHAR(50)

我要执行的查询:

从表WHERE A ="a"

SELECT A, B, C, D FROM table WHERE A="a"

可以执行的第一种方式:

first way it can be executed:

db.Get(&result, `SELECT A, B, C, D FROM table WHERE A="a"`)

第二种执行方式:

db.Get(&result, `SELECT A, B, C, D FROM table WHERE A=?`, "a")

我遇到的不一致之处如下:当以第一种方式执行查询时，Bfield的类型为 int .但是，第二次执行查询时，它是 [] uint8 .

The inconsistencies I am experiencing are as follows: When executing the query the first way, the type of Bfield is int. However, when executing the query the second time, it is []uint8.

例如当B为1时，就会出现这种结果.

This outcome is occurring for example when B is 1.

为什么Bfield的类型取决于查询的执行方式?

连接声明:

// Connection is an interface for making queries.
type Connection interface {
    Exec(query string, args ...interface{}) (sql.Result, error)
    Get(dest interface{}, query string, args ...interface{}) error
    Select(dest interface{}, query string, args ...interface{}) error
}

编辑

使用Go数据库/sql软件包+驱动程序也会发生这种情况.下面的查询分别将 Bfield 分配给 [] uint8 和 int64 .

This is also happening using the Go database/sql package + driver. The queries below are assigning Bfield to []uint8 and int64 respectively.

db的类型为* sql.DB

db is of type *sql.DB

查询1:

db.QueryRow(SELECT A，B，C，D FROM table WHERE A ="a").Scan(& result.Afield，& result.Bfield，& result.Cfield，&result.Dfield)

-> Bfield 的类型为 [] uint8

查询2:

db.QueryRow(SELECT A，B，C，D FROM table WHERE A = ?,"a").Scan(& result.Afield，& result.Bfield，& result.Cfield，& result.Dfield)

-> Bfield 的类型是 int64

编辑

还有其他需要注意的地方，当链接多个WHERE子句时，只要使用?填充至少 1，查询就会返回 int .否则，如果它们都填充在字符串中，它将返回 [] uint8

Something else to note, when chaining multiple WHERE clauses, as long as at least 1 is populated using ?, the query will return int. Otherwise if they are all populated in the string, it will return []uint8

推荐答案

简短的回答:因为MySQL驱动程序对带有或不带有参数的查询使用不同的协议.使用准备好的语句以获得一致的结果.

Short answer: because the MySQL driver uses a different protocol for queries with and without parameters. Use a prepared statement to get consistent results.

以下说明是指标准MySQL驱动程序 github.com/go-sql-driver/mysql，版本1.4

The following explanation refers to the standard MySQL driver github.com/go-sql-driver/mysql, version 1.4

在第一种情况下，驱动程序将查询直接发送到MySQL，并将结果解释为 * textRows 结构.这个结构(几乎)总是将结果解码为字节分片，并将转换保留为Go sql 包中的更好类型.如果目标是 int ， string ， sql.Scanner 等，但不适用于 interface {} >.

In the first case, the driver sends the query directly to MySQL, and interprets the result as a *textRows struct. This struct (almost) always decodes results into a byte slice, and leaves the conversion to a better type to the Go sql package. This works fine if the destination is an int, string, sql.Scanner etc, but not for interface{}.

在第二种情况下，驱动程序检测到有参数并返回 driver.ErrSkip .这将导致Go SQL程序包使用PreparedStatement.在这种情况下，MySQL驱动程序使用 * binaryRows 结构来解释结果.此结构使用声明的列类型(<> INT (这种情况下)以解码值，在这种情况下将值解码为 int64 .

In the second case, the driver detects that there are arguments and returns driver.ErrSkip. This causes the Go SQL package to use a PreparedStatement. And in that case, the MySQL driver uses a *binaryRows struct to interpret the results. This struct uses the declared column type (INT in this case) to decode the value, in this case to decode the value into an int64.

有趣的事实:如果您向数据库DSN提供 interpolateParams = true 参数(例如"root:testing @/mysql?interpolateParams = true" )，则MySQL驱动程序将在客户端准备查询，而不使用PreparedStatement.此时，两种查询的行为均相同.

Fun fact: if you provide the interpolateParams=true parameter to the database DSN (e.g. "root:testing@/mysql?interpolateParams=true"), the MySQL driver will prepare the query on the client side, and not use a PreparedStatement. At this point both types of query behave the same.

一个小的概念证明:

package main

import (
    "database/sql"
    "log"

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

type Result struct {
    Afield string
    Bfield interface{}
}

func main() {
    db, err := sql.Open("mysql", "root:testing@/mysql")
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    if _, err = db.Exec(`CREATE TABLE IF NOT EXISTS mytable(A VARCHAR(50), B INT);`); err != nil {
        log.Fatal(err)
    }
    if _, err = db.Exec(`DELETE FROM mytable`); err != nil {
        log.Fatal(err)
    }
    if _, err = db.Exec(`INSERT INTO mytable(A, B) VALUES ('a', 3)`); err != nil {
        log.Fatal(err)
    }

    var (
        usingLiteral         Result
        usingParam           Result
        usingLiteralPrepared Result
    )
    row := db.QueryRow(`SELECT B FROM mytable WHERE A='a'`)
    if err := row.Scan(&usingLiteral.Bfield); err != nil {
        log.Fatal(err)
    }
    row = db.QueryRow(`SELECT B FROM mytable WHERE A=?`, "a")
    if err := row.Scan(&usingParam.Bfield); err != nil {
        log.Fatal(err)
    }
    stmt, err := db.Prepare(`SELECT B FROM mytable WHERE A='a'`)
    if err != nil {
        log.Fatal(err)
    }
    defer stmt.Close()
    row = stmt.QueryRow()
    if err := row.Scan(&usingLiteralPrepared.Bfield); err != nil {
        log.Fatal(err)
    }

    log.Printf("Type when using literal:  %T", usingLiteral.Bfield)         // []uint8
    log.Printf("Type when using param:    %T", usingParam.Bfield)           // int64
    log.Printf("Type when using prepared: %T", usingLiteralPrepared.Bfield) // int64
}

这篇关于去SQL查询不一致的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！