Learning Mysql
-> album_id SMALLINT(4) NOT NULL DEFAULT 0
Download 4.24 Mb. View original pdf
|
- Navigate this page:
- INSERT INTO shuffle (artist_id, album_id, track_id) -> SELECT artist_id, album_id, track_id FROM -> track ORDER BY RAND) LIMIT 10;
- SELECT * FROM shuffle;
| -> 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 book’s website. 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, then track 2, and soon. 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 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. Importantly, 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 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 inset 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 Download 4.24 Mb. Share with your friends:
|