postgresql 为什么甚至在 Golang 中使用 *DB.exec() 或准备好的语句？

Why even use *DB.exec() or prepared statements in Golang?

提问by CommonSenseCode

I'm using golang with Postgresql.

我在 Postgresql 中使用 golang。

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

它在这里说，对于不返回行（插入、删除、更新）的操作，我们应该使用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().

如果一个函数名包含 Query，它被设计用来询问数据库的问题，并且会返回一组行，即使它是空的。不返回行的语句不应使用查询函数；他们应该使用 Exec()。

Then it says here:

然后它在这里说：

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.

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

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

如果query()在幕后使用准备好的语句，我为什么还要费心使用准备好的语句？

回答by mkopriva

"Why even use db.Exec()":

“为什么还要使用 db.Exec()”：

It's true that you can use db.Execand db.Queryinterchangeably 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.Execcan tell you how many rows were affected by the query, while db.Querywill return the rows object instead.

确实，您可以使用db.Exec和db.Query可互换地执行相同的 sql 语句，但是这两种方法返回不同类型的结果。如果由驱动程序实现，则返回的结果db.Exec可以告诉您有多少行受到查询的影响，而db.Query将返回行对象。

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

例如，假设您要执行一条DELETE语句，并且想知道它删除了多少行。你可以用正确的方式做到这一点：

res, err := db.Exec(`DELETE FROM my_table WHERE expires_at = `, 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 =  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)

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

有一个第3的方式，你可以用Postgres的热膨胀系数的组合做到这一点SELECT COUNT，db.QueryRow而row.Scan但我不认为一个例子是要说明如何不合理的做法相比时，这将是db.Exec。

Another reason to use db.Execover db.Queryis 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:

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

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)
}

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.Rowsvalue without first making the mandatory Closecall on it, and so you end up with the number of open connections going up and eventually hitting the server's limit.

这样做之后，您的程序将在短时间内出现类似too many connections open. 这是因为您在db.Rows没有首先对其进行强制Close调用的情况下就丢弃了返回值，因此最终打开的连接数会增加并最终达到服务器的限制。

---

---

"or prepared statements in Golang?":

“或在 Golang 中准备好的语句？”：

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

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

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

例如，请参见queryDC（由 调用的未导出方法db.Query）的这两部分：without Prepared statement和with Prepared statement。

Regardless of whether the book is correct or not a db.Stmtcreated by db.Querywould be, unless there is some internal caching going on, thrown away after you close the returned Rowsobject. If you instead manually call db.Prepareand then cache and reuse the returned db.Stmtyou can potentially improve the performance of the queries that need to be executed often.

不管这本书是否正确，db.Stmtcreated bydb.Query都会是，除非有一些内部缓存正在进行，在关闭返回的Rows对象后将其丢弃。如果您改为手动调用db.Prepare，然后缓存并重用返回的值db.Stmt，则可以潜在地提高需要经常执行的查询的性能。

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

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