Learning Mysql

-> USING (artist_id, album_id, track_id)

Download 4.24 Mb.

View original pdf

Page	242/366
Date	04.08.2023
Size	4.24 Mb.
	#61806

1 ... 238 239 240 241 242 243 244 245 ... 366

Learning MySQL

-> USING (artist_id, album_id, track_id)
-> WHERE played = @recent;
+------------+
| track_name |
+------------+
| New Blues |
+------------+
1 row inset sec)
This can save you cutting and pasting, and it certainly helps you avoid typing errors.
Here are some guidelines on using user variables User variables are unique to a connection variables that you create can’t be seen by anyone else, and two different connections can have two different variables with the same name The variable names can be alphanumeric strings and can also include the period, underscore (
_
), and dollar (
$
) characters Variable names are case-sensitive in MySQL versions earlier than version 5, and case-insensitive from version 5 onward Any variable that isn’t initialized has the value NULL you can also manually set a variable to be NULL Variables are destroyed when a connection closes You should avoid trying to both assign a value to a variable and use the variable as part of a
SELECT
query. Two reasons for this are that the new value may not be available for use immediately in the same statement, and a variable’s type is set when it’s first assigned in a query trying to use it later as a different type in the same SQL statement can lead to unexpected results.
Let’s look at the first issue in more detail using the new variable aid. Since we haven’t used this variable before, it’s empty. Now, let’s show the artist_id for artists who have an entry in the album table. Instead of showing it directly, we’ll assign the artist_id to the aid variable. Our query will show the variable twice:
once before the assignment operation, once as part of the assignment operation,
and once afterwards:
User Variables | 265

mysql> SELECT aid, @aid:=artist.artist_id, aid FROM artist,album
-> WHERE album.artist_id=@aid;
Empty set (0.00 sec)
This returns nothing since there’s nothing in the variable to start with, the
WHERE
clause tries to look for empty artist_id values. If we modify the query to use artist.artist_id as part of the
WHERE
clause, things work as expected:
mysql> SELECT aid, @aid:=artist.artist_id, aid FROM artist,album

Download 4.24 Mb.

Share with your friends:

1 ... 238 239 240 241 242 243 244 245 ... 366