Related Documentation Download this Manual
PDF (US Ltr) - 35.4Mb
PDF (A4) - 35.6Mb
PDF (RPM) - 33.8Mb
EPUB - 8.5Mb
HTML Download (TGZ) - 8.3Mb
HTML Download (Zip) - 8.4Mb
HTML Download (RPM) - 7.2Mb
Eclipse Doc Plugin (TGZ) - 9.2Mb
Eclipse Doc Plugin (Zip) - 11.3Mb
Man Pages (TGZ) - 198.4Kb
Man Pages (Zip) - 302.4Kb
Info (Gzip) - 3.2Mb
Info (Zip) - 3.2Mb
Excerpts from this Manual

MySQL 5.7 Reference Manual  /  Language Structure  /  User-Defined Variables

10.4 User-Defined Variables

You can store a value in a user-defined variable in one statement and then refer to it later in another statement. This enables you to pass values from one statement to another.

User variables are written as @var_name, where the variable name var_name consists of alphanumeric characters, ., _, and $. A user variable name can contain other characters if you quote it as a string or identifier (for example, @'my-var', @"my-var", or @`my-var`).

User-defined variables are session-specific. A user variable defined by one client cannot be seen or used by other clients. (Exception: A user with access to the Performance Schema user_variables_by_thread table can see all user variables for all sessions.) All variables for a given client session are automatically freed when that client exits.

User variable names are not case sensitive. Names have a maximum length of 64 characters as of MySQL 5.7.5. (Length is not constrained before that.)

One way to set a user-defined variable is by issuing a SET statement:

SET @var_name = expr [, @var_name = expr] ...

For SET, either = or := can be used as the assignment operator.

You can also assign a value to a user variable in statements other than SET. In this case, the assignment operator must be := and not = because the latter is treated as the comparison operator = in non-SET statements:

mysql> SET @t1=1, @t2=2, @t3:=4;
mysql> SELECT @t1, @t2, @t3, @t4 := @t1+@t2+@t3;
+------+------+------+--------------------+
| @t1  | @t2  | @t3  | @t4 := @t1+@t2+@t3 |
+------+------+------+--------------------+
|    1 |    2 |    4 |                  7 | 
+------+------+------+--------------------+

User variables can be assigned a value from a limited set of data types: integer, decimal, floating-point, binary or nonbinary string, or NULL value. Assignment of decimal and real values does not preserve the precision or scale of the value. A value of a type other than one of the permissible types is converted to a permissible type. For example, a value having a temporal or spatial data type is converted to a binary string. A value having the JSON data type is converted to a string with a character set of utf8mb4 and a collation of utf8mb4_bin.

If a user variable is assigned a nonbinary (character) string value, it has the same character set and collation as the string. The coercibility of user variables is implicit. (This is the same coercibility as for table column values.)

Bit values assigned to user variables are treated as binary strings. To assign a bit value as a number to a user variable, use CAST() or +0:

mysql> SET @v1 = b'1000001';
mysql> SET @v2 = CAST(b'1000001' AS UNSIGNED), @v3 = b'1000001'+0;
mysql> SELECT @v1, @v2, @v3;
+------+------+------+
| @v1  | @v2  | @v3  |
+------+------+------+
| A    |   65 |   65 |
+------+------+------+

If the value of a user variable is selected in a result set, it is returned to the client as a string.

If you refer to a variable that has not been initialized, it has a value of NULL and a type of string.

User variables may be used in most contexts where expressions are permitted. This does not currently include contexts that explicitly require a literal value, such as in the LIMIT clause of a SELECT statement, or the IGNORE N LINES clause of a LOAD DATA statement.

As a general rule, other than in SET statements, you should never assign a value to a user variable and read the value within the same statement. For example, to increment a variable, this is okay:

SET @a = @a + 1;

For other statements, such as SELECT, you might get the results you expect, but this is not guaranteed. In the following statement, you might think that MySQL will evaluate @a first and then do an assignment second:

SELECT @a, @a:=@a+1, ...;

However, the order of evaluation for expressions involving user variables is undefined.

Another issue with assigning a value to a variable and reading the value within the same non-SET statement is that the default result type of a variable is based on its type at the start of the statement. The following example illustrates this:

mysql> SET @a='test';
mysql> SELECT @a,(@a:=20) FROM tbl_name;

For this SELECT statement, MySQL reports to the client that column one is a string and converts all accesses of @a to strings, even though @a is set to a number for the second row. After the SELECT statement executes, @a is regarded as a number for the next statement.

To avoid problems with this behavior, either do not assign a value to and read the value of the same variable within a single statement, or else set the variable to 0, 0.0, or '' to define its type before you use it.

In a SELECT statement, each select expression is evaluated only when sent to the client. This means that in a HAVING, GROUP BY, or ORDER BY clause, referring to a variable that is assigned a value in the select expression list does not work as expected:

mysql> SELECT (@aa:=id) AS a, (@aa+3) AS b FROM tbl_name HAVING b=5;

The reference to b in the HAVING clause refers to an alias for an expression in the select list that uses @aa. This does not work as expected: @aa contains the value of id from the previous selected row, not from the current row.

User variables are intended to provide data values. They cannot be used directly in an SQL statement as an identifier or as part of an identifier, such as in contexts where a table or database name is expected, or as a reserved word such as SELECT. This is true even if the variable is quoted, as shown in the following example:

mysql> SELECT c1 FROM t;
+----+
| c1 |
+----+
|  0 |
+----+
|  1 |
+----+
2 rows in set (0.00 sec)

mysql> SET @col = "c1";
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @col FROM t;
+------+
| @col |
+------+
| c1   |
+------+
1 row in set (0.00 sec)

mysql> SELECT `@col` FROM t;
ERROR 1054 (42S22): Unknown column '@col' in 'field list'

mysql> SET @col = "`c1`";
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @col FROM t;
+------+
| @col |
+------+
| `c1` |
+------+
1 row in set (0.00 sec)

An exception to this principle that user variables cannot be used to provide identifiers, is when you are constructing a string for use as a prepared statement to execute later. In this case, user variables can be used to provide any part of the statement. The following example illustrates how this can be done:

mysql> SET @c = "c1";
Query OK, 0 rows affected (0.00 sec)

mysql> SET @s = CONCAT("SELECT ", @c, " FROM t");
Query OK, 0 rows affected (0.00 sec)

mysql> PREPARE stmt FROM @s;
Query OK, 0 rows affected (0.04 sec)
Statement prepared

mysql> EXECUTE stmt;
+----+
| c1 |
+----+
|  0 |
+----+
|  1 |
+----+
2 rows in set (0.00 sec)

mysql> DEALLOCATE PREPARE stmt;
Query OK, 0 rows affected (0.00 sec)

See Section 14.5, “SQL Syntax for Prepared Statements”, for more information.

A similar technique can be used in application programs to construct SQL statements using program variables, as shown here using PHP 5:

<?php
  $mysqli = new mysqli("localhost", "user", "pass", "test");

  if( mysqli_connect_errno() )
    die("Connection failed: %s\n", mysqli_connect_error());

  $col = "c1";

  $query = "SELECT $col FROM t";

  $result = $mysqli->query($query);

  while($row = $result->fetch_assoc())
  {
    echo "<p>" . $row["$col"] . "</p>\n";
  }

  $result->close();

  $mysqli->close();
?>

Assembling an SQL statement in this fashion is sometimes known as Dynamic SQL.


User Comments
  Posted by Joey Bartlett on April 14, 2006
These are very useful. You can use them for ranks.

Example:

SET @pos=0;
SELECT @pos:=@pos+1,name FROM players ORDER BY score DESC;
  Posted by Mark Malakanov on April 30, 2006
Sometimes it needs to exactly mimic Oracle's ROWNUM where is no possibility to initiate a counter in previous statement by SET @rownum:=0;.
It is still possible in a single SQL.

SELECT @rownum:=@rownum+1 rownum, t.*
FROM (SELECT @rownum:=0) r, mytable t;
  Posted by Martin Klang on May 26, 2006
I found this quite useful for imitating rownum without first setting the variable:
SELECT if(@a, @a:=@a+1, @a:=1)-1 as rownum
It will of course only work as expected when @a hasn't already been set.
  Posted by Marc Grue on June 24, 2006
Example of pagination approach using examples above, and an alternative to the LIMIT clause in stored procedures: http://dev.mysql.com/doc/refman/5.1/en/select.html - scroll to comment of June 24 2006.
  Posted by Nicholas Bernstein on July 7, 2006
Certainly you can use user-variables to do running totals. But doing a running total BY GROUP is a little more difficult. Here is an example

select
t.ID,
t.TIMESTAMP,
@running:=if(@previous=t.ID,@running,0)+t.NUM as TOTAL,
@previous:=t.ID from (
select
ID,
TIMESTAMP,
count(*) as NUM
from HISTORY
group by ID, TIMESTAMP
order by ID, TIMESTAMP
)
as t;

Note: You have to do this as a subquery because "order by" only orders the output, it doesn't change the order in which the rows are processed.
  Posted by Miika Heiskanen on January 9, 2009
I'm using this as an alternative to Limit in my stored procs:

SET @rownum = 0;
SET @startRow = 0;
SET @maxRows = 20;

SELECT * FROM (
SELECT @rownum:=@rownum+1 as rownum, t.*
FROM (SELECT @rownum:=0) r, MYTABLE t
) t
WHERE rownum BETWEEN @startRow and @startRow + @maxRows;
  Posted by Petr Neuman on January 4, 2008
to select every 100th row

set @counter=0;
select *, @counter rownum from tbl having (@counter:=@counter+1)%100=0 order by id;
  Posted by Jan Paces on July 2, 2008
How to fill column with unique random values:

set @a:=0;
update my_table set rand_column=@a:=@a+1 order by rand();

  Posted by Bradley Perkins on April 21, 2009
I was having trouble using a user variable in a LIKE clause. This solved the problem.

set @n := "foo";

SELECT name
FROM tbl
WHERE tbl.name LIKE CONCAT("%",@n,"%"); # not LIKE "%@n%"

  Posted by Drew Anderson on July 23, 2009
Nice job with the running totals BY GROUP, Nicholas Bernstein (see post on this page). But if you are keeping the connection open, and you only happen to have one kind of timestamp in your list, you'll keep counting up and up if you repeat the query. So either add the query:

SET @running = @previous = NULL;

after your running total query, or you can keep it within one query and NULL it out in the outermost SELECT:

select
@running:=@previous:=NULL,
f.ID,
f.TIMESTAMP,
f.TOTAL
from (
select
t.ID,
t.TIMESTAMP,
@running:=if(@previous=t.ID,@running,0)+1 as TOTAL,
@previous:=t.ID from (
select
ID,
TIMESTAMP
from HISTORY
order by ID, TIMESTAMP
)as t
)as f;

This is Nicholas Bernstein's original example of keeping a running total (on each record) of how many duplicate timestamps per id. You'll notice I also removed the COUNT and GROUP BY that creates a column of 1's, and just added a number 1 instead. The original example only really works if there are no duplicates in the ID field.
  Posted by Ilan Hazan on June 7, 2010
More elegant solution for the group totals can be:
select t. ID, t. TIMESTAMP, @total:=@total+t.c as TOTAL from (select @total:=0) a, (select ID, TIMESTAMP,count(*) as c from HISTORY group by ID, TIMESTAMP) t ;
NOTES:
1. I have used only one user-defined variable.
2. I have initialized the user-defined variable in the same query
3. There is no need for the ORDER BY clause as GROUP BY returning ordered results

You can learn more MySQL User Defined Variables examples at
http://www.mysqldiary.com/user-defined-variables/

Thank you all.

  Posted by David Park on December 31, 2009
Thanks, everyone, for the great examples! Here's another one.

create table trashme (pk int not null primary key, fk varchar(1) not null, seq int not null)
insert into trashme values (1, 'A', 1)
insert into trashme values (2, 'A', 2)
insert into trashme values (3, 'B', 2)
insert into trashme values (4, 'B', 3)
insert into trashme values (5, 'B', 5)
set @previous := null
set @counter := 0
start transaction
update trashme
set seq = case when @previous = fk then @counter := @counter + 1 else case when @previous := fk then @counter := 0 else @counter := 0 end end
order by fk, seq
select * from trashme order by fk, seq

You'll see that the sequence has been renumbered (starting at zero and closing any previous gaps) for each group.

Note the "trick" to set @previous := fk using a 2nd case statement. Interestingly, the @counter := 0 used is the one in the else clause. Also note that I couldn't nest an if function inside of an if function (v5.0.37), and that's why I used the case statement.
  Posted by Md. Mahmud Ahsan on April 29, 2010
I've created a ranking example using mysql variable. Checkout the link
http://thinkdiff.net/mysql/how-to-get-rank-using-mysql-query/
  Posted by Anton Schattenfeld on June 25, 2010
Ilan, seems your solution differs from Nicholas's one because
the numbering doesn't restart every time ID changes.
  Posted by Luc Vidal on November 12, 2010
The "trick" mentionned above by David Park can be replace by this :

set seq = case when @previous = fk then @counter := @counter + 1 else concat( left(@previous := fk, 0), @counter := 0) end
order by fk, seq

More generally, each time you want to assign user variables in an update, you can do

update table set col = concat ( left(@var1='val1', 0), left(@var2='val2', 0), .... , 'newcolvalue')

  Posted by Sanford Whiteman on March 25, 2011
Thought I'd share a little uservar-related thingy I just came up with for a peculiar use case.

So I've got TABLE stuff
(name VARCHAR(255), type INT(11))

with VALUES like
('Jimbo Jones',1)
('Blues Baby',2)
('Homburg Harry',1)
('Charlie Chaps',1)
('Ike Igneous',2)

I needed output that had all the rows grouped together by `type` (not just a GROUP BY summary) AND -- the peculiar part, related to a pagination-related thing on the client side -- needed a "countdown" indicator telling me how many more rows remained in the group after the current row.

Desired output:

Name Type Number_Remaining_in_Group
-----------------------------------------------
Jimbo Jones 1 2
Homburg Harry 1 1
Charlie Chaps 1 0
Blues Baby 2 1
Ike Igneous 2 0

So I came up with this:

SELECT *
, CASE
WHEN COALESCE(@grpremain,0) = 0
THEN @grpremain:=sorted.cnt-1
ELSE
@grpremain:=@grpremain-1
END
FROM
(
select
stuff.name
,grouped.type
,grouped.cnt
from stuff
inner join
(
select type,count(*) cnt from stuff
group by type
) grouped
on stuff.type=grouped.type
order by stuff.type
) sorted

Basically, innermost query gets the count by group, next one out does the sorting (otherwise, row order passed to the outermost one is undefined). Then the outermost query ticks down the expected remaining rows, resetting group-by-group.

There may be a more optimized way to do this -- I haven't rolled it out as yet because I have to make sure it uses the right indexes and is happy with a production-size dataset. But it does the trick on the functional level and would be a starting point for anybody with this exact need, which I couldn't find around the net. Cheers!

  Posted by Luc Vidal on October 30, 2012
Another way to set user variable in an update without changing a column, if you want to manipulate number instead of strings :
update table set col = col + (@var1 := 'val1')*0 + (@var2 := 'var2')*0 + ...
  Posted by Avto Vasadze on April 8, 2014
Example, for calculating running totals BY GROUP, Nicholas Bernstein (see post on this page):

select
t.ID,
t.TIMESTAMP,
@running:=if(@previous=t.ID,@running,0)+t.NUM as TOTAL,
@previous:=t.ID from (
select
ID,
TIMESTAMP,
count(*) as NUM
from HISTORY
group by ID, TIMESTAMP
order by ID, TIMESTAMP
)
as t;

In my opinion, contradict this general rule:

"As a general rule, other than in SET statements, you should never assign a value to a user variable and read the value within the same statement....
... Such as SELECT, you might get the results you expect, but this is not guaranteed. In the following statement, you might think that MySQL will evaluate @a first and then do an assignment second:
SELECT @a, @a:=@a+1, ...;
However, the order of evaluation for expressions involving user variables is undefined."

Because, there is no guarantee that the variable @previous first will be checked and then assigned new value t.ID

Am I wrong?

  Posted by George Petrov on February 11, 2015
You're correct Avto Vasadze. I've experienced a lot of random output (sometimes correct) using the approach you quoted.
Sign Up Login You must be logged in to post a comment.