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 |