Load data infile with blob field

Question

Given I have to dump a lot amount of inserts in a short period managed to dump all records to a file and then loading them by the load data infile sentence of mysql. This was working fine but now I compress a little more the values into a blob field to make less inserts. The problem is that I cant find a way to dump the blob field into the file so when loading data inserts the correct values. I've tried different ways but no happy ending and want to avoid inserting one by one.

Has anyone knows how to do this properly?

Answer 1

Store the HEX()'d string of your blob data in the file.

For your SQL, use the SET clause. For example:

LOAD DATA INFILE 'path/to/file.txt'
INTO TABLE mytable
(column1, column2, @hexColumn3)
SET column3=UNHEX(@hexColumn3);