• Home   /  
  • Archive by category "1"

Select Mysql Variable Assignment

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 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+1as counter, users.*FROM(SELECT @counter:=0) v, users;

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=0ORDER BY user_id;

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=0ORDER BY user_id;

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,(SELECTyear,month,sum(amount)AS c FROM account_transactions GROUP BYyear,month) t

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;

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;

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

Tags: case sensitivity, mysql features, mysql query, procedural logic, relational logic, User defined variable, variables
Comment (RSS)  |  Trackback

Summary: in this tutorial, you will learn how to use MySQL user-defined variables in SQL statements.

Introduction to MySQL user-defined variables

Sometimes, you want to pass a value from an SQL statement to another SQL statement. To do this, you store the value in a MySQL user-defined variable in the first statement and refer to it in the subsequent statements.

To create a user-defined variable, you use the format , where the consists of alphanumeric characters. The maximum length of the user-defined variable is 64 characters as of MySQL 5.7.5

The user-defined variables are not case-sensitive. It means that the and are the same.

You can assign the user-defined variable to a certain data types such as integer, floating point, decimal, string or NULL.

A user-defined variable defined by one client is not visible by other clients. In other words, an user-defined variable is session-specific.

Note that the user-defined variables are the MySQL-specific extension to SQL standard. They may not be available in other database systems.

MySQL variable assignment

There are two ways to assign a value to a user-defined variable.

The first way is to use the statement as follows:

You can use either := or = as the assignment operator in the SET statement. For example, the statement assigns number 100 to the variable @counter.

The second way to assign a value to a variable is to use the SELECT statement. In this case, you must use the := assignment operator because, within the SELECT statement, MySQL treats the = operator as the equal operator.

After the assignment, you can use the variable in the subsequent statement where an expression is permitted e.g., in WHERE clause, INSERT or UPDATE statement.

MySQL variable examples

The following statement gets the most expensive product in the table and assigns the price to the user-defined variable @msrp:

The following statement uses the @msrp variable to query the information of the most expensive product.

Sometimes, you want to insert a row into a table, get the last insert id, and use it for inserting data into another table. In this case, you can use the user-defined variable to store the most recent id generated by an AUTO_INCREMENT column as follows.

A user-defined variable can hold a single value only. If the SELECT statement returns multiple values, the variable will take the value of the last row in the result.

In this tutorial, we have shown you how to use the MySQL variables in the SQL statements to pass data between statements within a session.

  • Was this tutorial helpful ?
  • Yes   No
SET@variable_name:=value;
SELECT@variable_name:=value;
    @msrp:=MAX(msrp)
    products;
    productCode,productName,productLine,msrp
    products
    msrp=@msrp;
SELECT@id:=LAST_INSERT_ID();
    @buyPrice:=buyprice
    products
    buyprice>95

One thought on “Select Mysql Variable Assignment

Leave a comment

L'indirizzo email non verrà pubblicato. I campi obbligatori sono contrassegnati *