英文:
What's the recommended way to initialize and keep prepared statements in Go?
问题
我正在使用sqlx编写我的第一个Go项目，并希望使用预处理语句。
我不确定在一个好的可管理的方式中初始化和保持预处理语句变量的推荐做法是什么。
我希望它们只能从实际需要使用它们的代码部分访问，到目前为止，每个语句都由一个单独的函数使用，所以全局变量不是一个好的选择（除了一般被反对之外）。
在C/C++中，我可能会使用一个函数静态变量，并在第一次进入函数时初始化它。这样，关于语句内容和使用它的调用的信息就会彼此靠近。
但据我目前所知，Go中没有"方法静态变量"，那么有什么替代方案呢？
我找到了关于闭包的参考资料，闭包是匿名函数，但这是实现这一目标的最佳方式吗？从"预处理语句最佳实践"的角度来看，我是在追求正确的东西吗？
英文:
I'm writing my first Go project using sqlx and want to use Prepared statements.
I'm not sure what's the recommended practice to initialise and keep the Prepared statement variables in a nice manageable way.
I want them to be accessible only from the part of the code which actually has to use them, so far each statement is used by a single function, so global variables are not a good option (besides generally being frowned upon).
In C/C++ I would probably use a function static variable and initialise it the first time that the function is entered. This way the information about the statement content and the call which uses it are close to each other.
But from what I know so far there is no "method static variables" in Go, so what's the alternative?
I found references to Closures, which are anonymous functions, but is this the best way to achieve this? Am I aiming for the right thing from perspective of "prepared statements best practices"?
答案1
得分: 2
我处理这个问题的一种方式是在主函数中初始化所有我想要“保持活动”的预处理语句（即经常使用的语句），并将它们保存在一个映射中，然后将该映射作为参数传递给需要访问这些预处理语句的函数。
这种方式虽然不能满足你要求只能从实际使用它们的函数中访问，但它避免了全局变量，并且在需要时避免了重新准备预处理语句。
使用闭包，你可以这样做：
func main() {
// 初始化数据库等
getData, stmt := initGetData(db) // db 是 *sql.DB，initGetData 是下面的函数
defer stmt.Close()
myResult := getData()
}
func initGetData(db *sql.DB) ((func() string), *sql.Stmt) {
stmt, err := db.Prepare("SELECT something FROM some_table")
if err != nil {
log.Fatal(err)
}
return func() string {
var result string
err := stmt.QueryRow().Scan(&result)
if err != nil {
log.Fatal(err)
}
return result
}, stmt
}
这样做可以将预处理语句与执行查询的函数绑定在一起。但是，只有在调用 initGetData() 函数时才会准备语句并返回闭包。闭包运行查询并可以访问在调用 myQuery 时创建的预处理语句。
然后，每次需要运行查询时，只需使用 getData()。这样可以满足你的要求。
英文:
One way I have dealt with this is to initializes all the prepared statements I want to "keep alive" (i.e. those that are frequently used) in the main function and save them into a map which I then pass as an argument to the functions that need access to the prepared statements.
This doesn't meet your requirement of being only accessible from the functions that actually use them, but it does avoid globals and prevents having to re-prepare them when they are needed.
Using closures you could do something like this:
func main() {
// initialize your database etc.
getData, stmt := initGetData(db) // db is *sql.DB, initGetData is the function below
defer stmt.Close()
myResult := getData()
}
func initGetData(db *sql.DB) ((func() string), *sql.Stmt) {
stmt, err := db.Prepare("SELECT something FROM some_table")
if err != nil {
log.Fatal(err)
}
return func() string {
var result string
err := stmt.QueryRow().Scan(&result)
if err != nil {
log.Fatal(err)
}
return result
}, stmt
}
Doing it like this you have the prepared statement with your function that makes the query. But the statement is prepared only when the initGetData() function is called which returns the closure. The closure runs the query and has access to the prepared statement that was created when myQuery was called.
You then simply use getData() every time you need to run the query. This would meet your requirements.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论