Import / Export BLOB with native mysql environment

Sometimes it is needed to load binary file into BLOB column. I know there are a lot of useful tools, but they aren't always available: perhaps you work on customer site or in exotic OS.

Basic approach is using LOAD_FILE function:

UPDATE t SET blob_col=LOAD_FILE('/tmp/picture') WHERE id=1;

Using LOAD DATA INFILE command also does this job. Usually it fails working with BLOBs because of special characters, but providing dummy string in fields terminated by and empty string for other options enclosed by '' escaped by '' lines terminated by '' does the trick. Here are example steps:

create temporary table
test.blobtable (bcolumn blob) engine=myisam;

load data infile '/tmp/image.jpg'
into table test.blobtable character set binary
fields terminated by 'this is dummy string'
enclosed by ''
escaped by ''
lines terminated by '';

well, data is in database so you can run:

update mytable, test.blobtable
set mytable.bcolumn = test.blobtable.bcolumn
where mytable.id = 1;


Now, let's verify data imported properly: use md5:

mysql> select md5(bcolumn) from mytable where id=1;
+----------------------------------+
| md5(bcolumn) |
+----------------------------------+
| aced13be21aa488bf40cf41e14c3f662 |
+----------------------------------+

shell> md5sum /tmp/image.jpg
aced13be21aa488bf40cf41e14c3f662 */tmp/image.jpg


Well done!
If BLOB is really huge and extra-copying should be avoided, I think following steps will work:

1. Insert data at the beginning of binary file your ID and 'dummy string which should not happen in binary file'.
2. use
LOAD INFILE 'file' mytable
REPLACE ...
fields terminated by 'dummy string which should not happen in binary file' ...

Steps above assume the file and mysql instance are on the same host privileges set properly. Refer documentation or mysql support if have related problems.

I tested steps above with mysql 5.0.77 and 6.0.9 and hope they will help someone. Please provide any feedback especially if the same result can be achieved easier.

BTW if you need to export BLOB in binary format use SELECT ... INTO DUMPFILE