Export Table Into Files Grouping By A Column

Question

I have a MySQL table that needs to be exported into several separate files. The table should be grouped by a particular column and files should have names of the corresponding values of this column. Format is not relevant I just need a suitable technique, program, whatever. Any help would be much appreciated!

Answer 1

If it's less than 10 files or so, it's easy to manually craft a script like:

SELECT *
FROM YourTable
WHERE col1 = 'alfa'
INTO OUTFILE 'c:\result-alfa.txt' 
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' 
LINES TERMINATED BY '\n' 

If typing it out is too tedious, consider a query like this to generate the script:

SELECT concat('SELECT * FROM YourTable WHERE col1 = ''',
    col1, ''' INTO OUTFILE '''c:\result-', col1, '.txt'' ', 
    'FIELDS TERMINATED BY '','' OPTIONALLY ENCLOSED BY ''"''',
    'LINES TERMINATED BY ''\n'';')
FROM YourTable
GROUP BY col1