MySQL: @variable vs. variable. Whats the difference?

Question

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?

Answer 1

In MySQL, @variable indicates a user-defined variable. You can define your own.

SET @a = 'test';
SELECT @a;

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.

Answer 2

MySQL has concept of session 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 variable is that procedure variable is reinitialized to NULL each time the procedure is called, while the session 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 variable) is not.