Bug #3841 Select Data Into OutFile As Is

• Submitted: 21 May 2004 7:33
• Modified: 15 Dec 2005 10:39
• Reporter: Sergei Kulakov (Candidate Quality Contributor)
• Status: Closed
• Category: MySQL Server
• Severity: S4 (Feature request)
• CPU Architecture: Any

[21 May 2004 7:33] Sergei Kulakov

Description:
Whenever MySQL executes SELECT ... INTO OUTFILE it converts integers into strings. I read the file with a program and have to convert the strings back into integers. So I have 2 extra conversions. What if MySQL had a function that prevented conversion to strings? Probably it should accept a parameter specifying length in bytes. Then the integer field would be written to the file as is that is in binary form. Of course, processing binary files is a little bit more complicated but I can afford it. And then I just read 4 bytes into an integer variable, no atoi() call. 

Probably there should a way to LOAD DATA INFILE INTO TABLE using binay data as well. Then the binary format has to be specified in the statement. It would prevent string-to-binary conversion. 

How to repeat:
SELECT Id INTO OUTFILE '/Dir/file' FROM Tbl LIMIT 10 - always strings!

Suggested fix:
The point is nothing new has to be done - just skip doing what has already been done when saving data in a file (reading it from a file).

[12 Dec 2005 13:11] Valeriy Kravchuk

Thank you for a feature request. Please, try some newert version of MySQL (latest 4.1.x, for example). There are no quotes by default for integer values:

[openxs@Fedora 4.1]$ bin/mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4 to server version: 4.1.17

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+-----------------------------------+
| Tables_in_test                    |
+-----------------------------------+
| Acknowledge                       |
...
| test                              |
...
| users                             |
+-----------------------------------+
67 rows in set (0.00 sec)

mysql> desc test;
+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| num   | int(10) unsigned | YES  |     | NULL    |       |
+-------+------------------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> select * into outfile '/tmp/test.txt' from test;
Query OK, 1 row affected (0.00 sec)

mysql> exit
Bye
[openxs@Fedora 4.1]$ cat /tmp/test.txt
4294967295

So, looks like MySQL already do exectly what you wanted.

[15 Dec 2005 10:39] Sergei Kulakov

It was not about quotes, it was about format (binary, 4 bytes). But times have passed and I must admit I do not need this any more.