MySQL Cookbook, 2nd Edition

Using User-Defined Variables in SQL Statements

Problem

You want to save a value produced by an expression so that you can refer to it in a subsequent statement.

Solution

Use a user-defined variable to store the value for later use.

Discussion

You can assign a value returned by a SELECT statement to a user-defined variable, and then refer to the variable later in your mysql session. This provides a way to save a result returned from one statement, and then refer to it later in other statements. The syntax for assigning a value to a user variable within a SELECT statement is @ var_name := value, where var_name is the variable name, and value is a value that you’re retrieving. The variable can be used in subsequent statements wherever an expression is allowed, such as in a WHERE clause or in an INSERT statement.

A common situation in which user variables come in handy is when you need to issue successive statements on multiple tables that are related by a common key value. Suppose that you have a customers table with a cust_id column that identifies each customer, and an orders table that also has a cust_id column to indicate which customer each order is associated with. If you have a customer name and you want to delete the customer record as well as all the customer’s orders, you need to determine the proper cust_id value for that customer, and then delete rows from both the customers and orders tables that match the ID. One way to do this is to first save the ID value in a variable, and then refer to the variable in the DELETE statements:

mysql>SELECT @id := cust_id FROM customers WHERE cust_id='
               customer name
               ';
mysql> DELETE FROM orders WHERE cust_id = @id;
mysql> DELETE FROM customers WHERE cust_id = @id;

The preceding SELECT statement assigns a column value to a variable, but variables also can be assigned values from arbitrary expressions. The following statement determines the highest sum of the arms and legs columns in the limbs table and assigns it to the @max_limbs variable:

mysql>SELECT @max_limbs := MAX(arms+legs) FROM limbs;

Another use for a variable is to save the result from LAST_INSERT_ID() after creating a new row in a table that has an AUTO_INCREMENT column:

mysql>SELECT @last_id := LAST_INSERT_ID();

LAST_INSERT_ID() returns the value of the new AUTO_INCREMENT value. By saving it in a variable, you can refer to the value several times in subsequent statements, even if you issue other statements that create their own AUTO_INCREMENT values and thus change the value returned by LAST_INSERT_ID(). This technique is discussed further in Chapter 11.

User variables hold single values. If you assign a value to a variable using a statement that returns multiple rows, the value from the last row is used:

mysql>SELECT @name := thing FROM limbs WHERE legs = 0;
+----------------+
| @name := thing |
+----------------+
| squid          |
| octopus        |
| fish           |
| phonograph     |
+----------------+
mysql> SELECT @name;
+------------+
| @name      |
+------------+
| phonograph |
+------------+

If the statement returns no rows, no assignment takes place, and the variable retains its previous value. If the variable has not been used previously, that value is NULL:

mysql>SELECT @name2 := thing FROM limbs WHERE legs < 0;
Empty set (0.00 sec)
mysql> SELECT @name2;
+--------+
| @name2 |
+--------+
| NULL   |
+--------+

To set a variable explicitly to a particular value, use a SET statement. SET syntax can use either := or = to assign the value:

mysql>SET @sum = 4 + 7;
mysql> SELECT @sum;
+------+
| @sum |
+------+
|   11 |
+------+

SET also can be used to assign a SELECT result to a variable, provided that you write the SELECT as a subquery (that is, within parentheses), and it returns a single value. For example:

mysql>SET @max_limbs = (SELECT MAX(arms+legs) FROM limbs);

A given variable’s value persists until you assign it another value or until the end of your mysql session, whichever comes first.

User variable names are not case-sensitive:

mysql>SET @x = 1, @X = 2; SELECT @x, @X;
+------+------+
| @x   | @X   |
+------+------+
| 2    | 2    |
+------+------+

Note

Before MySQL 5.0, user variable names are case-sensitive.

User variables can appear only where expressions are allowed, not where constants or literal identifiers must be provided. Although it’s tempting to attempt to use variables for such things as table names, it doesn’t work. For example, you might try to generate a temporary table name using a variable as follows, but it won’t work:

mysql>SET @tbl_name = CONCAT('tbl_',FLOOR(RAND()*1000000));
mysql> CREATE TABLE @tbl_name (int_col INT);
ERROR 1064: You have an error in your SQL syntax near '@tbl_name
(int_col INT)'

User variables are a MySQL-specific extension to standard SQL. They will not work with other database engines.