Database Reference
In-Depth Information
Here is an example that assigns a value to a user variable, then refers to that variable
later. This is a simple way to determine a value that characterizes some row in a table,
then select that particular row:
mysql> SELECT @max_limbs := MAX(arms+legs) FROM limbs;
+------------------------------+
| @max_limbs := MAX(arms+legs) |
+------------------------------+
| 100 |
+------------------------------+
mysql> SELECT * FROM limbs WHERE arms+legs = @max_limbs;
+-----------+------+------+
| thing | legs | arms |
+-----------+------+------+
| centipede | 100 | 0 |
+-----------+------+------+
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 most recent 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() . Recipe 13.10 discusses this technique further.
User variables hold single values. If a statement returns multiple rows, the value from
the last row is assigned:
mysql> SELECT @name := thing FROM limbs WHERE legs = 0;
+----------------+
| @name := thing |
+----------------+
| squid |
| 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, its value is NULL :
mysql> SELECT @name2 := thing FROM limbs WHERE legs < 0;
Empty set (0.00 sec)
mysql> SELECT @name2;
+--------+
| @name2 |
Search WWH ::




Custom Search