MySQL User Defined Variables

When I have discovered MySQL User-defined variables at the first time, I didn’t pay much attention to them and didn’t realize their strength. At first, one can think that they are just one of the many unnoticed MySQL features. Well, I was wrong. Here, I will try to summarize their strength, though I think there is much more to explore.
 
By using User-defined variables, one can add some kind of procedural logic to the MySQL’s relational logic. They are connection specific variables, that means that they persist as long as the connection to the database persists and are specific to a connection (the variable value is not accessible via different connections). User variables can be assigned a value with a SET or SELECT statements and are accessible written as @var_name.

Before we dive into the fascinating uses and examples I would like to point some cautions with MySQL User Defined Variables:

  • Case sensitivity: Currently they are case sensitive (they are case insensitive at older versions >5.0).
  • They prevent query cache.
  • If you refer to a variable that has not been initialized, it has a value of NULL and a type of string.
  • The order of user defined evaluation is undefined.

So lets see some examples:

Example 1:
The first example is the simplest one. We would like to numerate the MySQL query result. Lets say we have a table called users and we would like to numerate some result from it:

SELECT
    @counter:=@counter+1 as counter,
    users.*
FROM
    (SELECT @counter:=0) v,
    users;

Example 2:
How can you produce only the 100th rows? Can you do it in a pure relational logic? I don’t think so. However, this is possible and easy to do with the User defined variables:
Lets say we have a table called users.

SELECT
    users.*
FROM
    (SELECT @counter:=0) v, 
    users
HAVING
    (@counter:=@counter+1)%100=0
ORDER BY
    user_id;

This query will print only the 100th users from that table. The statement (select @counter:=0) initialize the @counter variable to zero. This is not the only way to do it, we could wrote a prior statement to set the variable to zero:

SET @counter:=0;
SELECT
    users.*
FROM
    users
HAVING
    (@counter:=@counter+1)%100=0
ORDER BY
    user_id;

The statement having (@counter:=@counter+1)%2=0 has two operation: the first is to increment the variable and the second is to return true value for the 100th records.

Example 3:

It is quit simple to produce an accumulative totals with User-defined variable:

Say we have a table of bank account transactions by year and month.  To produce a bank account transaction report by months we can use:

SELECT
   t.year,
   t.month,
   @x:=@x+t.c
FROM
   (SELECT @x:=0) a,
   (SELECT
        year,month,sum(amount) AS c
    FROM
        account_transactions
    GROUP BY year,month) t

Example 4:

Numerate the MySQL query result per user (restarts the numbering every time user_id changes)

SELECT
   user_id,
   user_time,
   user_total_value,
   @x:=IF(@same_value=user_id,@x+1,1) as numerate,
   @same_value:=user_id as dummy
FROM
   users,
   (SELECT  @x:=0, @same_value:='') t
ORDER BY user_id,user_time;

Accumulative totals per user (restarts the numbering every time user_id changes)

SELECT
   user_id,
   user_time,
   user_total_value,
   @x:=IF(@same_value=user_id,@x+user_total_value,1) as numerate,
   @same_value:=user_id as dummy
FROM
   users,
   (SELECT  @x:=0, @same_value:='') t
ORDER BY user_id,user_time;

That is it for now.
I will be glad to receive more interesting examples that uses MySQL’s User Defined variables.
 

Tip: If you liked this post I am recommending reading also the following great post: Advanced MySQL user variable techniques

MySQL Quiz
29 Comments
  1. pell grant says:

    Valuable info. Lucky me I found your site by accident, I bookmarked it.

  2. Anton says:

    In the Example #3 what is t.c ?

  3. Hazan Ilan says:

    Thank you very much for pointing this out. In example #3 t.c refers to the sum(amount). I have fix it, thanks to you.
    sum(amount) AS c

  4. rajeev kumar says:

    I found this web valuable

  5. Shameer says:

    Hi..
    To get the 100th row we need not use variables. We can just use the query like “SELECT * FROM table LIMIT 100,1″

  6. Hazan Ilan says:

    You are talking about example 2. The query at example 2 is finding every 100th elements in the list (e.g. 100,200,300,…).
    In your example you will get only the first 100th element

  7. Jeffrey Doak says:

    I really like your site! I find user-defined variables to be very helpful in filling tables with test data.

  8. lee says:

    hi,
    I tried this in phpmyadmin:
    SET @t1=1, @t2=2, @t3:=4;
    SELECT @t1, @t2, @t3, @t4 := @t1+@t2+@t3;

    and get this:
    @t1 @t2 @t3 @t4 := @t1+@t2+@t3
    [BLOB – 0B] [BLOB – 0B] [BLOB – 0B] NULL

    Would someone please point out what I did wrong. Thank you.

  9. lee says:

    Clarification:
    In the above post, I did:
    SET @t1=1, @t2=2, @t3:=4;
    then
    SELECT @t1, @t2, @t3, @t4 := @t1+@t2+@t3;
    (two consecutive but separate ‘go’)
    If I did both statements in 1 ‘go’, I get the expected result:
    @t1 @t2 @t3 @t4 := @t1+@t2+@t3
    1 2 4 7
    I would very much appreciate someone telling me what’s going on.

  10. Hazan Ilan says:

    Hi Lee,
    I run your example on the MySQL shell and in SQLyog with two consecutive but separate ‘go’, and it runs great:

    mysql> SET @t1=1, @t2=2, @t3:=4;
    Query OK, 0 rows affected (0.12 sec)

    mysql> SELECT @t1, @t2, @t3, @t4 := @t1+@t2+@t3;
    +——+——+——+——————-+
    | @t1 | @t2 | @t3 | @t4 := @t1+@t2+@t3 |
    +——+——+——+——————-+
    | 1 | 2 | 4 | 7 |
    +——+——+——+——————-+
    1 row in set (0.00 sec)

    mysql>
    mysql>
    mysql> SELECT @t11, @t12, @t13, @t14 := @t11+@t12+@t13;
    +——+——+——+————————+
    | @t11 | @t12 | @t13 | @t14 := @t11+@t12+@t13 |
    +——+——+——+————————+
    | NULL | NULL | NULL | NULL |
    +——+——+——+————————+
    1 row in set (0.00 sec)

    This means that your terminal is executing each query in a separate connection.
    User-defined variables are connection specific variables (the variable value is not accessible via different connections). Because your application is executing each query in a separate connection, any refer to variable, is treated as it is not been initialized and thus returning NULL.

  11. lee says:

    Thank you, Hazan, for the explanation.

  12. asjain01 says:

    Can some one please tell me when to use @ for user defined variables and when not to use it. On dev.mysql.com I have seen examples of both.

    when I try to execute

    Declare h INT;
    set @h = select count(*) from x;

    gives an error

    also can variables be used only within a prcedure?

  13. Jaimin says:

    dude i tried your example 2 but it is not working as it should.
    my table had 30 rows. and when i executed your query it returned 30 rows and added one extra custom column from 10…300..

    i changed %10…

  14. Rich says:

    lee,

    To add to the answer Hazan provided, it’s more of a phpmyadmin ‘thing’ getting the [BLOB] response than MySQL itself. You can use type casting to get the desired results.

    asjain01,
    wrapping your select statement in brackets removes the error.

    set @h = (select count(*) from reportx);

  15. Jesse Donat says:

    Thank you very much! Used this to write a fairly epic query to sort my tags to display pretty ?

    SELECT *
    FROM (SELECT ( @counter := @counter + 1 ) & 1 AS x,
    w.*
    FROM (SELECT @counter := 0) v,
    (SELECT tag, COUNT(*)AS c
    FROM categories_tags
    INNER JOIN categories USING(categories_id)
    WHERE `status` AND `list`
    GROUP BY tag
    ORDER BY c)w)x
    ORDER BY x, IF(x, -c, c), IF(x, -CHAR_LENGTH(tag), CHAR_LENGTH(tag))

  16. Hazan Ilan says:

    I have tried it right now and it is working fine. Maybe you have a typo.

  17. Murali says:

    hi friends…i dont know how to apply this for java..

    1.so how to insert variable in mysql using java..

    for ex: int a=20

    stmt.executeUpdate(“SET @b:=a INSERT INTO product VALUES (b);”);

    it gives error…so how to insert..

    2.Similarly how to retrieve and assign data from database to int , string variable and applet label in JAVA…”

  18. David Coppit says:

    Your Example #4 is buggy in the way it uses @same_value. Quoting the MySQL docs at http://dev.mysql.com/doc/refman/5.0/en/user-variables.html:

    As a general rule, you should never assign a value to a user variable and read the value within the same statement. You might get the results you expect, but this is not guaranteed. The order of evaluation for expressions involving user variables is undefined and may change based on the elements contained within a given statement; in addition, this order is not guaranteed to be the same between releases of the MySQL Server. In SELECT @a, @a:=@a+1, …, you might think that MySQL will evaluate @a first and then do an assignment second. However, changing the statement (for example, by adding a GROUP BY, HAVING, or ORDER BY clause) may cause MySQL to select an execution plan with a different order of evaluation.

    Does anyone know a good way to fix this?

  19. Hazan Ilan says:

    There isn’t such a case in example number 4. MySQL must evaluate the “IF” statement before assigning the value to the variable.

  20. Larsen says:

    Hi,

    I want to create a user with the password from another user like this:
    SELECT PASSWORD FROM USER WHERE USER = ‘jon’ INTO @var;
    CREATE USER ‘jane’@’localhost’ IDENTIFIED BY PASSWORD @var;

    Unfortunately, this only gives me “You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘@var’ at line 1”.

    I have tried other ways but didn´t succeed. Is what I´m trying to do even possible?

    Lars

  21. Hazan Ilan says:

    There is no problem with your first MySQL query.
    However, your second one has syntax Error. From the MySQL CREATE USER Syntax page: “to assign a password, use IDENTIFIED BY with the literal plaintext password value”.
    Please try to use the solution suggested by MySQL Assigning Account Passwords page.

  22. Larsen says:

    Thanks for your reply.

    The SELECT already returns the hashed value, so if I don´t use “BY PASSWORD”, MySQL sets the hashed value itself as the password:

    SELECT PASSWORD FROM USER WHERE USER = ‘jon’ INTO @var;
    SELECT @var; -> *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B
    CREATE USER ‘jane’@’127.0.0.1’ IDENTIFIED BY ‘@var’;
    SELECT PASSWORD FROM USER WHERE USER = ‘jane’ ; -> *33EF0CD2D187BCD7DBFFDE2943960E094684DAE6

    As you can see, the hash values of the two passwords differ, but they should be the same. Not using apostrophes with @var only gives a MySQL error…

  23. Hazan Ilan says:

    Try this:
    SELECT PASSWORD FROM USER WHERE USER = ‘jon’ INTO @var;
    CREATE USER ‘jane’@’127.0.0.1′;
    SET PASSWORD FOR ‘jane’@’127.0.0.1′ = PASSWORD(@var);

    Please note: when you write ‘@var’ (with the apostrophes) it means a string with the letters ‘@’,’v’,’a’,’r’.

  24. Jamie says:

    Hazan,
    I believe David Coppit has a valid point. You are clearly assigning and reading @same_value in the same statement. Mysql makes no guarantees as to the order of evaluation of expressions containing user variables, but your algorithm is dependent upon numerate’s expression being evaluated before dummy’s expression. In all likelihood, your algorithm will produce the correct results but you cannot guarantee it.

  25. xhavick says:

    hey! I have a question

    what if I want to do:
    set @myvar1 = ‘2012-05-31′;
    set @myvar2 = mytable2.rango;

    UPDATE mytable1 INNER JOIN mytable2 ON
    mytable1.RGO = mytable2.rango
    and mytable1.tipo=’value1’
    SET mytable1.col1 = CASE mytable1.col2
    WHEN @myvar1 then @myvar2=100
    else
    …suppose something
    end;

    What i need to do is to use a var like the name of a column, is it possible?
    I think my query actually do a comparison between text “mytable2.rango” and 100, so the result is False

    Very useful page!!

  26. dennis says:

    in the second example we can write something like this:
    “SELECT * FROM table WHERE id%100=0”

  27. Hazan Ilan says:

    This is true only if you have incremental id without holes.

  28. Gerald says:

    in example # 4 i could not allow an additional dummy column so here is how to set both tracking variable in one column:

    @x:=IF(@same_value=user_id,@x+user_total_value,
    if(@same_value:=user_id,1,1)
    ) as numerate

  29. psxtekno says:

    Hi, I would like to generate only 100 consecutive numbers starting from a value stored in a table with only one record.
    It’s possible?
    i.e.
    serial.serial_n = 10000

    the output should be:
    100001
    100002

    100100

Leave a Reply