为什么甚至在Golang中使用* DB.exec（）或准备好的语句？ [英] Why even use *DB.exec() or prepared statements in Golang?

问题描述

我正在将golang与Postgresql一起使用。

I'm using golang with Postgresql.

它说在此对于不返回行（插入，删除，更新）的操作，我们应使用 exec（）

It says here that for operations that do not return rows (insert, delete, update) we should use exec()

如果函数名称包含Query，则它被设计为询问数据库问题，并且将返回一组行，即使它为空。不返回行的语句不应使用查询功能；他们应该使用Exec（）。

If a function name includes Query, it is designed to ask a question of the database, and will return a set of rows, even if it’s empty. Statements that don’t return rows should not use Query functions; they should use Exec().

然后它说此处：

Go在幕后为您创建了准备好的语句。例如，一个简单的db.Query（sql，param1，param2）的工作方式是准备sql，然后使用参数执行它，最后关闭语句。

Go creates prepared statements for you under the covers. A simple db.Query(sql, param1, param2), for example, works by preparing the sql, then executing it with the parameters and finally closing the statement.

如果 query（）在准备好的语句的掩护下使用，为什么我还要打扰使用准备好的语句？

If query() uses under the covers prepared statements why should I even bother using prepared statements?

推荐答案

为什么还要使用db.Exec（）：

您可以交替使用 db.Exec 和 db.Query 来执行相同的sql语句，但是两种方法返回的类型不同结果。如果由驱动程序实现，则 db.Exec 返回的结果可以告诉您查询影响了多少行，而 db.Query 将改为返回行对象。

"Why even use db.Exec()":

It's true that you can use db.Exec and db.Query interchangeably to execute the same sql statements however the two methods return different types of results. If implemented by the driver the result returned from db.Exec can tell you how many rows were affected by the query, while db.Query will return the rows object instead.

例如，假设您要执行 DELETE 语句您想知道它删除了多少行。您可以通过适当的方式进行操作：

For example let's say you want to execute a DELETE statement and you want to know how many rows were deleted by it. You can do it either the proper way:

res, err := db.Exec(`DELETE FROM my_table WHERE expires_at = $1`, time.Now())
if err != nil {
    panic(err)
}

numDeleted, err := res.RowsAffected()
if err != nil {
    panic(err)
}
print(numDeleted)

或更冗长且客观上更昂贵的方式：

or the more verbose and objectively costlier way:

rows, err := db.Query(`DELETE FROM my_table WHERE expires_at = $1 RETURNING *`, time.Now())
if err != nil {
    panic(err)
}
defer rows.Close()

var numDelete int
for rows.Next() {
    numDeleted += 1
}
if err := rows.Err(); err != nil {
    panic(err)
}
print(numDeleted)

您可以通过以下三种方式结合使用Postgres CTE， SELECT COUNT ， db.QueryRow 和 row.Scan ，但我认为没有必要举一个例子来说明与 db相比该方法有多不合理。 。

There's a 3rd way you could do this with a combination of postgres CTEs, SELECT COUNT, db.QueryRow and row.Scan but I don't think an example is necessary to show how unreasonable an approach that would be when compared to db.Exec.

在上使用 db.Exec 的另一个原因db.Query 是当您不关心返回的结果时，只需要执行查询并检查是否有错误。在这种情况下，您可以执行以下操作：

Another reason to use db.Exec over db.Query is when you don't care about the returned result, when all you need is to execute the query and check if there was an error or not. In such a case you can do this:

if _, err := db.Exec(`<my_sql_query>`); err != nil {
    panic(err)
}

手，您不能（可以但不能）这样做：

On the other hand, you cannot (you can but you shouldn't) do this:

if _, err := db.Query(`<my_sql_query>`); err != nil {
    panic(err)
}

片刻之后，您的程序将出现错误，提示错误，提示类似于打开太多连接。这是因为您要丢弃返回的 db.Rows 值，而无需先对其进行强制性的 Close 调用，然后因此您最终会遇到打开的连接数上升并最终达到服务器的限制。

Doing this, after a short while, your program will panic with an error that says something akin to too many connections open. This is because you're discarding the returned db.Rows value without first making the mandatory Close call on it, and so you end up with the number of open connections going up and eventually hitting the server's limit.

我认为您引用的书不正确。至少在我看来， db.Query 调用是否每次都取决于您使用的驱动程序都创建一个新的准备好的语句。

I don't think the book you've cited is correct. At least to me it looks like whether or not a db.Query call creates a new prepared statement every time is dependent upon the driver you are using.

例如，请参见 queryDC 的这两部分（ db.Query ）：没有准备好的语句和带有准备好的语句。

See for example these two sections of queryDC (an unexported method called by db.Query): without prepared statement and with prepared statement.

不管书是否正确， db.Stmt 关闭返回的 Rows 对象之后，> db.Query 会被丢弃，除非进行一些内部缓存。如果改为手动调用 db.Prepare ，然后缓存并重用返回的 db.Stmt ，则可能会提高性能

Regardless of whether the book is correct or not a db.Stmt created by db.Query would be, unless there is some internal caching going on, thrown away after you close the returned Rows object. If you instead manually call db.Prepare and then cache and reuse the returned db.Stmt you can potentially improve the performance of the queries that need to be executed often.

要了解如何使用准备好的语句来优化性能，可以查看官方文档： https://www.postgresql.org/docs/current/static/sql-prepare。 html

To understand how a prepared statement can be used to optimize performance you can take a look at the official documentation: https://www.postgresql.org/docs/current/static/sql-prepare.html

这篇关于为什么甚至在Golang中使用* DB.exec（）或准备好的语句？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！