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
INNER JOIN myTable2
ON myTable.Id = myTable2.Id;
# Get the column info
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.
INTO OUTFILE '/home/user/export.csv'
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
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…