Database Reference
In-Depth Information
A second benefit of placeholders is that you can “prepare” a statement in advance, then
reuse it by binding different values to it each time it's executed. Prepared statements
thus encourage statement reuse. Statements become more generic because they contain
placeholders rather than specific data values. If you perform an operation over and over,
you may be able to reuse a prepared statement and simply bind different data values to
it each time you execute it. Some database systems (MySQL not among them) have the
capability of performing some preparsing or even execution planning prior to executing
a prepared statement. For a statement that is executed multiple times later, this reduces
overhead because anything that can be done prior to execution need be done only once,
not once per execution. For example, if a program executes a particular type of
SE
LECT
statement several times while it runs, such a database system can construct a plan
for the statement and then reuse it each time, rather than rebuild the plan over and over.
MySQL doesn't build query plans in advance, so you get no performance boost from
using prepared statements. However, if you port a program to a database that does reuse
query plans and you've written your program to use prepared statements, you can get
this advantage of prepared statements automatically. You need not convert from non‐
prepared statements to enjoy that benefit.
A third (admittedly subjective) benefit is that code that uses placeholder-based state‐
ments can be easier to read. As you work through this section, compare the statements
used here with those from
Recipe 2.4
that did not use placeholders to see which you
prefer.
Using a quoting function
Some APIs provide a quoting function that takes a data value as its argument and returns
a properly quoted and escaped value suitable for safe insertion into an SQL statement.
This is less common than using placeholders, but it can be useful for constructing state‐
ments that you do not intend to execute immediately. However, you must have a con‐
nection open to the database server while you use such a quoting function because the
API cannot select the proper quoting rules until the database driver is known. (The rules
differ among database systems.)
As we'll indicate later, some APIs quote as strings all non-
NULL
val‐
ues, even numbers, when binding them to parameter markers. This
can be an issue in contexts that
require
numbers, as described fur‐
ther in
Recipe 3.11
.
Generating a List of Placeholders
You cannot bind an array of data values to a single placeholder. Each value must be
bound to a separate placeholder. To use placeholders for a list of data values that may
