Learning Mysql

mysql> SELECT @artist:=artist_name FROM artist WHERE artist_id = 1;
+----------------------+
| @artist:=artist_name |
+----------------------+
| New Order |
+----------------------+
1 row inset 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 printout the contents of the user variable with the following very short query:
mysql> SELECT @artist;
+-----------+
| artist |
+-----------+
| New Order |
+-----------+
1 row inset 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 abetter 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 inset 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 inset sec)
264 | Chapter 7:
Advanced Querying

You can use a user variable to save the result for input into the following query. Here’s the same query pair rewritten using this approach:
mysql> SELECT recent := MAX(played) FROM played;
+-------------------------+
| recent := MAX(played) |
+-------------------------+
| 2006-08-15 14:33:57 |
+-------------------------+
1 row inset sec)
mysql> SELECT track_name FROM track INNER JOIN played

Download 4.24 Mb.

Share with your friends: