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*