Add column headers to a MySQL Outfile
Unfortunately the MySQL SELECT INTO OUTFILE command doesn’t support an option to output the headers of the result set you are exporting. A feature request has been open for over 2 years to sort this with no apparent activity.
A few people have had the idea of using a UNION ALL to include the headers but this becomes tedious with large queries. Tools like sqlyog are handy but can painfully slow for large result sets due to RBAR methods.
For large result sets I like to first run;
# Create temp table CREATE TEMPORARY TABLE tmp_MyTable ENGINE = MEMORY SELECT col1, col2, col3, col4 # etc FROM myTable INNER JOIN myTable2 ON myTable.Id = myTable2.Id; # Get the column info DESCRIBE tmp_MyTable;
This will output the result set we want to export into a temporary table. We can then get the column headers using the DESCRIBE command. Then I export my result set to a file using the OUTFILE command.
SELECT * INTO OUTFILE '/home/user/export.csv' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' FROM tmp_MyTable;
Now it’s roll your sleeves up time with the Linux command-line and sed.
sed -i '1iOne,Two,Three,Four,Five' /home/user/export.csv
This command will add a single header line, to the file previously exported, and should end up looking something like below…
One,Two,Three,Four,Five 1,2,3,4,5 1,2,3,4,5 1,2,3,4,5