mysql> PREPARE my_stmt FROM 'SELECT COUNT(*) FROM CountryLanguage WHERE CountryCode = ?';
mysql> SET @code = 'ESP'; EXECUTE my_stmt USING @code;
mysql> SET @code = 'RUS'; EXECUTE my_stmt USING @code;
mysql> DEALLOCATE PREPARE my_stmt;
The PREPARE statement is used to define an SQL statement that will be executed later. PREPARE takes two arguments: a name to assign to the statement once it has been prepared, and the text of the SQL statement. Prepared statement names are not case sensitive. The text of the SQL statement can be given either as a literal string or as a user variable containing the statement.
The statement may not be complete, because data values that are unknown at preparation time are represented by the question mark characters that serve as parameter markers. At the time the statement is executed, you provide specific data values, one for each parameter in the statement. The server replaces the markers with the data values to complete the statement. Different values can be used each time the statement is executed.
If you prepare a statement using a name that already exists, the server first discards the existing prepared statement, and then prepares the new statement. If the new statement contains an error and cannot be prepared, the result is that no statement with the given name exist.
MySQL does not allow every type of SQL statement to be prepared. Those that may be prepared are limited to SELECT, INSERT, REPLACE, UPDATE, DELETE, CREATE TABLE, SET, DO, and many SHOW statements.
A prepared statement exists only for the duration of the session, and is visible only to the session which it is created. When a session ends, all prepared statements for that session are discarded.
DROP PREPARE is an alias for DEALLOCATE PREPARE.
Prepared statement enhance performance because the statement is parsed only once by the server.
