dump selected result with fields name

Question

It is simple to dump select result into a file:

select *  from table_name into outfile  result.txt;

The result.txt contains records ,but no fields in it,how can dump selected result with fields name?

Answer 1

You can use two steps for large tables

First get all colum nnames from your

set session group_concat_max_len = 1000000;

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

And use the collected information for the next Query instead of ColumnName1-3

SELECT 'ColumnName1', 'ColumnName2', 'ColumnName3'm, 'ColumnName4'
UNION ALL
SELECT `ColumnName1`, `ColumnName2`, `ColumnName3`, `ColumnName4`
    FROM table_name 
    INTO OUTFILE '/path/result.txt'

If the table is not that big, you can combine the both with user defined variables and prepared statements