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;

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? — xQbert
– xQbert, Commented Jul 22, 2014 at 20:55
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. — Zon
– Zon, Commented Jul 23, 2014 at 4:42
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. — Zon
– Zon, Commented Apr 6, 2015 at 13:29

Community · Accepted Answer · 2017-05-23 11:47:07Z

18

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.

edited May 23, 2017 at 11:47

CommunityBot

11 silver badge

answered Jul 23, 2014 at 9:52

RandomSeed

29.9k6 gold badges54 silver badges91 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Zon Over a year ago

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.

RandomSeed Over a year ago

@Zon 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.

Zon Over a year ago

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).

RandomSeed Over a year ago

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).

RandomSeed Over a year ago

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.

|

C.Poh · Accepted Answer · 2021-06-22 12:04:17Z

5

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 !

answered Jun 22, 2021 at 12:04

C.Poh

4805 silver badges9 bronze badges

Comments

user1904991 · Accepted Answer · 2015-04-03 18:58:09Z

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.

answered Apr 3, 2015 at 18:58

user1904991

1395 bronze badges

1 Comment

xQbert Over a year ago

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.

Collectives™ on Stack Overflow

MySQL Delete Session Variable

3 Answers 3

8 Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

8 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related