Advanced Querying - Learning MySQL - page 266

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
-> WHERE album.artist_id=artist.artist_id;
+------+------------------------+------+
| @aid | @aid:=artist.artist_id | @aid |
+------+------------------------+------+
| | 1 | 1 |
| 1 | 1 | 1 |
| 1 | 1 | 1 |
| 1 | 1 | 1 |
| 1 | 1 | 1 |
| 1 | 1 | 1 |
| 1 | 1 | 1 |
| 1 | 2 | 2 |
| 2 | 3 | 3 |
| 3 | 3 | 3 |
| 3 | 4 | 4 |
| 4 | 5 | 5 |
| 5 | 6 | 6 |
+------+------------------------+------+
13 rows in set (0.01 sec)
Now that @aid isn't empty, the initial query will produce some results:
mysql> SELECT @aid, @aid:=artist.artist_id, @aid FROM artist,album
-> WHERE album.artist_id=@aid;
+------+------------------------+------+
| @aid | @aid:=artist.artist_id | @aid |
+------+------------------------+------+
| 6 | 1 | 1 |
| 1 | 1 | 1 |
| 1 | 2 | 2 |
+------+------------------------+------+
3 rows in set (0.01 sec)
It's best to avoid such circumstances where the behavior is not guaranteed and is
hence unpredictable.
Transactions and Locking
When a database is concurrently accessed by several users, you have to consider how
you may be affected if other users change the data that you're accessing, and how
changes you make may affect other users. For example, you might get the wrong value
for the total sales so far this year if new sales are being added to the database while
you're adding up the sales figures.