使用相同的SQL查询时，转到包“ database / sql”得到差异结果

Question

package main

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

func main() {
    db, _ := sql.Open("mysql", "root:root@tcp(127.0.0.1:3306)/test")

    // Just Query
    rows, _ := db.Query("SELECT id FROM test_1 WHERE id=123456")
    for rows.Next() {
        var id interface{}
        rows.Scan(&id)
        fmt.Println("Query no args =>", id)
    }

    // Query with args
    rows, _ = db.Query("SELECT id FROM test_1 WHERE id=?", 123456)
    for rows.Next() {
        var id interface{}
        rows.Scan(&id)
        fmt.Println("Query has args =>", id)
    }
}

Output:

$ go run main.go
Query no args => [49 50 51 52 53 54]
Query has args => 123456

Question:

In my mind, "SELECT id FROM test_1 WHERE id=123456" and "SELECT id FROM test_1 WHERE id=?", 123456 are same SQL-query.

Why the result's TYPE is not same?

Is it a bug or just unfriendly API design?

Answer 1

This is related to issue #366. There are two protocols in MySQL:

1. Text protocol. When text protocol is used, regardless of column's type, the query result will be stored as []byte. Related source code can be found at method textRows.readRow. Further conversion will be done during rows.Scan which is part of database/sql package. Here, the result will be converted (if convertible) from []byte to the type of argument being passed to Scan.
2. Newer binary protocol. When this protocol is used, the query result will be converted to appropriate type based on column's type. Related source code can be found at binaryRows.readRow.

In this question, since the type of Scan argument is interface{}, in database/sql side, no conversion will occurs. When issuing a query without args, it seems that the driver will used text protocol, but when the query has argument(s), the driver will create prepared-statement then communicate to the server using binary protocol. In short:

1. Query no args: text protocol → result in []byte → result scanned to interface{} → result is returned as ASCII code of 123456 (i.e. [49 50 51 52 53 54]).
2. Query with args: binary protocol → conversion based on column type (int64) → result scanned to interface{} → result returned as int64.

If you want to get same result, use the following code for first query (for clarity, error handling is omitted):

//Comment out
//rows, _ := db.Query("SELECT id FROM test_1 WHERE id=123456")

//then replace with
stmt, _ := db.Prepare("SELECT id FROM test_1 WHERE id=123456")
defer stmt.Close()
rows, _ := stmt.Query()