Using SQL Variables in Queries

1.15.1 Problem

You want to save a value from a query so you can refer to it in a subsequent query.

1.15.2 Solution

Use a SQL variable to store the value for later use.

1.15.3 Discussion

As of MySQL 3.23.6, you can assign a value returned by a SELECT statement to a variable, then refer to the variable later in your mysql session. This provides a way to save a result returned from one query, then refer to it later in other queries. The syntax for assigning a value to a SQL variable within a SELECT query is @var_name := value, where var_name is the variable name and value is a value that you're retrieving. The variable may be used in subsequent queries wherever an expression is allowed, such as in a WHERE clause or in an INSERT statement.

A common situation in which SQL variables come in handy is when you need to issue successive queries on multiple tables that are related by a common key value. Suppose 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, then delete records 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, then refer to the variable in the DELETE statements:[4]

[4] In MySQL 4, you can use multiple-table DELETE statements to accomplish tasks like this with a single query. See Chapter 12 for examples.

mysql> SELECT @id := cust_id FROM customers WHERE cust_id=' customer name '; 
mysql> DELETE FROM customers WHERE cust_id = @id; 
mysql> DELETE FROM orders 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 record 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 is discussed further in Chapter 11.

SQL 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 uses = rather than := to assign the value:

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

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

Variable names are case sensitive:

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

SQL variables can be used 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 the result is only an error message:

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

SQL variables are a MySQL-specific extension, so they will not work with other database engines.