In another question I posted someone told me that there is a difference between:
@variable
and:
variable
in MySQL. He also mentioned how MSSQL has batch scope and MySQL has session scope. Can someone elaborate on this for me?
MySQL has a concept of user-defined variables.
They are loosely typed variables that may be initialized somewhere in a session and keep their value until the session ends.
They are prepended with an @
sign, like this: @var
You can initialize this variable with a SET
statement or inside a query:
SET @var = 1
SELECT @var2 := 2
When you develop a stored procedure in MySQL, you can pass the input parameters and declare the local variables:
DELIMITER //
CREATE PROCEDURE prc_test (var INT)
BEGIN
DECLARE var2 INT;
SET var2 = 1;
SELECT var2;
END;
//
DELIMITER ;
These variables are not prepended with any prefixes.
The difference between a procedure variable and a session-specific user-defined variable is that a procedure variable is reinitialized to NULL
each time the procedure is called, while the session-specific variable is not:
CREATE PROCEDURE prc_test ()
BEGIN
DECLARE var2 INT DEFAULT 1;
SET var2 = var2 + 1;
SET @var2 = @var2 + 1;
SELECT var2, @var2;
END;
SET @var2 = 1;
CALL prc_test();
var2 @var2
--- ---
2 2
CALL prc_test();
var2 @var2
--- ---
2 3
CALL prc_test();
var2 @var2
--- ---
2 4
As you can see, var2
(procedure variable) is reinitialized each time the procedure is called, while @var2
(session-specific variable) is not.
(In addition to user-defined variables, MySQL also has some predefined "system variables", which may be "global variables" such as @@global.port
or "session variables" such as @@session.sql_mode
; these "session variables" are unrelated to session-specific user-defined variables.)
In MySQL, @variable
indicates a user-defined variable. You can define your own.
SET @a = 'test';
SELECT @a;
Outside of stored programs, a variable
, without @
, is a system variable, which you cannot define yourself.
The scope of this variable is the entire session. That means that while your connection with the database exists, the variable can still be used.
This is in contrast with MSSQL, where the variable will only be available in the current batch of queries (stored procedure, script, or otherwise). It will not be available in a different batch in the same session.
SET @@a = 'test';
, cf. dev.mysql.com/doc/refman/5.1/en/set-statement.html
@@
. For example, set@@my_var=1
, set@@session.my_var=1
, and set session my_var=1
wouldn't work because my_var
is not a system variable, whereas we can do set@@big_tables=1
, set@@session.big_tables=1
, and set session big_tables=1
because big_tables
is a system variable.
var2
is a variable without a @
prefix, but it is not a system variable: it's a procedure variable. This is allowed because it's in a stored procedure (a.k.a. stored program). Outside of stored procedures, a variable without @
is a system variable.
MSSQL requires that variables within procedures be DECLAREd and folks use the @Variable
syntax (DECLARE @TEXT VARCHAR(25) = 'text'
). Also, MS allows for declares within any block in the procedure, unlike MySQL which requires all the DECLAREs at the top.
While good on the command line, I feel using the set = @variable
within stored procedures in MySQL is risky. There is no scope and variables live across scope boundaries. This is similar to variables in JavaScript being declared without the var
prefix, which are then the global namespace and create unexpected collisions and overwrites.
I am hoping that the good folks at MySQL will allow DECLARE @Variable
at various block levels within a stored procedure. Notice the @
(at sign). The @
sign prefix helps to separate variable names from table column names - as they are often the same. Of course, one can always add an "v" or "l_" prefix, but the @
sign is a handy and succinct way to have the variable name match the column you might be extracting the data from without clobbering it.
MySQL is new to stored procedures and they have done a good job for their first version. It will be a pleasure to see where they take it form here and to watch the server side aspects of the language mature.
In principle, I use UserDefinedVariables (prepended with @) within Stored Procedures. This makes life easier, especially when I need these variables in two or more Stored Procedures. Just when I need a variable only within ONE Stored Procedure, than I use a System Variable (without prepended @).
@Xybo: I don't understand why using @variables in StoredProcedures should be risky. Could you please explain "scope" and "boundaries" a little bit easier (for me as a newbe)?
@@GLOBAL
variables are even more "global" and insidious. They cross sessions! @variables
have "session scope", so at least they they stay confined in that manner. In any normal language that is what you call "global" scope however (when they cross functions etc). The MySQL concept of "global" should perhaps be called "universal", in that it extends beyond the boundaries of the process running it. A "global" cannot normally do that in a standard language, as processes do not share memory space. This stems from the persistent (vs volatile) tendency of SQL.
@variable
is very useful if calling stored procedures from an application written in Java , Python etc. There are ocassions where variable values are created in the first call and needed in functions of subsequent calls.
Side-note on PL/SQL (Oracle)
The advantage can be seen in Oracle PL/SQL where these variables have 3 different scopes:
Function variable for which the scope ends when function exits.
Package body variables defined at the top of package and outside all functions whose scope is the session and visibility is package.
Package variable whose variable is session and visibility is global.
My Experience in PL/SQL
I have developed an architecture in which the complete code is written in PL/SQL. These are called from a middle-ware written in Java. There are two types of middle-ware. One to cater calls from a client which is also written in Java. The other other one to cater for calls from a browser. The client facility is implemented 100 percent in JavaScript. A command set is used instead of HTML and JavaScript for writing application in PL/SQL.
I have been looking for the same facility to port the codes written in PL/SQL to another database. The nearest one I have found is Postgres. But all the variables have function scope.
Opinion towards @ in MySQL
I am happy to see that at least this @
facility is there in MySQL. I don't think Oracle will build same facility available in PL/SQL to MySQL stored procedures since it may affect the sales of Oracle database.
@variable
in context of either MS-SQL or MySQL, please!
Success story sharing
SELECT @@version;
for example. This is also a reason, why usingDELIMITER @@
is not really a good idea.@
vs not?:=
and=
, and that is that:=
works as a variable-assignment operator everywhere, while=
only works that way inSET
statements, and is a comparison operator everywhere else. SoSELECT @var = 1 + 1;
will leave @var unchanged and return a boolean (1 or 0 depending on the current value of @var), whileSELECT @var := 1 + 1;
will change @var to 2, and return 2.