Databases Reference
In-Depth Information
mysql>
CREATE TABLE shuffle (
-> artist_id SMALLINT(5) NOT NULL DEFAULT 0,
-> album_id SMALLINT(4) NOT NULL DEFAULT 0,
-> track_id SMALLINT(3) NOT NULL DEFAULT 0,
-> sequence_id SMALLINT(3) AUTO_INCREMENT NOT NULL,
-> PRIMARY KEY (sequence_id));
Query OK, 0 rows affected (0.01 sec)
You can download these instructions from the the file
shuffle.sql
on the topic's web
site. This table stores the details of the track, allowing you to find the artist, album,
and track names using simple queries on the other tables. It also stores a
sequence_id
,
which is a unique number that enumerates where the track is in your playlist. When
you first start using the shuffle feature, you'll listen to the track with a
sequence_id
of
1, then track 2, and so on. When we get to track 999, we can have our application reset
the counter and table so it starts again at 1. Our reasoning is that after you've heard
999 tracks, it doesn't matter if you start hearing the same ones again. You can see that
we're using the MySQL
auto_increment
feature to allocate the
sequence_id
values.
Now we need to fill up our new
shuffle
table with a random selection of tracks. Im-
portantly, we're going to do the
SELECT
and
INSERT
together in one statement. Here we
go:
mysql>
INSERT INTO shuffle (artist_id, album_id, track_id)
-> SELECT artist_id, album_id, track_id FROM
-> track ORDER BY RAND() LIMIT 10;
Query OK, 10 rows affected (0.07 sec)
Records: 10 Duplicates: 0 Warnings: 0
Now, let's investigate what happened before we explain how this command works:
mysql>
SELECT * FROM shuffle;
+-----------+----------+----------+-------------+
| artist_id | album_id | track_id | sequence_id |
+-----------+----------+----------+-------------+
| 1 | 7 | 0 | 1 |
| 3 | 1 | 3 | 2 |
| 1 | 3 | 10 | 3 |
| 6 | 1 | 1 | 4 |
| 4 | 1 | 8 | 5 |
| 1 | 7 | 1 | 6 |
| 1 | 1 | 4 | 7 |
| 2 | 1 | 6 | 8 |
| 1 | 6 | 0 | 9 |
| 4 | 1 | 1 | 10 |
+-----------+----------+----------+-------------+
10 rows in set (0.00 sec)
You can see that we got 10 tracks into our shuffle playlist, numbered with
sequence_id
values from 1 to 10. We're ready to start playing the shuffled tracks!
Let's discuss how the command works. There are two parts to the SQL statement: an
INSERT INTO
and a
SELECT
. The
INSERT INTO
statement lists the destination table into
which the data will be stored, followed by an optional list of column names in
