The MySQL client/server protocol provides for the use of prepared
statements. This capability uses the MYSQL_STMT
statement handler data structure returned by the
mysql_stmt_init()
initialization
function. Prepared execution is an efficient way to execute a
statement more than once. The statement is first parsed to prepare
it for execution. Then it is executed one or more times at a later
time, using the statement handle returned by the initialization
function.
Prepared execution is faster than direct execution for statements executed more than once, primarily because the query is parsed only once. In the case of direct execution, the query is parsed every time it is executed. Prepared execution also can provide a reduction of network traffic because for each execution of the prepared statement, it is necessary only to send the data for the parameters.
Prepared statements might not provide a performance increase in some situations. For best results, test your application both with prepared and nonprepared statements and choose whichever yields best performance.
Another advantage of prepared statements is that it uses a binary protocol that makes data transfer between client and server more efficient.
The following SQL statements can be used as prepared statements:
CALL
, CREATE
TABLE
, DELETE
,
DO
,
INSERT
,
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 | RENAME | 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 in MySQL 5.1.
MySQL Enterprise. MySQL Enterprise subscribers will find more information about using prepared statements in the Knowledge Base article, How can I create server-side prepared statements?. Access to the MySQL Knowledge Base collection of articles is one of the advantages of subscribing to MySQL Enterprise. For more information, see http://www.mysql.com/products/enterprise/advisors.html.
User Comments
Add your own comment.