Exporting Blob from MySQL database to file with only SQL
Using INTO
, and assuming you have write permission as the mysql
user in the location you wish to store the files, you can do:
SELECT id, blob INTO DUMPFILE '/tmp/path' FROM table;
Unfortunately, in MySQL it is not possible to specify the dumpfile as an expression/variable. However, you could achieve this if you wrapped it in a stored procedure and use variables.
I don't like the idea ...
drop procedure if exists dump_image;
delimiter //
create procedure dump_image()
begin
declare this_id int;
declare cur1 cursor for select imageId from image;
open cur1;
read_loop: loop
fetch cur1 into this_id;
set @query = concat('select blob_field from image where imageId=',
this_id, ' into outfile "/tmp/xyz-', this_id,'.jpg"');
prepare write_file from @query;
execute write_file;
end loop;
close cur1;
end //
delimiter ;
Despite the error
mysql> call dump_image(); ERROR 1329 (02000): No data - zero rows fetched, selected, or processed
ls -1 /tmp/xyz*