6.1.4 User Variables

MySQL supports connection-specific user variables with the @variablename syntax. A variable name may consist of alphanumeric characters from the current character set and also `_', `$', and `.' . The default character set is ISO-8859-1 Latin1; this may be changed with the --default-character-set option to mysqld. See section 4.6.1 The Character Set Used for Data and Sorting. User variable names are case insensitive in versions >= 5.0, case sensitive in versions < 5.0.

Variables don't have to be initialised. They contain NULL by default and can store an integer, real, or string value. All variables for a thread are automatically freed when the thread exits.

You can set a variable with the SET syntax:

SET @variable= { integer expression | real expression | string expression }
[,@variable= ...].

You can also assign a value to a variable in statements other than SET. However, in this case the assignment operator is := rather than =, because = is reserved for comparisons in non-SET statements:

mysql> SELECT @t1:=(@t2:=1)+@t3:=4,@t1,@t2,@t3;
+----------------------+------+------+------+
| @t1:=(@t2:=1)+@t3:=4 | @t1  | @t2  | @t3  |
+----------------------+------+------+------+
|                    5 |    5 |    1 |    4 |
+----------------------+------+------+------+

User variables may be used where expressions are allowed. Note that this does not currently include contexts where a number is explicitly required, such as in the LIMIT clause of a SELECT statement, or the IGNORE number LINES clause of a LOAD DATA statement.

Note: in a SELECT statement, each expression is evaluated only when it's sent to the client. This means that in the HAVING, GROUP BY, or ORDER BY clause, you can't refer to an expression that involves variables that are set in the SELECT part. For example, the following statement will NOT work as expected:

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

The reason is that @aa will not contain the value of the current row, but the value of id for the previous accepted row.

The rule is to never assign and use the same variable in the same statement.

User Comments

Be aware that the following does NOT work:
SET @TABLENAME="db";
SELECT * FROM @TABLENAME;

Be warned that user variables also fail if they
are used in an aggregate function.

For example:

SELECT SUM(@myVal:=someDBfield * 2) AS field1,
SUM(@myVal * 2) AS field2 ...

will not return the answer that you are expecting!
Field2 will return with an incorrect value.

Would be it useful to add ability to store
lists
of values inside user variables, this could
then
be expanded within IN statements for example.





For example:



SELECT @list:=id FROM t1 WHERE ....
SELECT id FROM t2 WHERE id IN ( @list ) .....

Re: User variables in aggregate functions... I was
able to use user variables in aggregate functions if I
submitted query twice in the same session / thread.
Perhaps this isn't the most stable method to use
user variables but for the moment it seems to work.
Only time and load will tell if this method holds up
reliably.

You can get a cumulative total this way, like so:

SET @runningTotal:=0;
SELECT *,@runningTotal:=transaction_amount+@runningTotal AS running_total FROM customer_transaction

WOW, THIS IS GREAT!!! I just realized how powerful user variables are, especially when used with the IF command. Together, they basically give SQL some of the capabilities of a low-level procedural language! I'd used variables in MySQL before, but only for use on each row, not for use across multiple rows.

My mind's racing to come up with ways to apply this in my queries, but here's the first one I've come up with. We pull order data out of the database that has individual rows for each item ordered. The invoice number shows up on every row, but the client only wants it on the first line for each invoice. Till now I've had to dump the recordset into a file and process it with perl to take the duplicate invoice numbers out. But now, I can do that right in my SELECT statement:

SET @inv=0;
SELECT IF(@inv=invoice.id,'',@inv:=invoice.id), item.id, ...
FROM ...
WHERE ...
ORDER BY invoice.id, item.id;

Read the IF statement like this: IF the invoice number of the current line equals the variable value, THEN put an empty string in the field, ELSE put the invoice number in the field and set it as the new value for the variable. Note that the ORDER BY clause is important, as it assures that the rows will be processed in proper order - in this case, all the items for each invoice.

Add your own comment.