Learning MySQL

Writing Data into Comma-Delimited Files

You can use the SELECT INTO OUTFILE statement to write out the result of a query into a comma-separated values (CSV) file that can be opened by a spreadsheet or other program.

Let’s export the list of artists from our music database into a CSV file. The query used to list all the artists is shown below:

mysql> USE music;
Database changed

mysql> SELECT artist_name, album_name FROM
    -> artist, album WHERE artist.artist_id=album.artist_id;
+---------------------------+------------------------------------------+
| artist_name               | album_name                               |
+---------------------------+------------------------------------------+
| New Order                 | Retro - John McCready FAN                |
| New Order                 | Substance (Disc 2)                       |
| New Order                 | Retro - Miranda Sawyer POP               |
| New Order                 | Retro - New Order / Bobby Gillespie LIVE |
| New Order                 | Power, Corruption & Lies                 |
| New Order                 | Substance 1987 (Disc 1)                  |
| New Order                 | Brotherhood                              |
| Nick Cave & The Bad Seeds | Let Love In                              |
| Miles Davis               | Live Around The World                    |
| Miles Davis               | In A Silent Way                          |
| The Rolling Stones        | Exile On Main Street                     |
| The Stone Roses           | Second Coming                            |
| Kylie Minogue             | Light Years                              |
+---------------------------+------------------------------------------+
13 rows in set (0.10 sec)

We can change this SELECT query slightly to write this data into an output file as comma-separated values:

mysql> SELECT artist_name, album_name FROM
    -> artist, album WHERE artist.artist_id=album.artist_id
 -> INTO OUTFILE '/tmp/artists_and_albums.csv' ...