[+/-]
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 21.9.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, available in PHP 5.0 and later.
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
prepares a statement for
execution (see Section 12.6.1, “PREPARE
Syntax”).
EXECUTE
executes a prepared
statement (see Section 12.6.2, “EXECUTE
Syntax”).
DEALLOCATE PREPARE
releases a
prepared statement (see Section 12.6.3, “DEALLOCATE PREPARE
Syntax”).
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;
Here is an additional example that demonstrates how to choose the table on which to perform a query at runtime, by storing the name of the table as a user variable:
mysql>USE test;
mysql>CREATE TABLE t1 (a INT NOT NULL);
mysql>INSERT INTO t1 VALUES (4), (8), (11), (32), (80);
mysql>SET @table = 't1';
mysql>SET @s = CONCAT('SELECT * FROM ', @table);
mysql>PREPARE stmt3 FROM @s;
mysql>EXECUTE stmt3;
+----+ | a | +----+ | 4 | | 8 | | 11 | | 32 | | 80 | +----+ mysql>DEALLOCATE PREPARE stmt3;
A prepared statement is specific to the session in which it was created. If you terminate a session without deallocating a previously prepared statement, the server deallocates it automatically.
A prepared statement is also global to the session. If you create a prepared statement within a stored routine, it is not deallocated when the stored routine ends.
To guard against too many prepared statements being created
simultaneously, set the
max_prepared_stmt_count
system
variable. To prevent the use of prepared statements, set the value
to 0.
The following SQL statements can be used in prepared statements:
ALTER TABLE
,
CALL
,
COMMIT
, CREATE
INDEX
, CREATE TABLE
,
DELETE
,
DO
, DROP
INDEX
, DROP TABLE
,
INSERT
, RENAME
TABLE
, REPLACE
,
SELECT
,
SET
,
UPDATE
, and most
SHOW
statements.
As of MySQL 5.1.10, the following additional statements are supported:
ANALYZE TABLE OPTIMIZE TABLE REPAIR TABLE
As of MySQL 5.1.12, the following additional statements are supported:
CACHE INDEX CHANGE MASTER CHECKSUM {TABLE | TABLES} {CREATE | DROP} DATABASE {CREATE | RENAME | DROP} USER FLUSH {TABLE | TABLES | TABLES WITH READ LOCK | HOSTS | PRIVILEGES | LOGS | STATUS | MASTER | SLAVE | DES_KEY_FILE | USER_RESOURCES} GRANT REVOKE KILL LOAD INDEX INTO CACHE RESET {MASTER | SLAVE | QUERY CACHE} SHOW BINLOG EVENTS SHOW CREATE {PROCEDURE | FUNCTION | EVENT | TABLE | VIEW} SHOW {AUTHORS | CONTRIBUTORS | WARNINGS | ERRORS} SHOW {MASTER | BINARY} LOGS SHOW {MASTER | SLAVE} STATUS SLAVE {START | STOP} INSTALL PLUGIN UNINSTALL PLUGIN
Other statements are not yet supported.
Statements not allowed in SQL prepared statements are generally also not permitted in stored routines. Any exceptions to this rule are noted in Section 19.2, “Using Stored Routines (Procedures and Functions)”.
Placeholders can be used for the arguments of the
LIMIT
clause when using prepared statements. See
Section 12.2.8, “SELECT
Syntax”.
In prepared CALL
statements used with
PREPARE
and
EXECUTE
, placeholder support for
OUT
and INOUT
parameters is
not available in MySQL 5.1. See Section 12.2.1, “CALL
Syntax”,
for an example and a workaround. Placeholders can be used for
IN
parameters regardless of version.
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. However, a
cursor cannot be used for a dynamic statement that is prepared and
executed with PREPARE
and
EXECUTE
. The statement for a cursor
is checked at cursor creation time, so the statement cannot be
dynamic.
SQL syntax for prepared statements does not support multi-statements
(that is, multiple statements within a single string separated by
“;
” characters).
Before MySQL 5.1.17, prepared statements do not use the query cache. As of 5.1.17, prepared statements use the query cache under the conditions described in Section 7.5.5.1, “How the Query Cache Operates”.
To write C programs that use the CALL
SQL statement to execute stored procedures that contain prepared
statements, the CLIENT_MULTI_RESULTS
flag must be
enabled. This is because each CALL
returns a result to indicate the call status, in addition to any
result sets that might be returned by statements executed within the
procedure.
CLIENT_MULTI_RESULTS
can be enabled when you call
mysql_real_connect()
, either
explicitly by passing the CLIENT_MULTI_RESULTS
flag itself, or implicitly by passing
CLIENT_MULTI_STATEMENTS
(which also enables
CLIENT_MULTI_RESULTS
). For additional
information, see Section 12.2.1, “CALL
Syntax”.
User Comments
Add your own comment.