13.7. SQL Syntax for Prepared Statements
MySQL 5.1 provides support for server-side prepared
statements. This support takes advantage of the efficient
client/server binary protocol implemented in MySQL 4.1, provided
that you use an appropriate client programming interface.
Candidate interfaces include the MySQL C API client library (for C
programs), MySQL Connector/J (for Java programs), and MySQL
Connector/NET. For example, the C API provides a set of function
calls that make up its prepared statement API. See
Section 25.2.4, “C API Prepared Statements”. Other language
interfaces can provide support for prepared statements that use
the binary protocol by linking in the C client library, one
example being the
mysqli
extension in PHP 5.0.
An alternative SQL interface to prepared statements is available.
This interface is not as efficient as using the binary protocol
through a prepared statement API, but requires no programming
because it is available directly at the SQL level:
You can use it when no programming interface is available to
you.
You can use it from any program that allows you to send SQL
statements to the server to be executed, such as the
mysql client program.
You can use it even if the client is using an old version of
the client library. The only requirement is that you be able
to connect to a server that is recent enough to support SQL
syntax for prepared statements.
SQL syntax for prepared statements is intended to be used for
situations such as these:
You want to test how prepared statements work in your
application before coding it.
An application has problems executing prepared statements and
you want to determine interactively what the problem is.
You want to create a test case that describes a problem you
are having with prepared statements, so that you can file a
bug report.
You need to use prepared statements but do not have access to
a programming API that supports them.
SQL syntax for prepared statements is based on three SQL
statements:
-
PREPARE stmt_name FROM
preparable_stmt
The PREPARE statement prepares a statement
and assigns it a name, stmt_name,
by which to refer to the statement later. Statement names are
not case sensitive. preparable_stmt
is either a string literal or a user variable that contains
the text of the statement. The text must represent a single
SQL statement, not multiple statements. Within the statement,
‘?’ characters can be used as
parameter markers to indicate where data values are to be
bound to the query later when you execute it. The
‘?’ characters should not be
enclosed within quotes, even if you intend to bind them to
string values. Parameter markers can be used only where data
values should appear, not for SQL keywords, identifiers, and
so forth.
If a prepared statement with the given name already exists, it
is deallocated implicitly before the new statement is
prepared. This means that if the new statement contains an
error and cannot be prepared, an error is returned and no
statement with the given name exists.
The scope of a prepared statement is the client session within
which it is created. Other clients cannot see it.
-
EXECUTE stmt_name [USING
@var_name [,
@var_name] ...]
After preparing a statement, you execute it with an
EXECUTE statement that refers to the
prepared statement name. If the prepared statement contains
any parameter markers, you must supply a
USING clause that lists user variables
containing the values to be bound to the parameters. Parameter
values can be supplied only by user variables, and the
USING clause must name exactly as many
variables as the number of parameter markers in the statement.
You can execute a given prepared statement multiple times,
passing different variables to it or setting the variables to
different values before each execution.
-
{DEALLOCATE | DROP} PREPARE
stmt_name
To deallocate a prepared statement, use the
DEALLOCATE PREPARE statement. Attempting to
execute a prepared statement after deallocating it results in
an error.
If you terminate a client session without deallocating a
previously prepared statement, the server deallocates it
automatically.
The following SQL statements can be used in prepared statements:
CREATE TABLE, DELETE,
DO, INSERT,
REPLACE, SELECT,
SET, UPDATE, and most
SHOW statements. Other statements are not yet
supported.
The following examples show two equivalent ways of preparing a
statement that computes the hypotenuse of a triangle given the
lengths of the two sides.
The first example shows how to create a prepared statement by
using a string literal to supply the text of the statement:
mysql> PREPARE stmt1 FROM 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse';
mysql> SET @a = 3;
mysql> SET @b = 4;
mysql> EXECUTE stmt1 USING @a, @b;
+------------+
| hypotenuse |
+------------+
| 5 |
+------------+
mysql> DEALLOCATE PREPARE stmt1;
The second example is similar, but supplies the text of the
statement as a user variable:
mysql> SET @s = 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse';
mysql> PREPARE stmt2 FROM @s;
mysql> SET @a = 6;
mysql> SET @b = 8;
mysql> EXECUTE stmt2 USING @a, @b;
+------------+
| hypotenuse |
+------------+
| 10 |
+------------+
mysql> DEALLOCATE PREPARE stmt2;
SQL syntax for prepared statements cannot be used in nested
fashion. That is, a statement passed to PREPARE
cannot itself be a PREPARE,
EXECUTE, or DEALLOCATE
PREPARE statement.
SQL syntax for prepared statements is distinct from using prepared
statement API calls. For example, you cannot use the
mysql_stmt_prepare() C API function to prepare
a PREPARE, EXECUTE, or
DEALLOCATE PREPARE statement.
SQL syntax for prepared statements can be used within stored
procedures, but not in stored functions or triggers.
Placeholders can be used for the arguments of the
LIMIT clause when using prepared statements.
See Section 13.2.7, “SELECT Syntax”.
