Mysql | User-defind Variables

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 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 ahould be assigned a value.
  • An undeclared variable can also be accessed in a SQL statemet 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 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, varialbe @var3 is undeclared so its default value is NULL.

  3. Assigning value to 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   |  
    +----------+
    
  4. 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.



    My Personal Notes arrow_drop_up

    Check out this Author's contributed articles.

    If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to contribute@geeksforgeeks.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.

    Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.




    Article Tags :
    Practice Tags :


    Be the First to upvote.


    Please write to us at contribute@geeksforgeeks.org to report any issue with the above content.