MySQL Bugs: #1806: SELECT INTO OUTFILE

Bug #1806	SELECT INTO OUTFILE
Submitted:	11 Nov 2003 10:24	Modified:	27 Nov 2005 11:52
Reporter:	[ name withheld ]	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server	Severity:	S4 (Feature request)
Version:	4.1 and 4	OS:
Assigned to:		CPU Architecture:	Any

Description:
A fixed-row SELECT INTO OUTFILE as the corresponding LOAD DATA INFILE!

"If the FIELDS TERMINATED BY and FIELDS ENCLOSED BY values are both empty (''), a fixed-row (non-delimited) format is used. With fixed-row format, no delimiters are used between fields (but you can still have a line terminator). Instead, column values are written and read using the ``display'' widths of the columns. For example, if a column is declared as INT(7), values for the column are written using 7-character fields. On input, values for the column are obtained by reading 7 characters. LINES TERMINATED BY is still used to separate lines. If a line doesn't contain all fields, the rest of the fields will be set to their default values. If you don't have a line terminator, you should set this to ''. In this case the text file must contain all fields for each row. Fixed-row format also affects handling of NULL values; see below. Note that fixed-size format will not work if you are using a multi-byte character set. "
From MySQL manual 6.4.8 LOAD DATA INFILE Syntax

How to repeat:
Not repeatable... That is the problem!

Suggested fix:
>Because the LOAD DATA syntax is also used by the 
>SELECT .. OUTFILE command, it would be useful to 
>have some sort of "WITH COLUMN_NAMES" clause. 
>This would simply exporting into spreadsheets and 
>other databases. If you wanted to get radical, there 
>could also be a "WITH COLUMN_INFORMATION" 
>clause that would allow self describing text files by 
>having the first two lines describing the column 
>names and the the types, respectively.

This is something that realy is missing! To have a fixed-field format output from the SQL is realy missing since it is also more human-readable than all other output format!!!

Thank you for the feature request. 

Sorry, but I do not think that this kind of output format is really needed. Any spreadsheet application (and RDBMS) can import delimited data, and using TAB as a delimiter you get a perfectly human-readable output in most cases. Fixed format was common at the age of FORTRAN and mainframes, and I am not sure there is a real need to import data from MySQL to old mainframe now. There is a real need to be able to import fixed-format data, though. That is why this feature is implemented in LOAD DATA INFILE.

Can you, please, give an example of modern RDBMS that provide "easy way" to unload data in a fixed-width fields format?

What a pity this bug has not been resolved! Was it really that difficult to correct or was it just not worthy??

I have done some research and it seems that it is quite a simple task to export, in fixed width, from SQL server using SSIS.

My client (a major internatinal bank) insists on a fixed width file which they import into....wait for it....a MAINFRAME!!!!! Seems for me the MAINFRAME days aren't over!!