Databases Reference
In-Depth Information
mysql>
SELECT @artist:=artist_name FROM artist WHERE artist_id = 1;
+----------------------+
| @artist:=artist_name |
+----------------------+
| New Order |
+----------------------+
1 row in set (0.05 sec)
The user variable is named
artist
, and it's denoted as a user variable by the
@
character
that precedes it. The value is assigned using the
:=
operator. You can print out the
contents of the user variable with the following very short query:
mysql>
SELECT @artist;
+-----------+
| @artist |
+-----------+
| New Order |
+-----------+
1 row in set (0.00 sec)
You can explicitly set a variable using the
SET
statement without a
SELECT
. Suppose you
want to initialize a counter to 0:
mysql>
SET @counter := 0;
Query OK, 0 rows affected (0.11 sec)
You should separate several assignments with a comma, or put each in a statement of
its own:
mysql>
SET @counter := 0, @age:=23;
Query OK, 0 rows affected (0.00 sec)
The most common use of user variables is to save a result and use it later. You'll recall
the following example from earlier in the chapter, which we used to motivate nested
queries (which are certainly a better solution for this problem). We want to find the
name of the track that was played most recently:
mysql>
SELECT MAX(played) FROM played;
+---------------------+
| max(played) |
+---------------------+
| 2006-08-15 14:33:57 |
+---------------------+
1 row in set (0.00 sec)
mysql>
SELECT track_name FROM track INNER JOIN played
-> USING (artist_id, album_id, track_id)
-> WHERE played = "2006-08-15 14:33:57";
+------------+
| track_name |
+------------+
| New Blues |
+------------+
1 row in set (0.31 sec)
