top of page
CerebroSQL

MySQL: 

PREPARE

Syntax:
PREPARE stmt_name FROM preparable_stmt

The PREPARE statement prepares a SQL statement and assigns it a name,
stmt_name, by which to refer to the statement later. The prepared
statement is executed with EXECUTE and released with DEALLOCATE
PREPARE. For examples, see
https://dev.mysql.com/doc/refman/8.0/en/sql-prepared-statements.html.

Statement names are not case-sensitive. preparable_stmt is either a
string literal or a user variable that contains the text of the SQL
statement. The text must represent a single statement, not multiple
statements. Within the statement, ? characters can be used as parameter
markers to indicate where data values are to be bound to the query
later when you execute it. The ? characters should not be enclosed
within quotation marks, even if you intend to bind them to string
values. Parameter markers can be used only where data values should
appear, not for SQL keywords, identifiers, and so forth.

If a prepared statement with the given name already exists, it is
deallocated implicitly before the new statement is prepared. This means
that if the new statement contains an error and cannot be prepared, an
error is returned and no statement with the given name exists.

The scope of a prepared statement is the session within which it is
created, which as several implications:

o A prepared statement created in one session is not available to other
sessions.

o When a session ends, whether normally or abnormally, its prepared
statements no longer exist. If auto-reconnect is enabled, the client
is not notified that the connection was lost. For this reason,
clients may wish to disable auto-reconnect. See C API Automatic
Reconnection Control
(https://dev.mysql.com/doc/c-api/8.0/en/c-api-auto-reconnect.html).

o A prepared statement created within a stored program continues to
exist after the program finishes executing and can be executed
outside the program later.

o A statement prepared in stored program context cannot refer to stored
procedure or function parameters or local variables because they go
out of scope when the program ends and would be unavailable were the
statement to be executed later outside the program. As a workaround,
refer instead to user-defined variables, which also have session
scope; see
https://dev.mysql.com/doc/refman/8.0/en/user-variables.html.

Beginning with MySQL 8.0.22, a user variable referenced in a prepared
statement has its type determined when the statement is first prepared,
and retains this type whenever EXECUTE is invoked for this prepared
statement.

URL: https://dev.mysql.com/doc/refman/8.0/en/prepare.html

Example

bottom of page