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.