MySQL in a Nutshell, 2nd Edition

Name

PREPARE

Synopsis

PREPARE statement_name FROM statement

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 can potentially improve performance. Prepared statements are local to the user and session; they’re not global. The name given can be any nonreserved 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 EXECUTE statement, the placeholders will be replaced with the values given. The values must be user variables (set with the SET statement) and must be passed to the EXECUTE statement in the order that the placeholders appear in the prepared statement. Here is a simple example using these statements:

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 ...