jtuki jtuki - 6 months ago 94
MySQL Question

golang sql driver's prepare statement

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?

Answer

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 stmt.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.