MySQL: @variable vs. variable. What's 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; 

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.

Answer 2

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