Open In App

Mysql | User-defined Variables

Improve
Improve
Improve
Like Article
Like
Save Article
Save
Share
Report issue
Report

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.

Syntax:

SET @var_name = expression 

Examples:

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;

Output:

+-------+-------+
| @var1 | @var2 |
+-------+-------+
|   8   |   6   |
+-------+-------+

2. Accessing a undeclared variable

mysql>SELECT @var3;

Output:

+-------+
| @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;

Output:

+----------+
| @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;

Output:

+----------+
| @var4=5  | 
+----------+
|   NULL   |  
+----------+

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

Consider, the following Student table-

s_id s_name mark
1 shagun 15
2 taruna 5
3 riya 15
4 palak 10
5 neha 7
6 garima 17

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;

Query:

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.

Output:

s_name rank marks
garima 1 17
shagun 2 15
riya 2 15
palak 3 10
neha 4 7
taruna 5 5

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.


Last Updated : 03 Sep, 2021
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads