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;

Buckley

if your application is using connection pooling, using sessions can be dangerous. Global variables declared can be left in place. Wouldn't it be better to simply pass the value in as a parameter to whatever query/procedure needs it? – Dung

Yes, connection pooling is used. I think of @id as of a session parameter, not global. Using name parameters is more flexible than place parameters using "?", because adding/removing a variable needs no order changes in code. – Buckley

Resulted in passing to method key-value pairs and replacing all @-keys with corresponding values. That was a good idea, thank you. This helps me keep different languages in different files unmixed. – Buckley

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.

Kleeman

Hello. Thanks for the answer. I use NO GLOBAL VARIABLES OR PROCEDURES. I use session variables - it is different. It is not my invention, but a standard MySQL method. I found no arguements why shouldn't it have a code smell or why is it a bad practice. Although the problem is how to finish with the variable, not how to pass the parameters, I'd like to clarify the position. "?"-method discomfort is not in declaring work, but in changes you have to make in language code after shifting parameters order in MySQL script. – Buckley

@Buckley You may want to post some code that shows how you use the session variables. I may be misunderstanding your use case. But the very fact that you want to destroy a session variable before the end of the session is a strong indicator that you probably need to use another mechanism. – Kleeman

Added example - nothing special, see the first code block. I don't want to destroy, I just ask should I, why and is there another way to cope with it (variable). – Buckley

I would need to see the way you invoke this snippet. As far as I can see, there is simply no need for a variable here. As for the way to "unset" a variable, setting it to null is the only way to go (see the second part of my answer). – Kleeman

To pass a value by means of a session variable is an obligatory condition in my question. It is not a good practice to solve a task by denying its conditions. :) – Buckley

You may be having an XY problem. I have provided an answer to your initial question, I believe. You are most likely taking a wrong approach, but I don't really care after all. – Kleeman

@RandomSeed, Interesting post. So there is no way to differentiate between a variable @some_variable that has never been set an a variable ` @some_variable which has been set to NULL? – Storied

@Storied Correct, very much like shell environment variables. – Kleeman

Answer 2

The direct answer is when using @-variables in MySQL there is no delete/unset a session variable, closing the session will clear all the session variables. Checked the MySQL reference manual 8.0. Was reading through the answers and thought of making it compact Cheers !

Joyajoyan

Answer 3

I haven't read the manual or anything. But what I've found when using @-variables is that they disappear with the database connection. I guess that's what you call a session. In any case, reconnecting to the database seems to remove all variables.
If you use MySQL with a web server, this means that all your variables get deleted as soon as a page has been delivered. That is actually how I found this out. You cannot keep an SQL @-variable from one http call to another.

Egypt

Except when using connection pooling as the connection is not destroyed; simply released for re-use...until the thread times out and is recycled at which time the global variables would be reset. – Dung