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
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:
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;
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.
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
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