Search This Blog

Monday, November 12, 2018

Difference between @variable and variable in MySQL

@variable is user-defined variable which is loosely typed variable that may be initialized somewhere in a session and keep their value until the session ends.

You can initialize this variable with a SET statement or inside in a query:
SET @variable = 8
SELECT @variable := 88
(Note: := works as a variable-assignment operator everywhere, while = only works that way in SET statements, and is a comparison operator everywhere else. So SELECT @var = 1 + 1; will leave @var unchanged and return a boolean (1 or 0 depending on the current value of @var), while SELECT @var := 1 + 1; will change @var to 2, and return 2.)

Variable which is not prepended with any prefix can be used in a stored procedure to pass the input parameter and used as a local variable. 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.

Using the "set = @variable" within stored procedures 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.

Another difference between a procedure variable (variable) and a session-specific user-defined variable (@variable) is that procedure variable is reinitialized to NULL each time the procedure is called, while the 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.

There are global variables available for example SELECT @@version;