Exporting Blob from MySQL database to file with only SQL

Question

I have a table with image data stored in a blob field in a MySQL database. Is there a way to export those images to files on the filesystem by using only SQL? The images should be named {imageId}.jpg

I know that it is easy to do this with Java or whatever but is it possible with just a SQL script?

Solution 1

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*

Solution 2

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.