CODEWITHSUNDEEP
phpmysql
caspersky 48
caspersky 48

Reputation: 239

mysql & select into OUTFILE - writing binary data to file

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

Upvotes: 3

Views: 4426

Answers (2)

Frank Forte
Frank Forte

Reputation: 2190

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.

Upvotes: 1

user319198
user319198

Reputation:

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.

Upvotes: 5

Related Questions