11

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;
Brian Tompsett - 汤莱恩
  • 5,195
  • 62
  • 50
  • 120
Zon
  • 12,838
  • 4
  • 69
  • 82
  • 3
    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 Jul 22 '14 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 Jul 23 '14 at 04: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 Apr 06 '15 at 13:29

2 Answers2

12

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

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

(1) ... except if you make sure the session is closed after you are done. Closing the session will surely clear all session variables.

(2) I have learnt that bash session variables can indeed be unset.

Community
  • 1
  • 1
RandomSeed
  • 27,760
  • 6
  • 45
  • 82
  • 3
    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. – Zon Jul 23 '14 at 11:47
  • 1
    @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. – RandomSeed Jul 23 '14 at 12:46
  • 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). – Zon Jul 23 '14 at 13:03
  • 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 Jul 23 '14 at 13:18
  • 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. :) – Zon Jul 23 '14 at 19:12
  • You may be having an [XY problem](http://meta.stackexchange.com/questions/66377/what-is-the-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. – RandomSeed Jul 23 '14 at 21:14
  • @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`? – Pacerier Jul 08 '15 at 10:08
  • @Pacerier Correct, very much like shell environment variables. – RandomSeed Jul 08 '15 at 10:30
1

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.

user1904991
  • 111
  • 4
  • 5
    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. – xQbert Apr 06 '15 at 13:42