一次为多个请求准备SQL语句可以吗? [英] Is it okay to prepare SQL statement once for multiple request?

问题描述

我有一些不会在每个请求上都改变的SQL查询(只是它的参数).因此，不要对每个请求都这样做:

I have some SQL queries that do not change on every request (only it's parameter). So, instead of doing this for each request:

func HandleRequest() {
    rows, err := db.Query(sqlQuery, params...)
    // do something with data
}

如果对每个要求我都这样做，可以吗:

Is it okay if for each reqest I do this instead:

// together with server initialization
stmt, err := db.Prepare(sqlQuery)

func HandleRequest() {
    rows, err := stmt.Query(params...)
    // do something with data
}

推荐答案

作为 DB.Prepare() 状态:

可以从返回的语句中同时运行多个查询或执行.

Multiple queries or executions may be run concurrently from the returned statement.

这对并发使用是安全的，尽管对准备好的语句的预期用途不是在多个请求之间共享它们.主要原因是，准备好的语句(可能)会在数据库服务器本身中分配资源，并且只有在调用返回的语句的 Close()方法之后，该语句才会被释放.所以我建议不要这样做.

It is safe for concurrent use, although the intended use for prepared statements is not to share them between multiple requests. The main reason is that a prepared statement (may) allocate resources in the DB server itself, and it's not freed until you call the Close() method of the returned statement. So I'd advise against it.

典型的用例是如果您必须使用不同的参数多次运行同一条语句，例如文档中的示例:

The typical use case is if you have to run the same statement multiple times with different parameters, such as the example in the documentation:

projects := []struct {
    mascot  string
    release int
}{
    {"tux", 1991},
    {"duke", 1996},
    {"gopher", 2009},
    {"moby dock", 2013},
}

stmt, err := db.Prepare("INSERT INTO projects(id, mascot, release, category) VALUES( ?, ?, ?, ? )")
if err != nil {
    log.Fatal(err)
}
defer stmt.Close() // Prepared statements take up server resources and should be closed after use.

for id, project := range projects {
    if _, err := stmt.Exec(id+1, project.mascot, project.release, "open source"); err != nil {
        log.Fatal(err)
    }
}

这篇关于一次为多个请求准备SQL语句可以吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！