Skip to content

PREPARE

Parameter Explanation

Parameter Description
stmt_name The name of the prepared SQL statement.
preparable_stmt A string literal or user variable containing the SQL statement text. The text must represent a single statement, not multiple statements. Within the statement, the ? character can be used as a parameter marker to indicate where data values will be bound to the query when it is executed later. The ? character is not enclosed in quotes, even if you intend to bind them to string values. Parameter markers can only be used where data values should appear, not for SQL keywords, identifiers, etc.

If a prepared statement with the given name already exists, it is implicitly released before preparing the new statement.

The following points should be noted when creating prepared statements:

  • Prepared statements created in one session are not available to other sessions.
  • When a session ends, whether normally or abnormally, its prepared statements no longer exist. If auto-reconnect is enabled, the client is not notified of the connection loss. Therefore, the client may disable auto-reconnect.

The parameters used in a prepared statement must have their types determined when the statement is first prepared, and these types are retained when the prepared statement is executed with EXECUTE. The following rules determine parameter types:

  • The parameter data types of both operands of a binary arithmetic operator must be consistent.
  • If both operands of a binary arithmetic operator are formal parameters, the parameter type is determined by the operator's context.
  • If the operand of a unary arithmetic operator is a formal parameter, the parameter type is determined by the operator's context.
  • If an arithmetic operator has no context to determine the operand parameter type, the derived type of the relevant parameters is DOUBLE PRECISION. For example, if the parameter is a top-level node in a SELECT list or part of a comparison operator, the derived type of the relevant parameters is DOUBLE PRECISION.
  • The formal parameters of string operators have the same derived type as the aggregate type of other operands. If all operands of the operator are formal parameters, the derived type is VARCHAR, with collation determined by the value of collation_connection.
  • For formal parameters that are operands of temporal operators, if the operator returns DATETIME, the parameter type is DATETIME; if it returns TIME, the parameter type is TIME; if it returns DATE, the parameter type is DATE.
  • The derived types of the two parameters of a binary comparison operator's operands must be consistent.
  • For formal parameters that are operands of ternary comparison operators (e.g., BETWEEN), their derived types match the aggregate type of other operands.
  • If all operands of a comparison operator are formal parameters, the derived type of each operand is VARCHAR, with collation determined by collation_connection.
  • The derived type of a formal parameter in CASE, COALESCE, IF, IFNULL, or NULLIF matches the aggregate type of other output operands.
  • If all output operands of CASE, COALESCE, IF, IFNULL, or NULLIF are formal parameters or NULL, the parameter type is determined by the operator's context.
  • If a parameter is an operand of CASE, COALESCE(), IF, or IFNULL and the operator context cannot determine its type, the derived type is VARCHAR, with collation determined by collation_connection.
  • The formal parameter of a CAST() operand has the same type as specified by CAST().
  • If a formal parameter is a direct member of a SELECT list (not part of an INSERT statement), its derived type is VARCHAR, with collation determined by collation_connection.
  • If a formal parameter is a direct member of a SELECT list in an INSERT statement, its derived type matches the type of the corresponding column being inserted.
  • If a formal parameter is used as the assignment source in an UPDATE statement's SET clause or an INSERT statement's ON DUPLICATE KEY UPDATE clause, its derived type matches the type of the column being updated.
  • If a formal parameter is an actual argument of a function, its derived type depends on the function's return type.

For certain combinations of actual and derived types, automatic re-preparation of the prepared statement is triggered. Re-preparation is not required in the following cases:

  • The actual parameter is NULL.
  • The formal parameter is an operand of CAST(). (CAST() attempts to convert the parameter to the derived type; if conversion fails, an exception is raised.)
  • The parameter type is a string. (In this case, CAST(? AS derived_type) is implicitly executed.)
  • The derived and actual types of the parameter are both INTEGER and have the same sign.
  • The derived type is DECIMAL, and the actual type is DECIMAL or INTEGER.
  • The derived type is DOUBLE, and the actual type is any numeric type.
  • The derived and actual types are both string types.
  • The derived and actual types are both temporal types. Exceptions: If the derived type is TIME and the actual type is not TIME, or if the derived type is DATE and the actual type is not DATE.
  • The derived type is a temporal type, and the actual type is a numeric type.

In all other cases, the prepared statement must be re-prepared using the actual parameter type, not the derived type.

These rules also apply to user variables referenced in prepared statements.

Using different data types for a given parameter or user variable in a prepared statement during the first execution may cause re-preparation, reducing efficiency and potentially altering the actual type of the parameter (or variable). This may lead to inconsistencies between the actual execution results and the expected results. It is recommended to use consistent data types for parameters in prepared statements.

Examples

```sql

create table t13 (a int primary key); insert into t13 values (1); select * from t13 where 3 in (select (1+1) union select 1); Empty set (0.01 sec)

select * from t13 where 3 in (select (1+2) union select 1); +------+ | a | +------+ | 1 | +------+ 1 row in set (0.01 sec)

prepare st_18492 from 'select * from t13 where 3 in (select (1+1) union select 1)'; Query OK, 0 rows affected (0.00 sec)

execute st_18492; Empty set (0.01 sec)

prepare st_18493 from 'select * from t13 where 3 in (select (2+1) union select 1)'; Query OK, 0 rows affected (0.00 sec)

execute st_18493; +------+ | a | +------+ | 1 | +------+ 1 row in set (0.00 sec)

deallocate prepare st_18492; Query OK, 0 rows affected (0.00 sec)

deallocate prepare st_18493; Query OK, 0 rows affected (0.00 sec)