你的位置:首页 > 数据库

[数据库]MySQL: @variable vs. variable. Whats the difference?


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


 
up vote351down votefavorite121

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 voteaccepted

MySQL 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 = 1SELECT @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   2CALL prc_test();var2 @var2---  ---2   3CALL 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.)