MySQL provides us with the ability to create multiple prepared statements and run them as many times as we like, while changing the parameter values with each run.

While prepared statements are only available to the session that created them, they are stored in the server. So it’s quite possible that a server could have a build up of lots of prepared statements hanging around once different users have created them.

This obviously uses up resources and could lead to problems if too many prepared statements are created over time.

That’s why you should drop (or “deallocate”) your prepared statements once you’re done with them.

The DEALLOCATE PREPARE Statement

We can use the DEALLOCATE PREPARE statement to drop a given prepared statement once we’re finished with it. The syntax goes like this:

{DEALLOCATE | DROP} PREPARE stmt_name

So we can use either DEALLOCATE or DROP. We must use PREPARE, and here. stmt_name is the name of the statement we want to remove.

Example

Here’s an example of dropping a prepared statement:

DEALLOCATE PREPARE get_user_by_username;

That removed a prepared statement called get_user_by_username.

As mentioned, we can alternatively use DROP instead of DEALLOCATE. Therefore we could do the following:

DROP PREPARE get_user_by_email;

That dropped (or deallocated) a prepared statement called get_user_by_email.