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
NULLand 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
Valuable info. Lucky me I found your site by accident, I bookmarked it.
In the Example #3 what is t.c ?
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
I found this web valuable
Hi..
To get the 100th row we need not use variables. We can just use the query like “SELECT * FROM table LIMIT 100,1″
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
I really like your site! I find user-defined variables to be very helpful in filling tables with test data.
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.
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.
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.
Thank you, Hazan, for the explanation.
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?
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…
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);
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))
I have tried it right now and it is working fine. Maybe you have a typo.
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…”
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?
There isn’t such a case in example number 4. MySQL must evaluate the “IF” statement before assigning the value to the variable.
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
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.
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…
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’.
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.
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!!
in the second example we can write something like this:
“SELECT * FROM table WHERE id%100=0”
This is true only if you have incremental id without holes.
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
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