1

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?

1
  • What do you want? "name=value"? Columns named? What client are you using -- MySQL commmandline tool / Java / PHP / etc?
    – Rick James
    Feb 24, 2021 at 21:48

1 Answer 1

0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.