Include headers when using SELECT INTO OUTFILE?

Question

Is it possible to include the headers somehow when using the MySQL INTO OUTFILE?

Answer 1

This will alow you to have ordered columns and/or a limit

SELECT 'ColName1', 'ColName2', 'ColName3'
UNION ALL
SELECT * from (SELECT ColName1, ColName2, ColName3
    FROM YourTable order by ColName1 limit 3) a
    INTO OUTFILE '/path/outfile';

Answer 2

I would like to add to the answer provided by Sangam Belose. Here's his code:

select ('id') as id, ('time') as time, ('unit') as unit
UNION ALL
SELECT * INTO OUTFILE 'C:/Users/User/Downloads/data.csv'
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY '\n'
  FROM sensor

However, if you have not set up your "secure_file_priv" within the variables, it may not work. For that, check the folder set on that variable by:

SHOW VARIABLES LIKE "secure_file_priv"

The output should look like this:

mysql> show variables like "%secure_file_priv%";
+------------------+------------------------------------------------+
| Variable_name    | Value                                          |
+------------------+------------------------------------------------+
| secure_file_priv | C:\ProgramData\MySQL\MySQL Server 8.0\Uploads\ |
+------------------+------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

You can either change this variable or change the query to output the file to the default path showing.

Answer 3

SELECT 'ColName1', 'ColName2', 'ColName3'
UNION ALL
SELECT ColName1, ColName2, ColName3
    FROM YourTable
    INTO OUTFILE 'c:\\datasheet.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' 

Answer 4

The solution provided by Joe Steanelli works, but making a list of columns is inconvenient when dozens or hundreds of columns are involved. Here's how to get column list of table my_table in my_schema.

-- override GROUP_CONCAT limit of 1024 characters to avoid a truncated result
set session group_concat_max_len = 1000000;

select GROUP_CONCAT(CONCAT("'",COLUMN_NAME,"'"))
from INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'my_table'
AND TABLE_SCHEMA = 'my_schema'
order BY ORDINAL_POSITION

Now you can copy & paste the resulting row as first statement in Joe's method.

Answer 5

Actually you can make it work even with an ORDER BY.

Just needs some trickery in the order by statement - we use a case statement and replace the header value with some other value that is guaranteed to sort first in the list (obviously this is dependant on the type of field and whether you are sorting ASC or DESC)

Let's say you have three fields, name (varchar), is_active (bool), date_something_happens (date), and you want to sort the second two descending:

select 
        'name'
      , 'is_active' as is_active
      , date_something_happens as 'date_something_happens'

 union all

 select name, is_active, date_something_happens

 from
    my_table

 order by
     (case is_active when 'is_active' then 0 else is_active end) desc
   , (case date when 'date' then '9999-12-30' else date end) desc