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-defined variables are connection-specific. That is, a user variable defined by one client cannot be seen or used by other clients. All variables for a given client connection are automatically freed when that client exits.
User variables are written as
@
, where the
variable name var_name
var_name
consists of
alphanumeric characters from the current character set,
“.
”,
“_
”, 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`
).
The default character set is latin1
(cp1252
West European). This can be changed with the
--character-set-server
option to
mysqld. See
Section 9.5, “Character Set Configuration”.
User variable names are not case sensitive in MySQL 5.0 and up.
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 =
is
treated as a 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 allowable types is
converted to an allowable type. For example, a value having a
temporal or spatial data type is converted to a binary string.
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
allowed. 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
clause of a N
LINESLOAD DATA
statement.
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 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.
Another issue with assigning a value to a variable and reading the value within the same 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 that if you are constructing a string for use as a prepared statement to be executed 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 12.6, “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
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;
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;
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.
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.
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.
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;
to select every 100th row
set @counter=0;
select *, @counter rownum from tbl having (@counter:=@counter+1)%100=0 order by id;
How to fill column with unique random values:
set @a:=0;
update my_table set rand_column=@a:=@a+1 order by rand();
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%"
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.
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
Thank you all.
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.
Add your own comment.