# **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**

```text
> 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)