golang sql驱动程序的准备语句 [英] golang sql driver's prepare statement

问题描述

关于golang的sql驱动程序，下面两条语句有什么不同？

  // store.DB是* sql.DB type 
 rows，err：= store.DB.Query（SQL，args ...）
 // err！= nil 
推迟rows.Close（）
  

和

  // store.DB is * sql.DB type 
 stmt，err：= store.DB.Prepare（SQL）
 // err！= nil 
 defer stmt.Close（）
 
行，err：= stmt.Query（args ...）
 // err！= nil 
推迟rows.Close（）
  

看起来它们是一样的吗？有什么细微差别吗？

更新：

我们不需要在 db.Prepare stmt.Exec 或 stmt.Query 在每个准备后执行一个 exec 或查询 $ C>。当我们使用 db.Query 或 db.Exec 时，我们将参数传递给方法而不是使用原始SQL字符串（为安全考虑）。



我找到了一个引用链接： http ：//go-database-sql.org/prepared.html

这两种方式似乎都使用准备好的语句，有什么区别？

解决方案

这些差异可能微妙，有时很重要，有时甚至不存在。通常，准备好的语句1.准备好服务器（解析SQL，生成执行计划等），2.使用附加参数执行，然后3.关闭。它可以让您重复使用每次传入的不同参数的相同SQL，它可以帮助防范SQL注入，可以提供一些性能增强（特定于驱动程序/协议，YMMV）并防止重复步骤，如执行计划生成和SQL解析上面的准备步骤。 对于编写源代码的人来说，准备好的语句可能比连接字符串并将它们发送到数据库服务器更方便。

DB.Query（）方法将SQL作为一个字符串，以及零个或多个参数（如 Exec（）或 QueryRow（））。没有附加参数的SQL字符串将完全查询您写的内容。但是，提供了一个带有占位符和附加参数的SQL字符串，正在为您准备好一个准备好的语句。
$ b DB.Prepare（）方法明确执行准备好的语句，然后您将参数传递给： stmt.Exec（... args）。

有几件事值得思考，您可以使用 DB.Query（）两者之间的差异条款以及为什么要使用其中一个或另一个。 c>没有参数。这可以非常有效，因为它可以绕过准备好的语句必然要经过的 prepare - > execute - > close 序列。



您也可以在查询字符串中使用额外的参数和占位符，并且会像上面提到的那样执行准备好的语句。这里潜在的问题是，当你进行一些查询时，每个查询都会产生一个准备好的语句。由于涉及额外的步骤，因此每次执行该查询时都会重新准备，执行和关闭，因此效率可能相当低。

使用明确的准备语句，您可以避免效率低下，因为您试图重复使用之前准备好的SQL，可能会有不同的参数。

但并非总是如你所愿。 。因为由db / sql管理的底层连接池，你的数据库连接是非常虚拟的。 DB.Prepare（）方法将针对特定连接准备语句，然后尝试在执行时返回相同的连接，但如果该连接不可用它会简单地抓住一个可用的并重新准备并执行。如果你一遍又一遍地使用相同的预备声明，那么你可能会不知不觉地一遍又一遍地准备它。当你处理大量流量时，这显然很明显。

所以很明显，你在什么情况下使用取决于你的具体用例，但我希望细节上面的帮助为你澄清，你可以在每种情况下做出最好的决定。

更新

在OP中进行更新时，查询只需要执行一次就没有什么区别了，因为带参数的查询是在后台执行的准备语句。

使用直接方法，例如 DB.Query（）及其类似物，与显式使用预准备语句相比，因为它将导致源代码更简单。

由于准备语句在这种情况下出于安全原因而被使用，因此可能值得通过其他方式来处理安全问题，并使用明文查询来代替，因为它会提高性能。但是，除非有足够的流量（或预计未来流量会大幅增长），否则任何收益都可能无关紧要，从而无法减轻服务器的负载。再次回到真实世界的用例。

对于任何对准备语句和直接纯文本查询之间的差异感兴趣的人，有一篇很好的文章< a href =https://www.vividcortex.com/blog/2014/11/19/analyzing-prepared-statement-performance-with-vividcortex/ =noreferrer>这里（也有一个很好的解释大部分上述内容的工作）。

With regards to golang's sql driver, what is the difference between the two statements below?

// store.DB is *sql.DB type
rows, err := store.DB.Query(SQL, args ...)
// err != nil
defer rows.Close()

and

// store.DB is *sql.DB type
stmt, err := store.DB.Prepare(SQL)
// err != nil
defer stmt.Close()

rows, err := stmt.Query(args ...)
// err != nil
defer rows.Close()

It seems that they are the same? Are there any subtle difference?

Update:

We don't need to perform many stmt.Exec or stmt.Query after db.Prepare, only one exec or query is performed after each prepare. And when we use db.Query or db.Exec, we pass arguments to the methods instead of using raw SQL string (for security consideration).

I have found one reference links: http://go-database-sql.org/prepared.html
It seems that both way are using prepared statement, what's the difference?

解决方案

The differences can be subtle, sometimes important, and sometimes effectively nonexistent.

In general a prepared statement 1. gets prepared with the server (SQL parsed, execution plan generated, etc.), 2. is executed with the additional parameters, and then 3. is closed. It lets you reuse the same SQL with different parameters passed in each time, it can help guard against SQL injection, may provide some performance enhancements (driver/protocol specific, YMMV) and prevent repeated steps, as in execution plan generation and SQL parsing in the prepare step above.

For someone writing source code a prepared statement may be more convenient than concatenating strings and sending those to the DB server.

The DB.Query() method takes SQL as a string, and zero or more arguments (as does Exec(), or QueryRow()). A SQL string with no additional arguments will query exactly what you wrote. However, provided a SQL string with placeholders and additional arguments, a prepared statement is being done for you under the hood.

The DB.Prepare() method explicitly performs a prepared statement, which you then pass arguments to, as in: stmt.Exec(...args).

There are a couple of things worth thinking about, in terms of the differences between the two and why to use one or the other.

You can use DB.Query() without arguments. This can be very efficient since it can bypass the prepare --> execute --> close sequence that the prepared statement necessarily goes through.

You can also use it with additional arguments, and placeholders in the query string, and it will execute a prepared statement under the covers as I mentioned above. The potential problem here is that when you are making a number of queries, each is resulting in an under-the-hood prepared statement. Since there are extra steps involved this can be rather inefficient as it re-prepares, executes and closes each time you do that query.

With an explicit prepared statement you can possibly avoid that inefficiency as you are attempting to reuse the SQL that you earlier prepared, with potentially different arguments.

But that doesn't always work out as you might expect... Because of the underlying connection pool that is managed by db/sql, your "database connection" is quite virtual. The DB.Prepare() method will prepare the statement against a particular connection and then try to get that same connection back when it is time to execute, but if that connection is unavailable it will simply grab one that is available and re-prepare and execute against that. If you're using that same prepared statement over and over again then you might, unknowingly, also be preparing it over and over again. This obviously mostly comes to light when you're dealing with heavy traffic.

So obviously which you for what circumstance use depends on your specific use case, but I hope the details above help clarify for you enough that you can make the best decision in each case.

Update

Given the update in OP there is essentially no difference when the query only needs to be performed once, as queries with arguments are done as prepared statements behind the scenes.

Use the direct methods, e.g. DB.Query() and its analogs, vs. explicitly using prepared statements, as it will result in somewhat simpler source code.

Since prepared statements, in this case, are being utilized for security reasons, it may be worth the effort to handle the security concerns by other means and use plaintext queries instead, as it will improve performance. Any gains, however, may be irrelevant unless there is sufficient traffic (or the traffic is predicted to grow considerably in the future) to necessitate lightening the load on the server. Again it comes down to the real-world use case.

For anyone interested in some metrics on the difference between prepared statements and direct plaintext queries, there is a good article here (which also does an excellent job of explaining much of the above).

这篇关于golang sql驱动程序的准备语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！