Related Documentation Download this Manual
PDF (US Ltr) - 45.1Mb
PDF (A4) - 45.2Mb
PDF (RPM) - 40.9Mb
HTML Download (TGZ) - 10.6Mb
HTML Download (Zip) - 10.6Mb
HTML Download (RPM) - 9.2Mb
Man Pages (TGZ) - 207.7Kb
Man Pages (Zip) - 309.9Kb
Info (Gzip) - 4.0Mb
Info (Zip) - 4.0Mb
Excerpts from this Manual

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

9.4 User-Defined Variables

You can store a value in a user-defined variable in one statement and 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.

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.

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.)

Hexadecimal or bit values assigned to user variables are treated as binary strings. To assign a hexadecimal or bit value as a number to a user variable, use it in numeric context. For example, add 0 or use CAST(... AS UNSIGNED):

mysql> SET @v1 = X'41';
mysql> SET @v2 = X'41'+0;
mysql> SET @v3 = CAST(X'41' AS UNSIGNED);
mysql> SELECT @v1, @v2, @v3;
| @v1  | @v2  | @v3  |
| A    |   65 |   65 |
mysql> SET @v1 = b'1000001';
mysql> SET @v2 = b'1000001'+0;
mysql> SET @v3 = CAST(b'1000001' AS UNSIGNED);
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.

Previous releases of MySQL made it possible to assign a value to a user variable in statements other than SET. This functionality is supported in MySQL 8.0 for backward compatibility but is subject to removal in a future release of MySQL.

When making an assignment in this way, you must use := as the assignment operator; = is treated as the comparison operator in statements other than SET.

The order of evaluation for expressions involving user variables is undefined. For example, there is no guarantee that SELECT @a, @a:=@a+1 evaluates @a first and then performs the assignment.

In addition, the default result type of a variable is based on its type at the beginning of the statement. This may have unintended effects if a variable holds a value of one type at the beginning of a statement in which it is also assigned a new value of a different type.

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.

HAVING, GROUP BY, and ORDER BY, when referring to a variable that is assigned a value in the select expression list do not work as expected because the expression is evaluated on the client and thus can use stale column values from a previous 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)

Query OK, 0 rows affected (0.00 sec)

See Section 13.5, “Prepared SQL Statement Syntax”, 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:

  $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";



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

User Comments
User comments in this section are, as the name implies, provided by MySQL users. The MySQL documentation team is not responsible for, nor do they endorse, any of the information provided here.
  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:

WHEN COALESCE(@grpremain,0) = 0
THEN @grpremain:=sorted.cnt-1
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):

@running:=if(@previous=t.ID,@running,0)+t.NUM as TOTAL,
@previous:=t.ID from (
count(*) as NUM
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.