mysql & select into OUTFILE - writing binary data to file

Question

I have a table which has column of type binary.

---------------+---------+----------+-------------+
| appid | imei             | request_timestamp | city_id | state_id | country_id  |
+-------+------------------+-------------------+---------+----------+------------+-----------+
| 45861 | 101111000110     |        1327119595 |     700 |       43 |          5      |
| 93502 | 1000011001010    |        1327119595 |     305 |      490 |          9 |      |
+-------+------------------+-------------------+---------+----------+------------+-------

SELECT * INTO OUTFILE 'test.txt' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'  LINES TERMINATED
 BY '\n'  FROM log_track_tbl_382 ,the csv file has data in the form 

45861,"101111000110\0\0\0\0",1327119595,700,43,5
93502,"1000011001010\0\0\0",1327119595,305,490,9

The binary column is not written to txt file in proper format. Can any one suggest what should be the proper mysql command to preserve binary data in the file . When i run this command from mysql prompt

Answer 1

Try to Use DUMPFILE in place of OUTFILE.

Selecting this option over OUTFILE results in the query results being written as a single line, omitting column or line terminations. This is useful when exporting binary data such as a graphic or a Word file. Keep in mind that you cannot choose OUTFILE when exporting a binary file, or the file will be corrupted.

Hote that a DUMPFILE query must target a single row; combining output from two binary files doesn’t make any sense, and an error will be returned if you attempt it.

Answer 2

To export, you can encode binary columns:

SELECT appid, to_base64(imei), request_timestamp, city_id, state_id, country_id
FROM some_table
INTO OUTFILE 'test.txt' 
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'  LINES TERMINATED BY '\n'
FROM log_track_tbl_382

Then to import, you will have to decode the binary data

LOAD DATA LOCAL INFILE 'test.txt' INTO TABLE some_table;

UPDATE some_table SET imei = from_base64(imei);

Of course, if you have bad data to begin with, you need to know exactly what is bad and how to undo the operation that made it bad.