What happens if I will prepare the statement with the same name without de-allocating the earlier one?

Actually, in MySQL, we can prepare a statement with the same name without de-allocating the earlier one because MySQL automatically drops the prepared statements when they are redefined or when we close the connection to the server. In other words, we can say that we can use the same name for prepared statements without de-allocating them explicitly. But, to free the memory on the server side we must have to de-allocate them. It can be done with the help of DEALLOCATE statement as follows −

DEALLOCATE PREPARE statement;

Here statement is the name of the prepared statements.

DROP PREPARE statements is the synonym for de-allocating the prepared statements

Example

mysql> DROP PREPARE stmt11;
Query OK, 0 rows affected (0.00 sec)

Prabhas

Published on 20-Feb-2018 15:51:05