PREPARE

This MySQL statement creates a prepared statement.

Syntax

Below is the syntax for this command. Text within square brackets (i.e, [ and ] ) are optional. Choices are separated by bars (i.e, | ). Ellipses preceded by a comma indicates a repeating pattern. Ellipses before or after syntax are used to highlight a relevant except from the larger syntax. Text displayed in italic letters represent text that should be replaced with the specific names related to the database (e.g., column with the name of the column).

PREPARE statement_name FROM statement

Explanation

This statement creates a prepared statement. A prepared statement is used to cache an SQL statement, so as to save processing time during multiple executions of the statement. This could potentially can improve performance. Prepared statements are local to the user and session; they're not global. The name given can be any non-reserved name and is case-insensitive. The statement given within quotes can be any type of SQL statement.

If you want to include a value that will be changed when the statement is executed, give a question mark as a placeholder within statement. When the prepared statement is executed later with the MySQL statement EXECUTE, the placeholders will be replaced with the values given. The values must be user variables (set by with the SET statement) and must be passed to the EXECUTE statement in the order that the placeholders appear in the prepared statement.

Examples

A simple example using these MySQL statements follows:

PREPARE state_tally
FROM 'SELECT COUNT(*)
      FROM students
      WHERE home_city = ?';

SET @city = 'New Orleans';
EXECUTE state_tally USING @city;

SET @city = 'Boston';
EXECUTE state_tally USING @city;

In this example, the query within the prepared statement will return a count of the number of students from the city given. By setting the value of the user-defined variable @city to another city, we can execute the prepared statement state_tally again without having to reenter the PREPARE statement. The results will probably be different, of course. To remove a prepared statement from the cache, use the MySQL statement, DROP PREPARE.