MySQL: Export all rows with the same column data into same file

Question

Say you have a large table with thousands of rows and 3 columns, looking something like this:

Name               City               Birthyear

Egon Spengler       New York           1957  
Mac Taylor          New York           1955  
Sarah Connor        Los Angeles        1959  
Jean-Luc Picard     La Barre           2305  
Ellen Ripley        Nostromo           2092  
James T. Kirk       Riverside          2233  
Henry Jones         Chicago            1899  
Jason Bourne        Paris              1971

...... and so on. Is it possible to sort people by City into separate files? For example, if you have 100 or more cities in the table, is there a query that would create 100 or more files with people names and birth years in it? For a smaller number of cities one could use

-e "select Name, Birthyear from Table where City = New York" > NewYork.csv

and so on for each city in the table. But it's not fun to do it manually if you have hundreds of cities.

Answer 1

I think your best bet would be to write a little script that does a SELECT DISTINCT on each column and uses the results to write the querys of the form you suggest.

Answer 2

You could also put "select Name, Birthyear from Table order by City" into a csv, then use a small script (Perl or something) to split the file where the name in the city column changes. Anyway, probably impossible without some external scripting.

Answer 3

From the sick and wrong department (may contain syntax errors, this is just to give you an idea of the twisted possibilities):

SELECT concat("SELECT name, birthyear from table where city = '", 
                           city,
                          "' into outfile '",
                          city,"';") 
   FROM table

Put that in query1.sql, then:

mysql < query1.sql > query2.sql
mysql < query2.sql

Write a query that generates queries on the fly, then execute the resulting queries against the database. This definitely falls into the "quick and dirty" category.

Note also that the queries execute on the MySQL server, which could be a different box than where you're executing the MySQL client. If so, you'll need some way at getting at the resulting files. If you don't have shell access to the server, this could be a problem unless there is a network file system that the server can write to that you can read from another host.