Skip to content
Related Articles

Related Articles

Mysql | User-defined Variables

View Discussion
Improve Article
Save Article
  • Last Updated : 03 Sep, 2021
View Discussion
Improve Article
Save Article

Mysql also supports the concept of User-defined variables, which allows passing of a value from one statement to another. A user-defined variable in Mysql is written as @var_name where, var_name is the name of the variable and can consist of alphanumeric characters, ., _, and $.

  • A user-defined variable is session specific i.e variable defined by one client is not shared to other client and when the session ends these variables are automatically expired.
  • These variables are not case-sensitive. So, @mark or @Mark both refer to same value.
  • Maximum length of variables can be 64 characters.
  • Variable name can include other characters like- {!, #, ^, -, ..} in its name, if they are quoted. For ex- @’var@1′ or @”var^2″ or @`var3`.
  • These variables can’t be declared, they are only initialized i.e at time of declaration they should be assigned a value.
  • An undeclared variable can also be accessed in a SQL statement but their values is set as NULL.
  • These variables can take values from the following set of datatypes- { integer, floating-point, decimal, binary, nonbinary string or NULL value.


SET @var_name = expression 


1. Assigning value to a variable using SET command.

mysql>SET @var1 = 2+6;
mysql>SET @var2 := @var1-2;

Values of these variables can be displayed by referring them in SELECT statement-

mysql>SELECT @var1, @var2;


| @var1 | @var2 |
|   8   |   6   |

2. Accessing a undeclared variable

mysql>SELECT @var3;


| @var3 | 
|  NULL |  

Here, variable @var3 is undeclared so its default value is NULL.

3. Assigning value to a variable without using SET.

mysql>SELECT @var3 := 4;


| @var3:=4 | 
|    4     |  

In the above example- the variable @var3 should be assigned value using only := not =, the latter is treated as comparison in non-SET statement. Like-

mysql>SELECT @var4 = 5;


| @var4=5  | 
|   NULL   |  

How these variables are used for storing values, that are used in future.

Consider, the following Student table-


Now, we have to find rank of these students by using user-defined variables.

For this, we initialize two variables- @rank and @prev_mark.

mysql>SET @rank=0, @prev_mark=0;


mysql>Select s_name, if (@prev_mark != mark, @rank:=@rank+1, @rank) as 'rank',
                    @prev_mark:=mark as 'marks' from student order by mark desc;

Here, variable @rank is used to store the rank of student and @prev_mark is used to store the marks of previous student marks.

Comparison between marks is made so that in case if two students are having equal marks then increment in @rank variable can be avoided.

Changes in both variables take place after student table is sorted in descending order by “mark” column.



Thus, we get the resulted student table sorted by “marks” column in descending order along with rank of students.

Note: In the above query, take care of order of column in select statement. If “marks” column is written before “rank” column then we don’t get desired output. Because every time @prev_mark is assigned the mark of current student which results evaluation @prev_mark!=mark as false. So, the rank of every student is displayed as non-incremented i.e it will remain as 0.

My Personal Notes arrow_drop_up
Recommended Articles
Page :

Start Your Coding Journey Now!