MySQL export command

Question

I have a MySQL db which needs to be exported to a CSV file. Google told me about a lot of examples for doing this, but all the examples are for exporting just a single table.

Could anyone please help with exporting some data in a particular way (given below)?

The database is like this:

artist

id
name

album

id
name

title

id
artist_id <- FK
album_id <- FK
name

title has FK fields which point to artist name and album name.

How can do the export so that the output csv file looks like this:

column1 (artist), column2 (album), column3 (title)
John, The Latest, First Song
John, Work in progress, Nice song
John, California, Oh SF
Luke, My Songs, One Song
Luke, My Songs, Two Song
Luke, Collected Works, Magnum Opus
Tom, My works, I song
Tom, My works, II song
Tom, My works, III song

TIA.

Sorry about not mentioning the tool which I would like to use. Ideally it should be mysqldump, or any other std tool which runs from console/shell script on Unix/Linux. — sphere4a
– sphere4a, Commented Jul 12, 2012 at 7:15
ok.. why don't you use a db interaction tool to do so? like SQL yog or phpmyadmin? — manurajhada
– manurajhada, Commented Jul 12, 2012 at 7:23

alds · Accepted Answer · 2012-07-12 08:26:21Z

1

You can create a VIEW and export from there.

Extending Mahesh's query:

CREATE VIEW view_example AS 
SELECT artist.name AS artist_name, album.name AS album_name , title.name AS title_name 
FROM artist,album,title 
WHERE artist.id=title.artist_id AND album.id=title.album_id;

SELECT * FROM view_example INTO OUTFILE 'example_output.csv' FIELDS TERMINATED BY ',';

I tested this and it works.

answered Jul 12, 2012 at 8:26

alds

56510 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

sphere4a Over a year ago

Yep. This is working fine. Can't thank you enough for the solution.

Ajay Kadyan · Accepted Answer · 2012-07-12 08:16:32Z

0

In this case just write a query for that and when output come then export result sheet enter image description here

answered Jul 12, 2012 at 8:16

Ajay Kadyan

1,0893 gold badges13 silver badges36 bronze badges

Collectives™ on Stack Overflow

MySQL export command

2 Answers 2

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Related