It is simple to dump select result into a file:
select * from table_name into outfile result.txt;
result.txt contains records ,but no fields in it,how can dump selected result with fields name?
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
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