MySQL Delete Session Variable

Question

SITUATION: MySQL query uses value, delivered from outside. This is done by declaring and initializing a session variable, that remains valid until the end of session:

SET @id = 0;

SELECT * FROM my_table
WHERE id = @id;

QUESTION: Is it a good practice to delete session variable after the query has finished (for safety reasons)? What is the most adequate way to do this?

STUDIES: I found the following suggestion, but have some doubts as it looks like "uninitialise", not like "undeclare":

SET @id = NULL;

Answer 1

Using session variables to pass parameters is a bad practice (using global variables often indicates code smell). Therefore there is no adequate or recommended way to deal with your situation⁽¹⁾.

You commented:

Using name parameters is more flexible than place parameters using "?", because adding/removing a variable needs no order changes in code.

This is an illusion. Using this approach, you will still need to document "somewhere" that your procedure requires some variables be declared before calling. This also introduce the very problem you are trying to address.

On the other hand, a stored procedure (or prepared statement) is self-documenting and parameters have a well-known scope and life span.

Now, to answer the specific question:

How to delete/unset a session variable?

SET @id = NULL; is the only way to go. You cannot "undeclare" a session variable, since you cannot "declare" a session variable (try SELECT @dummy_variable_never_declared_before;). MySQL session variables are very similar to shell environment variables.⁽²⁾

---

⁽¹⁾ ... except if you make sure the session is closed after you are done. Closing the session will surely clear all session variables.

⁽²⁾ I have learnt that bash session variables can indeed be unset.