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 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 parameter used 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 (unless the statement is reprepared, as explained later in
this section). Rules for determining a parameter's type are listed
here:

o A parameter which is an operand of a binary arithmetic operator has
  the same data type as the other operand.

o If both operands of a binary arithmetic operator are parameters, the
  type of the parameters is decided by the context of the operator.

o If a parameter is the operand of a unary arithmetic operator, the
  parameter's type is decided by the context of the operator.

o If an arithmetic operator has no type-determining context, the
  derived type for any parameters involved is DOUBLE PRECISION. This
  can happen, for example, when the parameter is a top-level node in a
  SELECT list, or when it is part of a comparison operator.

o A parameter which is an operand of a character string operator has
  the same derived type as the aggregated type of the other operands.
  If all operands of the operator are parameters, the derived type is
  VARCHAR; its collation is determined by the value of
  collation_connection.

o A parameter which is an operand of a temporal operator has type
  DATETIME if the operator returns a DATETIME, TIME if the operator
  returns a TIME, and DATE if the operator returns a DATE.

o A parameter which is an operand of a binary comparison operator has
  the same derived type as the other operand of the comparison.

o A parameter that is an operand of a ternary comparison operator such
  as BETWEEN has the same derived type as the aggregated type of the
  other operands.

o If all operands of a comparison operator are parameters, the derived
  type for each of them is VARCHAR, with collation determined by the
  value of collation_connection.

o A parameter that is an output operand of any of CASE, COALESCE, IF,
  IFNULL, or NULLIF has the same derived type as the aggregated type of
  the operator's other output operands.

o If all output operands of any of CASE, COALESCE, IF, IFNULL, or
  NULLIF are parameters, or they are all NULL, the type of the
  parameter is decided by the context of the operator.

o If the parameter is an operand of any of CASE, COALESCE(), IF, or
  IFNULL, and has no type-determining context, the derived type for
  each of the parameters involved is VARCHAR, and its collation is
  determined by the value of collation_connection.

o A parameter which is the operand of a CAST() has the same type as
  specified by the CAST().

o If a parameter is an immediate member of a SELECT list that is not
  part of an INSERT statement, the derived type of the parameter is
  VARCHAR, and its collation is determined by the value of
  collation_connection.

o If a parameter is an immediate member of a SELECT list that is part
  of an INSERT statement, the derived type of the parameter is the type
  of the corresponding column into which the parameter is inserted.

o If a parameter is used as source for an assignment in a SET clause of
  an UPDATE statement or in the ON DUPLICATE KEY UPDATE clause of an
  INSERT statement, the derived type of the parameter is the type of
  the corresponding column which is updated by the SET or ON DUPLICATE
  KEY UPDATE clause.

o If a parameter is an argument of a function, the derived type depends
  on the function's return type.

For some combinations of actual type and derived type, an automatic
repreparation of the statement is triggered, to ensure closer
compatibility with previous versions of MySQL. Repreparation does not
occur if any of the following conditions are true:

o NULL is used as the actual parameter value.

o A parameter is an operand of a CAST(). (Instead, a cast to the
  derived type is attempted, and an exception raised if the cast
  fails.)

o A parameter is a string. (In this case, an implicit CAST(? AS
  derived_type) is performed.)

o The derived type and actual type of the parameter are both INTEGER
  and have the same sign.

o The parameter's derived type is DECIMAL and its actual type is either
  DECIMAL or INTEGER.

o The derived type is DOUBLE and the actual type is any numeric type.

o Both the derived type and the actual type are string types.

o If the derived type is temporal and the actual type is temporal.
  Exceptions: The derived type is TIME and the actual type is not TIME;
  the derived type is DATE and the actual type is not DATE.

o The derived type is temporal and the actual type is numeric.

For cases other than those just listed, the statement is reprepared and
the actual parameter types are used instead of the derived parameter
types.

These rules also apply to a user variable referenced in a prepared
statement.

Using a different data type for a given parameter or user variable
within a prepared statement for executions of the statement subsequent
to the first execution causes the statement to be reprepared. This is
less efficient; it may also lead to the parameter's (or variable's)
actual type to vary, and thus for results to be inconsistent, with
subsequent executions of the prepared statement. For these reasons, it
is advisable to use the same data type for a given parameter when
re-executing a prepared statement.

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

