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?
add a comment up vote445down voteacceptedMySQL
has the 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 in 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 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.)