User-defined variables vs Local Variables in MySQL?


The user defined variable is also known as session-specific variable. It is a type of loosely typed variable which can be initialized somewhere in session and contains the value of user defined variable until session ends.

The user defined variable is prefixed with symbol @. For Example:

@anyVariableName;

There are two approaches by which you can initialize the user-defined variable. You can use SET command or using SELECT query. The first approach is as follows:

SET @anyVariableName=anyValue;

The second approach is as follows:

SELECT @anyVariableName :=anyValue;

If you do not use colon (:) in SELECT query then it evaluates it as expression. The result will either be true or false:

mysql> select @m=10;

The following is the output:

+-------+
| @m=10 |
+-------+
|     1 |
+-------+
1 row in set (0.00 sec)

Local variable can be used in stored procedure, function etc. It is used with the DECLARE keyword. No @ prefix like user-defined variable is needed.

The syntax is as follows to local variables.

DECLARE yourVariableName dataType;

NOTE: The main difference between local variables and user-defined variable is that local variable is reinitialized with NULL value each time whenever stored procedure is called while session-specific variable or user-defined variable does not reinitialized with NULL. A user-defined variable set by one user can not be seen by other user.Whatever session variable for a given user is automatically destroyed when user exits.

Here is the demo of session-specific variable and local variable with stored procedure. The query to create a stored procedure with local variable and user-defined variable is as follows:

mysql> DELIMITER //
mysql> CREATE PROCEDURE sp_LocalAndUserDefinedVariableDemo()
   -> BEGIN
   -> DECLARE localVariable int default 10;
   -> SET localVariable=localVariable+10;  
   -> SET @userVariable=@userVariable+10;
   -> SELECT localVariable;
   -> SELECT @userVariable;
   -> END;
   -> //
Query OK, 0 rows affected (0.39 sec)
mysql> DELIMITER ;

Now set the value for user-defined variable. The query is as follows:

mysql> SET @userVariable=10;
Query OK, 0 rows affected (0.00 sec)

Now call the stored procedure. In first call, the user-defined variable will be 10+10=20 while local variable will 10+10=20.

Call the stored procedure using call command:

mysql> CALL sp_LocalAndUserDefinedVariableDemo();

The following is the output:

+---------------+
| localVariable |
+---------------+
|            20 |
+---------------+
1 row in set (0.32 sec)
+---------------+
| @userVariable |
+---------------+
|            20 |
+---------------+
1 row in set (0.34 sec)
Query OK, 0 rows affected (0.36 sec)

In second call, the user-defined variable will hold value 20 and add 10 like 20+10=30 while local variable again reinitialized with 10 and add 10 like 10+10=20.

Call the stored procedure and check the sample output:

mysql> CALL sp_LocalAndUserDefinedVariableDemo();

The following is the output:

+---------------+
| localVariable |
+---------------+
|            20 |
+---------------+
1 row in set (0.00 sec)
+---------------+
| @userVariable |
+---------------+
|            30 |
+---------------+
1 row in set (0.01 sec)
Query OK, 0 rows affected (0.02 sec)

In the third call, the user-defined variable will hold value 30 and add 10 like 30+10=40 while local variable again reinitialized with 10 and add 10 like 10+10=20.

Now you can say that in every procedure call local variable is re-initialized with some value which may be NULL or some other value like in my case I have provided default value which is 10. This means it sets the local variable with value 10 in every procedure call while user defined variable does not.

Updated on: 30-Jul-2019

940 Views

Kickstart Your Career

Get certified by completing the course

Get Started
Advertisements