Golang中跨数据库的准备好的语句绑定(如和在何处) [英] Cross-database prepared statement binding (like and where in) in Golang

问题描述

阅读了许多教程之后，我发现有很多方法可以在Go中的预准备语句上绑定参数

After reading many tutorials, I found that there are many ways to bind arguments on prepared statement in Go, some of them

SELECT * FROM bla WHERE x = ?col1 AND y = ?col2
SELECT * FROM bla WHERE x = ? AND y = ?
SELECT * FROM bla WHERE x = :col1 AND y = :col2
SELECT * FROM bla WHERE x = $1 AND y = $2

第一个问题，绑定参数的跨数据库方式是什么? (适用于任何数据库)

First question, what is the cross-database way to bind arguments? (that works on any database)

第二个问题，我没有阅读过有关LIKE语句，如何正确绑定LIKE语句的参数的教程?

Second question, none of the tutorial I've read mention about LIKE statement, how to bind arguments for LIKE-statement correctly?

SELECT * FROM bla WHERE x LIKE /*WHAT?*/

第三个问题，它们都没有给出IN语句的示例，如何正确绑定IN语句的参数?

Third question, also none of them give an example for IN statement, how to bind arguments for IN statement correctly?

`SELECT * FROM bla WHERE x IN ( /*WHAT?*/ )

推荐答案

跨数据库绑定参数的方法是什么?

对于数据库/sql，没有任何内容.每个数据库都有其表示参数占位符的方式. Go数据库/sql程序包不为准备好的语句提供任何规范化工具.准备好的语句文本只是传递给基础驱动程序，该驱动程序通常会将它们未经修改地发送给数据库服务器(或嵌入式数据库的库).

With database/sql, there is none. Each database has its own way to represent parameter placeholders. The Go database/sql package does not provide any normalization facility for the prepared statements. Prepared statement texts are just passed to the underlying driver, and the driver typically just sends them unmodified to the database server (or library for embedded databases).

如何正确绑定LIKE语句的参数?

您可以在like语句后使用参数占位符，并将其绑定为字符串.例如，您可以将准备好的语句写为:

You can use parameter placeholders after a like statement and bind it as a string. For instance, you could write a prepared statement as:

SELECT a from bla WHERE b LIKE ?

这是一个示例(省略了错误管理处理).

Here is an example (error management handling omitted).

package main

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

// > select * from bla ;
// +------+------+
// | a    | b    |
// +------+------+
// | toto | titi |
// | bobo | bibi |
// +------+------+

func main() {

    // Open connection
    db, err := sql.Open("mysql", "root:XXXXXXX@/test")
    if err != nil {
         panic(err.Error())  // proper error handling instead of panic in your app
    }
    defer db.Close()

    // Prepare statement for reading data
    stmtOut, err := db.Prepare("SELECT a FROM bla WHERE b LIKE ?")
    if err != nil {
        panic(err.Error()) // proper error handling instead of panic in your app
    }
    defer stmtOut.Close()

    var a string
    b := "bi%"    // LIKE 'bi%'
    err = stmtOut.QueryRow(b).Scan(&a)
    if err != nil {
        panic(err.Error()) // proper error handling instead of panic in your app
    }
    fmt.Printf("a = %s\n", a)
} 

请注意，％字符是绑定字符串的一部分，而不是查询文本的一部分.

Note that the % character is part of the bound string, not of the query text.

如何正确绑定IN语句的参数?

我知道的所有数据库都不允许直接使用IN子句绑定参数列表.这不是数据库/sql或驱动程序的限制，但是大多数数据库服务器根本不支持.

None of the databases I know allows binding a list of parameters directly with a IN clause. This is not a limitation of database/sql or the drivers, but this is simply not supported by most database servers.

您可以通过多种方法来解决此问题:

You have several ways to work the problem around:

• 您可以在IN子句中使用固定数量的占位符来构建查询.仅绑定提供的参数，并使用NULL值完成其他占位符.如果您的值比所选的固定数更多，则只需多次执行查询即可.这不是非常优雅，但是可以有效.

• you can build a query with a fixed number of placeholders in the IN clause. Only bind the parameters you are provided with, and complete the other placeholders by the NULL value. If you have more values than the fixed number you have chosen, just execute the query several times. This is not extremely elegant, but it can be effective.

您可以使用多个占位符构建多个查询.一个查询IN(?)，第二个查询IN(?，?)，第三个查询IN(?，?，?)，等等……将准备好的查询保留在语句高速缓存中，然后在以下位置选择正确的查询:运行时间取决于输入参数的数量.请注意，它占用内存，并且通常预准备语句的最大数量是有限的，因此当参数数量很大时就不能使用它.

you can build multiple queries with various number of placeholders. One query for IN ( ? ), a second query for IN (?, ?), a third for IN (?,?,?), etc ... Keep those prepared queries in a statement cache, and choose the right one at runtime depending on the number of input parameters. Note that it takes memory, and generally the maximum number of prepared statements is limited, so it cannot be used when the number of parameters is high.

如果输入参数的数量很多，请将其插入到临时表中，然后用与临时表的联接将查询替换为IN子句.如果您在一次往返中设法在临时表中执行插入操作，将非常有效.使用Go和数据库/sql，这不方便，因为无法批量查询.

if the number of input parameters is high, insert them in a temporary table, and replace the query with the IN clause by a join with the temporary table. It is effective if you manage to perform the insertion in the temporary table in one roundtrip. With Go and database/sql, it is not convenient because there is no way to batch queries.

这些解决方案中的每一个都有缺点.他们都不是完美的.

Each of these solutions has drawbacks. None of them is perfect.

这篇关于Golang中跨数据库的准备好的语句绑定(如和在何处)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！