Name
PREPARE
Synopsis
PREPAREstatement_name
FROMstatement
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 ...
Get MySQL in a Nutshell, 2nd Edition now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.