User-Defined Variables
User-Defined Variables in are a way to store temporary values that can be used within a session. These variables are unique to a session and are not persistent across multiple sessions. They can be very useful for storing intermediate results or values that you want to reuse within a single session.
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 $.
Key Features of a User-Defined Variable
- 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.
User-Defined Variables – FAQs
What is an example of a user variable?
A user variable is a variable that you define and use in your SQL session to store temporary values. For example, if you want to store the number 10 in a variable, you can do it like this:
SET @myVar = 10;
Now, @myVar holds the value 10, and you can use it in your queries:
SELECT @myVar;
— This will return 10
What is a user-defined variable in SQL?
A user-defined variable in SQL is a variable that you, the user, create and use within a session. These variables are used to store temporary values that can be reused in subsequent SQL statements within the same session. They are not stored permanently in the database.
For example:
SET @totalSales = 1000;
Here, @totalSales is a user-defined variable that stores the value 1000.
Contact Us