DEALLOCATE PREPARE

Grammar Description

The purpose of the DEALLOCATE PREPARE statement is to release precompiled statements generated using PREPARE. After releasing the precompiled statement, executing the precompiled statement again will result in an error. If too many precompiled statements are created and not released using the DEALLOCATE PREPARE statement, the system variable will enforce the precompiled statement upper limit max_prepared_stmt_count prompt.

Grammar Structure

{DEALLOCATE | DROP} PREPARE stmt_name

Parameter definition

Parameters	Description
stmt_name	Name of precompiled SQL statement

Example

> CREATE TABLE numbers(pk INTEGER PRIMARY KEY, ui BIGINT UNSIGNED, si BIGINT);
> INSERT INTO numbers VALUES (0, 0, -9223372036854775808), (1, 18446744073709551615, 9223372036854775807);
> SET @si_min = -9223372036854775808;
> SET @si_max = 9223372036854775807;
> PREPARE s2 FROM 'SELECT * FROM numbers WHERE si=?';
Query OK, 0 rows affected (0.00 sec)

> EXECUTE s2 USING @si_min;
+------+-----------------------------------------------------------------------------------------------------------------------
| pk | ui | si |
+------+-----------------------------------------------------------------------------------------------------------------------
| 0 | 0 | -9223372036854775808 |
+------+-----------------------------------------------------------------------------------------------------------------------
1 row in set (0.01 sec)

> EXECUTE s2 USING @si_max;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| pk | ui | si |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 1 | 18446744073709551615 | 9223372036854775807 |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 row in set (0.01 sec)

> DEALLOCATE PREPARE s2;
Query OK, 0 rows affected (0.00 sec)