User Variables

MySQL has a feature that allows you to store values as temporary variables, which you can use again in a later statement. In the vast majority of cases you'd use a programming language to do this sort of thing (see Chapter 5, "Programming with MySQL"), but MySQL variables can be useful when working on the MySQL command line.

The value of a variable is set with the SET statement or in a SELECT statement with :=. To see all the sales reps with a commission higher than the average commission, you could do the following:

mysql> SELECT @avg := AVG(commission) FROM sales_rep; +-------------------------+ | @avg := AVG(commission) | +-------------------------+ |                 11.0000 | +-------------------------+ mysql> SELECT surname,first_name FROM sales_rep WHERE commission>@avg; +----------+------------+ | surname  | first_name | +----------+------------+ | Gordimer | Charlene   | +----------+------------+

The at (@) sign signifies a MySQL variable. The average commission is stored in the variable @avg, which can be accessed again at a later stage.

You can also set a variable specifically. For example, instead of repeating a complex calculation each time you require it, set the variable with the value once upfront:

mysql> SET @result = 22/7*33.23; mysql> SELECT @result; +-----------------+ | @result         | +-----------------+ | 104.43714285714 | +-----------------+

User variables can be strings, integers, or floating-point numbers. They can be set to an expression (excluding places where certain literal values are required, such as in the LIMIT clause). They cannot yet be used to replace part of the query, such as replacing the name of a table. For example:

mysql> SET @t = 'sales'; mysql> SELECT * FROM @t; ERROR 1064: You have an error in your SQL syntax near '@t' at line 1 mysql> SET @v=2; mysql> SELECT * FROM sales LIMIT 0,@v; ERROR 1064: You have an error in your SQL syntax near '@v' at line 1

User variables are set in a particular thread (or connection to a server) and cannot be accessed by another thread. They are unset when the thread is closed or the connection lost.

Run the following from the first thread, Window1:

mysql> SET @a = 1; mysql> SELECT @a; +------+ | @a   | +------+ |    1 | +------+

You won't be able to access this variable from another thread. Run the following from Window2:

mysql> SELECT @a; +------+ | @a   | +------+ | NULL | +------+

If you close the connection and reconnect from Window1, MySQL will have cleared the variable, as follows, from Window1:

mysql> exit % mysql firstdb Welcome to the MySQL monitor.  Commands end with ; or \g. Your MySQL connection id is 14 to server version: 4.0.1-alpha-max Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> SELECT @a; +------+ | @a   | +------+ | NULL | +------+

Note that in a SELECT statement, the WHERE clause is calculated first and then the field list. If no records are returned, the user variable will not be set for that statement. For example, because no records are returned from this statement, the user variable will not be set:

mysql> SELECT @a:=2 FROM sales WHERE value>10000; Empty set (0.00 sec) mysql> SELECT @a; +------+ | @a   | +------+ | NULL | +------+ 

However, if you had returned at least one record, the user variable would have been set correctly:

mysql> SELECT @a:=2 FROM sales WHERE value>2000; +-------+ | @a:=2 | +-------+ |     2 | +-------+ mysql> SELECT @a; +------+ | @a   | +------+ | 2    | +------+

Similarly, a user variable set in the field list cannot be used as a condition. The following will not work because the user variable has not been set in time for the condition:

mysql> SELECT @d:=2000,value FROM sales WHERE value>@d; Empty set (0.00 sec)

You would have had to set the variable specifically before the query, as follows:

mysql> SET @d=2000; Query OK, 0 rows affected (0.00 sec) mysql> SELECT @d,value FROM sales WHERE value>@d; +------+-------+ | @d   | value | +------+-------+ | 2000 |  3800 | +------+-------+

You can also set a variable in the WHERE clause itself. Be aware then that it will not be correctly reflected in the field list unless you reset the variable again! For example:

mysql> SELECT @e,value FROM sales WHERE value>(@e:=2000); +------+-------+ | @e   | value | +------+-------+ | NULL |  3800 | +------+-------+

To reflect this correctly, you'd have to set the variable again in the field list:

mysql> SELECT @f:=2000,value FROM sales WHERE value>(@f:=2000); +----------+-------+ | @f:=2000 | value | +----------+-------+ |     2000 |  3800 | +----------+-------+ 

This is not an elegant way of implementing user variables; instead, set them separately beforehand.

Warning 

Remember that user variables are set for the duration of the thread. You may not get the results you expect if you forget to initialize a user variable.