Mysql – dump selected result with fields name

dumpMySQL

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?

Best Answer

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

Related Question