Get binary file via MySQL

Just a note for getting binary file via MySQL because I just had to do it. But I cannot find a method on the internet (with google).

If a binary file is small, the easy way is using LOAD_FILE(). For example,

SELECT HEX(LOAD_FILE('c:/windows/repair/sam'));

But if a binary file is big, MySQL throws a warning "Result of load_file() was larger than max_allowed_packet (1048576) - truncated" then returns NULL to me.

Someone on MySQL forum said using "SET SESSION max_allowed_packet=16*1024*1024;" before using LOAD_FILE(). But it does not work for me. :(

After read the MySQL doc, I found a method to do it with "LOAD DATA INFILE". This command definitely needs a table to keep the data. Here is my SQL commands to load binary file into table.

use test;
CREATE TABLE files (bin_data longblob);
LOAD DATA INFILE 'c:/windows/repair/system' INTO TABLE files FIELDS TERMINATED BY 'AAAAAAAAAAA' ESCAPED BY '' LINES TERMINATED BY 'BBBBBBBBBBBBBBBB';

After these commands, the binary data will be in the "files" table without modification. :)

Note about "FIELDS TERMINATED BY" and "LINES TERMINATED BY" values. They can be any string patterns that do not exist in the binary file.