MySQL User-defined variables(UDV)

12.07.2020 — mysql — 1 min read

MySQL UDV's are similar to variables used in programs. It can hold only single value. Prefixed with @ before the variable name. You can use UDV without declaring or initializing it. If its not declared or initialized, it will have NULL

1mysql> select @abc;
2+------+
3| @abc |
4+------+
5| NULL |
6+------+
71 row in set (0.00 sec)

MySQL variable assignment

Method 1

1mysql> set @fn = 'Jenni';
2Query OK, 0 rows affected (0.04 sec)

Method 2

1mysql> select @ln:='bluto';
2+--------------+
3| @ln:='bluto' |
4+--------------+
5| bluto        |
6+--------------+
71 row in set (0.00 sec)

View the stored variable value

1mysql> select @fn, @ln;
2+-------+-------+
3| @fn   | @ln   |
4+-------+-------+
5| Jenni | bluto |
6+-------+-------+
71 row in set (0.00 sec)
8
9mysql> select * from actor where first_name = @fn;
10+----------+------------+-----------+---------------------+
11| actor_id | first_name | last_name | last_update         |
12+----------+------------+-----------+---------------------+
13|        4 | Jenni      | Lewis     | 2017-04-07 19:35:18 |
14+----------+------------+-----------+---------------------+
151 row in set (0.01 sec)

At any point in time, it can hold only one value

1mysql> select @aid:=actor_id, first_name, last_name, last_update from actor order by last_update desc limit 10;
2+----------------+------------+--------------+---------------------+
3| @aid:=actor_id | first_name | last_name    | last_update         |
4+----------------+------------+--------------+---------------------+
5|           1009 | Talulah    | Riley        | 2020-04-13 17:53:59 |
6|           1006 | Eiza       | González     | 2020-04-13 17:47:55 |
7|              4 | Jenni      | Lewis        | 2017-04-07 19:35:18 |
8|           1002 | Hilary     | SWAY         | 2017-04-03 23:31:54 |
9|           1000 | Cruz       | bluto        | 2017-04-03 22:36:24 |
10|              3 | ED         | CHASE        | 2016-12-02 00:00:00 |
11|              5 | JOHNNY     | LOLLOBRIGIDA | 2016-12-02 00:00:00 |
12|              6 | BETTE      | NICHOLSON    | 2016-12-02 00:00:00 |
13|              7 | GRACE      | MOSTEL       | 2016-12-02 00:00:00 |
14|             10 | CHRISTIAN  | GABLE        | 2016-12-02 00:00:00 |
15+----------------+------------+--------------+---------------------+
1610 rows in set (0.00 sec)
17
18mysql> select @aid;
19+------+
20| @aid |
21+------+
22|   10 |
23+------+
241 row in set (0.00 sec)

Where this could be useful ? (Usecases)

Searching for same value in multiple tables or operations.

1select 'table 1', count(*) from table1 where name = @fn
2union all
3select 'table 2', count(*) from table2 where name = @fn; 
4(or)
5delete from table1 where name = @fn;
6delete from table2 where name = @fn;

Any type of complex ranking or any counters which you would like to generate.
In the below query, @rank is getting initialized in the FROM clause like a join and incremented and SET in the SELECT clause

1mysql> select actor_id, first_name, last_name, last_update, @rank:=@rank + 1 as "rank" 
2       from actor, (select @rank:=0)b 
3       order by last_update desc limit 10;
4+----------+------------+--------------+---------------------+------+
5| actor_id | first_name | last_name    | last_update         | rank |
6+----------+------------+--------------+---------------------+------+
7|     1009 | Talulah    | Riley        | 2020-04-13 17:53:59 |    1 |
8|     1006 | Eiza       | González     | 2020-04-13 17:47:55 |    2 |
9|        4 | Jenni      | Lewis        | 2017-04-07 19:35:18 |    3 |
10|     1002 | Hilary     | SWAY         | 2017-04-03 23:31:54 |    4 |
11|     1000 | Cruz       | bluto        | 2017-04-03 22:36:24 |    5 |
12|        3 | ED         | CHASE        | 2016-12-02 00:00:00 |    6 |
13|        5 | JOHNNY     | LOLLOBRIGIDA | 2016-12-02 00:00:00 |    7 |
14|        6 | BETTE      | NICHOLSON    | 2016-12-02 00:00:00 |    8 |
15|        7 | GRACE      | MOSTEL       | 2016-12-02 00:00:00 |    9 |
16|       10 | CHRISTIAN  | GABLE        | 2016-12-02 00:00:00 |   10 |
17+----------+------------+--------------+---------------------+------+
1810 rows in set (0.00 sec)